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:

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

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



Table structure for table 'courses'

  1. CREATE TABLE IF NOT EXISTS `courses` (
  2. `Course` varchar(100) NOT NULL,
  3. `Name` varchar(100) NOT NULL,
  4. `Amount` int(11) NOT NULL
  5. ) 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

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Student.aspx.cs" Inherits="MySqlTransactionApplication.Student" %>
  2. <asp:Content ID="Content1" ContentPlaceHolderID="titleContent" runat="server">
  3. </asp:Content>
  4. <asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
  5. <script type="text/javascript">
  6. function validationCheck() {
  7. var summary = "";
  8. summary += isvalidName();
  9. summary += isvalidAmount();
  10. summary += isvalidAddress();
  11. summary += isvalidEmail();
  12. summary += isvalidMobileno();
  13. if (summary != "") {
  14. alert(summary);
  15. return false;
  16. }
  17. else {
  18. return true;
  19. }
  20. }
  21. function isvalidName() {
  22. var id;
  23. var temp = document.getElementById("<%=txtName.ClientID %>");
  24. id = temp.value;
  25. if (id == "") {
  26. return ("Please Enter Name" + "\n");
  27. }
  28. else {
  29. return "";
  30. }
  31. }
  32. function isvalidAddress() {
  33. var id;
  34. var temp = document.getElementById("<%=txtAddress.ClientID %>");
  35. id = temp.value;
  36. if (id == "") {
  37. return ("Please Enter Address" + "\n");
  38. }
  39. else {
  40. return "";
  41. }
  42. }
  43. function isvalidAmount() {
  44. var id;
  45. var temp = document.getElementById("<%=txtAmount.ClientID %>");
  46. id = temp.value;
  47. if (id == "") {
  48. return ("Please Enter Amount" + "\n");
  49. }
  50. else {
  51. return "";
  52. }
  53. }
  54. function isvalidEmail() {
  55. var id;
  56. var temp = document.getElementById("<%=txtEmail.ClientID %>");
  57. id = temp.value;
  58. var re = /\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*/;
  59. if (id == "") {
  60. return ("Please Enter Email" + "\n");
  61. }
  62. else if (re.test(id)) {
  63. return "";
  64. }
  65. else {
  66. return ("Email should be in the form [email protected]" + "\n");
  67. }
  68. }
  69. function isvalidMobileno() {
  70. var id;
  71. var temp = document.getElementById("<%=txtMobile.ClientID %>");
  72. id = temp.value;
  73. var re;
  74. re = /^[0-9]+$/;
  75. var digits = /\d(10)/;
  76. if (id == "") {
  77. return ("Please Enter Mobile no" + "\n");
  78. }
  79. else if (re.test(id)) {
  80. return "";
  81. }
  82. else {
  83. return ("Phone no should be digits only" + "\n");
  84. }
  85. }
  86. </script>
  87. </asp:Content>
  88. <asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
  89. <table>
  90. <tr>
  91. <td class="td">Name:<span style="color:red;">*</span></td>
  92. <td>
  93. <asp:TextBox ID="txtName" runat="server" CssClass="txt"></asp:TextBox></td>
  94. </tr>
  95. <tr>
  96. <td class="td">Course:<span style="color:red;">*</span></td>
  97. <td>
  98. <asp:DropDownList ID="ddlCourse" runat="server" CssClass="txt" Width="216px">
  99. <asp:ListItem Value="0">--------Select--------</asp:ListItem>
  100. <asp:ListItem>.NET</asp:ListItem>
  101. <asp:ListItem>Java</asp:ListItem>
  102. <asp:ListItem>SQL Server</asp:ListItem>
  103. <asp:ListItem>MySQL</asp:ListItem>
  104. <asp:ListItem>Oracle</asp:ListItem>
  105. <asp:ListItem>PHP</asp:ListItem>
  106. <asp:ListItem>A+/Network+</asp:ListItem>
  107. <asp:ListItem>CISCO</asp:ListItem>
  108. </asp:DropDownList>
  109. </td>
  110. </tr>
  111. <tr>
  112. <td class="td">Amount: <span style="color:red;">*</span></td>
  113. <td>
  114. <asp:TextBox ID="txtAmount" runat="server" CssClass="txt"></asp:TextBox></td>
  115. </tr>
  116. <tr>
  117. <td class="td">Address: <span style="color:red;">*</span></td>
  118. <td>
  119. <asp:TextBox ID="txtAddress" runat="server" CssClass="txt"></asp:TextBox>
  120. </td>
  121. </tr>
  122. <tr>
  123. <td class="td">Mobile: <span style="color:red;">*</span></td>
  124. <td>
  125. <asp:TextBox ID="txtMobile" runat="server" CssClass="txt"></asp:TextBox></td>
  126. </tr>
  127. <tr>
  128. <td class="td">Email ID: <span style="color:red;">*</span></td>
  129. <td>
  130. <asp:TextBox ID="txtEmail" runat="server" CssClass="txt"></asp:TextBox>
  131. </td>
  132. </tr>
  133. <tr>
  134. <td></td>
  135. <td>
  136. <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" CssClass="button" />
  137. <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" CssClass="button" /></td>
  138. <td></td>
  139. </tr>
  140. </table>
  141. <asp:Panel ID="Panel1" runat="server" >
  142. <table style="width: 50%;">
  143. <tr>
  144. <td>
  145. <asp:GridView ID="GridViewStudent" CssClass="handsontable" runat="server"></asp:GridView>
  146. </td>
  147. <td><asp:GridView ID="GridViewCourses" CssClass="handsontable" runat="server"></asp:GridView></td>
  148. </tr>
  149. </table>
  150. </asp:Panel>
  151. </asp:Content>

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

Student.aspx.cs

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Configuration;
  4. using System.Data;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.UI;
  8. using System.Web.UI.WebControls;
  9. using MySql.Data.MySqlClient;
  10. namespace MySqlTransactionApplication
  11. {
  12. public partial class Student : System.Web.UI.Page
  13. {
  14. #region MySqlConnection Connection and Page Lode
  15. MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
  16. protected void Page_Load(object sender, EventArgs e)
  17. {
  18. try
  19. {
  20. if (!Page.IsPostBack)
  21. {
  22. btnSubmit.Attributes.Add("onclick", "javascript:return validationCheck()");
  23. BindStudentGridView(); BindCoursesGridView();
  24. }
  25. }
  26. catch (Exception ex)
  27. {
  28. ShowMessage(ex.Message);
  29. }
  30. }
  31. #endregion
  32. #region show message
  33. /// <summary>
  34. /// This function is used for show message.
  35. /// </summary>
  36. /// <param name="msg"></param>
  37. void ShowMessage(string msg)
  38. {
  39. ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('" + msg + "');</script>");
  40. }
  41. /// <summary>
  42. /// This Function is used TextBox Empty.
  43. /// </summary>
  44. void clear()
  45. {
  46. txtName.Text = string.Empty; txtAddress.Text = string.Empty; txtMobile.Text = string.Empty; txtEmail.Text = string.Empty;
  47. ddlCourse.SelectedIndex = 0; txtAmount.Text = string.Empty; txtName.Focus();
  48. }
  49. #endregion
  50. #region bind data to GridViewStudent and GridViewCourses
  51. private void BindStudentGridView()
  52. {
  53. try
  54. {
  55. if (conn.State == ConnectionState.Closed)
  56. {
  57. conn.Open();
  58. }
  59. MySqlCommand cmd = new MySqlCommand("Select * from Student ORDER BY SID DESC", conn);
  60. MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
  61. DataSet ds = new DataSet();
  62. adp.Fill(ds);
  63. GridViewStudent.DataSource = ds;
  64. GridViewStudent.DataBind();
  65. }
  66. catch (MySqlException ex)
  67. {
  68. ShowMessage(ex.Message);
  69. }
  70. finally
  71. {
  72. if (conn.State == ConnectionState.Open)
  73. {
  74. conn.Close();
  75. }
  76. }
  77. }
  78. private void BindCoursesGridView()
  79. {
  80. try
  81. {
  82. if (conn.State == ConnectionState.Closed)
  83. {
  84. conn.Open();
  85. }
  86. MySqlCommand cmd = new MySqlCommand("Select * from courses", conn);
  87. MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
  88. DataSet ds = new DataSet();
  89. adp.Fill(ds);
  90. GridViewCourses.DataSource = ds;
  91. GridViewCourses.DataBind();
  92. }
  93. catch (MySqlException ex)
  94. {
  95. ShowMessage(ex.Message);
  96. }
  97. finally
  98. {
  99. if (conn.State == ConnectionState.Open)
  100. {
  101. conn.Close();
  102. }
  103. }
  104. }
  105. #endregion
  106. #region Transaction
  107. //this code used to Transaction are implememted
  108. protected void btnSubmit_Click(object sender, EventArgs e)
  109. {
  110. MySqlTransaction tr = null;
  111. try
  112. {
  113. conn.Open();
  114. tr = conn.BeginTransaction();
  115. MySqlCommand cmd = new MySqlCommand();
  116. cmd = new MySqlCommand("Insert into student (Name,Address,Mobile,Email ) values (@Name,@Address,@Mobile,@Email)", conn,tr);
  117. cmd.Parameters.AddWithValue("@Name", txtName.Text);
  118. cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
  119. cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
  120. cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
  121. cmd.ExecuteNonQuery();
  122. cmd = new MySqlCommand("Insert into courses (Course,Name,Amount ) values (@Course,@Name1,@Amount )", conn,tr);
  123. cmd.Parameters.AddWithValue("@Name1", txtName.Text);
  124. cmd.Parameters.AddWithValue("@Course",ddlCourse.SelectedValue.ToString());
  125. cmd.Parameters.AddWithValue("@Amount",txtAmount.Text);
  126. cmd.ExecuteNonQuery();
  127. tr.Commit();
  128. cmd.Dispose();
  129. ShowMessage("Student Course Enrollment Successfully......!");
  130. clear();
  131. BindStudentGridView(); BindCoursesGridView();
  132. }
  133. catch (MySqlException ex)
  134. {
  135. tr.Rollback();
  136. ShowMessage(ex.Message);
  137. }
  138. finally
  139. {
  140. conn.Close();
  141. }
  142. }
  143. #endregion
  144. // used TextBox Empty.
  145. protected void btnCancel_Click(object sender, EventArgs e)
  146. {
  147. clear();
  148. }
  149. }
  150. }

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.