Introduction

This article explains how to insert Select, Update and Delete data into a MySQL database from an ASP.NET web application.

So, let's proceed with the following procedure:

Now, open a 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=31 ;



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



In the code behind file (Student.aspx.cs) write the code as in the following.

Student.aspx

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true"
  2. CodeBehind="Student.aspx.cs" Inherits="MYSQLCRUDApplication.Student" %>
  4. <asp:Content ID="Content1" ContentPlaceHolderID="titleContent" runat="server">
  5. Simple Insert Select Update and Delete in ASP.NET using MySQL Database
  6. </asp:Content>
  7. <asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
  8. </asp:Content>
  9. <asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
  10. <table>
  11. <tr>
  12. <td class="td">Name:</td>
  13. <td>
  14. <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
  15. <td>
  16. <asp:Label ID="lblSID" runat="server" Visible="false"></asp:Label> </td>
  17. </tr>
  18. <tr>
  19. <td class="td">Address:</td>
  20. <td>
  21. <asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
  22. <td> </td>
  23. </tr>
  24. <tr>
  25. <td class="td">Mobile:</td>
  26. <td>
  27. <asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>
  28. <td> </td>
  29. </tr>
  30. <tr>
  31. <td class="td">Email ID:</td>
  32. <td>
  33. <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
  34. <td> </td>
  35. </tr>
  36. <tr>
  37. <td></td>
  38. <td>
  39. <asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
  40. <asp:Button ID="btnUpdate" runat="server" Text="Update" Visible="false"
  41. OnClick="btnUpdate_Click" />
  42. <asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" /></td>
  43. <td></td>
  44. </tr>
  45. </table>
  47. <div style="padding: 10px; margin: 0px; width: 100%;">
  48. <p>
  49. Total Student:<asp:Label ID="lbltotalcount" runat="server" Font-Bold="true"></asp:Label>
  50. </p>
  51. <asp:GridView ID="GridViewStudent" runat="server" DataKeyNames="SID"
  52. OnSelectedIndexChanged="GridViewStudent_SelectedIndexChanged"
  53. OnRowDeleting="GridViewStudent_RowDeleting">
  54. <Columns>
  55. <asp:CommandField HeaderText="Update" ShowSelectButton="True" />
  56. <asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
  57. </Columns>
  58. </asp:GridView>
  59. </div>
  60. </asp:Content>

In the Web.config file create the connection string as in the following.

Web.config

  1. <connectionStrings>
  2. <add name="ConnectionString"
  3. connectionString="Server=localhost;userid=root;password=;Database=Testdb"
  4. providerName="MySql.Data.MySqlClient"/>
  5. </connectionStrings>

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;
  12. namespace MYSQLCRUDApplication
  13. {
  14. public partial class Student : System.Web.UI.Page
  15. {
  16. #region MySqlConnection Connection and Page Lode
  17. MySqlConnection conn = new
  18. MySqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
  19. protected void Page_Load(object sender, EventArgs e)
  20. {
  21. Try
  22. {
  23. if (!Page.IsPostBack)
  24. {
  25. BindGridView();
  27. }
  28. }
  29. catch (Exception ex)
  30. {
  31. ShowMessage(ex.Message);
  32. }
  33. }
  34. #endregion
  35. #region show message
  36. /// <summary>
  37. /// This function is used for show message.
  38. /// </summary>
  39. /// <param name="msg"></param>
  40. void ShowMessage(string msg)
  41. {
  42. ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script
  43. language='javascript'>alert('" + msg + "');</script>");
  44. }
  45. /// <summary>
  46. /// This Function is used TextBox Empty.
  47. /// </summary>
  48. void clear()
  49. {
  50. txtName.Text = string.Empty; txtAddress.Text = string.Empty; txtMobile.Text = string.Empty;
  51. txtEmail.Text = string.Empty;
  52. txtName.Focus();
  53. }
  54. #endregion
  55. #region bind data to GridViewStudent
  56. private void BindGridView()
  57. {
  58. Try
  59. {
  60. if (conn.State == ConnectionState.Closed)
  61. {
  62. conn.Open();
  63. }
  64. MySqlCommand cmd = new MySqlCommand("Select * from Student ORDER BY SID DESC;",
  65. conn);
  66. MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
  67. DataSet ds = new DataSet();
  68. adp.Fill(ds);
  69. GridViewStudent.DataSource = ds;
  70. GridViewStudent.DataBind();
  71. lbltotalcount.Text = GridViewStudent.Rows.Count.ToString();
  72. }
  73. catch (MySqlException ex)
  74. {
  75. ShowMessage(ex.Message);
  76. }
  77. Finally
  78. {
  79. if (conn.State == ConnectionState.Open)
  80. {
  81. conn.Close();
  82. }
  83. }
  84. }
  85. #endregion
  86. #region Insert Data
  87. /// <summary>
  88. /// this code used to Student Data insert in MYSQL Database
  89. /// </summary>
  90. /// <param name="sender"></param>
  91. /// <param name="e"></param>
  92. protected void btnSubmit_Click(object sender, EventArgs e)
  93. {
  94. Try
  95. {
  96. conn.Open();
  97. MySqlCommand cmd = new MySqlCommand("Insert into student (Name,Address,Mobile,Email )
  98. values (@Name,@Address,@Mobile,@Email)", conn);
  99. cmd.Parameters.AddWithValue("@Name",txtName.Text);
  100. cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
  101. cmd.Parameters.AddWithValue("@Mobile",txtMobile.Text);
  102. cmd.Parameters.AddWithValue("@Email",txtEmail.Text);
  103. cmd.ExecuteNonQuery();
  104. cmd.Dispose();
  105. ShowMessage("Registered successfully......!");
  106. clear();
  107. BindGridView();
  108. }
  109. catch (MySqlException ex)
  110. {
  111. ShowMessage(ex.Message);
  112. }
  113. Finally
  114. {
  115. conn.Close();
  116. }
  117. }
  119. #endregion
  120. #region SelectedIndexChanged
  121. /// <summary>
  122. /// this code used to GridViewRow SelectedIndexChanged value show textbox
  123. /// </summary>
  124. /// <param name="sender"></param>
  125. /// <param name="e"></param>
  126. protected void GridViewStudent_SelectedIndexChanged(object sender, EventArgs e)
  127. {
  128. GridViewRow row = GridViewStudent.SelectedRow;
  129. lblSID.Text = row.Cells[2].Text;
  130. txtName.Text = row.Cells[3].Text;
  131. txtAddress.Text = row.Cells[4].Text;
  132. txtEmail.Text = row.Cells[5].Text;
  133. txtMobile.Text = row.Cells[6].Text;
  134. btnSubmit.Visible = false;
  135. btnUpdate.Visible = true;
  136. }
  137. #endregion
  138. #region Delete Student Data
  139. /// <summary>
  140. /// This code used to GridViewStudent_RowDeleting Student Data Delete
  141. /// </summary>
  142. /// <param name="sender"></param>
  143. /// <param name="e"></param>
  144. protected void GridViewStudent_RowDeleting(object sender, GridViewDeleteEventArgs e)
  145. {
  146. Try
  147. {
  148. conn.Open();
  149. int SID = Convert.ToInt32(GridViewStudent.DataKeys[e.RowIndex].Value);
  150. MySqlCommand cmd = new MySqlCommand("Delete From student where SID='" + SID + "'",
  151. conn);
  152. cmd.ExecuteNonQuery();
  153. cmd.Dispose();
  154. ShowMessage("Student Data Delete Successfully......!");
  155. GridViewStudent.EditIndex = -1;
  156. BindGridView();
  157. }
  158. catch (MySqlException ex)
  159. {
  160. ShowMessage(ex.Message);
  161. }
  162. Finally
  163. {
  164. conn.Close();
  165. }
  166. }
  167. #endregion
  168. #region student data update
  169. /// <summary>
  170. /// This code used to student data update
  171. /// </summary>
  172. /// <param name="sender"></param>
  173. /// <param name="e"></param>
  174. protected void btnUpdate_Click(object sender, EventArgs e)
  175. {
  176. Try
  177. {
  178. conn.Open();
  179. string SID = lblSID.Text;
  180. MySqlCommand cmd = new MySqlCommand("update student Set
  181. Name=@Name,Address=@Address,Mobile=@Mobile,Email=@Email where SID=@SID", conn);
  182. cmd.Parameters.AddWithValue("@Name", txtName.Text);
  183. cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
  184. cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text);
  185. cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
  186. cmd.Parameters.AddWithValue("SID",SID);
  187. cmd.ExecuteNonQuery();
  188. cmd.Dispose();
  189. ShowMessage("Student Data update Successfully......!");
  190. GridViewStudent.EditIndex = -1;
  191. BindGridView(); btnUpdate.Visible = false;
  192. }
  193. catch (MySqlException ex)
  194. {
  195. ShowMessage(ex.Message);
  196. }
  197. Finally
  198. {
  199. conn.Close();
  200. }
  201. }
  202. #endregion
  203. #region textbox clear
  204. protected void btnCancel_Click(object sender, EventArgs e)
  205. {
  206. clear();
  207. }
  208. #endregion
  209. }
  210. }

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



Now, enter the student data insert and Grid view Show Data. Message box “Registered successfully”.



Now, select the Student then show the data TextBox and update the data shown in the Message box “Student Data update successfully”.



Now, delete the Student data shown in the Message box “Student Data Delete Successfully”.



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