ASP.NET

Simple Insert Select Update and Delete in ASP.NET Using MYSQL Database

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:

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

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" %>  
  3.   
  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>  
  46.   
  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;  
  10.   
  11.   
  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();  
  26.                       
  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.         }  
  118.           
  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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found