Multiple Sql Insert String And RollBack With Catch(Exception)

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.

You can explicitly define the transactions in your ADO.NET Code.

 private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection  db = new SqlConnection("constring");
        SqlCommand com = new SqlCommand();
        SqlCommand com2 = new SqlCommand();
        SqlTransaction tran;
        db.Open();
        tran = db.BeginTransaction();
        try
        {
            //Run all your insert statements here here
            com.CommandText = "Insert into a(Date) Values(@Date)";
            com.Connection = db;
            com.Transaction = tran;
            com.Parameters.Add("@Date", SqlDbType.DateTime);
            com.Parameters["@Date"].Value = DateTime.Now;
            com.ExecuteNonQuery();

            com2.CommandText = "Insert into bb(name,amount) values(@name, @amount)";
            com2.Connection = db;
            com2.Transaction = tran;
            com2.Parameters.Add("@name", SqlDbType.VarChar, 25);
            com2.Parameters.Add("@amount", SqlDbType.Decimal);

            for (int i = 0; i < datagrid.Rows.Count; i++)
            {
                //on each loop replace @name value and @amount value with appropiate
                //value from your row collection
                com2.Parameters["@name"].Value = datagrid.Rows[i].Cells["Name"].Value; 
                com2.Parameters["@amount"].Value = datagrid.Rows[i].Cells["Amount"].Value;
                com2.ExecuteNonQuery();
            }
            tran.Commit();
        }
        catch (SqlException sqlex)
        {
            tran.Rollback();
        }
        finally
        {
            db.Close();
        }
    }