This Asp.net will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE. using Sql Database. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.
We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the employee table in the database named locompany. employee table consist of following columns empno,name,address,title.
First we have to write the namespaces inorder to connect sqlserver and Asp.net
using System.Data.SqlClient;
Feature of projects
The system shall be able to record employee details : name,address,title.
The system shall be able to retrieve the employee details : name,address,title.
Then system shall be able to Edit and Delete the employee details : name,address,title.
First we have to Design the Page.
Index.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="index.aspx.cs" Inherits="index" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<div class="form-group">
<div class="col-sm-12">
<h2 style="text-align: center; color: blue">Employee Crud Asp.Net Step by Step</h2>
</div>
</div>
<hr/>
<div class="row">
<div class="form-group col-md-6">
<label>Employee No</label>
<asp:DropDownList ID="DropDownList1" class="form-control" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>
</div>
<div class="form-group col-md-6">
<label>Name</label>
<asp:TextBox ID="txtname" runat="server" class="form-control" placeholder="Address"></asp:TextBox>
</div>
<div class="form-group col-md-6">
<label>Address</label>
<asp:TextBox ID="txtaddress" runat="server" class="form-control" placeholder="Address"></asp:TextBox>
</div>
<div class="form-group col-md-6">
<label>Title</label>
<asp:TextBox ID="txttitle" runat="server" class="form-control" placeholder="Title"></asp:TextBox>
</div>
<div class="form-group col-md-6" align="center">
<asp:Button ID="Button1" runat="server" class="btn btn-success" Text="Add" OnClick="Button1_Click"></asp:Button>
<asp:Button ID="Button2" runat="server" class="btn btn-warning" Text="Update" OnClick="Button2_Click"></asp:Button>
<asp:Button ID="Button3" runat="server" class="btn btn-danger" Text="Delete" OnClick="Button3_Click"></asp:Button>
</div>
</div>
</div>
</form>
</body>
</html>
Load the Employee No into the DropDownList
SqlConnection con = new SqlConnection("server=.; database=locompany; integrated security=true");
SqlCommand cmd = new SqlCommand();
SqlDataReader dread;
protected void Page_Load(object sender, EventArgs e)
{
if(!Page.IsPostBack)
{
cmd = new SqlCommand("select empno from employee",con);
con.Open();
dread = cmd.ExecuteReader();
DropDownList1.Items.Clear();
while(dread.Read())
{
DropDownList1.Items.Add(dread.GetValue(0).ToString());
}
con.Close();
}
}After loaded DropDownList we will be able to select the Emp no from the DropDownList result will be displayed according to Emp no selected.
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
Button1.Enabled = false;
string empno = DropDownList1.Text;
cmd = new SqlCommand("select * from employee where empno = '" + DropDownList1.Text + "'", con);
try
{
con.Open();
dread = cmd.ExecuteReader();
while (dread.Read())
{
txtname.Text = dread.GetValue(1).ToString();
txtaddress.Text = dread.GetValue(2).ToString();
txttitle.Text = dread.GetValue(3).ToString();
}
}
catch (SqlException ex)
{
Response.Write(ex.Message);
string msg = "alert(\"Employee Registered Success\")";
ScriptManager.RegisterStartupScript(this, GetType(), "Serverscript", msg, true);
}
finally
{
con.Close();
}
}Add Records in to the database
protected void Button1_Click(object sender, EventArgs e)
{
string cmdstr;
cmd.Connection = con;
cmdstr = "Insert into employee(name,address,title) values('{0}','{1}','{2}')";
cmdstr = cmdstr.Replace("{0}", txtname.Text);
cmdstr = cmdstr.Replace("{1}", txtaddress.Text);
cmdstr = cmdstr.Replace("{2}", txttitle.Text);
cmd.CommandText = cmdstr;
try
{
con.Open();
cmd.ExecuteNonQuery();
string msg = "alert(\"Employee Registered Success\")";
ScriptManager.RegisterStartupScript(this, GetType(), "Serverscript", msg, true);
}
catch (SqlException ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}Update Records in to the database
protected void Button2_Click(object sender, EventArgs e)
{
string cmdstr;
cmd.Connection = con;
cmdstr = "Update employee set name = '{1}',address='{2}',title='{3}' where empno = '{0}'";
cmdstr = cmdstr.Replace("{1}", txtname.Text);
cmdstr = cmdstr.Replace("{2}", txtaddress.Text);
cmdstr = cmdstr.Replace("{3}", txttitle.Text);
cmdstr = cmdstr.Replace("{0}", DropDownList1.Text);
cmd.CommandText = cmdstr;
try
{
con.Open();
cmd.ExecuteNonQuery();
string msg = "alert(\"Employee Updateee\")";
ScriptManager.RegisterStartupScript(this, GetType(), "Serverscript", msg, true);
txtname.Text = "";
txtaddress.Text = "";
txttitle.Text = "";
DropDownList1.SelectedIndex = -1;
Button1.Enabled = true;
}
catch (SqlException ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}Delete Records in to the database
string cmdstr;
cmd.Connection = con;
cmdstr = "Delete from employee where empno = '{0}'";
cmdstr = cmdstr.Replace("{0}", DropDownList1.Text);
cmd.CommandText = cmdstr;
try
{
con.Open();
cmd.ExecuteNonQuery();
string msg = "alert(\"Employee Deleteddd\")";
ScriptManager.RegisterStartupScript(this, GetType(), "Serverscript", msg, true);
txtname.Text = "";
txtaddress.Text = "";
txttitle.Text = "";
DropDownList1.SelectedIndex = -1;
Button1.Enabled = true;
}
catch (SqlException ex)
{
Response.Write(ex.Message);
}
finally
{
con.Close();
}
}I have attached the video tutorial below it will help you to do this step by step.
