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++;
            }

Answer 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

Answer 2

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;

Answer 3

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

Answer 4

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++;
    }