Multiple Insert statements in one connection

问题:

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();
        }

回答1:

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();

回答2:

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

回答3:

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'

回答4:

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

1. Make a stored procedure
2. 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()