如何使用C＃限制mysql中的数据插入 [英] How to limit data insertion in mysql using C#
问题描述
大家好，
当天的问候！
首先，我是这个编码的新手&安培;一步一步学习。
好吧，我的问题是，如何在数据库中限制特定日期或时间的30行插入。例如，我有一个名为Batch的表，每个批次应限制在一个月内30个学生（目标）。目标由管理员分配给批处理。 Hench基于那个特定月份应该只有30名学生而且不能插入休息（即> 31不能保存）。
缺乏逻辑启动有，希望有人能帮助我理解。
＃最大行插入限制。
< b>我尝试了什么：
最好的方法是C＃方面还是MySQL？
这里我厌倦了MySQL：嗯，它不起作用。
Hello All,
Greetings for the day!
Firstly, am new to this coding & learning step by step.
Well, my question is, how to limit the insertion for only 30 rows in particular date or time in database. For example, i have a table called Batch and each batch should be limited to 30 students(targets) in a month. The target is assigned by the administrator to the batch. Hench based on that there should only be 30 students in that particular month and rest cannot be inserted (i.e. >31 cannot be saved).
Am lacking logic to start with, hoping some one can help me to understand.
# Maximum row insert limit.
What I have tried:
What could be the best way to do it, is it from C# side or MySQL?
Here what i tired in MySQL: Well, it didn't work.
INSERT INTO `Batch` (field_name)
SELECT field_name
FROM Batch
LIMIT 30;
问候
Saikrishna
Regards
Saikrishna
推荐答案
我不知道MySQL可以一次性完成这项任务，但还有其他选择：
1。为表创建触发器
使用触发时间= BEFORE并触发事件= INSERT创建触发器。
在触发器内部检查是否有数字行数，如果数字> 30，则表示错误。
参见 MySQL :: MySQL 5.7参考手册:: 14.1.20 CREATE TRIGGER语法 [ ^ ]
另见 sql - MySQL触发器，以防止在某些条件下INSERT - Stack Overflow [ ^ ]
（正确的解决方案取决于版本的MySQL）
2。创建存储过程
这可能是最好的选择，因为你只做一个外部交易，你可以通知用户。
参见MySQL :: MySQL 5.7参考手册:: 14.1.16创建过程和创建函数语法 [ ^ ]
和
MySQL存储过程入门 [ ^ ]
然后从C＃代码调用存储过程。请参阅为MySql存储过程IN，OUT工作C＃代码，和INOUT参数 [ ^ ]
3。在C＃中进行多次调用吗
在这种方法中，您首先发送请求以了解有多少行
I don't know of a way in MySQL to do this in one go, but there are other options:
1. Create a trigger for the table
Create a trigger with the trigger time = BEFORE and the trigger event = INSERT.
Inside the trigger you check if the number of rows and if it the number is >30 you signal an error.
See MySQL :: MySQL 5.7 Reference Manual :: 14.1.20 CREATE TRIGGER Syntax[^]
See also sql - MySQL Trigger to prevent INSERT under certain conditions - Stack Overflow[^]
(The correct solution depends on the version of MySQL)
2. Create a stored procedure
This is probably the best option as you only do one external transaction and you can notify the user.
See MySQL :: MySQL 5.7 Reference Manual :: 14.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax[^]
and
Getting Started with MySQL Stored Procedures[^]
Then you call the stored procedure from C# code. See Working C# code for MySql Stored Procedures IN, OUT, and INOUT parameters[^]
3. Do it with multiple calls in C#
In this approach you you first send a request to get to know how many rows there are
string cmd = "SELECT count(field_name) FROM Batch;";
然后使用MySqlCommand.ExecuteScalar（）
然后检查行数是否<30然后发送插入代码。
Then use MySqlCommand.ExecuteScalar()
then check if the number of rows are <30 and then send the insertion code.
string cmd = "INSERT INTO `Batch` (field_name) VALUES ('What Ever');";
在这里，您应该使用参数化查询，如上面的CodeProject文章中所示。
但是，您的设置有点奇怪。
这样做你需要在每批次创建一个表或删除表中的所有行，然后再添加下一批。
有您考虑添加一个包含添加行的日期的列，然后检查同一个月内是否有超过30行。
这样您还将拥有所有批次的历史记录在一个表格中。
Here you should use a parameterized query like shown in the CodeProject article above.
Your setup is a bit weird, however.
Doing it this way you would need to either create one table per batch or delete all rows in your table before adding the next batch.
Have you considered to add a column that contains the date when you added the row, and then check if there are more than 30 rows within the same month.
This way you will also have a history of all batches in one table.
这篇关于如何使用C＃限制mysql中的数据插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
