C# Transaction

[Sumith Asanka]

Hi Guys

I have a problem while an error and rollback

Please check this example

if i have an error at CreateClassPayment() method, it will go and execute rollback function but it doesn't rollback the transaction at CreateAttendent() but will not go for next transactions also formSMSContents() and UPDATESMSPORTAL(),can any one tell me what is wrong with this code, each method has its own connection object also

 private void btnsave_Click(object sender, EventArgs e)
        {
            using (SqlConnection Contr = new SqlConnection(CS))
            {
                SqlTransaction Trn;
                Contr.Open();
                Trn = Contr.BeginTransaction();
                try
                {
                    CreateAttendent(txtstudentno.Text);
                    CreateClassPayment();
                    formSMSContents();
                    ParamList P = new ParamList();
                    P.UPDATESMSPORTAL(txtsmstpnumber.Text.Trim(), txtsms.Text.Trim(), DateTime.Now, "IN", "", DBCON.INSID, txtcourseid.Text, txtstudentno.Text, Convert.ToInt32(txtteacherid.Text.Trim()), 0, "", "");
                    Trn.Commit();
                    MessageBox.Show("Data Saved Success", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    Clearform();
                }
                catch (SqlException sqlex)
                {
                    MessageBox.Show(sqlex.Message);
                    Trn.Rollback();
                }
            }

        }

thanks
regards
sumith

[Ketsuekiame]

Unless you're using distributed transactions, you need to share the connection on which you create the transaction.

The better way in my opinion is to use TransactionScope outside of your SqlConnection

eg.

using(TransactionScope tScope = new TransactionScope())
{
    using(SqlConnection conn = new SqlConnection(CS))
    {
        // Do stuff   
    }

    // You could create a new connection here if you really needed
    // This will elevate the Transaction to a Distributed Transaction
    // Don't recommend it though, usually better to do all
    // your work on a single connection.

    tScope.Commit();      
}