Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to use Transaction for MS Access database in ASP.NET

Author: Mike Green
Download Source Code : 1389_TransactionWithAccessDatabase.zip

I will explain in this article that how we can use transaction for MS access database in ASP.NET.

 

Transaction can be used for MS Access database by using OleDbTransaction class. We use System.Data.OleDb namespace to work with MS Access database and OleDbTransaction is also in this namespace. In one of my previous article on DevASP.NET, I have explained how we can insert values into MS Access database and this article will explain the use of transaction with MS Access database in ASP.NET. 

 

  1. Open MS Visual Studio 2010
  2. File > New > Website > Visual C# or Visual Basic > ASP.NET Empty Web Site
  3. Select Web Location as File System and Click OK
  4. From Menu, Website > Add New Item > Select Web Form and Click Add.
  5. Write below connection string in web.config file

    <connectionStrings>
           <add name="CustomersDatabase" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Customers.accdb"/>
    </connectionStrings>
     
    We need to write provider and set data source for MS Access database.
     
  6. Write code below in default.aspx page

    <h3>Insert Values into Customers table</h3>
    <table>
        <tr>
            <td>CustomerID</td>
            <td>
                <asp:TextBox ID="txtID" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>CompanyName</td>
            <td>
                <asp:TextBox ID="txtCompany" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>ContactName</td>
            <td>
                <asp:TextBox ID="txtContact" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
           <td>City</td>
           <td>
               <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
           </td>
        </tr>
        <tr>
           <td>Country</td>
           <td>
               <asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
           </td>
           </tr>
    </table>
    <br />
    <h3>Update ContactName for Customer ID</h3>
    <table>
        <tr>
            <td>CustomerID</td>
            <td>
                <asp:TextBox ID="txtIDUpdate" runat="server"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>ContactName</td>
            <td>
                <asp:TextBox ID="txtContactUpdate" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <br />
    <h3>Delete record for Customer ID</h3>
    <table>
        <tr>
            <td>CustomerID</td>
            <td>
                <asp:TextBox ID="txtIDDelete" runat="server"></asp:TextBox>
            </td>
        </tr>
    </table>
    <br />
    <asp:Button ID="btnTransaction" runat="server" Text="Start Transaction"
         onclick="btnTransaction_Click" />
    <br />
    <asp:Label ID="lblMessage" runat="server"></asp:Label>
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>

     
  7. Include following namespaces in your code file

    C#
     
    using System.Configuration;
    using System.Data.OleDb;
    using System.Data;
     
    VB.NET
     
    Imports System.Configuration
    Imports System.Data.OleDb
    Imports System.Data
     
  8. Write below code in Insert Button click event for transaction

    C#
     
    protected void btnTransaction_Click(object sender, EventArgs e)
    {
        string custID = txtID.Text;
        string CompName = txtCompany.Text;
        string contact = txtContact.Text;
        string city = txtCity.Text;
        string country = txtCountry.Text;
        string custIDUpdate = txtIDUpdate.Text;
        string contactUpdate = txtContactUpdate.Text;
        string custIDDelete = txtIDDelete.Text;
     
        string connString = ConfigurationManager.ConnectionStrings["CustomersDatabase"].ConnectionString;
        OleDbConnection connection = new OleDbConnection(connString);
        connection.Open();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        OleDbTransaction transaction = connection.BeginTransaction();
        command.Transaction = transaction;
     
        try
        {
            command.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, City, Country) VALUES(@CustomerID, @CompanyName, @ContactName, @City, @Country)";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@CustomerID", custID);
            command.Parameters.AddWithValue("@CompanyName", CompName);
            command.Parameters.AddWithValue("@ContactName", contact);
            command.Parameters.AddWithValue("@City", city);
            command.Parameters.AddWithValue("@Country", country);
            command.ExecuteNonQuery();
     
            command.CommandText = "UPDATE Customers SET ContactName = @ContactName2 WHERE CustomerID = @CustomerID2";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@CustomerID2", custIDUpdate);
            command.Parameters.AddWithValue("@ContactName2", contactUpdate);
            command.ExecuteNonQuery();
     
            command.CommandText = "DELETE FROM Customers WHERE CustomerID = @CustomerID3";
            command.CommandType = CommandType.Text;
            command.Parameters.AddWithValue("@CustomerID2", custIDUpdate);
            command.ExecuteNonQuery();
     
            command.CommandText = "SELECT * FROM Customers WHERE Country = 'USA'";
            command.CommandType = CommandType.Text;
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            GridView1.DataSource = table;
            GridView1.DataBind();
     
            transaction.Commit();
            lblMessage.Text = "Transaction successfully completed";
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            lblMessage.Text = "Transaction is not completed";
        }
        finally
        {
            connection.Close();
        }
    }
     
    VB.NET
     
    Protected Sub btnTransaction_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnTransaction.Click
        Dim custID As String = txtID.Text
        Dim CompName As String = txtCompany.Text
        Dim contact As String = txtContact.Text
        Dim city As String = txtCity.Text
        Dim country As String = txtCountry.Text
        Dim custIDUpdate As String = txtIDUpdate.Text
        Dim contactUpdate As String = txtContactUpdate.Text
        Dim custIDDelete As String = txtIDDelete.Text
     
        Dim connString As String = ConfigurationManager.ConnectionStrings("CustomersDatabase").ConnectionString
        Dim connection As New OleDbConnection(connString)
        connection.Open()
        Dim command As New OleDbCommand()
        command.Connection = connection
        Dim transaction As OleDbTransaction = connection.BeginTransaction()
        command.Transaction = transaction
     
        Try
            command.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, City, Country) VALUES(@CustomerID, @CompanyName, @ContactName, @City, @Country)"
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@CustomerID", custID)
            command.Parameters.AddWithValue("@CompanyName", CompName)
            command.Parameters.AddWithValue("@ContactName", contact)
            command.Parameters.AddWithValue("@City", city)
            command.Parameters.AddWithValue("@Country", country)
            command.ExecuteNonQuery()
     
            command.CommandText = "UPDATE Customers SET ContactName = @ContactName2 WHERE CustomerID = @CustomerID2"
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@CustomerID2", custIDUpdate)
            command.Parameters.AddWithValue("@ContactName2", contactUpdate)
            command.ExecuteNonQuery()
     
            command.CommandText = "DELETE FROM Customers WHERE CustomerID = @CustomerID3"
            command.CommandType = CommandType.Text
            command.Parameters.AddWithValue("@CustomerID2", custIDUpdate)
            command.ExecuteNonQuery()
     
            command.CommandText = "SELECT * FROM Customers WHERE Country = 'USA'"
            command.CommandType = CommandType.Text
            Dim adapter As New OleDbDataAdapter(command)
            Dim table As New DataTable()
            adapter.Fill(table)
            GridView1.DataSource = table
            GridView1.DataBind()
     
            transaction.Commit()
            lblMessage.Text = "Transaction successfully completed"
        Catch ex As Exception
            transaction.Rollback()
            lblMessage.Text = "Transaction is not completed"
        Finally
            connection.Close()
        End Try
    End Sub
     
    First we are getting all values from text boxes and then we need to get connection string from web.config file and we have to set our connection to MS Access database. We have created OleDbCommand object and provide connection object to Connection property of OleDbCommand object. We have to create OleDbTransaction object to work with transaction for MS Access database. To create OleDbTransaction object, we have to call BeginTransaction() method of OleDbConnection object and we have to provide this OleDbTransaction object to Transaction property of OleDbCommand object. First we are inserting values into Customers table of MS Access database, and then we are updating contact name for provided customer ID. After that a delete statement to delete a customer from table for specific ID and at the end a select statement to select all customer detail for USA. Transaction will be rolled back and will not complete if any error occurs in any query. 
     

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to create a Signup page in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net