Insert some data into multiple tables using C#

Question

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.

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

As Steve pointed out, the SELECT statement at the start of your code sample is potentially dangerous. If the value of Name can be set through user input then you have a SQL injection vulnerability. Please look that up. You can avoid that danger by parameterizing the query rather than using string concatenation. — Odrade, Apr 18 '13 at 21:31

score 5 · Answer 1 · answered Apr 18 '13 at 20:57

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:

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.

Steve · Accepted Answer · 2013-04-18T21:12:11.480

You are setting the commandtext of the same 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

score 2 · Answer 3 · answered Apr 18 '13 at 21:01

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

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.

score 0 · Answer 4 · answered Apr 18 '13 at 21:53

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

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

Insert some data into multiple tables using C#

4 Answers4

Linked