使用 C# 将一些数据插入到多个表中

Insert some data into multiple tables using C#

提问by moe

I am trying to insert some data into 2 tables at one time but the problem I am having with my code is... I am only able to insert one table at a time. If I comment out the first insert statement then the 2nd insert will work and vise-versa.

我试图一次将一些数据插入到 2 个表中，但我的代码遇到的问题是......我一次只能插入一个表。如果我注释掉第一个 insert 语句，那么第二个 insert 将起作用，反之亦然。

Here is my code

这是我的代码

 SqlCommand cmd = new SqlCommand("select Name from MainTable where Name= '" + Name+ "'  ", sqlcon);
 SqlDataReader dr = cmd.ExecuteReader();
 if (dr.Read())
 {
    lblmsg.Text = "We are already have  this Name" + Name;
 }

 else
 {
    dr.Close();
    sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
    sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

    sqlcmd.Parameters.Clear();
    sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
    sqlcmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = Address;
    sqlcmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = Company
    sqlcmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = Address2;

    sqlcmd.Connection = sqlcon;
    sqlcmd.ExecuteNonQuery();

    DV_NameAdd.ChangeMode(DetailsViewMode.ReadOnly);
    sqlcon.Close();
 }
 sqlcon.Close();

采纳答案by Steve

You are setting the commandtext of the same SqlCommand

您正在设置相同 SqlCommand 的命令文本

  sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
  sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

Only the last one will be executed because it has replaced the first one. Probably you need to execute the first one, clear the parameters collection, set the new text and then reexecute, or create a separate command

只有最后一个会被执行，因为它已经取代了第一个。可能你需要执行第一个，清除参数集合，设置新文本然后重新执行，或者创建一个单独的命令

  sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
  sqlcmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
  sqlcmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = Address;
  sqlCmd.ExecuteNonQuery();
  sqlCmd.Parameters.Clear();
  sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";
  sqlcmd.Parameters.Add("@Company", SqlDbType.VarChar).Value = Company
  sqlcmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = Address2;
  sqlCmd.ExecuteNonQuery();

BY the way, the first SELECT is very dangerous. You should use the parameters also for that command

顺便说一下，第一个 SELECT 非常危险。您也应该为该命令使用参数

回答by Paul Sasik

You are overwriting your original SqlCommand and that is why only one works at a time. A SqlCommand only runs one command at a time. It doesn't accept additional versions of CommandText as your code seems to expect. If you want to run a two or more commands at the same time you have to create a single, semicolon-delimited CommandText, something like this:

您正在覆盖原来的 SqlCommand，这就是为什么一次只有一个工作。SqlCommand 一次只运行一个命令。正如您的代码所期望的那样，它不接受额外版本的 CommandText。如果您想同时运行两个或多个命令，您必须创建一个以分号分隔的 CommandText，如下所示：

sqlcmd.CommandText = 
"INSERT INTO Table1(Name, Address) VALUES(@Name, @Address);" + 
"INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

Note the semicolon (;) in between the two commands. Your parameter names are unique so you should be OK there.

请注意两个命令之间的分号 (;)。您的参数名称是唯一的，因此您应该没问题。

回答by duckbrain

Sometimes you can have a semicolon between two SQL statements to execute two at once. See if changing this:

有时，您可以在两个 SQL 语句之间使用分号来同时执行两个语句。看看是否改变这个：

sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address)";
                    sqlcmd.CommandText = "INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

to this:

对此：

sqlcmd.CommandText = "INSERT INTO Table1(Name, Address) VALUES(@Name, @Address);" +
"INSERT INTO Table2(Company, Address2) VALUES(@Company, @Address2)";

works for you.

为你工作。

回答by Nicholas Carey

Another thing to consider is pushing the logic into your insertquery and eliminating a race condition, like so:

要考虑的另一件事是将逻辑推入您的insert查询并消除竞争条件，如下所示：

public bool InsertEntry( string name , string addr1 , string company , string addr2 )
{
  const string QUERY = @"
begin transaction
declare @success bit

insert dbo.table_1
select @name , @addr1
where not exists ( select *
                   from dbo.main_table
                   where name = @name
                 )

insert dbo.table_2
select @company , @addr2
where @@rowcount = 1
  and not exists ( select *
                   from dbo.main_table
                   where name = @name
                 )
set @success = case when @@ROWCOUNT > 0 then 1 else 0 end

if ( @success = 1 )
begin
  commit transaction
end
else
begin
  rollback transaction
end

select @success
" ;
  bool success = false ;
  using ( SqlConnection connection = new SqlConnection( "Server=(local);Database=sandbox;Trusted_Connection=True;"))
  using ( SqlCommand command = connection.CreateCommand())
  {
    command.CommandType = CommandType.Text;
    command.CommandText = QUERY ;

    command.Parameters.Add( "@name"    , SqlDbType.VarChar ).Value = name    ;
    command.Parameters.Add( "@addr1"   , SqlDbType.VarChar ).Value = addr1   ;
    command.Parameters.Add( "@company" , SqlDbType.VarChar ).Value = company ;
    command.Parameters.Add( "@addr2"   , SqlDbType.VarChar ).Value = addr2   ;

    object returnedValue = command.ExecuteScalar() ;
    if ( returnedValue is bool )
    {
      success = (bool) returnedValue ;
    }
  }
  return success ;
}