Hi in this blog we are going to see how perform CRUD(create,update and delete) operation using C# and Sql Stored Procedure.Now open your visual studio and create a New Project and design a form like this.
Download Source Code
Now open you MS Sql and create a new database called "TestDB" and copy paste the below code in SQL Query Editor Window.
USE [TestDB]
GO
/****** Object: Table [dbo].[tblCRUD] Script Date: 10/01/2016 21:56:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblCRUD](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](25) NULL,
[department] [nvarchar](25) NULL,
[year] [int] NULL,
[place] [nvarchar](25) NULL,
CONSTRAINT [PK_tblCRUD] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
After you created the table Copy and paste below code in the another query editor for Window. The following stored procedure performs our Create,update and delete operation.
USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spCRUDDEMO] Script Date: 10/01/2016 21:58:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRUDDEMO]
@Action varchar(10) = null,
@id int = null,
@name nvarchar(25) = null,
@department nvarchar(25) = null,
@year nvarchar(25) = null,
@place nvarchar(25) = null
AS
BEGIN
SET NOCOUNT ON;
--SELECT --
IF @Action = 'SELECT'
BEGIN
SELECT id,name,department,year,place from tblCRUD
END
-- UPDATE --
IF @Action = 'UPDATE'
BEGIN
Update tblCRUD set name = @name, department = @department ,year = @year ,
place = @place where id = @id
END
IF @Action = 'INSERT'
BEGIN
Insert into tblCRUD (name,department,year,place) values(@name,@department,@year,@place)
END
--DELETE--
IF @Action = 'DELETE'
BEGIN
Delete From tblCRUD where id = @id;
END
END
Switch to your Visual Studio and copy paste the below code.
Full Source Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace test
{
public partial class CRUD : Form
{
int index = 0;
public CRUD()
{
InitializeComponent();
}
private void CRUD_Load(object sender, EventArgs e)
{
UpdateGrid();
}
private void btnInsert_Click(object sender, EventArgs e)
{
try
{
SqlConnection constr = Constr();
using (SqlCommand cmd = new SqlCommand("spCRUDDEMO"))
{
cmd.Connection = constr;
constr.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "INSERT");
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@department", txtDept.Text.ToString());
cmd.Parameters.AddWithValue("@year", txtYear.Text.ToString());
cmd.Parameters.AddWithValue("@place", txtPlace.Text.ToString());
cmd.ExecuteNonQuery();
constr.Close();
MessageBox.Show("Data Inserted Successfully...");
UpdateGrid();
clearData();
}
}
catch (Exception ex)
{
MessageBox.Show("Exception:"+ex.ToString());
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
SqlConnection constr = Constr();
using (SqlCommand cmd = new SqlCommand("spCRUDDEMO"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = constr;
cmd.Parameters.AddWithValue("@Action", "DELETE");
constr.Open();
cmd.Parameters.AddWithValue("@id", index);
cmd.ExecuteNonQuery();
constr.Close();
MessageBox.Show("Record Deleted Successfully...");
UpdateGrid();
clearData();
}
}
/*Create Connection String*/
private SqlConnection Constr()
{
string sqlConnection = @"Data Source=(local);Initial Catalog=TestDB;Integrated Security=True";
SqlConnection constr = new SqlConnection(sqlConnection);
return constr;
}
/*Update DataGrid*/
private void UpdateGrid()
{
SqlConnection constr = Constr();
constr.Open();
SqlCommand cmd = new SqlCommand("spCRUDDEMO");
cmd.Parameters.AddWithValue("@Action", "SELECT");
using(SqlDataAdapter sda = new SqlDataAdapter()){
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = constr;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
/*Clear date*/
private void clearData()
{
txtName.Text = string.Empty;
txtDept.Text = string.Empty;
txtYear.Text = string.Empty;
txtPlace.Text = string.Empty;
}
private void btnUpdate_Click(object sender, EventArgs e)
{
SqlConnection constr = Constr();
using (SqlCommand cmd = new SqlCommand("spCRUDDEMO"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = constr;
constr.Open();
cmd.Parameters.AddWithValue("@Action", "UPDATE");
cmd.Parameters.AddWithValue("@id", index);
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@department", txtDept.Text.ToString());
cmd.Parameters.AddWithValue("@year", txtYear.Text.ToString());
cmd.Parameters.AddWithValue("@place", txtPlace.Text.ToString());
cmd.ExecuteNonQuery();
constr.Close();
MessageBox.Show("Data updated Successfully...");
UpdateGrid();
clearData();
}
}
private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
try
{
index = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
txtName.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
txtDept.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
txtYear.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
txtPlace.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
}
catch (Exception ex)
{
}
}
}
}
Download Source Code
No comments:
Post a Comment