Create Datatable,Insert/Delete Row In Datatable and Finaly Save Record In SQL Server Table
Here we'll discuss about how to create Datatable(by creating coloumns and then filling rows),how to delete row from temporary created Datatable,and Finally how to fetch Datatabledata into a SQL Server table
Now Lets figure out what we need to do
1)Instantiate DataTable
2)Create Colomns
3)Insert Rows
4)Insert Rows with Previous saved Rows
5)Delete Rows
6)Finally save all Rows in SQL table
Now Lets discuss these points bit broadly
1)As we discussed above, here we are instantiating DataTable
DataTable TempDTable=new DataTable();
2)Now creating/add columns in DataTable
TempDTable.Columns.Add("ColumnName", typeof(string));
3)Insert Rows
DataRow row=new TempDTable.NewRow();//Instantiating DataRow for inserting row in particular columns
row["ColumnName"] = TextBox1.Text;
4)Insert Rows with Previous saved Rows
if (ViewState["PagedataTable"] == null) //if there is any row in datatable
{
//Add coloum for your DataTable
}
else(else if exist then just fill up rows)
{
TempDTable = (DataTable)ViewState["PagedataTable"];
//store previous rows saved in viewstate to Datatable
}
DataRow row =new TempDTable.NewRow();
//Instantiating DataRow for inserting row in particular columns
row["ColumnName"] =TextBox1.Text;
//fill row for perticular column
if (ViewState["PagedataTable"] == null) //if there is any row in datatable (if not then create if exist then just fill up rows)
{
//below code will create coloum for your DataTable
TempDTable.Columns.Add("Type", typeof(string));
TempDTable.Columns.Add("Group", typeof(string));
TempDTable.Columns.Add("Name", typeof(string));
TempDTable.Columns.Add("GroupID", typeof(string));
TempDTable.Columns.Add("NameID", typeof(string));
TempDTable.Columns.Add("Particular", typeof(string));
TempDTable.Columns.Add("ChequeNo", typeof(string));
TempDTable.Columns.Add("Credit", typeof(float));
TempDTable.Columns.Add("Debit", typeof(float));
}
else
{
TempDTable = (DataTable)ViewState["PagedataTable"];
}
DataRow row;//Instantiating DataRow for inserting row in particular columns
row = TempDTable.NewRow();
row["Type"] = LabelType.Text;
row["Group"] = DropDownListGroup.SelectedItem.Text;
row["Name"] = DropDownListName.SelectedItem.Text;
row["GroupID"] = DropDownListGroup.SelectedValue;
row["NameID"] = DropDownListName.SelectedValue;
row["Particular"] = TextBoxPerticular.Text;
row["ChequeNo"] = TextBoxChequeNo.Text;
row["Credit"] = TextBoxCredit.Text;
row["Debit"] = TextBoxDebit.Text;
TempDTable.Rows.Add(row);//Fetching All Row in DataTable
ViewState["PagedataTable"] = TempDTable;//After filling rows,we are storing DataTable in View State so that we insert new value with previous stored value in viewstate
this.GridView1.Visible = true;
GridView2.DataSource = TempDTable;//Bind it with a grid view where you want to store datatable values
GridView2.DataBind();
ButtonSaveRecord.Visible = true;
5)To Check whether a coloumn entry alreay Existed with your Entered Value
if (ViewState["tempProductDatatable"] != null)
{
TempDTable= (DataTable)ViewState["PagedataTable"];
for (int i = 0; i < TempDTable.Rows.Count; i++)
{
if (TextBoxITEMCODE.Text == TempDTable.Rows[i]["Item_Code"].ToString())
{
LblCode.Text = "xx";
}
}
}
if (LblCode.Text == "xx")
{
// means value already existed in Item_Code coloumn
}
else
{
// means value does not existed in Item_Code coloumn
}
6) Delete Rows
between creating and fetching record
from datatable to SQL server table you may also like (you must know) how
to delete a record/row if its needs to be deleted!!
So here is a below code which will fired on RowDeleting Command of grid view!!
protected void GridView2_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int s = e.RowIndex;
DataTable TempDtForDel = (DataTable)ViewState["PagedataTable"];
TempDtForDel.Rows[s].Delete();
GridView2.DataSource = TempDtForDel;
GridView2.DataBind();
}
So we have Done!!we've created a datatable which will further fillup rows as per button click event
7) Finally save all Rows in SQL table:
Now thing comes later is save that data (in datatable in SQL Server table) so here we go...
a)Here we are first creating a table to save final value from data table...
CREATE TABLE [dbo].[tb_Sample]
(
[Voucher_Id] [int] IDENTITY(1,1) NOT NULL,
[VoucherType] [varchar](50) NULL,
[OfGroup] [varchar](100) NULL,
[Acc_Name] [varchar](100) NULL,
[Particulars] [varchar](100) NULL,
[ChequeNo] [varchar](60) NULL,
[Debit] [float] NULL,
[Credit] [float] NULL
)
b)Now below is a command to save datatable records/rows in SQL server table
con.Open();
DataTable dtTemp = new DataTable();
dtTemp = (DataTable)ViewState["PagedataTable"];
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
SqlCommand cmd2 = new SqlCommand("insert into tb_Sample values (@VoucherType,@Group,@Acc_Name, @Particulars,@ChequeNo,@Debit,@Credit)", con);
cmd2.Parameters.Add("@VoucherType", SqlDbType.VarChar).Value = dtTemp.Rows[i]["Type"].ToString();
cmd2.Parameters.Add("@Group", SqlDbType.VarChar).Value = dtTemp.Rows[i]["GroupID"].ToString();
cmd2.Parameters.Add("@Acc_Name", SqlDbType.VarChar).Value = dtTemp.Rows[i]["NameID"].ToString();
cmd2.Parameters.Add("@Particulars", SqlDbType.VarChar).Value = dtTemp.Rows[i]["Particular"].ToString();
cmd2.Parameters.Add("@ChequeNo", SqlDbType.VarChar).Value = dtTemp.Rows[i]["ChequeNo"].ToString();
cmd2.Parameters.Add("@Debit", SqlDbType.VarChar).Value = dtTemp.Rows[i]["Debit"].ToString();
cmd2.Parameters.Add("@Credit", SqlDbType.VarChar).Value = dtTemp.Rows[i]["Credit"].ToString();
cmd2.ExecuteNonQuery();
}
con.Close();
GridfinalBind();//binding gridview
//below is nothing but a way of inserting datatable record into server table but i m already using above method so u dont have to worry about below code....its all commented!!
//foreach (DataRow row in TempDTable.Rows)
//{
// SqlDataAdapter sqlInsert = new SqlDataAdapter();
// sqlInsert.InsertCommand = con.CreateCommand();
// sqlInsert.InsertCommand.CommandText = "INSERT INTO ACE_SALE_CONTRACT_UPDATE (ITEM_ID, ITEM_NO) SELECT '" +
// (row["ITEM_ID"]) + "' AS ITEM_ID, '" +
// (row["ITEM_NO"]) + "' AS ITEM_NO";
// sqlInsert.InsertCommand.ExecuteNonQuery();
//}
and here is the final running sample...kindly attach your own webconfig and run it!
