How to get rows from Gridview and Insert them into database?

Question

I am developing a web application for a pharma-laboratory.

While taking the order from the customer and clicking on the submit button, I have inserted the data into three tables of my database (customer, orderMaster, orderDetail). I have inserted into customer and orderMaster table. For orderDetail I have to insert data from the gridview.

When I add a test against a customer, the test is added in the gridview. One gridview can have multiple tests against one customer.

How can I insert data from gird view to database?

My function for getting the data from the gridview and inserting it into the database is:

private void bindOrderDetails()
{
    database_connectivity dbConnect = new database_connectivity();
    SqlConnection con = new SqlConnection();
    con = dbConnect.ConnectDB();
    con.Open();
    SqlCommand cmmd = new SqlCommand("SELECT MAX(orderID) as Order_Id FROM testOrder_master WHERE user_id = '" + Convert.ToInt32(txtUserID.Text) + "'", con);
    cmmd.Connection = con;
    SqlDataReader dr1 = cmmd.ExecuteReader();
    int orderID = 0;
    while (dr1.Read())
    {
        orderID = Convert.ToInt32(dr1["Order_Id"]);
    }
    con.Close();
    //for (int i = 0; i < GridView1.Rows.Count - 1; i++)
    //{
    //    Stquery = @"INSERT INTO testOrder_details (orderID, labID, srNo, test_id, price, createdBy, creationTime) VALUES ('"+orderID+"','"+Convert.ToInt32(txtLabID.Text)+"','"+Convert.ToString(GridView1.Rows[i].Cells[1].
    //}

    foreach (GridViewRow row in GridView1.Rows)
    {
        con.Open();
        SqlCommand cmd1 = new SqlCommand("INSERT INTO testOrder_details (orderID, labID, srNo, test_id, price, createdBy, creationTime) VALUES (@orderId1,@lab_id,@serialNo,@testID, @testPrice, @created_by, @creation_time)", con);
        cmd1.Parameters.AddWithValue("@orderId1", orderID);
        cmd1.Parameters.AddWithValue("@lab_id", Convert.ToInt32(txtLabID.Text));
        cmd1.Parameters.AddWithValue("@serialNo", row.Cells[1].Text);
        cmd1.Parameters.AddWithValue("@testID", row.Cells[2].Text);
        cmd1.Parameters.AddWithValue("@testPrice", row.Cells[4].Text);
        cmd1.Parameters.AddWithValue("@created_by", Convert.ToString(txtUserTitle.Text));
        cmd1.Parameters.AddWithValue("@creation_time", DateTime.Now);
        cmd1.ExecuteNonQuery();
    }
}

i have used break points and not getting any value for Serial No column. as i have used template field on auto increment for Serial No column. This is the asp code of my gridview:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
    BackColor="White" BorderColor="#CCCCCC" 
    BorderStyle="None" BorderWidth="1px" CellPadding="3"
    onrowdeleting="GridView1_RowDeleting" 
    onselectedindexchanged="GridView1_SelectedIndexChanged"
    onrowcommand="GridView1_RowCommand" 
    onrowdatabound="GridView1_RowDataBound">
    <Columns>
        <asp:CommandField ShowDeleteButton="True" ButtonType="Image" 
            DeleteImageUrl="~/Imagess/delete.jpg" />
        <asp:TemplateField HeaderText="Serial No">
            <ItemTemplate><%# Container.DataItemIndex + 1 %>
            </ItemTemplate>
            <ItemStyle Width="2%" /> 
        </asp:TemplateField>

        <asp:BoundField DataField="testID" HeaderText="Test ID" />
        <asp:BoundField DataField="testName" HeaderText="Test Name" />
        <asp:BoundField DataField="testPrice" HeaderText="Price" />
    </Columns>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000066" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>

Solution

This will take your gridview and convert it into a datatable:

DataTable GetDataTable(GridView dtg)
        {
            DataTable dt = new DataTable();

            // add the columns to the datatable            
            if (dtg.HeaderRow != null)
            {

                for (int i = 0; i < dtg.HeaderRow.Cells.Count; i++)
                {
                    dt.Columns.Add(dtg.HeaderRow.Cells[i].Text);
                }
            }

            //  add each of the data rows to the table
            foreach (GridViewRow row in dtg.Rows)
            {
                DataRow dr;
                dr = dt.NewRow();

                for (int i = 0; i < row.Cells.Count; i++)
                {
                    dr[i] = row.Cells[i].Text.Replace(" ", "");
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

The above is generic function, you might need to tweak it to fit your specific case.

If you iterate over the data table you can insert it back into the database. You seem capable of writing to the database based on your existing code, post in comments if you need help with this.

Comments

[Admin]

thanks for your feedback. but i have solved a bit my issue with following modifications cmd1.Parameters.AddWithValue("@serialNo", row.Cells[1].Text); cmd1.Parameters.AddWithValue("@testID", row.Cells[2].Text); cmd1.Parameters.AddWithValue("@testPrice", row.Cells[4].Text); after using breakpoints its giving me correct value in testID and testPrice but i m not getting any value for serial NO column . as i have used template field on auto increment for serial no column. kindly help me to resolve it thanks..