CODEWITHSUNDEEP
c#asp.netdatagridviewrows
Anoushka Seechurn
Anoushka Seechurn

Reputation: 2256

Loop So as to read several rows of a datagridview in C#

I have a datagrid (grid_detail),since now i have been only reading a single row of it and saving to database using codes below

            //number of columns actually in second dgv
            int col_no = grid_detail.ColumnCount;
            int row_no = grid_detail.RowCount - 1; 
            int r = 0;



            //array to contain the values of the cells
            string[] col_value = new string[100];
            for (i = 0; i < col_no; i++) 
            {
                //fill in the Array with values from cells
                col_value[i] = Convert.ToString(grid_detail.Rows[0].Cells[i].Value);
            }
            //Build insert command 
            StringBuilder Q = new StringBuilder();
            Q.Append("INSERT INTO ");
            Q.Append("[Ref].[");
            Q.Append(_lstview_item);
            Q.Append("]");
            Q.Append(" VALUES");
            Q.Append("(");
            for (i = 0; i < col_no; i++)
            {
                Q.Append("'");
                if (string.IsNullOrEmpty(col_value[i]))
                {
                    x = false;
                    break;
                }
                else
                {
                    Q.Append(col_value[i]);
                    Q.Append("'");
                    Q.Append(",");
                    x = true;
                }
            }
            //removing last COMMA from loop
            if (col_no > 0)
            {
                Q.Length--;
            }
            Q.Append(")");
            query = Q.ToString();



            if (x == true)
            {
                //open connection to db and send query
                SqlConnection conn = new SqlConnection(cc.connectionString(cmb_dblist.Text));
                SqlCommand cmd_server = new SqlCommand(query);
                cmd_server.CommandType = CommandType.Text;
                cmd_server.Connection = conn;
                conn.Open();
                cmd_server.ExecuteNonQuery();
                conn.Close();

However,my requirements are that I now read MORE THAN one row of z grid and save to database. i tried using a for loop for the number of rows like

for (r=0;r<row_no;r++)
{

/////CODES ABOVE PASTED HERE

}

I am unable to do the loop.

Help please.

Upvotes: 1

Views: 885

Answers (1)

Damith
Damith

Reputation: 63065

i assume you have row_no set according to grid_detail row count, then do below changes and test 

for (int r=0; r< grid_detail.RowCount; r++)
{
    // your code...
    //int r = 0; comment this line 
    // your code...
    //.............
            for (i = 0; i < col_no; i++) 
            {
                //fill in the Array with values from cells
                col_value[i] = Convert.ToString(grid_detail.Rows[r].Cells[i].Value); // use row r 
            }
    //.............
    // your code...

}

but I would re factor your code to below 

foreach (GridViewRow row in GridView1.Rows)
{
    string sql = GenerateSql(row);
    if (!string.IsNullOrEmpty(sql))
        ExecuteNonQuery(sql);
}
private void ExecuteNonQuery(string query)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd_server = new SqlCommand(query, conn))
    {
        conn.Open();
        cmd_server.ExecuteNonQuery();
    }
}

private string GenerateSql(GridViewRow row)
{
    var values = row.Cells.Cast<TableCell>().Select(x => x.Text).ToArray();
    if (values.Any(string.IsNullOrEmpty))
    {
        return string.Empty;
    }
    var sql = string.Format("INSERT INTO  [Ref].[{0}] VALUES ({1})", _lstview_item, string.Join(",", values));

    return sql;    

}

Upvotes: 1

Related Questions