Multiple Sql Insert String And RollBack With Catch(Exception)

mahesh waghela

If I have to insert with two sql table and the place of string is as below:

// Single Record Insert
String  AA="Insert into aa(date)values(@date)";

//Multiple Record Insert with datagridview1
For(int i=0;i<datagridview1.rows.count-1;i++)
{
String BB= "insert into bb(name,amount)values(@name,@amount)";
}

If there are two different strings of sql insert as per as above by single and multiple insert on Button click Event than Is it possible to handle it on single sql command if yes than reply how?.

But as per I think it is not possible by single SqlCommand and if it is true than there are very big problem to adjust SqlTransaction Class for Rollback and Commit. with try and catch block of the EventHandller

I am really struggling to insert multiple sql statement as above at one Button1_Click Event. Suggest me proper solution or proper way , technique.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Zoran Horvat15y ago
Accepted answer
You can try something like this:

SqlTransaction tran = null;

try
{
tran = conn.BeginTransaction();

String AA="Insert into aa(date)values(@date)";
String BB= "insert into bb(name,amount)values(@name,@amount)";

SqlCommand cmdA = new SqlCommand(AA, conn);
SqlCommand cmdB = new SqlCommand(BB, conn);

cmdA.Parameters.Add("@date", SqlDbType.Date);
cmdB.Parameters.Add("@name", SqlDbType.VarChar, 100); // Change type/length
cmdB.Parameters.Add("@amount", SqlDbType.Int); // Change type

cmdA.Parameters["@date"] = DateTime.Now; // Change date

cmdA.ExecuteNonQuery();

//Multiple Record Insert with datagridview1
For(int i=0;i {
cmdB.Parameters["@name"] = "put name";
cmdB.Parameters["@amount"] = 0; // put amount;
cmdB.ExecuteNonQuery();
}

tran.Commit();

}
catch (System.Exception)
{

try
{
tran.Rollback();
}
catch
{
// Ignore errors on rollback - there's nothing you can do to recover the failed rollback
}
}

I haven't tried this code, but generally that should be it.

Zoran
Zoran Horvat15y ago
You would have to bind numerous variables then, which might become very inefficient.

Zoran
Suthish Nair15y ago
Why generating a single statement won't work?

inside for loop
BB = BB + "insert into bb (name,amount) values (@name,@amount); ";

outside loop, execute only once instead of multiple hits..
cmdB.ExecuteNonQuery();
Zoran Horvat15y ago
Mahesh,

Blocks of code have been mixed up in the previous code. I've rearranged them so try now.

Regarding data types, you should place correct data types and lengths that correspond with your database schema. I've just put some data types so that code makes sense, but that should be specialized according to your data.

Zoran
mahesh waghela15y ago
hi
Zoran,

It's not tested hence it's need to be test because there are many error like datatype not well of date instead datetime and other the technique like the sql statements should be at the block of try{} not at catch(){} block hence it's always execute the command when the system catch the error not usual.