//Design gridview
DataTable dt = new DataTable();
DataRow dr = null;
dt.Columns.Add(new DataColumn("CustName", typeof(string)));
dt.Columns.Add(new DataColumn("CustPosition", typeof(string)));
dt.Columns.Add(new DataColumn("CustCity", typeof(string)));
dt.Columns.Add(new DataColumn("CustState", typeof(string)));
gvCustomer.DataSource = dt;
gvCustomer.DataBind();
//Add button Insert to gridview
private void Insert()
{
DataTable dt = new DataTable();
DataRow dr = null;
dr = dt.NewRow();
dr["CustName"] = txtName.Text.ToString();
dr["CustPosition"] = txtPos.Text.ToString();
dr["CustCity"] = txtCity.Text.ToString();
dr["CustState"] = txtState.Text.ToString();
dt.Rows.Add(dr);
gvCustomer.DataSource = dt;
gvCustomer.DataBind();
}
//Save button using string builder
protected void btnSave_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
sb.Append("<root>");
for (int i = 0; i < gvCustomer.Rows.Count; i++)
{
string Name= gvCustomer.Rows[i].Cells[0].Text.ToString();
string Position = gvCustomer.Rows[i].Cells[1].Text.ToString();
string City = gvCustomer.Rows[i].Cells[2].Text.ToString();
string State= gvCustomer.Rows[i].Cells[3].Text.ToString();
if(Name != "")
sb.Append("<row Name='" + Name + "' Position='" + Position + "' City='" + City + "' State='" + State + "'/>");
}
sb.Append("</root>");
string conStr = WebConfigurationManager.
ConnectionStrings["BlogConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(conStr);
//InsertCustomer as Stored Procedure
SqlCommand cmd = new SqlCommand("InsertCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@XMLCustomer", sb.ToString());
try
{
using (con)
{
con.Open();
cmd.ExecuteNonQuery();
}
lblError.Text = "Record(s) Inserted successfully";
lblError.ForeColor = System.Drawing.Color.Green;
}
catch (Exception ex)
{
lblError.Text = "Error Occured";
lblError.ForeColor = System.Drawing.Color.Red;
}
}
//Stored Procedure Insert Query in Sql server
CREATE PROCEDURE [dbo].[InsertCustomer]
(
@XMLCustomer XML
)
AS
BEGIN
INSERT INTO CUSTOMER
(CustName,CustPosition,CustCity,CustState)
SELECT
TempCustomer.Item.value('@Name', 'VARCHAR(50)'),
TempCustomer.Item.value('@Position', 'VARCHAR(50)'),
TempCustomer.Item.value('@City', 'VARCHAR(50)'),
TempCustomer.Item.value('@State', 'VARCHAR(50)')
FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)
RETURN 0
END
//Upadate button Same Process of Save button differnt stored procedure
//Stored Procedure Update Query in Sql server
CREATE PROCEDURE [dbo].[UpdateCustomer]
(
@XMLCustomer XML
)
AS
BEGIN
UPDATE Customer
SET CustName=TempCustomer.Item.value('@Name', 'VARCHAR(50)'),
CustPosition=TempCustomer.Item.value('@Position', 'VARCHAR(50)'),
CustCity=TempCustomer.Item.value('@City', 'VARCHAR(50)'),
CustState=TempCustomer.Item.value('@State', 'VARCHAR(50)')
FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)
WHERE CustID=TempCustomer.Item.value('@CustID', 'INT')
RETURN 0
END
No comments:
Post a Comment