CODEWITHSUNDEEP
c#asp.net
moe
moe

Reputation: 5249

Insert some data into multiple tables using C#

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

Upvotes: 4

Views: 24473

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74187

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

Upvotes: 0

Steve
Steve

Reputation: 216253

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

Upvotes: 5

duckbrain
duckbrain

Reputation: 1279

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.

Upvotes: 2

Paul Sasik
Paul Sasik

Reputation: 81429

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.

Upvotes: 5

Related Questions