Fun With Reflection (and Generics).

On a recent project I had to hand write a lot of CRUD and I used SQL Views and Stored Procedures to isolate the MS-SQL database I was using. 

I found myself coding up a bunch of methods that made calls to those stored procedures and then populated objects in a generic List<> from the returned data set. After my Nth method I figured there had to be a better way, so I came up with the following:

public static List<T> SprocToList<T>(String SprocName, String Connection)

{

   List data = new List();

   Type c = typeof(T);

   T instanceOf;

   try

   {

     using (SqlConnection conn = new SqlConnection(Connection))

     {

      try

         {

            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = SprocName;

            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())

            {

               instanceOf = (T)Activator.CreateInstance(

                            Type.GetType(c.AssemblyQualifiedName));

               foreach (var propInfo in c.GetProperties())

               {

                  try

                  {

                     if (reader[propInfo.Name] != DBNull.Value)

                        try

                        {

                           propInfo.SetValue(instanceOf, 

                                    reader[propInfo.Name], null);

                        }

                        catch (Exception oEx)

                        {

                           Console.WriteLine(oEx.Message);

                        }

                     else

                     {

                        Console.WriteLine("Value is null!!");

                     }

                  }

                  catch (Exception oEx)

                  {

                      Console.WriteLine(oEx.Message);

                  }

               }

               data.Add(instanceOf);

            }

         }

         catch (Exception oEx)

         {

            Console.WriteLine(oEx.Message);

            return null;

         }

     }

   }

   catch (Exception oEx)

   {

      Console.WriteLine(oEx.Message);

      return null;

   }

   return data;

}

This Generic method takes a stored procedure name and a SQL connection and returns a List<> of type T. It traverses the returned data set by getting the property names in the Object of type T and using those property names as the data set's field names. The Object of type T is instanced using reflection (see Activator.CreateInstance statement).

One caveat: Object of type T must have properties of the exact same name as the fields returned by the stored procedure.

This can be used for ObservableCollections as well. I hope this helps someone, although if you're considering using something like this you might consider Entity Framework 4.0 instead.