Reading Multiple Result Sets from a C# Reader Object

Modern ORM frameworks like NHibernate and Entity Framework has made the life easier to access the data in a object fashion. I personally go with the Traditional ADO.Net which i feel still the best without compromising the performance and having full control of how the application is interacted with the backend database. In this post i am going to share a simple code snippet which enables to read multiple result sets from a stored procedure

The objective is to have a common method in the datalayer to read the Product and Order Resultsets and process them as needed using the ResultSetCounter.

 

ReaderObject

A Sample representation of a reader object which can able to read all the resultsets returned by a stored proc

 

public IEnumerable<IDataRecord> GetResultSets(
string StoredProcName,
          Action<MySqlCommand> AddParameters, Action<int> nextResult)
      {
          using (var cn = new MySqlConnection(_activeConnString))
          using (var cmd = new MySqlCommand(StoredProcName, cn))
          {
              cn.Open();
              cmd.CommandType = CommandType.StoredProcedure;

              if (AddParameters != null)
              {
                  AddParameters(cmd);
              }

              using (var rdr = cmd.ExecuteReader())
              {
                  var resultCtr = 0;

                  while (rdr.HasRows)
                  {
                      while (rdr.Read())
                          yield return rdr;

                      if (rdr.HasRows)
                      {
                          rdr.NextResult();
                          resultCtr++;
                          nextResult(resultCtr);
                      }
                  }

              }
          }
      }

Usage :

List<Product> products=new List<Product>();
List<Order> orders=new List<Order>();

var resultCtr = 0;

var results = GetResultSets(“GetProductsAndOrders”
              (MySqlCommand p)=> { p.Parameters.Add(new MySqlParameter(“BranchCode”, “BR222”)); }, // Lambda to pass the Input Params
              (int resultNextCtr) )=>{ resultCtr = resultNextCtr; } // To track the running reader result sets
              );
          foreach (var item in results)
          {
              if (resultCtr == 0)
              {
                   products.Add(new Product(){ Name=item[0]}); // Process the rows from first result set
              }

             if (resultCtr == 1)
              {
                  orders.Add(new Order(){ OrderId=item[0]});  // Process the rows from second result set

              }

          }

AuthorNirmal

Technical Geek - Gadget Enthusiast - Loves Programming C#,PHP & IOS - Blogger About