In this article, we will learn about how we can perform crud operations using ADO.NET in ASP.NET.
What is ADO.NET?
ADO stands for ActiveX Data Object. ADO.NET is one of the .Net Framework’s modules that is used to build a connection between application and data sources. Data sources can be such as SQL Server and XML (Extensible Markup Language). ADO.NET consists of classes that can be used to perform CRUD operation (Create, Read, Update and Delete).
Create a new project and select the ASP.NET Empty Web Site.
Here, we create a StudentInfo table with given fields.
Firstly click on View menu -> Server Explorer to access a database server either use Ctrl+Alt+S.
Now, right-click on Data Connections -> Add Connection.
Now simply choose (or enter) the server name, enter server authentication details (if required), choose the database name, and click Test Connection then click OK.
The connection will be entered into the Server Explorer window. Now, choose and right-click on Connection -> Properties (Alt+Enter) then copy connection string from Properties window -> Connection -> Connection String.
Now, open Web.config file from Solution Explorer window and then paste connection string as shown below.
<connectionStrings>
<add name="myConnectionString" connectionString="Data Source=DESKTOP-78L7I55;Initial Catalog=Student;User ID=sa;Password=vision" />
</connectionStrings>
Now, right-click the project name (CRUD Operations) in the Solution Explorer and select Add -> Add New Item.
Now, let’s add a new Default.aspx file, select Web Form and click Add.
Open the Default.aspx file and add the code in it.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Add Student</h1>
<table width="100%">
<tr>
<td> </td>
<td>
<asp:Label ID="Label1" runat="server" Text="Student Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Label ID="Label2" runat="server" Text="Age"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtAge" runat="server" TextMode="Number"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td colspan="3">
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
<table class="tblcolor" width="100%" border="1">
<tr>
<th>ID</th>
<th>Student Name</th>
<th>Age</th>
<th>Action</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td>
<asp:Label ID="lblStudentID" runat="server" Text='<%# Eval("ID") %>' />
</td>
<td>
<%#Eval("Name") %>
</td>
<td>
<%#Eval("Age") %>
</td>
<th>
<asp:HyperLink ID="lnkEdit" runat="server" NavigateUrl='<%#"~/Edit.aspx?id=" + Eval("ID") %>'>Edit</asp:HyperLink>
|
<asp:LinkButton ID="lnkDelete" Text="Delete" runat="server" OnClientClick="return confirm('Do you want to delete this Student?');" OnClick="DeleteStudent" />
</th>
</tr>
</ItemTemplate>
</asp:Repeater>
</form>
</body>
</html>
Open the Default.aspx.cs file and add the code in it.
using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da;
string qry;
protected void Page_Load(object sender, EventArgs e)
{
RepeaterData();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
cn.Open();
qry = "insert into StudentInfo values ('" + txtName.Text + "'," + txtAge.Text + ")";
cmd = new SqlCommand(qry, cn);
cmd.ExecuteNonQuery();
Response.Write("<script> alert('Record Inserted !') </script>");
cn.Close();
RepeaterData();
}
public void RepeaterData()
{
cn.Open();
qry = "Select * from StudentInfo";
cmd = new SqlCommand(qry, cn);
DataSet ds = new DataSet();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
Repeater1.DataSource = ds;
Repeater1.DataBind();
cn.Close();
}
protected void DeleteStudent(object sender, EventArgs e)
{
int studentID = int.Parse(((sender as LinkButton).NamingContainer.FindControl("lblStudentID") as Label).Text);
cn.Open();
qry = "DELETE FROM StudentInfo WHERE ID=" + studentID;
cmd = new SqlCommand(qry, cn);
cmd.ExecuteNonQuery();
Response.Write("<script> alert('Record Deleted !') </script>");
cn.Close();
RepeaterData();
}
}
Now, let’s add a new Edit.aspx file (follow same steps as adding Default.aspx).
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Edit.aspx.cs" Inherits="Edit" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>Edit Student</h1>
<table width="100%">
<tr>
<td> </td>
<td>
<asp:Label ID="Label1" runat="server" Text="Student Name"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Label ID="Label2" runat="server" Text="Age"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtAge" runat="server" TextMode="Number"></asp:TextBox>
</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Open the Edit.aspx.cs file and add the code in it.
using System;
using System.Data.SqlClient;
using System.Configuration;
public partial class Edit : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;
string qry;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString["id"] != null)
{
cn.Open();
qry = "select * from StudentInfo where id=" + Request.QueryString["id"];
cmd = new SqlCommand(qry, cn);
dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
txtName.Text = dr[1].ToString();
txtAge.Text = dr[2].ToString();
}
cn.Close();
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
cn.Open();
qry = "update StudentInfo set name='" + txtName.Text + "', age=" + txtAge.Text + " where id=" + Request.QueryString["id"];
cmd = new SqlCommand(qry, cn);
cmd.ExecuteNonQuery();
Response.Write("<script> alert('Record Updated !') </script>");
cn.Close();
}
}
That’s it.
Output:
