CodeBits: Object to SqlParameters Extension with Reflection

Don't get me wrong, code generators are a good thing.  However sometimes they generate pages and pages of code that are really redundant thanks to reflection.

Especially for generating a DAL for you application.  Consider the following extension:

public static List<SqlParamter> BuildParameters(this object obj)
{
    List<SqlParameter> collection = new List<SqlParameter>();

    foreach (PropertyInfo propertyInfo in obj.GetType().GetProperties())
    {
        if (propertyInfo.GetValue(obj, null) = null)
        {
            collection.Add(new SqlParameter(propertyInfo.Name, DBNull.Value));
        }
        else
        {
            collection.Add(new SqlParameter(propertyInfo.Name, propertyInfo.GetValue(obj, null)));
        }
    }

    return collection;
}

This will take any object, and create a list of sql parameters from all properties. This might sound a little overboard, but consider the following function.

public static void ExecuteNonQuery(string query, string connection, List<SqlParameter> parameters)
{
    using (SqlConnection connection = new SqlConnection(connection))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.CommandType = CommandType.Text;
            for (int i = 0; i < parameters.Count; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            command.Connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }
}

Now I've wrapped the sqlcommand class into a static helper. What I have done is basically removed any need for generated classes (or manually coded... ugh!) functions to create parameters for a sqlcommand. It would probably work for any DBCommand. The point is and DAL generated code is pointless because we can boil it down to a few functions thanks to reflection. Then usage would be something as follows :

 
public function Main()
{
    // Read object from Database or create new object
    // Then manipulate said object
    // Update or Insert object into the Database
    
    DalWrapper.ExecuteNonQuery(queryUpdateOrInsert, connection, obj.BuildParameters());
}

Of course my query should really be a store procedure name. At least my BLL / DAL has been condensed by about 75% of what the auto-generated code was, and makes adding new tables and records a breeze since all I really have to do is strong type my object and define the stored procedures.