Create Datatable,Insert/Delete Row In Data Table With Grid View

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!