Simple MySQL Transaction in ASP.Net

In this article, I explain how to insert data into multiple tables using a 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` (
  `
SIDint(100) NOT NULL AUTO_INCREMENT,
  `
Namevarchar(100) NOT NULL,
  `
Addressvarchar(500) NOT NULL,
  `
Emailvarchar(100) NOT NULL,
  `
Mobilevarchar(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` (

  `Coursevarchar(100) NOT NULL,

  `Namevarchar(100) NOT NULL,

  `Amountint(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 abc@xyz.com" + "\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="width50%;">

            <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.