How to Insert,Update,Delete and Select Data From SQL server using C#

04/06/2017   12:19:04 PM

Hello,in this C# tutorial i will explain how to insert data in to sql server database.also modify(update and delete) and select this data and display.using very simple and basic steps.for that we will take an example in this store employee id,name,designation,salary to sql server database.and to search,update,delete this data enter employee id in textbox.

1 .Create a database name as Employee using SQL SERVER. Create a Table name as EmployeeDetails in this table create following fields

  Columns Name            

DataTypes

EmpId

Int [Identity Property Set =True]

EmpName

varchar(50)

EmpDesgn

varchar(50)

EmpSalary

money

 

2. Open VS.net / Select C# / Windows Application / Rename Form1.cs as EmployeeDetails.

 

3. Make the design of the form as Follows.

 

4. Add the following code for add,update,delete,select and display data from database

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace IUDS_SimpleCS
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=Employee;Integrated Security=True");
 
 
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
if (txtEmpName.Text == "" || txtEmpDegn.Text == "" || txtSalary.Text == "")
{
MessageBox.Show("All Fields Are Compulsory");
}
else
{
SqlCommand cmdinsert = new SqlCommand("Insert into EmployeeDetails values( ' " + txtEmpName.Text + " ','" + txtEmpDegn.Text + "','" + txtSalary.Text + "' )", con);
con.Open();
cmdinsert.CommandType = CommandType.Text;
cmdinsert.ExecuteNonQuery();
MessageBox.Show("Data Inserted");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
 
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Update");
}
else
{
SqlCommand cmdupdate = new SqlCommand("Update EmployeeDetails SET EmpName='" + txtEmpName.Text + "',EmpDesgn='" + txtEmpDegn.Text + "' ,EmpSalary='" + txtSalary.Text + "' where EmpId=" + txtempid.Text + "", con);
con.Open();
cmdupdate.CommandType = CommandType.Text;
cmdupdate.ExecuteNonQuery();
MessageBox.Show("Data Updated");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
 
private void btnDel_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Delete");
}
else
{
SqlCommand cmddel = new SqlCommand("Delete EmployeeDetails where EmpId=" + txtempid.Text + "", con);
con.Open();
cmddel.CommandType = CommandType.Text;
cmddel.ExecuteNonQuery();
MessageBox.Show("Data Deleted");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
 
 
 
private void btnSelect_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Search");
}
else
{
SqlCommand cmd = new SqlCommand("Select * From EmployeeDetails where EmpId=" + txtempid.Text + "", con);
con.Open();
 
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
txtEmpName.Text = dr[1].ToString();
txtEmpDegn.Text = dr[2].ToString();
txtSalary.Text = dr[3].ToString();
}
dr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
 
private void btnReset_Click(object sender, EventArgs e)
{
foreach (Control T in Controls)
{
if (T is TextBox)
{
T.Text = "";
}
}
}
}
}

Output

                   

Support us by Sharing