2 Insert Statements One Connection

[chalupabatman]

I am in the beginning stages of writing my syntax for two insert statements in one connection (this doesn't throw any errors in the compiler, but it may crash since I haven't executed it yet). I was going to ask, before I start teaching myself bad principles/practices, is this the best way to perform 2 insert statements in one connection?

string cmd1 = "INSERT INTO tableInfo (ItemNumber,soldby) VALUES (@ItemNumber, @soldby)";
string cmd2 = "INSERT INTO saleInfo (ProgramID,StoreNumber) VALUES (@ItemNumber, @StoreNumber)";


using (SqlConnection conn = new SqlConnection(connString.SqlServerConnString))
{
	using (SqlCommand comm = new SqlCommand())
	{
		comm.Connection = conn;
		comm.CommandText = cmd1;
		comm.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm.Parameters.AddWithValue("@soldby", txtsoldby.Text);
		try 
		{ 
			conn.Open(); 
			comm.ExecuteNonQuery();
			conn.Close();
		}
		catch (SqlException e) { MessageBox.Show(e.ToString()); }
	}
	using (SqlCommand comm1 = new SqlCommand())
	{
		comm1.Connection = conn;
		comm1.CommandText = cmd2;
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm1.Parameters.AddWithValue("@StoreNumber", "18643");
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm1.Parameters.AddWithValue("@StoreNumber", "55241");
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm1.Parameters.AddWithValue("@StoreNumber", "818737);
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm1.Parameters.AddWithValue("@StoreNumber", "152423");
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		comm1.Parameters.AddWithValue("@StoreNumber", "991177");
		try
		{
			conn.Open();
			comm1.ExecuteNonQuery();
			conn.Close();
		}
		catch (SqlException e) { MessageBox.Show(e.ToString()); }
	}
}

 

[jmcilhinney]

Pretty much, except that you wouldn't open the connection, then close it, then open it again and then close it again. You would open the connection once, execute both commands and then close the connection once. There's no need to explicitly close the connection because it will be disposed and, therefore, closed at the end of the `using` block that created it.

You might also want to consider using a transaction to ensure that either both records are inserted or neither are. Perhaps you don't mind whether only one part of the operation completes successfully but I'm guessing that that's not the case.

 

[chalupabatman]

I added a check to verify if the data already exists in the table and a Transaction statement (hopefully I did that accurately as I have never attempted before). Again, no compile errors on my code, but haven't attempted to run yet. Also, I removed the opening/close of the connection with each statement I was attempting to run.

string cmd1 = "INSERT INTO tableInfo (ItemNumber,soldby) VALUES (@ItemNumber, @soldby)";
string cmd2 = "INSERT INTO saleInfo (ItemNumber,StoreNumber) VALUES (@ItemNumber, @StoreNumber)";
string checkinfo = "Select Count(*) from tableInfo where ItemNumber = @ItemNumber";
string checksaleinfo = "Select Count(*) from checksaleinfo where ItemNumber = @ItemNumber";
int count = 0;


using (SqlConnection conn = new SqlConnection(connString.SqlServerConnString))
{
	SqlTransaction sqlTran = conn.BeginTransaction();
	using (SqlCommand comm = new SqlCommand())
	{
		comm.Transaction = sqlTran;
		comm.Connection = conn;
		comm.CommandText = checkinfo;
		comm.Parameters.AddWithValue("@ItemNumber", textbox_StoreName);
		try
		{		
			if ( count <= 0)
			{
				comm.CommandText = cmd1;
				comm.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm.Parameters.AddWithValue("@soldby", txtsoldby.Text);
				comm.ExecuteNonQuery();
				sqlTran.Commit();
			}
			else {//No need to do anything}
		}
		catch (SqlException e) 
		{ 
			sqlTran.Rollback();
			MessageBox.Show(e.ToString()); 
		}
	}
	using (SqlCommand comm1 = new SqlCommand())
	{
	    comm1.Transaction = sqlTran;
		comm1.Connection = conn;
		comm1.CommandText = checksaleinfo;
		comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
		try
		{
			if (count <= 0)
			{		
				comm1.CommandText = cmd2;
				comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm1.Parameters.AddWithValue("@StoreNumber", "18643");
				comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm1.Parameters.AddWithValue("@StoreNumber", "55241");
				comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm1.Parameters.AddWithValue("@StoreNumber", "818737);
				comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm1.Parameters.AddWithValue("@StoreNumber", "152423");
				comm1.Parameters.AddWithValue("@ItemNumber", textbox_StoreName.Text);
				comm1.Parameters.AddWithValue("@StoreNumber", "991177");
				comm1.ExecuteNonQuery();
				comm1.Commit();
			}
			else { //no need to do anything }
		catch (SqlException e) 
		{ 
			sqlTran.Rollback();
			MessageBox.Show(e.ToString()); 
		}
	}
}

 

[jmcilhinney]

You can't begin a transaction on a connection that isn't open and you can't execute a command on a connection that isn't open either. Also, what would be the point of committing the transaction after executing the first command? The whole point of the transaction is to allow you to roll back the transaction if the second command fails but there's nothing to roll back if the results of the first command have already been committed. The sequence of events is:

Create connection.
Open connection.
Begin transaction.
Create first command.
Execute first command.
Create second command.
Execute second command.
Commit transaction.
Close connection.

That's assuming success. If either command fails to execute then you roll back the transaction instead of committing. That means that you put both commands inside the same `try` block because any failure on either means rolling back and not continuing.