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.

Answer 1

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
      }
     }
    }
  }
 }
}

Answer 2

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 ;-)