Entity transaction not working

Question

I have tried entity transaction with following code, but unfortunately it is not working for transaction rollback. Please help me.

    public void AddAGMDate()
    {
        DbTransaction Transaction = null;
        using (EntityConnection DatabaseConnection = new EntityConnection("name=Insta_SAEntities"))
        {
            try
            {
                if (DatabaseConnection.State == ConnectionState.Closed)
                    DatabaseConnection.Open();//open connection
            var CurrentContext = new Insta_SAEntities(DatabaseConnection);
            Transaction = CurrentContext.Connection.BeginTransaction();//begin transaction

            TR_AGMDatesEntityObject objAGMDates = new TR_AGMDatesEntityObject();
            objAGMDates.ClientID = Convert.ToInt64(ddlClientName.SelectedValue);
            objAGMDates.AGMDate =  Convert.ToDateTime(txtAGMDate.Text.Trim());
            objAGMDates.CreatedBy = 0;
            objAGMDates.CreatedDate = Convert.ToDateTime(System.DateTime.Now);
            objAGMDates.UpdatedBy = 0;
            objAGMDates.UpdatedDate = Convert.ToDateTime(System.DateTime.Now);
            int result = AGMDatesBL.AddAGMDate(objAGMDates);

            if (true)
            {
                int n1 = 10, n2 = 0;
                int res = n1 / n2;//Error, want transaction to rollback (for taesting)
            }

            Transaction.Commit();//commit transaction

            if (result == 1)
            {

                ScriptManager.RegisterClientScriptBlock(Page, GetType(), "script", "alert('AGM Date Successfully entered ');" +
                 "window.location.href='ManageAGMDate.aspx';", true);
            }
            else if (result == 2)
            {

                txtAGMDate.Text = "";
                ScriptManager.RegisterClientScriptBlock(Page, GetType(), "script", "alert('AGM Date Already Exist!');", true);


            }
            else if (result == 0)
            {
                ScriptManager.RegisterClientScriptBlock(Page, GetType(), "script", "alert('Unable to Add AGM Date!');" +
                "window.location.href='ManageAGMDate.aspx';", true);
            }
        }
        catch (Exception ex)
        {
            Transaction.Rollback();//rollback transaction

            if (log.IsErrorEnabled) log.Error(string.Format("{0} ,Page URL={1}, Method Name={2} ,Error={3}", _LogMsgAdd, Request.Url.AbsoluteUri, MethodInfo.GetCurrentMethod().DeclaringType.FullName + "." + MethodInfo.GetCurrentMethod().Name, ex.Message.ToString()));
            Session["error"] = ex.Message.ToString();
            Response.Redirect("~/Common/Errors/ErrorPageWM.aspx?ShB=Y", false);
        }
        finally
        {

            if (DatabaseConnection != null)
            {
                if (DatabaseConnection.State == ConnectionState.Open)
                    DatabaseConnection.Close();
            }
        }

}

Answer 1

Have you tried using a System.Transactions.TransactionScope** instead? (Which is the popular and common way to handle transactions in .NET).

eg.

using (EntityConnection DatabaseConnection =         
    new EntityConnection("name=Insta_SAEntities"))
    {
    using(var transactionScope = new TransactionScope())
    {
        TR_AGMDatesEntityObject objAGMDates = new TR_AGMDatesEntityObject();
        // Set properties...

        int result = AGMDatesBL.AddAGMDate(objAGMDates);

        // I have no idea what this logic does ... but I don't care, cause i'm just
        // copying what u wrote.
        if (true)
        {
            int n1 = 10, n2 = 0;
            int res = n1 / n2;//Error, want transaction to rollback (for taesting)
        }
        else
        {
            transactionScope.Commit();
        }
    }

    // The rest of your logic now happens here....

Ok.. so if u do NOT do a Commit() inside the TransactionScope() block, then it will automatically do a RollBack(); Otherwise, you can force a rollback with transactionScope.RollBack(); .. like

if (true)
{
    int n1 = 10, n2 = 0;
    int res = n1 / n2;
    transactionScope.RollBack();
}
else
{
    transactionScope.Commit();
}

HTH.

**** Trap for Young Players**: This transaction stuff code is found in the System.Transactions.dll .. so make sure you add that assembly.