Simple MySQL Transaction in ASP.Net

Introduction

In this article, I will explain how to insert data into multiple tables using a MySQL Transaction in ASP.NET.

So, let's proceed with the following procedure:

• ASP.NET web page
   
• Grid View Data Control , MySQL Database and MySQL Transaction

Now, open the MySQLAdmin Page then select "Create a New Table” > "View" >”Table Structure for Table 'student ' ”.

CREATE TABLE IF NOT EXISTS `student` (
  `SID` int(100) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) NOT NULL,
  `Address` varchar(500) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `Mobile` varchar(25) NOT NULL,
  PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Table structure for table 'courses'

CREATE TABLE IF NOT EXISTS `courses` (
  `Course` varchar(100) NOT NULL,
  `Name` varchar(100) NOT NULL,
  `Amount` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Open your instance of Visual Studio 2012 and create a new ASP.NET Web application. Name the project “MySqlTransactionApplication", as shown in the following figure:



Now design your Student.aspx View design part; use the following code:

Student.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Student.aspx.cs" Inherits="MySqlTransactionApplication.Student" %>
<asp:Content ID="Content1" ContentPlaceHolderID="titleContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
     <script type="text/javascript">
         function validationCheck() {
             var summary = "";
             summary += isvalidName();
             summary += isvalidAmount();
             summary += isvalidAddress();
             summary += isvalidEmail();
             summary += isvalidMobileno();
             if (summary != "") {
                 alert(summary);
                 return false;
             }
             else {
                 return true;
             }
         }
         function isvalidName() {
             var id;
             var temp = document.getElementById("<%=txtName.ClientID %>");
            id = temp.value;
            if (id == "") {
                return ("Please Enter Name" + "\n");
            }
            else {
                return "";
            }
         }
         function isvalidAddress() {
             var id;
             var temp = document.getElementById("<%=txtAddress.ClientID %>");
             id = temp.value;
             if (id == "") {
                 return ("Please Enter Address" + "\n");
             }
             else {
                 return "";
             }
         }
         function isvalidAmount() {
            var id;
            var temp = document.getElementById("<%=txtAmount.ClientID %>");
            id = temp.value;
            if (id == "") {
                return ("Please Enter Amount" + "\n");
            }
            else {
                return "";
            }
        }
        function isvalidEmail() {
            var id;
            var temp = document.getElementById("<%=txtEmail.ClientID %>");
            id = temp.value;
            var re = /\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*/;
            if (id == "") {
                return ("Please Enter Email" + "\n");
            }
            else if (re.test(id)) {
                return "";
            }
            else {
                return ("Email should be in the form [email protected]" + "\n");
            }
        }
        function isvalidMobileno() {
            var id;
            var temp = document.getElementById("<%=txtMobile.ClientID %>");
            id = temp.value;
            var re;
            re = /^[0-9]+$/;
            var digits = /\d(10)/;
            if (id == "") {
                return ("Please Enter Mobile no" + "\n");
            }
            else if (re.test(id)) {
                return "";
            }
            else {
                return ("Phone no should be digits only" + "\n");
            }
        }
</script>
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
     <table>
        <tr>
            <td class="td">Name:<span style="color:red;">*</span></td>
            <td>
                <asp:TextBox ID="txtName" runat="server" CssClass="txt"></asp:TextBox></td>
          </tr>
        <tr>
            <td class="td">Course:<span style="color:red;">*</span></td>
            <td>
                <asp:DropDownList ID="ddlCourse" runat="server" CssClass="txt" Width="216px">
                    <asp:ListItem Value="0">--------Select--------</asp:ListItem>
                    <asp:ListItem>.NET</asp:ListItem>
                    <asp:ListItem>Java</asp:ListItem>
                    <asp:ListItem>SQL Server</asp:ListItem>
                    <asp:ListItem>MySQL</asp:ListItem>
                    <asp:ListItem>Oracle</asp:ListItem>
                    <asp:ListItem>PHP</asp:ListItem>
                    <asp:ListItem>A+/Network+</asp:ListItem>
                    <asp:ListItem>CISCO</asp:ListItem>
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td class="td">Amount: <span style="color:red;">*</span></td>
            <td>
                <asp:TextBox ID="txtAmount" runat="server" CssClass="txt"></asp:TextBox></td>
        </tr>
        <tr>
            <td class="td">Address: <span style="color:red;">*</span></td>
            <td>
                <asp:TextBox ID="txtAddress" runat="server" CssClass="txt"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="td">Mobile: <span style="color:red;">*</span></td>
            <td>
                <asp:TextBox ID="txtMobile" runat="server" CssClass="txt"></asp:TextBox></td>
        </tr>
        <tr>
            <td class="td">Email ID: <span style="color:red;">*</span></td>
            <td>
                <asp:TextBox ID="txtEmail" runat="server" CssClass="txt"></asp:TextBox>
                   </td>
        </tr>
        <tr>
            <td></td>
            <td>
                <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" CssClass="button"  />
                <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" CssClass="button" /></td>
            <td></td>
        </tr>
    </table>
    <asp:Panel ID="Panel1" runat="server" >
        <table style="width: 50%;">
            <tr>
                <td>
                    <asp:GridView ID="GridViewStudent" CssClass="handsontable" runat="server"></asp:GridView>
                </td>
                <td><asp:GridView ID="GridViewCourses" CssClass="handsontable" runat="server"></asp:GridView></td>
            </tr>
        </table>
    </asp:Panel>
</asp:Content>

Now, in the code behind file "Student.aspx.cs" use the following code.

Student.aspx.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
namespace MySqlTransactionApplication
{
    public partial class Student : System.Web.UI.Page
    {
        #region MySqlConnection Connection and Page Lode
        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!Page.IsPostBack)
                {
                    btnSubmit.Attributes.Add("onclick", "javascript:return validationCheck()");
                    BindStudentGridView(); BindCoursesGridView();
                }
            }
            catch (Exception ex)
            {
                ShowMessage(ex.Message);
            }
        }
        #endregion
        #region show message
        /// <summary>
        /// This function is used for show message.
        /// </summary>
        /// <param name="msg"></param>
        void ShowMessage(string msg)
        {
            ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + msg + "');</script>");
        }
        /// <summary>
        /// This Function is used TextBox Empty.
        /// </summary>
        void clear()
        {
            txtName.Text = string.Empty; txtAddress.Text = string.Empty; txtMobile.Text = string.Empty; txtEmail.Text = string.Empty;
            ddlCourse.SelectedIndex = 0; txtAmount.Text = string.Empty; txtName.Focus();
        }
        #endregion
        #region bind data to GridViewStudent and GridViewCourses
        private void BindStudentGridView()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                MySqlCommand cmd = new MySqlCommand("Select * from Student ORDER BY SID DESC", conn);
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                GridViewStudent.DataSource = ds;
                GridViewStudent.DataBind();
            }
            catch (MySqlException ex)
            {
                ShowMessage(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        private void BindCoursesGridView()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                MySqlCommand cmd = new MySqlCommand("Select * from courses", conn);
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                GridViewCourses.DataSource = ds;
                GridViewCourses.DataBind();
            }
            catch (MySqlException ex)
            {
                ShowMessage(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
        #endregion
        #region Transaction
        //this code used to Transaction are implememted
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            MySqlTransaction tr = null;
            try
            {
                conn.Open();
                tr = conn.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand();
                cmd = new MySqlCommand("Insert into student (Name,Address,Mobile,Email ) values (@Name,@Address,@Mobile,@Email)", conn,tr);
                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
                cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand("Insert into courses (Course,Name,Amount ) values (@Course,@Name1,@Amount )", conn,tr);
                cmd.Parameters.AddWithValue("@Name1", txtName.Text);
                cmd.Parameters.AddWithValue("@Course",ddlCourse.SelectedValue.ToString());
                cmd.Parameters.AddWithValue("@Amount",txtAmount.Text);
                cmd.ExecuteNonQuery();
                tr.Commit();
                cmd.Dispose();
                ShowMessage("Student Course Enrollment Successfully......!");
                clear();
                BindStudentGridView(); BindCoursesGridView();
            }
            catch (MySqlException ex)
            {
                tr.Rollback();
                ShowMessage(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
        // used TextBox Empty.
        protected void btnCancel_Click(object sender, EventArgs e)
        {
            clear();
        }
    }
}

Now run the page, it will look like the following.



Now, Student information Enter and Submit, it will look like the following:



Now, show in the Message box “Student Course Enrollment Successfully”. And show the data to GridViewStudent and GridViewCourses.



Now, open the MySQLAdmin Page then show the Student and Courses table data



I hope this article is useful. If you have any other questions then please provide your comments below.