Read from Database and Insert into DataGridView

Question

How i can read records from Database and Can insert into DataGridView. I don't want to bind data source. Just want to read and insert using While Loop. I know how to read from Database Sql Server 2008 R2 Express Edition But unable to insert records in Datagridview.

Can anyone help me please ?

string query = "Select id, IdNo, status From Members Where FamilyHeadIqama=@Head";

sqlCommand = new SqlCommand(query, sqlConnection);
sqlConnection.Open();
sqlCommand.Parameters.AddWithValue("@Head", familyMemberId);
sqlDataReader = sqlCommand.ExecuteReader();

if (sqlDataReader.HasRows)
    {
      //What to write here while my DataGridView Name is ufGView and it has 
      //4 column. Names are Id, IdNo{TextBox}, Status{CheckBox}, Action{HyperLink}
    }
}
catch (Exception exp)
{
          MessageBox.Show(exp.Message.ToString(), "Exception in CheckFamilyMembers");
}

I am looking for something like this.

 int i = 0;
            while (sqlDataReader.Read())
            {
                ufGview.Rows[i].Cells["Column3"].Value = sqlDataReader["id"].ToString();
                ufGview.Rows[i].Cells["Column1"].Value = sqlDataReader["IdNo"].ToString();
                ufGview.Rows[i].Cells["Column2"].Value = sqlDataReader["status"].ToString();

                i++;
            }

Have you tried to write anything to go inside of the `if` statement? Possible duplicate of [this post.](http://stackoverflow.com/questions/6584817/direct-method-from-sql-command-text-to-dataset) — Hank, Nov 15 '16 at 15:27
Possible duplicate of [Direct method from SQL command text to DataSet](http://stackoverflow.com/questions/6584817/direct-method-from-sql-command-text-to-dataset) — Hank, Nov 15 '16 at 15:31

score 1 · Answer 1 · answered Nov 15 '16 at 15:33

1

Is something like this.

DataTable dt = new DataTable();
DataRow dr = new DataRow();

While sqlDataReader.Read(){
dr = dt.newRow();
dr["Id"] = sqlDataRead.item["id"];
.
.   
.
dt.rows.add(dr);
}

DataGridView.datasource = dt;

Hope it helps

answered Nov 15 '16 at 15:33

Nuno José Caldeira Teixeira

11
3

I don't want to bind it with data source. coz it's making update process complex – Mistr Mowgli Nov 15 '16 at 16:35

score 1 · Answer 2 · answered Nov 15 '16 at 15:34

1

You can get a DataTable from a SqlDataReader like this:

using (var sqlDataReader = sqlCommand.ExecuteReader())
{
    var dataTable = new DataTable();
    dataTable.Load(sqlDataReader);
    return dataTable;
}

Than you can set the gridviews source like this:

dataGridView1.DataSource = dataTable;

answered Nov 15 '16 at 15:34

Stefan

652
5
10

Thanks for ur help. But i am looking for something like in my edited post – Mistr Mowgli Nov 15 '16 at 16:57
This works but the issue I am facing is that the column names in the result grid are the ones we use in the DB, which is kind of *raw* and not really user-friendly. How can one use custom column names without having to address each and every one of the columns during the loading of the data? – Denislav Karagiozov Feb 04 '18 at 18:15

score 1 · Answer 3 · answered Nov 15 '16 at 16:08

1

Its pretty easy and i hope, this what you are looking for.

if (sqlDataReader.HasRows)
    {
           DataTable schemaTable = reader.GetSchemaTable();
           foreach (DataRow currentrow in schemaTable.Rows)
           {

               ufgView.rows.add(currentrow )  //  gridviewname as per your comment
           }
    }

hope it surely help, kindly let me know if you need further clarification or doubts.

thanks karthik

answered Nov 15 '16 at 16:08

Karthik Elumalai

1,574
1
11
12

Thanks for ur help. But i am looking for something like in my edited post – Mistr Mowgli Nov 15 '16 at 16:47

score 0 · Answer 4 · answered Nov 15 '16 at 17:27

Thanks Everyone,

Thanks for your kind responses. I got result by Changing AllowUserToAddRows Property to True and modifying some code as well like this..

   for (int i = 1; i <= TOTALMEMBERS; i++)
   {
       string[] row = new string[] { i.ToString() };
       ufGview.Rows.Add(row);
   }

   int ji = 0;

   while (sqlDataReader.Read())
   {
        ufGview.Rows[ji].Cells["Column3"].Value = sqlDataReader["id"].ToString();
        ufGview.Rows[ji].Cells["Column1"].Value = sqlDataReader["IdNo"].ToString();
        ufGview.Rows[ji].Cells["Column2"].Value = sqlDataReader["status"].ToString();

      ji++;
    }

Read from Database and Insert into DataGridView

4 Answers4