Monday, 2 May 2016

curd operation in gridview using stored procedure

Here we will learn curd operation in gridview using stored procedure in asp.net .
Before implement this application first we will design the database table name is 'CollageInfo' and then stored Procedure name is CurdOperationInGridView.
Create Database Table and Stored procedure like following if you needed

CREATE TABLE CollageInfo
(
Collageid int identity  PRIMARY KEY ,
Collagename nvarchar(50),
CollageRank double
)
GO

CREATE PROCEDURE CrudOperationsInGridView
@Collageid int = 0,
@Collagename varchar(50)=null,
@CollageRank int=0,
@status varchar(50)
AS
BEGIN
SET NOCOUNT ON;
--- Insert New Records For GridView
IF @status='INSERT'
BEGIN
INSERT INTO CollageInfo(Collagename,CollageRank) VALUES(@Collagename,@CollageRank)
END
--- Select Records in Table For GridView
IF @status='SELECT'
BEGIN
SELECT Collageid,Collagename,CollageRank FROM CollageInfo
END
--- Update Records in Table  For GridView
IF @status='UPDATE'
BEGIN
UPDATE CollageInfo SET @Collagename=@Collagename,CollageRank=@CollageRank WHERE Collageid=@Collageid
END
--- Delete Records from Table For GridView
IF @status='DELETE'
BEGIN
DELETE FROM CollageInfo where Collageid=@Collageid
END
SET NOCOUNT OFF
END

Once we Create stored procedure in database then add an aspx page and write the following code on .aspx Page.
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud  Operations using Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDesign {font-size: 100%; font-family: 'Trebuchet MS', 'Lucida Sans Unicode', 'Lucida Grande', 'Lucida Sans', Arial, sans-serif; color: #303933;}
.headerstyleForGrid
{
color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;background-color: #df5015;padding:0.5em 0.5em 0.5em 0.5em;text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div class="GridviewDesign">
<asp:GridView runat="server" ID="gvDetailsForCurdOperation" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="Collageid,Collagename" OnPageIndexChanging="gvDetailsForCurdOperation_PageIndexChanging" OnRowCancelingEdit="gvDetailsForCurdOperation_RowCancelingEdit"
OnRowEditing="gvDetailsForCurdOperation_RowEditing" OnRowUpdating="gvDetailsForCurdOperation_RowUpdating" OnRowDeleting="gvDetailsForCurdOperation_RowDeleting" OnRowCommand ="gvDetailsForCurdOperation_RowCommand" >
<HeaderStyle CssClass="headerstyleForGrid" />
<Columns>
<asp:BoundField DataField="Collageid" HeaderText="Collage Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Collage Name">
<ItemTemplate>
<asp:Label ID="lblCollagename" runat="server" Text='<%# Eval("Collagename")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollagename" runat="server" Text='<%# Eval("Collagename")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Collage Rank">
<ItemTemplate>
<asp:Label ID="lblCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" Text='<%# Eval("CollageRank")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCollageRank" runat="server" />
<asp:Button ID="btnAddNewItem" CommandName="AddNew" runat="server" Text="Add" />
</FooterTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" ShowDeleteButton="true" />
</Columns>
</asp:GridView>
<asp:Label ID="lblresult" runat="server"></asp:Label>
</div>
</form>
</body>
</html>

After completion code for .aspx page write the following code on .aspx.cs page
protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGridview();
            }
        }

        protected void BindGridview()
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation"))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@status", "SELECT");
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                con.Close();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    gvDetailsForCurdOperation.DataSource = ds;
                    gvDetailsForCurdOperation.DataBind();
                }
                else
                {
                    ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                    gvDetailsForCurdOperation.DataSource = ds;
                    gvDetailsForCurdOperation.DataBind();
                    int columncount = gvDetailsForCurdOperation.Rows[0].Cells.Count;
                    gvDetailsForCurdOperation.Rows[0].Cells.Clear();
                    gvDetailsForCurdOperation.Rows[0].Cells.Add(new TableCell());
                    gvDetailsForCurdOperation.Rows[0].Cells[0].ColumnSpan = columncount;
                    gvDetailsForCurdOperation.Rows[0].Cells[0].Text = "No Records Found";
                }
            }
        }

        protected void gvDetailsForCurdOperation_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("AddNew"))
            {
                TextBox txtname = (TextBox)gvDetailsForCurdOperation.FooterRow.FindControl("txtpname");
                TextBox txtprice = (TextBox)gvDetailsForCurdOperation.FooterRow.FindControl("txtprice");
                crudoperationsForGrid("INSERT", txtname.Text, txtprice.Text, 0);
            }
        }

        protected void gvDetailsForCurdOperation_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvDetailsForCurdOperation.EditIndex = e.NewEditIndex;
            BindGridview();
        }

        protected void gvDetailsForCurdOperation_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gvDetailsForCurdOperation.EditIndex = -1;
            BindGridview();
        }

        protected void gvDetailsForCurdOperation_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvDetailsForCurdOperation.PageIndex = e.NewPageIndex;
            BindGridview();
        }

        protected void gvDetailsForCurdOperation_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
            TextBox txtname = (TextBox)gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollagename");
            TextBox txtCollageRank = (TextBox)gvDetailsForCurdOperation.Rows[e.RowIndex].FindControl("txtCollageRank");
            crudoperationsForGrid("UPDATE", txtname.Text, txtCollageRank.Text, Collageid);
        }

        protected void gvDetailsForCurdOperation_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int Collageid = Convert.ToInt32(gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collageid"].ToString());
            string Collagename = gvDetailsForCurdOperation.DataKeys[e.RowIndex].Values["Collagename"].ToString();
            crudoperationsForGrid("DELETE", Collagename, "", Collageid);
        }

        protected void crudoperationsForGrid(string status, string Collagename, string price, int Collageid)
        {
            using (SqlConnection con = new SqlConnection("Data Source=Munesh;Integrated Security=true;Initial Catalog=GridCurdOperation"))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("CrudOperationsInGridView", con);
                cmd.CommandType = CommandType.StoredProcedure;
                if (status == "INSERT")
                {
                    cmd.Parameters.AddWithValue("@status", status);
                    cmd.Parameters.AddWithValue("@Collagename", Collagename);
                    cmd.Parameters.AddWithValue("@CollageRank", price);
                }
                else if (status == "UPDATE")
                {
                    cmd.Parameters.AddWithValue("@status", status);
                    cmd.Parameters.AddWithValue("@Collagename", Collagename);
                    cmd.Parameters.AddWithValue("@CollageRank", price);
                    cmd.Parameters.AddWithValue("@Collageid", Collageid);
                }
                else if (status == "DELETE")
                {
                    cmd.Parameters.AddWithValue("@status", status);
                    cmd.Parameters.AddWithValue("@Collageid", Collageid);
                }
                cmd.ExecuteNonQuery();
                lblresult.ForeColor = Color.Green;
                lblresult.Text = Collagename + " details " + status.ToLower() + "d successfully";
                gvDetailsForCurdOperation.EditIndex = -1;
                BindGridview();
            }
        }


Now  run your application and see the output and perform Curd operation For Gridview.

 You can download semple code from Here Download
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

C# program Selection Sorting

Selection sort is a straightforward sorting algorithm. This algorithm search for the smallest number in the elements array and then swap i...

  • SQL server database connection in Window Form
                                                                                                                                              ...
  • Interview Question on Asp.Net
                                                                                                                                             ...
  • Important SQL Query
                                                                                                                                             ...