CODEWITHSUNDEEP
c#asp.netsqlsql-serverado.net
kustomClass
kustomClass

Reputation: 3

DataReader Working Not Clear

 protected void btnFetch_Click(object sender, EventArgs e)
    {

    SqlConnection con = new SqlConnection(Helper.ConStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "select * from emptable";
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    gv1.DataSource = dr;
    gv1.DataBind();

    dr.NextResult();

    **while (dr.Read())**//It is returning the value as false!
    {
        SqlCommand cmd1 = new SqlCommand();
        cmd1.CommandText = "select * from table1";
        Response.Write(dr[0]);
        Response.Write(dr[1]);
    }
    con.Close();
 }

I am confused as to why, the data reader picks up one "select statement", reads it and binds it to a Gridview but when it comes down to using the next set of "select statement", the data reader doesn't do anything. dr.Read() becomes false.

Kindly explain to me why does this happen?

Upvotes: 0

Views: 2661

Answers (5)

Hasanka Rathnayake
Hasanka Rathnayake

Reputation: 137

you have to write a stored procedure in sql server

CREATE PROCEDURE selectFromTwoTables AS BEGIN BEGIN TRANSACTION

SELECT * FROM emptable

SELECT * FROM table1

IF(@@error = 0) -- if any error occured transaction will rollback. otherwise commit COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END

in c# SqlCommand cmd = new SqlCommand("selectFromTwoTables", conn); cmd.CommandType = CommandType.StoredProcedure;

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

You don't need NextResult, dr.NextResult(); advances the reader to the next batch:

using (var myCon = new SqlConnection(Helper.ConStr))
using (var selectCommand = new SqlCommand("select * from emptable", myCon))
{
    myCon.Open();
    using (var dr = selectCommand.ExecuteReader())
    {
        while (dr.Read()) 
        {
            // ...
        }
    }
}

So if you select two tables you can use NextResult to get the next, e.g.:

"select * from emptable; select * from otherTable"

Normally you don't need to use this method since ExecuteReader already returns the first batch.

Note that i'm using the using-statement to ensure that all unmanaged resources are disposed at the end of it even in case of an exception. You should use it whenever possible. That is for every object implementing IDisposable(you'll get a compiler error if you try to use using when it's impossible).

SqlConnection.Dispose also closes the connection, hence you don't need to do it manually (this applies to all connection types).

MSDN

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way. Within the using block, the object is read-only and cannot be modified or reassigned. The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object.

Upvotes: 1

jgauffin
jgauffin

Reputation: 101150

The data reader is forward only. that means that you can only read all rows once.

Hence when you have bound the GridView it will move through the data reader until Read() returns false (to be able to fill all rows).

So when you try to invoke your loop the reader is already in the end of the recordset.

This is explained in detail in MSDN.

Update:

The NextResult() method is used to select the next result if you added multiple SELECT statements to the Command.CommandText as @Petoj showed. Hence it has no effect whatsoever in your code.

Upvotes: 2

Peter
Peter

Reputation: 38465

Try this instead 

 protected void btnFetch_Click(object sender, EventArgs e)
 {

    SqlConnection con = new SqlConnection(Helper.ConStr);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "select * from emptable;select * from table1";
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    gv1.DataSource = dr;
    gv1.DataBind();

    dr.NextResult();

    while (dr.Read())
    {
        Response.Write(dr[0]);
        Response.Write(dr[1]);
    }
    con.Close();
 }

Upvotes: 1

शेखर
शेखर

Reputation: 17614

dr.NextResult(); works if you have multiple select statement in your query Reference.
You have only one select statement in your table as

cmd.CommandText = "select * from emptable";

So If you have to use multiple select statement then in commandtext you should have multiple select statement as follow

cmd.CommandText = "select * from emptable;select * from table1";

Upvotes: 0

Related Questions