What is the proper way of calling multiple "SQL DataReader"s in C#?

Question

I call ExecuteReader(); to get data, then i need to get another data with another query. My structure's been always like this :

class SomeClass
{
    public static void Main(String[] args)
    {
        SqlConnection sqlConn = new SqlConnection();
        sqlConn.ConnectionString = "some connection string" 

        SqlCommand SQLCmd = new SqlCommand();
        SQLCmd.CommandText = "some query";
        SQLCmd.Connection = sqlConn;
        sqlConn.Open();

        sqlReader = SQLCmd.ExecuteReader();

        while (sqlReader.Read())
        {
            //some stuff here
        }

        sqlReader.Dispose();
        sqlReader.Close();
        sqlConn.Close();

        SQLCmd.CommandText = "another query";
        sqlConn.Open();
        sqlReader = SQLCmd.ExecuteReader();

        while (sqlReader.Read())
        {
            //some other stuff here
        }

        sqlReader.Dispose();
        sqlReader.Close();
        sqlConn.Close();
    }
}

They share the same connection string. What else can they share ? Can they share same sqlConn.Open(); ? What is the proper way of resource allocating and avoiding errors ?

BTW it works as it is. Thanks in advance.

If possible, combine the two queries into a single one, and if that still produces two result sets, use [`NextResult`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult(v=vs.110).aspx) to process both. — Damien_The_Unbeliever, Nov 10 '14 at 08:10
`sqlConn.Open()` just opens the `SqlConnection`. If both `SqlDataReader` uses same connection, they can use `sqlConn.Open()` as well. Related: [ExecuteReader requires an open and available Connection. The connection's current state is Connecting](http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren) — Soner Gönül, Nov 10 '14 at 08:12
@EmreAcar Oddly enough, your code is very similar to [this post](http://csharp.net-informations.com/data-providers/csharp-multiple-resultsets.htm) that explains using MARS to read two sets of data from a single reader. — Adam Houldsworth, Nov 10 '14 at 08:20
@AdamHouldsworth Probably writer of the book i've read to learn C# copied examples from these posts because i've always used these variable names :) — Emre Acar, Nov 10 '14 at 08:23

score 5 · Answer 1 · edited May 23 '17 at 11:59

This is how I would write all of that:

class SomeClass
{
 public static void Main(String[] args)
 {
  using (SqlConnection sqlConn = new SqlConnection("some connection string"))
  {
   sqlConn.Open();

   using (SqlCommand comm = new SqlCommand("some query", conn))
   using (var sqlReader = comm.ExecuteReader())
   {
    while (sqlReader.Read())
    {
     //some stuff here
    }
   }

   using (SqlCommand comm = new SqlCommand("some query", conn))
   using (var sqlReader = comm.ExecuteReader())
   {
    while (sqlReader.Read())
    {
     //some other stuff here
    }
   }
  }
 }
}

The using statement handles disposing of items when the block is finished. As for sharing stuff, you could leave the connection open across the commands.

The most important thing to dispose out of all of that would be the connection, but I tend towards honouring a using statement if an item is IDisposable regardless of what it actually does in the background (which is liable to change as it's an implementation detail).

Don't forget, there is also Multiple Active Result Sets (as demonstrated in this answer) from a single command and a single reader, where you advance the reader onto the next result set.

My slightly more flippant answer to how I might write all of that would be:

 return connection.Query<T>(procedureName, param, commandType: CommandType.StoredProcedure);

Using Dapper ;-)

It's important to mention that he cannot use the second `DataReader` from within the first. So if he needs that it would be a good approach to fill a `List` first which he can access from the the second loop. — Tim Schmelter, Nov 10 '14 at 08:16
@TimSchmelter Oh I see, in case he intends to grab stuff from the prior reader on the second loop or something? Yeah, didn't spot that. — Adam Houldsworth, Nov 10 '14 at 08:18

score 4 · Accepted Answer · edited May 23 '17 at 12:30

4

As alluded to in my comment - if possible, combine the two queries into one and then (if it still produces multiple result sets), use NextResult to move on.

Stealing Adam's structure, but with that change:

class SomeClass
{
 public static void Main(String[] args)
 {
  using (SqlConnection sqlConn = new SqlConnection("some connection string"))
  {
   sqlConn.Open();

   using (SqlCommand comm = new SqlCommand("some query; some other query;", conn))
   using (var sqlReader = comm.ExecuteReader())
   {
    while (sqlReader.Read())
    {
     //some stuff here
    }
    if(sqlReader.NextResult())
    {
      while (sqlReader.Read())
      {
       //some other stuff here
      }
     }
    }
  }
 }
}

edited May 23 '17 at 12:30

Community

1
1

answered Nov 10 '14 at 08:15

Damien_The_Unbeliever

234,701
27
340
448

1

+1 For being bothered to write it, even with the theft ;-) I've linked your answer from mine for completeness. – Adam Houldsworth Nov 10 '14 at 08:25
1

@AdamHouldsworth - And I'm your +1 also (I'd not steal without that and explicit credit in the answer) – Damien_The_Unbeliever Nov 10 '14 at 08:27
@AdamHouldsworth Thank you both for helping me learn something new today. I had to approve Damien's answer but i would approve both of you if i could. – Emre Acar Nov 10 '14 at 08:53
@EmreAcar Not a problem, glad we could help :-) – Adam Houldsworth Nov 10 '14 at 09:12

score 1 · Answer 3 · answered Nov 10 '14 at 08:10

The proper way is to wrap SqlConnections and SqlCommands in using-statements. This will force Dispose to be invoked on the objects when the using block is left, even if an Exception is thrown. (This is not the case with your current code.)

Something in the line of

using(var cnn = new SqlConnection("connectionstring")){
    cnn.Open();
    using(var cmd = new SqlCommand("SELECT 1")){
       var reader = cmd.ExecuteReader();
       while(reader.Read()) { /* doStuff */ }
    }
}

Regardless of the approach Close/Dispose will not actually close the connection since connection setup is very expensive. It will just return the connection to a connection pool and allow other commands/readers to use it.

score 1 · Answer 4 · answered Nov 10 '14 at 08:20

To manage resource you can use using like as shown under ...

 SQLCmd.CommandText = "some query";
 SQLCmd.Connection = sqlConn;
 sqlConn.Open();

//using will dispose reader automatically.
 using(sqlReader = SQLCmd.ExecuteReader())
{
   while (sqlReader.Read())
    {
    //some stuff here
    }
}
 //sqlReader.Dispose();
 //sqlReader.Close();
 //sqlConn.Close();

 SQLCmd.CommandText = "another query";
//no need to open connection again.
// sqlConn.Open();
// sqlReader = SQLCmd.ExecuteReader();

 using(sqlReader = SQLCmd.ExecuteReader())
{
   while (sqlReader.Read())
    {
    //some stuff here
    }
}
 //sqlReader.Dispose();
 //sqlReader.Close();
 //sqlConn.Close();

you can use using only for those classes which have implemented IDispose interface. in your example you can use SqlConnection and SqlCommand also with using code block.

score 0 · Answer 5 · answered Nov 10 '14 at 08:10

0

Use 'using', you don't need to manually close and dispose.

using (SqlConnection connection = new SqlConnection(connectionString)) 
{    
     connection.Open();
     SqlCommand command = new SqlCommand("spTest", connection);
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add(new SqlParameter("@employeeid", employeeID));
     command.CommandTimeout = 5;

     command.ExecuteNonQuery();
}

answered Nov 10 '14 at 08:10

tickwave

3,335
6
41
82

Does _not_ answer the question which is: "What is the proper way of calling multiple “SQL DataReaders?". – Tim Schmelter Nov 10 '14 at 08:14

score 0 · Answer 6 · answered Nov 10 '14 at 08:16

0

Open a new connection every time you need it is a best practices. ADO.net use connection pool to menage connection. http://msdn.microsoft.com/it-it/library/8xx3tyca(v=vs.110).aspx

answered Nov 10 '14 at 08:16

Massimo Zerbini

3,125
22
22

score 0 · Answer 7 · answered Nov 10 '14 at 08:21

Dont forget your try catch statements though :)

class SomeClass
{
 public static void Main(String[] args)
 {
  using (SqlConnection sqlConn = new SqlConnection("some connection string"))
  {
   try{
   sqlConn.Open();

   using (SqlCommand comm = new SqlCommand("some query", conn))
   using (var sqlReader = comm.ExecuteReader())
   {
    while (sqlReader.Read())
    {
     //some stuff here
    }
   }

   using (SqlCommand comm = new SqlCommand("some query", conn))
   using (var sqlReader = comm.ExecuteReader())
   {
    while (sqlReader.Read())
    {
     //some other stuff here
    }
   }
   }
   catch()
{
// Do exception catching here or rollbacktransaction if your using begin transact
}
finally
{
sqlConn.Close();
}
  }
 }
}

What is the proper way of calling multiple "SQL DataReader"s in C#?

7 Answers7

Linked