Build a Webform using ASP.NET Insert/update data into a MS SQL table

Kindly go through the below

1. First you need to create a table in Database like below

Column Name Data Type Allow Nulls
productid Int(IDENTITY=TRUE) Yes
productname varchar(50) Yes
price varchar(50) Yes

2. Now create one new stored procedure “Crudoperations”

 CREATE PROCEDURE CrudOperations 
@productid int = 0, 
@productname varchar(50)=null, 
@price int=0, 
@status varchar(50) 
AS 
BEGIN 
SET NOCOUNT ON; 
--- Insert New Records 
IF @status='INSERT' 
BEGIN 
INSERT INTO productinfo1(productname,price) VALUES(@productname,@price) 
END 
--- Select Records in Table 
IF @status='SELECT' 
BEGIN 
SELECT productid,productname,price FROM productinfo1 
END 
--- Update Records in Table  
IF @status='UPDATE' 
BEGIN 
UPDATE productinfo1 SET productname=@productname,price=@price WHERE productid=@productid 
END 
--- Delete Records from Table 
IF @status='DELETE' 
BEGIN 
DELETE FROM productinfo1 where productid=@productid 
END 
SET NOCOUNT OFF 
END

3. Now open your .aspx Page and write a code like below

 <html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>GridView Crud (Select Insert Edit Update Delete) Operations using Single Stored Procedure in ASP.Net</title>
<style type="text/css">
.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, Helevetica, sans-serif; color: #303933;}
.headerstyle
{
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="GridviewDiv">
<asp:GridView runat="server" ID="gvDetails" ShowFooter="true" AllowPaging="true" PageSize="10" AutoGenerateColumns="false" DataKeyNames="productid,productname" OnPageIndexChanging="gvDetails_PageIndexChanging" OnRowCancelingEdit="gvDetails_RowCancelingEdit"
OnRowEditing="gvDetails_RowEditing" OnRowUpdating="gvDetails_RowUpdating" OnRowDeleting="gvDetails_RowDeleting" OnRowCommand ="gvDetails_RowCommand" >
<HeaderStyle CssClass="headerstyle" />
<Columns>
<asp:BoundField DataField="productid" HeaderText="Product Id" ReadOnly="true" />
<asp:TemplateField HeaderText="Product Name">
<ItemTemplate>
<asp:Label ID="lblProductname" runat="server" Text='<%# Eval("productname")%>'/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductname" runat="server" Text='<%# Eval("productname")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtpname" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText = "Price">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%# Eval("price")%>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtProductprice" runat="server" Text='<%# Eval("price")%>'/>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtprice" runat="server" />
<asp:Button ID="btnAdd" 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>

4. After completion of aspx page add following namespaces in codebehind

using System;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;

5. After completion of adding namespaces you need to write the code like as shown below

 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=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", 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)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else {
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "No Records Found";
}
}
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtname = (TextBox)gvDetails.FooterRow.FindControl("txtpname");
TextBox txtprice = (TextBox)gvDetails.FooterRow.FindControl("txtprice");
crudoperations("INSERT", txtname.Text, txtprice.Text, 0);
}
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
BindGridview();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
BindGridview();
}
protected void gvDetails_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
BindGridview();
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
TextBox txtname = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductname");
TextBox txtprice = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtProductprice");
crudoperations("UPDATE",txtname.Text,txtprice.Text,productid);
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int productid = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["productid"].ToString());
string productname = gvDetails.DataKeys[e.RowIndex].Values["productname"].ToString();
crudoperations("DELETE",productname,"",productid);
}
protected void crudoperations(string status, string productname, string price, int productid)
{
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("crudoperations", con);
cmd.CommandType= CommandType.StoredProcedure;
if(status=="INSERT")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
}
else if(status=="UPDATE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productname",productname);
cmd.Parameters.AddWithValue("@price",price);
cmd.Parameters.AddWithValue("@productid",productid);
}
else if(status=="DELETE")
{
cmd.Parameters.AddWithValue("@status",status);
cmd.Parameters.AddWithValue("@productid",productid);
}
cmd.ExecuteNonQuery();
lblresult.ForeColor = Color.Green;
lblresult.Text = productname+" details "+status.ToLower()+"d successfully";
gvDetails.EditIndex = -1;
BindGridview();
}
}

ASKER

where we add the add namespaces, just after loading default page?

On the top your back end code i.e. in aspx.cs file

ASKER

ASKER

first step

ASKER

please see my screenshot. where I'm I start with. Sorry, I'm a beginner for this.
2.PNG

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

great help. thank you.

ASKER

How to run this application, can you please provide me the step by step guide?

Hit F5 to build and run the project in debug mode.

ASKER

Here what I'm getting after Hit F5.
Doc1.docx

ASKER

any update on this Manoj