In the code behind file (Student.aspx.cs) write the code as in the following.
Student.aspx
<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true"
CodeBehind="Student.aspx.cs" Inherits="MYSQLCRUDApplication.Student" %>
<asp:Content ID="Content1" ContentPlaceHolderID="titleContent" runat="server">
Simple Insert Select Update and Delete in ASP.NET using MySQL Database </asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
<table>
<tr>
<td class="td">Name:</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
<td>
<asp:Label ID="lblSID" runat="server" Visible="false"></asp:Label> </td>
</tr>
<tr>
<td class="td">Address:</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td class="td">Mobile:</td>
<td>
<asp:TextBox ID="txtMobile" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td class="td">Email ID:</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
<td> </td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
<asp:Button ID="btnUpdate" runat="server" Text="Update" Visible="false"
OnClick="btnUpdate_Click" />
<asp:Button ID="btnCancel" runat="server" Text="Cancel" OnClick="btnCancel_Click" /></td>
<td></td>
</tr>
</table>
<div style="padding: 10px; margin: 0px; width: 100%;">
<p>
Total Student:<asp:Label ID="lbltotalcount" runat="server" Font-Bold="true"></asp:Label>
</p>
<asp:GridView ID="GridViewStudent" runat="server" DataKeyNames="SID"
OnSelectedIndexChanged="GridViewStudent_SelectedIndexChanged"
OnRowDeleting="GridViewStudent_RowDeleting">
<Columns>
<asp:CommandField HeaderText="Update" ShowSelectButton="True" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
</div>
</asp:Content>
In the Web.config file create the connection string as in the following.
Web.config
<connectionStrings>
<add name="ConnectionString"
connectionString="Server=localhost;userid=root;password=;Database=Testdb"
providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
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 MYSQLCRUDApplication
{
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)
{
BindGridView();
}
}
catch (Exception ex)
{
ShowMessage(ex.Message);
}
} #endregion
#region show message
void ShowMessage(string msg)
{
ClientScript.RegisterStartupScript(Page.GetType(), "validation", "");
}
void clear()
{
txtName.Text = string.Empty;
txtAddress.Text = string.Empty;
txtMobile.Text = string.Empty;
txtEmail.Text = string.Empty;
txtName.Focus();
} #endregion
#region bind data to GridViewStudent
private void BindGridView()
{
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();
lbltotalcount.Text = GridViewStudent.Rows.Count.ToString();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
} #endregion
#region Insert Data
protected void btnSubmit_Click(object sender, EventArgs e)
{
Try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("Insert into student (Name,Address,Mobile,Email )
values (@Name,@Address,@Mobile,@Email)", conn);
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.Dispose();
ShowMessage("Registered Inserted successfully......!");
clear();
BindGridView();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
}
#endregion
#region SelectedIndexChanged
protected void GridViewStudent_SelectedIndexChanged(object sender, EventArgs e)
{
GridViewRow row = GridViewStudent.SelectedRow;
lblSID.Text = row.Cells[2].Text;
txtName.Text = row.Cells[3].Text;
txtAddress.Text = row.Cells[4].Text;
txtEmail.Text = row.Cells[5].Text;
txtMobile.Text = row.Cells[6].Text;
btnSubmit.Visible = false;
btnUpdate.Visible = true;
} #endregion
#region Delete Student Data
protected void GridViewStudent_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Try
{
conn.Open();
int SID = Convert.ToInt32(GridViewStudent.DataKeys[e.RowIndex].Value);
MySqlCommand cmd = new MySqlCommand("Delete From student where SID='" + SID + "'",
conn);
cmd.ExecuteNonQuery();
cmd.Dispose();
ShowMessage("Student Record Delete Successfully......!");
GridViewStudent.EditIndex = -1;
BindGridView();
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
} #endregion
#region student data update
protected void btnUpdate_Click(object sender, EventArgs e)
{
Try
{
conn.Open();
string SID = lblSID.Text;
MySqlCommand cmd = new MySqlCommand("update student Set
Name=@Name,Address=@Address,Mobile=@Mobile,Email=@Email where SID=@SID", conn);
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.Parameters.AddWithValue("SID",SID);
cmd.ExecuteNonQuery();
cmd.Dispose();
ShowMessage("Student Record update Successfully......!");
GridViewStudent.EditIndex = -1;
BindGridView(); btnUpdate.Visible = false;
}
catch (MySqlException ex)
{
ShowMessage(ex.Message);
}
Finally
{
conn.Close();
}
} #endregion
#region textbox clear
protected void btnCancel_Click(object sender, EventArgs e)
{
clear();
} #endregion
}
}
