Multiple Insert statements in one connection

Question

I need some tips on how to do this better, I am inserting multiple queries with using one connection.

I understand this is not good programming, especially with it being very prone to sql injection, I also wanted to mention it's not going to be out on the internet just run locally.

This is what I have so far..

public partial class Modify : System.Web.UI.Page { OleDbConnection connection; OleDbCommand command; public void OpenConnection2() { connection = new OleDbConnection(""); command = new OleDbCommand(); connection.Open(); } protected void btnSave_Click1(object sender, EventArgs e) { if (AcctNumList.SelectedValue == "3") { string query2 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name1TxtBox.Text.Replace("'", "''"), Amt1TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); string query3 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name2TxtBox.Text.Replace("'", "''"), Amt2TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); string query4 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name3TxtBox.Text.Replace("'", "''"), Amt3TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); OpenConnection2(); command.Connection = connection; command.CommandText = query2; int c = command.ExecuteNonQuery(); connection.Close(); } if (AcctNumList.SelectedValue == "4") { string query2 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name1TxtBox.Text.Replace("'", "''"), Amt1TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); string query3 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name2TxtBox.Text.Replace("'", "''"), Amt2TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); string query4 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name3TxtBox.Text.Replace("'", "''"), Amt3TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); string query5 = String.Format(@"INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) values ('{0}','{1}','{2}','{3}','{4}','{5}')", id, newguid, Name4TxtBox.Text.Replace("'", "''"), Amt4TxtBox.Text.Replace("'", "''"), 3, DateTime.Now.ToString()); OpenConnection2(); command.Connection = connection; command.CommandText = query2; int c = command.ExecuteNonQuery(); connection.Close(); }

Another part that is not good: DbConnections and DbCommands should be created and disposed as soon as possible (within a using(...)) -- the OpenConnection2 method should not exist. — Austin Salonen
– Austin Salonen, Commented Jul 25, 2013 at 19:47
Are you executing these queries against an MS Access Database? — Steve
– Steve, Commented Jul 25, 2013 at 19:57
@kcray instead of sending multiple calls you can combine your insert. see my answer below — Ehsan
– Ehsan, Commented Jul 25, 2013 at 20:05
Each line where you create a query is just begging to be refactored into a method — reggaeguitar
– reggaeguitar, Commented Oct 28, 2014 at 21:56

Habib · Accepted Answer · 2013-07-26 12:38:25Z

You should parameterized your query - ALWAYS, but for now you can concatenate those queries with ; and then execute them once like:

string allQueries = string.join(';', query2, query3, query4, query5); command.CommandText = allQueries; int c = command.ExecuteNonQuery();

Currently you are just executing one query. Semicolon ; marks end of statement in SQL, so combining these statements with ; will make them separate statements but they will be executed under one execution.

kcray - This is what worked for me.

 string[] arr = { query2, query3 }; string allQueries = string.Join(";", arr); command.CommandText = allQueries; int c = command.ExecuteNonQuery();

I have a strong feeling that this is against a MS Access Database. No multiple command if true
@EhsanUllah and Steve, I am not sure about it, I will give it a shot and see what happens.
@kcray, glad it did work for you, surprised why string.Join didn't work for you
@Habib that was theory now I am not sure about it, I will give it a shot and see what happens :)
Steve is right, Access does not let multiple queries being executed at once. If you try like I am doing now, you will get an error that says Characters found after end of SQL statement.

Steve · Accepted Answer · 2013-07-25 19:56:31Z

You are executing only the query2 not the query3 and query4 command text

OpenConnection2(); command.Connection = connection; command.CommandText = query2; int c = command.ExecuteNonQuery(); command.CommandText = query3; c = command.ExecuteNonQuery(); command.CommandText = query4; c = command.ExecuteNonQuery(); connection.Close();

Said this, really you should use parameters also if you don't have concerns of Sql Injection because your code will be more clear and you don't need to worry about parsing strings to replace quotes, prepare the correct string for datetime field and use the correct decimal point character for floating point values

Another optimization is through the using statement.
In this case your OpenConnection2 should return the OleDbConnection created and opened and no need to use a global connection object (Always a bad practice also with file based databases)

public OleDbConnection OpenConnection2() { OleDbConnection connection = new OleDbConnection(""); connection.Open(); return connection; }

and then in your code you will be able to use the using statement that will ensure the correct close and dispose of the connection when is no more needed

using(OleDbConnection cn = OpenConnection2()) using(OleDbCommand command = new OleDbCommand()) { command.Connection = connection; command.CommandText = query2; int c = command.ExecuteNonQuery(); command.CommandText = query3; c = command.ExecuteNonQuery(); command.CommandText = query4; c = command.ExecuteNonQuery(); } // here the connection will be closed and disposed

As a last note, if you are running these queries against an MS Access Database then you need to execute them one by one because there is no support for multistatement

Cam Bruce · Accepted Answer · 2013-07-25 19:46:49Z

UNION your SELECT statements together to insert multiple rows into the same table.

INSERT INTO dbo.Products (ID, [Name]) SELECT 1, 'Car' UNION ALL SELECT 2, 'Boat' UNION ALL SELECT 3, 'Bike'

Ehsan · Accepted Answer · 2013-07-25 20:09:37Z

It is not possible to execute multiple queries in on OledbCommand. You have 2 options here

Make a stored procedure
call them one by one.

OR As you are inserting in only one table so In your case though you can Design your query like this (just an example)

INSERT INTO ACH (rptid, tableid, name, amount, stat, create_date) SELECT 1,1, 'Value3',2,2,DateTime.Now.ToString() UNION SELECT 1,1, 'Value3',2,2,DateTime.Now.ToString() UNION SELECT 1,1, 'Value3',2,2,DateTime.Now.ToString() UNION SELECT 1,1, 'Value3',2,2,DateTime.Now.ToString()

I tried doing this but when I put in SELECT id, newguid, Name1TxtBox.Text.Replace("'","''"), Amt1TxtBox.Text, 3, DateTime.Now.ToString(); I get all kinds of errors. It doesn't like those values in there.

Collectives™ on Stack Overflow

Multiple Insert statements in one connection

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Linked

Related