Entity Framework with Transaction Commit and Rollback using C# and VB.Net in ASP.Net

Last Reply 14 days ago By dharmendr

Posted 14 days ago

hello,

i have two tables and on button click i am adding insert data in both together.

i want insert should run in both if any out of one fails it should roll back and show error that in this table there is error coming

please advice

poduct_Brands productbrands = new Product_Brands();

productbrands.ProductGUID = id;
productbrands.BrandId = int.Parse(product_info_uc.ProductBasic_txtbrands);
productbrands.InsertDate = DateTime.Now;
productbrands.UserName = this.Page.User.Identity.Name;
db.Product_Brands.Add(productbrands);
db.SaveChanges();

Product_Category productcategory = new Product_Category();

productcategory.ProductGUID = id;
productcategory.Category_Id = int.Parse(product_info_uc.ProductBasic_txtcategories);
productcategory.InsertDate = DateTime.Now;
productcategory.UserName = this.Page.User.Identity.Name;
db.Product_Category.Add(productcategory);
db.SaveChanges();
You are viewing reply posted by: dharmendr 14 days ago.
Posted 14 days ago Modified on 8 days ago

Hi nauna,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<table>
    <tr>
        <td>Name</td>
        <td><asp:TextBox ID="txtName" runat="server" /></td>
    </tr>
    <tr>
        <td>Country</td>
        <td><asp:TextBox ID="txtCountry" runat="server" /></td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:Button Text="Save" runat="server" OnClick="Save" />
        </td>
    </tr>
</table>

Code

C#

protected void Save(object sender, EventArgs e)
{
    TestEntities db = new TestEntities();
    db.Connection.Open();
    using (System.Data.Common.DbTransaction transaction = db.Connection.BeginTransaction())
    {
        try
        {
            Customer customer = new Customer();
            customer.Name = txtName.Text.Trim();
            customer.Country = txtCountry.Text.Trim();
            db.Customers.AddObject(customer);

            Customer customerInvalid = new Customer();
            customerInvalid.Name = "Dharmendra";
            customerInvalid.Country = "ABCD"; // More than 50 characters.
            db.Customers.AddObject(customerInvalid);

            db.SaveChanges();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
        }
    }
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim db As TestEntities = New TestEntities()
    db.Connection.Open()
    Using transaction As System.Data.Common.DbTransaction = db.Connection.BeginTransaction()
        Try
            Dim customer As Customer = New Customer()
            customer.Name = txtName.Text.Trim()
            customer.Country = txtCountry.Text.Trim()
            db.Customers.AddObject(customer)

            Dim customerInvalid As Customer = New Customer()
            customerInvalid.Name = "Dharmendra"
            customerInvalid.Country = "ABCD" ' More than 50 characters.
            db.Customers.AddObject(customerInvalid)

            db.SaveChanges()
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Using
End Sub

Transaction in Entity Framework 6.0

For Entity Framework 6.0 you have to use below code.

C#

protected void Save(object sender, EventArgs e)
{
    TestEntities db = new TestEntities();
    using (System.Data.Entity.DbContextTransaction transaction = db.Database.BeginTransaction())
    {
        try
        {
            Customer customer = new Customer();
            customer.Name = txtName.Text.Trim();
            customer.Country = txtCountry.Text.Trim();
            db.Customers.Add(customer);

            Customer customerInvalid = new Customer();
            customerInvalid.Name = "Dharmendra";
            customerInvalid.Country = "ABCD"; // More than 50 characters.
            db.Customers.Add(customerInvalid);

            db.SaveChanges();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
        }
    }
}

VB.Net

Protected Sub Save(ByVal sender As Object, ByVal e As EventArgs)
    Dim db As TestEntities = New TestEntities()
    Using transaction As System.Data.Entity.DbContextTransaction = db.Database.BeginTransaction()
        Try
            Dim customer As Customer = New Customer()
            customer.Name = txtName.Text.Trim()
            customer.Country = txtCountry.Text.Trim()
            db.Customers.Add(customer)

            Dim customerInvalid As Customer = New Customer()
            customerInvalid.Name = "Dharmendra"
            customerInvalid.Country = "ABCD" ' More than 50 characters.
            db.Customers.Add(customerInvalid)

            db.SaveChanges()
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Using
End Sub

Output

I have checked the sample with one Table. I am trying to pass more that 50 charater for 2nd customer (Invalid) that means it falis due to datatype length. In that case 1st table data will be rollbacked.