latest Post

/ / / / / / / Most Frequently asked Program in Companies system-test Round perform CRUD operations using grid-view in c#

Most Frequently asked Program in Companies system-test Round perform CRUD operations using grid-view in c#

By , , , , ,
CRUD Operations also called(Create,Read,Update,Delete)

Today I will explain how to perform CRUD operations in easy way.

Add the following details into your web-config file and provide valid connection details.
<connectionStrings> <add name="constr" connectionString="Data Source=serverName; Initial Catalog=Databasename;Integrated Security=true;providerName="System.Data.SqlClient" /> 
</connectionStrings> 

Create a Table [Employee] for saving employee details.
CREATE TABLE [dbo].[Employee](
[EId] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
[Country] [varchar](50) NOT NULL
) ON [PRIMARY]
Insert records into Emloyee table for showing some default records in Grid-view
INSERT INTO Employee
SELECT 1, 'Test 1', 'UAE'
UNION ALL
SELECT 2, 'Test 2', 'UK'
UNION ALL
SELECT 3, 'Test 3', 'US'
UNION ALL
SELECT 4, 'Test 4', 'IN'
create a procedure for handling CRUD operations
CREATE PROCEDURE [dbo].[Employee_CRUD]
@Action VARCHAR(10)
,@EId INT = NULL
,@Name VARCHAR(100) = NULL
,@Country VARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;

--SELECT
IF @Action = 'SELECT'
BEGIN
SELECT EId, Name, Country 
FROM Employee
END

--INSERT
IF @Action = 'INSERT'
BEGIN
INSERT INTO Employee (Name, Country) 
VALUES (@Name, @Country)
END

--UPDATE
IF @Action = 'UPDATE'
BEGIN
UPDATE Employee 
SET Name = @Name, Country = @Country 
WHERE EId = @EId
END

--DELETE
IF @Action = 'DELETE'
BEGIN
DELETE FROM Employee 
WHERE EId = @EId
END
END

 Now create CRUD.aspx file
<%@ Page Language="C#" AutoEventWireup="true" 
CodeFile="CRUD.aspx.cs" Inherits="CS" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
table
{
border: 1px solid #ccc;
width: 450px;
margin-bottom: -1px;
}
table th
{
background-color: #F7F7F7;
color: #333;
font-weight: bold;
}
table th, table td
{
padding: 5px;
border-color: #ccc;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView ID="gvEmpCrud" runat="server" 
AutoGenerateColumns="false" DataKeyNames="EId"
OnRowDataBound="OnRowDataBound" 
OnRowEditing="OnRowEditing" 
OnRowCancelingEdit="OnRowCancelingEdit"
OnRowUpdating="OnRowUpdating" 
OnRowDeleting="OnRowDeleting" 
EmptyDataText="No records has been added.">
<Columns>
<asp:TemplateField HeaderText="Name" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblName" runat="server" Text='<%# Eval("Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country" ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Eval("Country") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Link" ShowEditButton="true" ShowDeleteButton="true"
ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse">
<tr>
<td style="width: 150px">
Name:<br />
<asp:TextBox ID="txtName" runat="server" Width="140" />
</td>
<td style="width: 150px">
Country:<br />
<asp:TextBox ID="txtCountry" runat="server" Width="140" />
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
</td>
</tr>
</table>
</form>
</body>
</html>

use following namespaces
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

Now add the following methods into Your Code behind file CRUD.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}

private void BindGrid()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Employee_CRUD"))
{
cmd.Parameters.AddWithValue("@Action", "SELECT");
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvEmpCrud.DataSource = dt;
gvEmpCrud.DataBind();
}
}
}
}
}

protected void Insert(object sender, EventArgs e)
{
string name = txtName.Text;
string country = txtCountry.Text;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Employee_CRUD"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "INSERT");
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
this.BindGrid();
}

protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
gvEmpCrud.EditIndex = e.NewEditIndex;
this.BindGrid();
}

protected void OnRowCancelingEdit(object sender, EventArgs e)
{
gvEmpCrud.EditIndex = -1;
this.BindGrid();
}

protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = gvEmpCrud.Rows[e.RowIndex];
int eId = Convert.ToInt32(gvEmpCrud.DataKeys[e.RowIndex].Values[0]);
string name = (row.FindControl("txtName") as TextBox).Text;
string country = (row.FindControl("txtCountry") as TextBox).Text;
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Employee_CRUD"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "UPDATE");
cmd.Parameters.AddWithValue("@EId", eId);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
gvEmpCrud.EditIndex = -1;
this.BindGrid();
}

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow && e.Row.RowIndex != gvEmpCrud.EditIndex)
{
(e.Row.Cells[2].Controls[2] as LinkButton).Attributes["onclick"] = "return confirm('Do you want to delete this row?');";
}
}

protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
int eId = Convert.ToInt32(gvEmpCrud.DataKeys[e.RowIndex].Values[0]);
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("Employee_CRUD"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "DELETE");
cmd.Parameters.AddWithValue("@EId", eId);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
this.BindGrid();
}

Click Here to Read Top 10 SqlServer Stored Procedure Interview Questions Answers For Freshers

About Mallikarjun A

Mallikarjun A
Recommended Posts × − +

0 comments:

Post a Comment

Subscribe to: Post Comments ( Atom )