一个连接中有多个Insert语句 [英] 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.

我知道这不是不好的编程，特别是因为它很容易进行sql注入，我还想提一提，它不会在本地运行的Internet上发布.

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

推荐答案

您应将查询参数化-始终，但现在您可以将这些查询与;连接，然后像执行一次一样:

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

当前，您正在执行一个查询.分号;标记SQL中的语句结束，因此将这些语句与;结合使用将使它们成为独立的语句，但是它们将在一次执行中执行.

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-这对我有用.

kcray - This is what worked for me.

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

这篇关于一个连接中有多个Insert语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！