Read data from DB generic function

Question

I am new to working in dot net. What I am trying to do is create a helper method which will fetch data from DB for any table. I have passed parameters dynamically it working fine. But when I try to read the data, I am finding it difficult to store the data in some collection. This I will be returning back to my calling point and bind it to a response type and return.

public static Dictionary<Dictionary<string, object>, object> GetData(SqlCommand cmd, string connectionString, List<SqlParameter> parameters)
        {
            try
            {
                SqlDataReader reader = null;
                Dictionary<Dictionary<string,object>, object> returnObjects = new Dictionary<Dictionary<string, object>, object>();
                Dictionary<string, object> returnObject = new Dictionary<string, object>();

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    cmd.Connection = sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                returnObject.Add(reader.GetName(i), reader[i]);
                            }
                            returnObject.Clear();
                            returnObjects.Add(returnObject, reader);
                        }
                    }
                }
                return returnObjects;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

In above code when i try to add in returnObjects dictionary it says key already added. Below is the code from where I am calling and patientResponse where I want to return.

Dictionary<Dictionary<string, object>, object> dct = new Dictionary<Dictionary<string, object>, object>();
                dct = Helper.GetData(cmd, connectionString, parameters);

List<Patient_Response> pp = new List<Patient_Response>();
 Patient_Response pr = new Patient_Response();
 pr.Patient_Id = int.Parse(reader["ID"].ToString());
 pr.FIRST_NAME = reader["FIRST_NAME"].ToString();
 pr.LAST_NAME = reader["LAST_NAME"].ToString();
 pr.phoneNumber = reader["TEL"].ToString();
 pr.email = reader["EMAIL"].ToString();
 pr.Address = reader["Address"].ToString();
 pr.Gender = reader["Gender"].ToString();
 pr.DOB = Convert.ToDateTime(reader["DOB"]).ToString("MM/dd/yyyy");
 pp.Add(pr);

What i can use instead of dictionary so that i can get a collection returned.

Answer 1

What surely doesn't work in your code is that you add the same instance of returnObject for each row. You should put new Dictionary<string, object>() inside the while. Also, if your result set contains duplicate field names, you'll get an exception.

public static List<Dictionary<string, object>> GetData(SqlCommand cmd, string connectionString, List<SqlParameter> parameters)
        {
            try
            {
                SqlDataReader reader = null;
                // changed this to be a list
                List<Dictionary<string,object>> returnObjects = new List<Dictionary<string, object>>();

                using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();
                    cmd.Connection = sqlConnection;
                    cmd.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }

                    reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // create a new returnObject for each row
                            Dictionary<string, object> returnObject = new Dictionary<string, object>();

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                // following line throws an exception if there are multiple fields with the same name
                                returnObject.Add(reader.GetName(i), reader[i]);
                            }
                            returnObjects.Add(returnObject);
                        }
                    }
                }
                return returnObjects;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

That said, there is not much benefit from first putting the content of reader into a list of dictionaries and then into a list of Patient_Response. You might as well directly fill a List<Patient_Response> from the reader (i.e. inside the while).

A generic way to do this might be (code is not verified to compile or run):

public static List<T> GetData<T>(
   SqlCommand cmd, 
   string connectionString, 
   List<SqlParameter> parameters, 
   Func<IDataReader, T> readerToRow) 
{
   List<T> returnObjects = new List<T>();

   using (SqlConnection sqlConnection = new SqlConnection(connectionString))
   {
      sqlConnection.Open();
      cmd.Connection = sqlConnection;
      cmd.CommandType = CommandType.StoredProcedure;

      foreach (SqlParameter parameter in parameters)
      {
         cmd.Parameters.Add(parameter);
      }

      SqlDataReader reader = cmd.ExecuteReader();

      while (reader.Read())
      {
         returnObjects.Add(readerToRow(reader));
      }
   }
   return returnObjects;
}

// To call above function:
List<Patient_Response> pp = Helper.GetData(
   cmd, 
   connectionString, 
   parameters, 
   reader => 
      new Patient_Response
      { 
         FIRST_NAME = reader["FIRST_NAME"].ToString(),
         LAST_NAME = reader["LAST_NAME"].ToString(),
         // more columns go here
      }
);