SQl cmd.ExecuteReader() what does it return

Question

I'm studying ASP.NET. I wondered if cmd.ExecuteReader()'s output could be temporally stored into something, like a temp variable, to later re-use it or alter it. I often use temp variables to store stuff.

How can I let a dropbox and a gridview both work with the result of cmd.exectuteReader. I don't want to create a new SQL connection for it.

A variable t might keep the content, but obviously I'm wrong here since it doesn't work. It executes the reader twice, and on the second run there is no data to fill the dropdown box.

How should i do that ?

 protected void Page_Load(object sender, EventArgs e)
 {
    string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; // reading by name DBCS out of the web.config file
    using (SqlConnection con = new SqlConnection(cs))
    {

      SqlCommand cmd = new SqlCommand("Select * from tblEmployees", con);
      con.Open();

       var t = cmd.ExecuteReader();

       GridView1.DataSource = t;// cmd.ExecuteReader();
       GridView1.DataBind();

        // DropDownList2.DataSource = cmd.ExecuteReader();
        DropDownList2.DataSource = t;//cmd.ExecuteReader();

       DropDownList2.DataTextField = "Name";
       DropDownList2.DataValueField = "EmployeeId";
       DropDownList2.DataBind();
    }
}

Fill a DataTable(f.e. with SqlDataAdapter.Fill) or a List<Employee> where Employee is a custom class that contains one property for each (relevant) column in tblEmployees. You can use it as DataSource for the GridView and the DropDownList. Therefore use the appropriate DataTextField and DataValueField — Tim Schmelter
– Tim Schmelter, Commented Oct 18, 2016 at 12:54
but i'm not using SQLdataAdapter here, isnt there a general object that can contain database reader result ? — Peter
– Peter, Commented Oct 18, 2016 at 12:58
In general you should not use a SqlDataReader because that is a one way stream to the database which needs an open connection and returns one record after the other. You should load it into a collection to store it in memory. — Tim Schmelter
– Tim Schmelter, Commented Oct 18, 2016 at 13:09
@TimSchmelter as shown in example, SQLreader here reads entire database not a single record, i'd like to know of what type it is the ExecuteReader stores its data in, i'm aware of that is a 1 time read action — Peter
– Peter, Commented Oct 18, 2016 at 14:31

William Xifaras · Accepted Answer · 2016-10-18 14:44:46Z

2

SqlDataReader is a forward-only stream of rows from a SQL Server database.

You can bind a SqlDataReader to a GridView in the following ways:

Simple Examples:

    connection.Open();
    command.Connection = connection;
    SqlDataReader reader = command.ExecuteReader();
    GridView1.DataSource = reader;
    GridView1.DataBind();

Or:

 DataTable dt = new DataTable();
 dt.Load(cmd.ExecuteReader());
 GridView1.DataSource = dt;

Don't forget to configure the columns inside the Gridview control.

answered Oct 18, 2016 at 14:44

William Xifaras

5,3622 gold badges22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Over a year ago

ok i had to get used a bit to the idea that a database is not a list or basic type (dword/int) their declaration starts with the type and then a value assignment, i though that could be done with a database as a whole as well, but its indeed your second answer the way things go in .net

uTeisT · Accepted Answer · 2016-10-18 14:16:57Z

0

To answer your question, it returns SqlDataReader as stated in Msdn documentation - SqlCommand.ExecuteReader Method ()

You can check documentation example for better understanding of ExecuteReader()

You have kinda turned this into an XY problem but for binding data to a GridView you should use a DataTable and a SqlDataAdapter. A generic example would be:

void FillData()
{
    // 1
    // Open connection
    using (SqlConnection c = new SqlConnection(
    Properties.Settings.Default.DataConnectionString))
    {
    c.Open();
    // 2
    // Create new DataAdapter
    using (SqlDataAdapter a = new SqlDataAdapter(
        "SELECT * FROM EmployeeIDs", c))
    {
        // 3
        // Use DataAdapter to fill DataTable
        DataTable t = new DataTable();
        a.Fill(t);

        // 4
        // Render data onto the screen
        // dataGridView1.DataSource = t; // <-- From your designer
    }
}

I won't use your code for the time being, so that you can experiment on this example. If you need further help, please edit your original post, after changing your code.

answered Oct 18, 2016 at 14:16

uTeisT

2,26616 silver badges27 bronze badges

4 Comments

Peter Over a year ago

sorry but, if i can run cmd.executedatareader(), it does provide a result datagrids or puldowns can use such results, so it must be of some general type (i'm aware there are other ways of reading databases), but my interest here is ExecuteDataReader

uTeisT Over a year ago

And that is exactly why I told you that you can check the documentation. There is an example that you can investigate and change your code accordingly.

Peter Over a year ago

SQLdatareader is a object, not a value type, like integer int dword etc. What i can code is System.Data.DataTable tb = new System.Data.DataTable(); tb.Load(cmd.ExecuteReader)

Peter Over a year ago

probaply last line is the answer, datareader returns a datatable , sorry its a bit confusing all together

Collectives™ on Stack Overflow

SQl cmd.ExecuteReader() what does it return

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related