So can you do this.. my project has many function/methods with the SQL writes in them.
If at the start of each set of rows to write to the many tables it has use one myConnection.Open(); and one myConnection.Close();?
That is like this:
if (fields[0] == "START_US_records") { myConnection.Open(); } //open the connect ... do a BEGIN TRY & BETIN TRANSACTION
else
if (fields[0] == "END_US_records") { myConnection.Close(); } //do a END TRY and ROLLBACK if error and then close the connection
else
if (fields[0] == "A") { writetableA(fields); } //record goes to table A
else
if (fields[0] == "B") { writetableB(fields); } //record goes to table B
else
if (fields[0] == "C") { writetableC(fields); } //record goes to table C
And inside each function
private void writetableA(string[] fields)
{
MSCmd01.CommandText = "INSERT INTO DBOwn.DB1.[AData](A,B,C,D) VALUES (@A,@B,@C,@D) ";
MSCmd01.Parameters.Clear();
MSCmd01.Parameters.AddWithValue("@A", AV01);
MSCmd01.Parameters.AddWithValue("@B", BV01);
MSCmd01.Parameters.AddWithValue("@C", CV01);
MSCmd01.Parameters.AddWithValue("@D", DV01);
MSCmd01.Parameters.Clear();
MSCmd01.ExecuteNonQuery();
MSCmd01.Clone();
}
private void writetableB(string[] fields)
{
MSCmd01.CommandText = "INSERT INTO DBOwn.DB1.[BData](B,D) VALUES (@B,@D) ";
MSCmd01.Parameters.Clear();
MSCmd01.Parameters.AddWithValue("@B", BV01);
MSCmd01.Parameters.AddWithValue("@D", DV01);
MSCmd01.Parameters.Clear();
MSCmd01.ExecuteNonQuery();
MSCmd01.Clone();
}
private void writetableC(string[] fields)
{
MSCmd01.CommandText = "INSERT INTO DBOwn.DB1.[CData](C) VALUES (@C) ";
MSCmd01.Parameters.Clear();
MSCmd01.Parameters.AddWithValue("@C", CV01);
MSCmd01.Parameters.Clear();
MSCmd01.ExecuteNonQuery();
MSCmd01.Clone();
}
So is that how one does the transactions and rollback with just sql commands?