How to limit data insertion in mysql using C#

Question

3.00/5 (1 vote)

See more:

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;

Regards
Saikrishna

Posted 14-May-16 17:58pm

Member 10376341

Updated 14-May-16 19:42pm

Add a Solution

Comments

Garth J Lancaster 15-May-16 0:23am

2 things with your query

1) `Batch` with back (or any quotes) - why the quotes ?
2) you're selecting from Batch and inserting into Batch ... ok, Im not sure thats what you really want - would you select from something like 'students' and insert into batch - your logic seems a little off on what you've indicated here. The 'limit 30' should be fine

'Well, it didnt work"

Well, we cant see your screen, read your mind, or know what you mean by 'it didnt work' unless you explain it a little clearer

Doing it from C# ? ok, but you still have to have a sql statement that produces the correct result before you do that, so getting it correct in the workbench or such is a good idea to start with

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2016-05-14T19:42:00

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

C#

string cmd = "SELECT count(field_name) FROM Batch;";

Then use MySqlCommand.ExecuteScalar()
then check if the number of rows are <30 and then send the insertion code.

C#

string cmd = "INSERT INTO `Batch` (field_name) VALUES ('What Ever');";

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.