How to restrict to execute a single sql query using OleDbCommand

Question

I have tried to give multiple insert or update in single query like

OleDbCommand command = new OleDbCommand("INSERT INTO Employee (Id, Name, Address, Salary) VALUES (2001, 'name1', 'address1', 100000);UPDATE Employee SET Salary = 2000000 WHERE Id = 2001;");

This executes successfully.

But I don't want multiple sql queries in single execution. Is there anything like any property or something like that in C# which can be used to restrict to only one query.

I want to restrict it to single sql query in single execution.

Thanks.

Why does it matters? If you write all the SQL you know exactly what's going on. — Alejandro, Sep 04 '17 at 14:48
You cannot update multiple records separately in a single query, you will have to make an update query for each of them. You can, however, put all queries in a single transaction. — Jerodev, Sep 04 '17 at 14:49
I want a single query in single execution. This is my requirement. — Sandip D, Sep 04 '17 at 14:51
Your question is unclear to me. If you want to restrict it to only one query, then why not give your command just one query? — Salah Akbari, Sep 04 '17 at 14:51
No, there is not. If you don't want multi-statement batches, don't submit them. Restrict the form of what is submitted yourself. Your question sounds like you've got some very nasty SQL injection waiting to happen, for lack of checking on your part. — Jeroen Mostert, Sep 04 '17 at 14:51
People are having a hard time with you wanting to implement a restriction through your development environment, where you can simply implement that restriction by not issuing sql commands with multiple queries in them. Perhaps you want to tell us **why** you want to put this restriction in. Maybe that will shed some light on why you want the environment to enforce it, rather than restricting yourself. — , Sep 04 '17 at 14:56
Apropos stored procedures: `EXEC dbo.DoLotsOfStuff;` is a single query, but it can execute an arbitrary number of statements. Would you like to restrict that? What about `DECLARE @T TABLE(ID INT); INSERT @T ...; UPDATE table FROM @t`? This is lots of statements, but updates only one physical table. Should it be disallowed? For that matter, how about an `INSERT` with a `VALUES` clause to insert multiple rows? Is that OK? — Jeroen Mostert, Sep 04 '17 at 14:59

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Try something like this

Create a stringbuilder of values

StringBuilder sb = new StringBuilder();
string sep = "";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
sb.AppendFormat("value1","value2" etc);
 }  
string insertthis = sb.ToString();

Then insert to sql

using (OleDbCommand cmd = new OleDbCommand("INSERT INTO Employee (ItemName) VALUES (@ins)", con))
  {
  cmd.Parameters.AddWithValue("@ins", insertthis );
  cmd.ExecuteNonQuery();
  }
MessageBox.Show("Data added");

Update also works like same as insert

Ref

How to restrict to execute a single sql query using OleDbCommand

1 Answers1