DotNet Programing Concept: CRUD Operation in Asp.net in C# using store procedure in 6 steps:-

Hello Friends,

In this Chapter we will discuss about CRUD operations of asp.net in c# using store procedure.

Step-1

Create a table like below (suppose table name is emp):

Step-2

Write the following code to create a store procedure for CRUD operation:

CREATE PROCEDURE dbo.gridoperate

@qtype varchar(10),

@id int=null,

@name varchar(50)=null,

@ads varchar(50)=null

begin

SET NOCOUNT ON

--select

if (@qtype ='select')

begin

select id,name,ads from emp

end

--insert

if(@qtype ='insert')

begin

insert into emp(name,ads)values(@name,@ads)

end

--update

if(@qtype ='update')

begin

update emp set name=@name,ads=@ads where id=@id

end

--delete

if(@qtype ='delete')

begin

delete from emp where id=@id

end

Step-3

Write the following code for Design a grid view:

<asp:GridView ID="grdview" runat="server" AutoGenerateColumns="False" DataKeyNames="id"

BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"

CellPadding="4" onrowcancelingedit="grdview_RowCancelingEdit1"

onrowediting="grdview_RowEditing1" onrowdeleting="grdview_RowDeleting"

onrowupdating="grdview_RowUpdating" ShowFooter="True" AllowPaging="True"

onpageindexchanging="grdview_PageIndexChanging">

<asp:TemplateField HeaderText="id" SortExpression="id">

<%-- <EditItemTemplate>

<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("id") %>' ReadOnly="true"></asp:TextBox>

</EditItemTemplate>--%>

<asp:Label ID="lblid" runat="server" Text='<%# Bind("id") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="name" SortExpression="name">

<asp:TextBox ID="txtname" runat="server" Text='<%# Bind("name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

</FooterTemplate>

<asp:Label ID="lblname" runat="server" Text='<%# Bind("name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="ads" SortExpression="ads">

<asp:TextBox ID="txtads" runat="server" Text='<%# Bind("ads") %>'></asp:TextBox>

</EditItemTemplate>

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

</FooterTemplate>

<asp:Label ID="lblads" runat="server" Text='<%# Bind("ads") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="edit items">

<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True"

CommandName="Update" Text="Update"></asp:LinkButton>

 <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False"

CommandName="Cancel" Text="Cancel"></asp:LinkButton>

</EditItemTemplate>

<asp:Button ID="btninsert" runat="server" onclick="btninsert_Click"

Text="insert" />

</FooterTemplate>

<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"

CommandName="Edit" Text="Edit"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

<asp:CommandField HeaderText="delete items" ShowDeleteButton="True"

ShowHeader="True" />

</Columns>

</asp:GridView>

<asp:Label ID="lblmsg" runat="server"></asp:Label>

Step-4

Impote this in code behind page:

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.Data.SqlClient;

using System.Drawing;

using System.Configuration;

Step-5

Write this code in web.config file for database connection:

</connectionStrings>

Step-6

Write this code in your code behind page for CRUD operation:

public partial class Default3 : System.Web.UI.Page

{

SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString);

DataTable dt = new DataTable();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

gridload();

}

public void gridload()

{

SqlCommand cmd = new SqlCommand("gridoperate", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@qtype ","select");

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dt);

grdview.DataSource =dt;

grdview.DataBind();

}

protected void grdview_RowEditing1(object sender, GridViewEditEventArgs e)

{

grdview.EditIndex = e.NewEditIndex; ;

gridload();

}

protected void grdview_RowCancelingEdit1(object sender, GridViewCancelEditEventArgs e)

{

grdview.EditIndex = -1;

gridload();

}

protected void grdview_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

int id = Convert.ToInt32(grdview.DataKeys[e.RowIndex].Value.ToString());

SqlCommand cmd = new SqlCommand("gridoperate", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@qtype ", "delete");

cmd.Parameters.AddWithValue("@id", id);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

adp.Fill(dt);

lblmsg.Text = "delete sucessfully............";

}

protected void grdview_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

int id = Convert.ToInt32(grdview.DataKeys[e.RowIndex].Value.ToString());

TextBox name=((TextBox)grdview.Rows[e.RowIndex].FindControl("txtname"));

TextBox ads=((TextBox)grdview.Rows[e.RowIndex].FindControl("txtads"));

SqlCommand cmd = new SqlCommand("gridoperate", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@qtype ", "update");

cmd.Parameters.AddWithValue("@id", id);

cmd.Parameters.AddWithValue("@name", name.Text);

cmd.Parameters.AddWithValue("@ads",ads.Text);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

adp.Fill(dt);

lblmsg.Text = "update sucessfully............";

}

protected void btninsert_Click(object sender, EventArgs e)

{

string name=((TextBox)grdview.FooterRow.FindControl("TextBox1")).Text;

string ads = ((TextBox)grdview.FooterRow.FindControl("TextBox2")).Text;

SqlCommand cmd = new SqlCommand("gridoperate", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@qtype ", "insert");

cmd.Parameters.AddWithValue("@name", name);

cmd.Parameters.AddWithValue("@ads", ads);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

adp.Fill(dt);

lblmsg.Text = "insert sucessfully...............";

}

protected void grdview_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grdview.PageIndex = e.NewPageIndex;

gridload();

}

After all operation out put will be look like this:

I have explained about CRUD operations of asp.net in c# using store procedure.So after read please Comments and suggestions.

Thanks & Regard

Rabi

DotNet Programing Concept

Pages

Tuesday 8 December 2015

CRUD Operation in Asp.net in C# using store procedure in 6 steps:-

No comments:

Post a Comment