Simple Add,Edit,Retrieve and Delete in Asp.net

Here is a example application of Add,Edit,Retrieve and Delete operations using Asp.net.

Download

Creating a table employeeinfo for proceeding the operations.

Sql Table Creation

create table employeeinfo
(
empid int not null primary key,
empname varchar(50),
empaddress varchar(200),
empsalary int
)

Declaring Connection String and Variables

Namespace : using System.Data.SqlClient;

string QryStr = "";
SqlConnection con = new SqlConnection("Server=servername;Integrated Security=true;Database=employee");
SqlCommand cmd;
SqlDataReader dr;

In this example i have used windows authentication.You have to change the connection string according your specification.

Adding Records

QryStr = "INSERT INTO employeeinfo VALUES('" +txtEmpId.Text + "','" + txtEmpName.Text + "','" + txtEmpAdd.Text + "','" + txtEmpSal.Text + "')";
con.Open();
cmd = new SqlCommand(QryStr, con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();

Updating Records

QryStr = "UPDATE employeeinfo SET empname = '"+txtEmpName.Text+"',empaddress = '"+txtEmpAdd.Text+"',empsalary = '"+txtEmpSal.Text+"' WHERE empid = '"+txtEmpId.Text+"'";
con.Open();
cmd = new SqlCommand(QryStr, con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();

Retrieve Records to textboxes

Records will be Retrieved from the database and displayed in the text boxes when text change of employee id is fired.

QryStr = "SELECT * FROM employeeinfo WHERE empid = '" + txtEmpId.Text + "'";

con.Open();
cmd = new SqlCommand(QryStr, con);
dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
txtEmpName.Text = dr["empname"].ToString();
txtEmpAdd.Text = dr["empaddress"].ToString();
txtEmpSal.Text = dr["empsalary"].ToString();
}
else
{
txtEmpName.Focus();
}
con.Close();

Deleting Records

QryStr = "DELETE FROM employeeinfo WHERE empid = '" + txtEmpId.Text + "'";
con.Open();
cmd = new SqlCommand(QryStr, con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();