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` 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, Oct 18 '16 at 12:54
but i'm not using SQLdataAdapter here, isnt there a general object that can contain database reader result ? — Peter, Oct 18 '16 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, Oct 18 '16 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, Oct 18 '16 at 14:31

score 1 · Accepted Answer · answered Oct 18 '16 at 14:44

1

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 '16 at 14:44

William Xifaras

5,212
2
19
21

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 – Peter Oct 18 '16 at 15:03

score 0 · Answer 2 · answered Oct 18 '16 at 14:16

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 '16 at 14:16

uTeisT

2,256
14
26

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 – Peter Oct 18 '16 at 14:25
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. – uTeisT Oct 18 '16 at 14:35
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 Oct 18 '16 at 14:45
probaply last line is the answer, datareader returns a datatable , sorry its a bit confusing all together – Peter Oct 18 '16 at 14:58

SQl cmd.ExecuteReader() what does it return

2 Answers2