datareader bind to datagridview

Question

hi,
how to bind datareader to a datagridview? without using datatable.

thanks,

Popskie

Answer 1

  try this code,

   cnn.Open();
   cmd = new SqlCommand("s_Y",cnn);
   cmd.CommandType = CommandType.StoredProcedure;
   dr=cmd.ExecuteReader();
   DataGrid1.DataSource=dr;
   DataGrid1.DataBind();

Answer 2

there is no DataBind() in a DatagridView. Wht i want here to to loop the datareader put the data in the DataGridView. But i dont knew how ?

thanks

Answer 3

Did you find the answer?  I've got the same problem as VB Express doesn't support a connection SQL Server 2000 via a DataSource - they excluded this adapter from the express version.

Answer 4

hi,
I knew to bind datagridview in a datasource using dataadapter not datareader.
DataSet ds = new DataSet();
SqlDataAdapter myadapter = new SqlDataAdapter(); myadapter.SelectCommand.CommandText = " Select * from tablename ";
myadapter.Fill(ds,"tablename ");
db.DataSource = ds;
db.DataMember = "tablename ";

My problem here is, I want to loop my datareader then using the command below.
datagridview.Rows.Add();
But i dont knew how to get new add row in my datagridview.

Answer 5

Why do you need to enter the data into dataview using loop? Why don't you just bind it to it.

ie:
datagridview.DataSource = ds;
datagridview.DataMember = "tablename";

this will automatic load that table into the datagridview.


To add a new row to a datagridview:

DataRow newRow;

newRow = datagridview.NewRow();
newRow["column1name"] = "somevalue";   // make sure it in string format.
newRow["column2name"] = "somevalue";

datagridview.Rows.Add(newRow);

Answer 6

DataRow newRow;

newRow = datagridview.NewRow(); there is no NewRow()  function here.
my project where stock about 3 days. Im alreadry tired of searching.

Answer 7

sorry that was for adding to the datatable.

ie.
ds.tablename.NewRow()

so if you binded your datagridview to that table it will update that.

for datagridview it something like this I think:

DataGridViewRow dview = new DataGridViewRow();

dview = tablename.NewRow();

dview[column1name] = "text";

I am just starting to learn programming as well so sorry if I gave you wrong info.

my messenger is [e-mail] if you want to chat.

Answer 8

 

 

Try this :

 

private void Form1_Load(object sender, EventArgs e)

{

string strCon = "Data Source=dbServer;Initial Catalog=testDB;Integrated Security=True";

string strSQL = “select * from table1”;

 

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strCon);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

 

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

dataAdapter.Fill(table);

 

// finally bind the data to the grid

dbGridView.DataSource = table;

}

 

 

cheers,

 

anwar.buchoo

 

Answer 9

SqlConnection con = new SqlConnection("Connection String");

con.Open();

SqlCommand com = new SqlCommand("SQL Query", con);

SqlDataReader read = com.ExecuteReader();

DataSet ds = new DataSet();

DataTable dt = new DataTable("Table1");

ds.Tables.Add(dt);

ds.Load(read, LoadOption.PreserveChanges, ds.Tables[0]);

gridControl1.DataSource = ds.Tables[0];

Answer 10

Thanks so much for that post.  That is the best example I could find.  Much cleaner then the others I found and works great!

Answer 11

Thanks Nigam for your help, This was the execellent example, I am searching for this for an hour and was not able to work on this b4 till I found ur solution.

Answer 12

Hallo I'm just learning as well but but as far as I understand it the Probl. is that sqlDataReader returns a Stream (which makes it very fast). If you would like to simply bind it to a DataControl Object like Datagridview and you do not wont do Itarate through this Stream with while and Foreach Loops you need a DataBindingSource between the reader and the datagridview. To Simply "assign" the result of the reader.
A very good example Arctilce is: (I could not describe it better and I think it answers everythink)

http://msdn2.microsoft.com/en-us/library/at23yw2h(vs.80).aspx

Answer 13

 

To anybody to finds this thread, please note if you're just filling a datagridview, you should always use a datareader over a dataadaptor.  It runs considerably faster!

 

You can loop fill the datagridview, by using a loop (while dr.read)

Answer 14

 

Thank u so much for ths code...!

Answer 15

Great!!!

why do we need to use a BindingSource for a datareader? not for a Datatset

 

thanks

 

Answer 16

Hi,

 

Thanks for this solution. it helped me in my current situation.

 

Still my question is: can't we use SQLDatareader as a datasource for DataGridview directly, in C#.NET windows application (Not web application)?

 

thanks in advance

Answer 17

You can't bind a datareader directly to a datagridview in WINFORMS.  You can however in webforms.  So your question is actually how do I create a datatable from a datareader.

DataTable dataTable = new DataTable();

dataTable.Load(reader);

grid.DataSource = dataTable;

Hope this helps.  It is what I needed to solve my problem.

Answer 18

Many thanks carisch. I faced the same issue way back. I scolded my self for not checking the Load method of data table. 

Answer 19

Or, you can just do the following to load directly from a DataReader:

While DataReader.Read
     Dim values(DataReader.VisibleFieldCount) As Object
     DataReader.GetValues(values)

     'dgvMain as the DataGridView
     dgvMain.Rows.Add(values)
End While

Answer 20

SqlConnection obj_Conn = new SqlConnection();
            obj_Conn.ConnectionString = strConn;

            obj_Conn.Open();
            SqlCommand obj_Cmd = new SqlCommand(strCmd, obj_Conn);
            SqlDataReader obj_Reader = obj_Cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Columns.Add("Id");
            dt.Columns.Add("FName");
            dt.Columns.Add("LName");

         
            while (obj_Reader.Read())
            {
                DataRow row = dt.NewRow();
                row["Id"] = obj_Reader["Id"];
                row["FName"] = obj_Reader["FName"];
                row["LName"] = obj_Reader["LName"];
                dt.Rows.Add(row);
            }
            dataGridView1.DataSource = dt;

Answer 21

what if i have a database with employee names and i wanted each column header to be filled with these names from my database using the while loop? is that possible?

Answer 22

hi all,

 

i have on list view, and when i bind list view with data reader, it skip one row, strange experience 

 

code is as following;

cmd=new SqlCommand("Select SUM(Cola,Colb) from tabel where x=100 Group By Cola",con);

reader=cmd.ExecuteReader();

if(Reader.Read())

{

lv.DataSource=reader;

lv.DataBind();

}

 

 

every time, my list view shows one record less, and if query return one row, it display nothing,

 

if i change reader.Read() method with reader.HasRows then it displays all rows.  

 

Can any body explain me this Thank you so much in Advance.

 

 

 

---

her

Answer 23

Worked well... the command  builder declaration wan't necessary tho.

Great!

Answer 24

Great Post. It worked for me also. I was looking for the same kind  of explanation. Its very clear and understandable.

Answer 25

Just an FYI here ... DataAccess should never be included directly in the UI. See my blog post about Multi-tier applications:

http://geek-goddess-bonnie.blogspot.com/2010/10/multi-tier-applications.html

---

~~Bonnie Berent DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 26

Oie,

tente da seguinte forma:

Carregue seu DataReader(sua lógica básica de consulta)

crie um DataTable 

Em seguida  no seu objeto DataTable faça

dt.Load(dr);

datagridviewd.DataSource=dt;

bye!

Answer 27

i like this code ,, but is there any code for update in datagridview ,,
i run his code over after the data appears in datagridview ,, i want to change or add ,, what code is suitable for update button

example

 connectdb1()
            conn.Open()
            CMD = New SqlCommand(RichTextBox1.SelectedText, conn)
            DR = CMD.ExecuteReader
            ds = New DataSet()
            dtb = New DataTable("mytable")
            ds.Tables.Add(dtb)
            ds.Load(DR, LoadOption.PreserveChanges, ds.Tables(0))
            DataGridView1.DataSource = ds.Tables(0)



 Private Sub Btn_update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_update.Click
        
          
**** i need help source code for update from datagridview

    End Sub 

I will be very grateful if you want to help me for the code above

tanks,,

Answer 28

Hi all,

long time has passed. but i believe some are still looking for the answer. here i have one.

SqlCommand cmd = new SqlCommand();
//other connection stuff
SqlDataReader dr = cmd.ExecuteReader();
int columnCount = dr.FieldCount;
object[] columns = new object[columnCount]; 
//first, we get the headers
for (int i = 0; i < columnCount-1; i++)
      {               
         this.dataGridView1.Columns.Add(dr.GetName(i), 
         dr.GetName(i));
       }
//now, time to get rows
while (dr.Read())
    {
          for (int i = 0; i < columnCount-1; i++)
          {
               columns[i] = dr.GetValue(i);
          }
          this.dataGridView1.Rows.Add(columns);
    }

---

Dig your well before you are thirsty