There are various ways through which you can update data from front end to back end. In this article we are going to learn how to update data using the OpenXML method of SQL Server 2005 and by using DataAdapter in ASP.Net. We'll see this step by step; first we will use the OpenXML method to update data in SQL Server by using ASP.Net application.
For this we need to create a stored procedure which will accept XML data and by using OpenXML method we will update data into the database.
To perform the following task we need to create a table in SQL Server as like this.
CREATE TABLE [dbo].[Product](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CostPrice] [int] NULL,
[SellPrice] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Insert some records in the table where you'll not specify the cost price and sellprice of the product; just specify the productname value.
Insert into Product(ProductName) values(Watch)
Insert into Product(ProductName) values(Pencile)
Insert into Product(ProductName) values(Soap)
I have created a table named Product in SQL Server 2005 with product id as an Identity column. Now we'll create a stored procedure.
CREATE PROCEDURE PrcInsert
(
@xmldoc xml
)
AS
begin
declare @doc int
exec sp_xml_preparedocument @doc output,@xmldoc
update Product
set CostPrice=ox.CostPrice,
SellPrice=ox.SellPrice
from openxml(@doc,'NewDataSet/Table1',2)
with(ProductId int,CostPrice int,SellPrice int)ox
where Product.ProductId=ox.ProductId
exec sp_xml_removedocument @doc
end
Now we have done with the database part.
For connecting your application with the back end we need to create a connection in web.config file.
<connectionStrings>
<add name="constr" connectionString="Data Source=D-NIIT-MR07;Initial Catalog=Vishal;User Id=sa;word=faculty" providerName="System.Data.SqlClient"/>
</connectionStrings>
Now design your application like the following.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td style="height: 90px" colspan="3">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductId">
<Columns>
<asp:TemplateField HeaderText="ProductId">
<ItemTemplate>
<asp:Label ID="lblproductid" runat="server" Text='<%# Eval("ProductId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ProductName">
<ItemTemplate>
<asp:Label ID="lblproductname" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CostPrice">
<ItemTemplate>
<asp:TextBox ID="txtcostprice" runat="server" Text='<%# Eval("CostPrice") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SellPrice">
<ItemTemplate>
<asp:TextBox ID="txtsellprice" runat="server" Text='<%# Eval("SellPrice") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td style="height: 19px" colspan="3">
<asp:Button ID="btnupdate" runat="server" OnClick="btnupdate_Click" Text="Update"
Width="80px" /></td>
</tr>
<tr>
<td style="height: 18px;" colspan="3">
<asp:GridView ID="GridView2" runat="server">
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Now the code behind for it.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Schema;
public partial class _Default : System.Web.UI.Page
{
string dbcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection con;
string xmlData;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
protected void btnupdate_Click(object sender, EventArgs e)
{
SaveData();
}
public void bind()
{
con = new SqlConnection(dbcon);
con.Open();
SqlCommand cmd = new SqlCommand("select * from product", con);
SqlDataReader rd = cmd.ExecuteReader();
GridView1.DataSource = rd;
GridView1.DataBind();
con.Close();
}
public void SaveData()
{
con = new SqlConnection(dbcon);
con.Open();
int txtCostPrice;
int txtSellPrice;
int Primarykey;
string ProductName;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
//Adding columns to the datatable.
dt.Columns.Add("ProductId", typeof(int));
dt.Columns.Add("ProductName", typeof(string));
dt.Columns.Add("CostPrice", typeof(int));
dt.Columns.Add("SellPrice", typeof(int));
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Primarykey = Convert.ToInt32(GridView1.DataKeys[i].Value);
ProductName = ((Label)GridView1.Rows[i].FindControl("lblproductname")).Text;
txtCostPrice = Convert.ToInt32(((TextBox)GridView1.Rows[i].FindControl("txtcostprice")).Text);
txtSellPrice = Convert.ToInt32(((TextBox)GridView1.Rows[i].FindControl("txtsellprice")).Text);
DataRow dr = dt.NewRow();
dr["ProductId"] = Primarykey;
dr["ProductName"] = ProductName;
if (txtCostPrice.Equals(string.Empty))
dr["CostPrice"] = 0;
else
dr["CostPrice"] = txtCostPrice;
if (txtSellPrice.Equals(string.Empty))
dr["SellPrice"] = 0;
else
dr["SellPrice"] = txtSellPrice;
dt.Rows.Add(dr);
}
con.Close();
ds.Tables.Add(dt);
//Reading the data from the dataset and storing it in xmlData variable.
xmlData = ds.GetXml();
//Calling the CallProc method ing xmldata as a parameter to it.
CallProc(xmlData);
}
public void CallProc(string xmldoc)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("prcInsert", con);
cmd.CommandType = CommandType.StoredProcedure;
//Calling the procedure.
cmd.Parameters.AddWithValue("@xmldoc", xmldoc);
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
con.Close();
string script = "<script>alert('Updated Successfully')</script>";
//Adding client side script to the page.
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "Updated", script);
bind();
}
}
catch (Exception e)
{
Response.Write(e.Message);
}
}
}
Now it's time to run the application.
For the first time when you run this application you will see the following.
Whereby all the records have zero for the cost and sell price. Now if a user wants to update all the rows on a single click event he just needs to put the values in the desired textboxes and on a single click his data will be updated through stored procedure which we created during this session.
To test the functionality enter some values in the textboxes and click on the update button.
And your records are updated…
The 2nd way of updating the data is by using a DataAdapter.
I'm taking the same form for the application and the same database.
Design the same application following the source code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductId">
<Columns>
<asp:TemplateField HeaderText="ProductId">
<ItemTemplate>
<asp:Label ID="lblproductid" runat="server" Text='<%# Eval("ProductId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ProductName">
<ItemTemplate>
<asp:Label ID="lblproductname" runat="server" Text='<%# Eval("ProductName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CostPrice">
<ItemTemplate>
<asp:TextBox ID="txtcostprice" runat="server" Text='<%# Eval("CostPrice") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="SellPrice">
<ItemTemplate>
<asp:TextBox ID="txtsellprice" runat="server" Text='<%# Eval("SellPrice") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btnupdate" runat="server" OnClick="btnupdate_Click" Text="Update" /><br />
</div>
</form>
</body>
</html>
Now is the code behind file for it.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
string dbcon = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection con;
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
}
}
public void bind()
{
con = new SqlConnection(dbcon);
con.Open();
SqlCommand cmd = new SqlCommand("Select * from Product", con);
da.SelectCommand = cmd;
ds = new DataSet();
da.Fill(ds,"Product");
GridView2.DataSource = ds.Tables["Product"].DefaultView;
GridView2.DataBind();
con.Close();
}
public void savedata()
{
con = new SqlConnection(dbcon);
con.Open();
int txtCostPrice=0;
int txtSellPrice=0;
int Primarykey=0;
string ProductName="";
DataTable dt = new DataTable();
dt.Columns.Add("ProductId", typeof(int));
dt.Columns.Add("ProductName", typeof(string));
dt.Columns.Add("CostPrice", typeof(int));
dt.Columns.Add("SellPrice", typeof(int));
da = new SqlDataAdapter("Select * from Product", con);
SqlCommandBuilder cmb = new SqlCommandBuilder(da);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds = new DataSet();
da.Fill(ds, "Product");
for (int i = 0; i < GridView2.Rows.Count; i++)
{
Primarykey = Convert.ToInt32(GridView2.DataKeys[i].Value);
ProductName = ((Label)GridView2.Rows[i].FindControl("lblproductname")).Text;
txtCostPrice = Convert.ToInt32(((TextBox)GridView2.Rows[i].FindControl("txtcostprice")).Text);
txtSellPrice = Convert.ToInt32(((TextBox)GridView2.Rows[i].FindControl("txtsellprice")).Text);
DataRow dr = ds.Tables["Product"].Rows[i];
if (txtCostPrice.Equals(string.Empty))
dr["CostPrice"] = 0;
else
dr["CostPrice"] = txtCostPrice;
if (txtSellPrice.Equals(string.Empty))
dr["SellPrice"] = 0;
else
dr["SellPrice"] = txtSellPrice;
da.Update(ds, "Product");
}
con.Close();
bind();
}
protected void btnupdate_Click(object sender, EventArgs e)
{ savedata();
}
}
Hope this has added and advantage to your knowledge and you've liked the application.
With Regards,
Vishal Gilbile.