Temporarily Save Data in a Datatable Before Inserting Or Updating into SQL SERVER Database.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace TestApp
{
public partial class dt : System.Web.UI.Page
{
DataTable dt1 = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings
["empConnectionString"].ToString());
SqlDataAdapter da = new SqlDataAdapter("select * from emp", cn);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
createDataTable();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = dt1.NewRow();
string fdf = ds.Tables[0].Rows[i][0].ToString();
dr["empno"] = ds.Tables[0].Rows[i][0].ToString();
dr["ename"] = ds.Tables[0].Rows[i][1].ToString();
dr["address"] = ds.Tables[0].Rows[i][2].ToString();
dr["city"] = ds.Tables[0].Rows[i][3].ToString();
dr["state"] = ds.Tables[0].Rows[i][4].ToString();
dr["zip"] = ds.Tables[0].Rows[i][5].ToString();
dr["upd"] = "0";
dr["nc"] = "0";
dt1.Rows.Add(dr);
}
dg1.DataSource = dt1;
dg1.DataBind();
dt1.AcceptChanges();
dt1.GetChanges();
this.ViewState.Add("table", dt1);
}
}
protected void dg1_ItemCommand(object source, DataGridCommandEventArgs e)
{
hidindex.Value = e.Item.ItemIndex.ToString();
if (e.CommandName == "edit")
{
TextBox1.Text = e.Item.Cells[1].Text.ToString();
TextBox2.Text = e.Item.Cells[2].Text.ToString();
TextBox3.Text = e.Item.Cells[3].Text.ToString();
TextBox4.Text = e.Item.Cells[4].Text.ToString();
TextBox5.Text = e.Item.Cells[5].Text.ToString();
}
}
protected void btnedit_Click(object sender, EventArgs e)
{
int ind = int.Parse(hidindex.Value.ToString());
dt1 = (DataTable)(this.ViewState["table"]);
DataRow dr = dt1.NewRow();
dr["ename"] = TextBox1.Text;
dr["address"] = TextBox2.Text;
dr["city"] = TextBox3.Text;
dr["state"] = TextBox4.Text;
dr["zip"] = TextBox5.Text;
dr["upd"] = "1";
dr["nc"] = "0";
dr["empno"] = dt1.Rows[ind]["empno"].ToString();
dt1.Rows.RemoveAt(ind);
dt1.Rows.InsertAt(dr, ind);
dt1.AcceptChanges();
dt1.GetChanges();
dg1.DataSource = dt1;
dg1.DataBind();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
}
protected void Button1_Click(object sender, EventArgs e)
{
dt1 = (DataTable)(this.ViewState["table"]);
DataRow dr = dt1.NewRow();
dr["ename"] = TextBox1.Text;
dr["address"] = TextBox2.Text;
dr["city"] = TextBox3.Text;
dr["state"] = TextBox4.Text;
dr["zip"] = TextBox5.Text;
dr["upd"] = "0";
dr["nc"] = "1";
dt1.Rows.Add(dr);
dt1.AcceptChanges();
dt1.GetChanges();
dg1.DataSource = dt1;
dg1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings
["empConnectionString"].ToString());
dt1 = (DataTable)(ViewState["table"]);
for (int i = 0; i < dt1.Rows.Count; i++)
{
string no = dt1.Rows[i]["empno"].ToString();
string st = dt1.Rows[i]["nc"].ToString();
if (dt1.Rows[i]["nc"].ToString() == "1")
{
SqlCommand cmd = new SqlCommand("insert into emp(ename,address,city,state,zip) values(@ename,@address,@city,@state,@zip)", cn);
SqlParameter p1 = new SqlParameter("@ename", SqlDbType.VarChar, 50);
p1.Value = dt1.Rows[i]["ename"].ToString();
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@address", SqlDbType.VarChar, 50);
p2.Value = dt1.Rows[i]["address"].ToString();
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@city", SqlDbType.VarChar, 50);
p3.Value = dt1.Rows[i]["city"].ToString();
cmd.Parameters.Add(p3);
SqlParameter p4 = new SqlParameter("@state", SqlDbType.VarChar, 50);
p4.Value = dt1.Rows[i]["state"].ToString();
cmd.Parameters.Add(p4);
SqlParameter p5 = new SqlParameter("@zip", SqlDbType.Int);
p5.Value = int.Parse(dt1.Rows[i]["zip"].ToString());
cmd.Parameters.Add(p5);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
if (dt1.Rows[i]["upd"].ToString() == "1" && dt1.Rows[i]["nc"].ToString() == "0")
{
SqlCommand cmd = new SqlCommand("update emp set ename=@ename,address=@address,city=@city,state=@state,zip=@zip where empno=@empno", cn);
string sdf = dg1.DataKeys[0].ToString();
string ddd = dt1.Rows[i]["empno"].ToString();
SqlParameter p1 = new SqlParameter("@ename", SqlDbType.VarChar, 50);
p1.Value = dt1.Rows[i]["ename"].ToString();
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@address", SqlDbType.VarChar, 50);
p2.Value = dt1.Rows[i]["address"].ToString();
cmd.Parameters.Add(p2);
SqlParameter p3 = new SqlParameter("@city", SqlDbType.VarChar, 50);
p3.Value = dt1.Rows[i]["city"].ToString();
cmd.Parameters.Add(p3);
SqlParameter p4 = new SqlParameter("@state", SqlDbType.VarChar, 50);
p4.Value = dt1.Rows[i]["state"].ToString();
cmd.Parameters.Add(p4);
SqlParameter p5 = new SqlParameter("@zip", SqlDbType.Int);
p5.Value = int.Parse(dt1.Rows[i]["zip"].ToString());
cmd.Parameters.Add(p5);
SqlParameter p6 = new SqlParameter("@empno", SqlDbType.Int);
p6.Value = int.Parse(dt1.Rows[i]["empno"].ToString());
cmd.Parameters.Add(p6);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
}
}
protected void createDataTable()
{
DataColumn c0 = new DataColumn("empno",typeof (Int32));
DataColumn c1 = new DataColumn("ename",typeof (string));
DataColumn c2 = new DataColumn("address");
DataColumn c3 = new DataColumn("city");
DataColumn c4 = new DataColumn("state");
DataColumn c5 = new DataColumn("zip");
DataColumn c6 = new DataColumn("upd");
DataColumn c7 = new DataColumn("nc");
dt1.Columns.Add(c0);
dt1.Columns.Add(c1);
dt1.Columns.Add(c2);
dt1.Columns.Add(c3);
dt1.Columns.Add(c4);
dt1.Columns.Add(c5);
dt1.Columns.Add(c6);
dt1.Columns.Add(c7);
}
}
}