Gal Ratner
Gal Ratner is a Techie who lives and works in Los Angeles CA and Austin TX. Follow galratner on Twitter Google
Get SQL Parameters From an Object Revisited.

I have my own custom data layer adapter. Its pretty slick and you probably know it by now if you read my blog post Move a DataReader to an Object with reflection Revisited. If you downloaded and are using the file ObjectHelper.cs you probably noticed a method called GetSQLParametersFromPublicProperties. This method returns an array of SqlParameters from a live object. You can use this array if you are invoking a Stored Procedure and need to send some parameters into it. Something like:

public int AddCustomer(Customer customer)

        {

            try

            {

                SqlParameter[] paramArray = ObjectHelper.GetSQLParametersFromPublicProperties(customer);

                customer.CustomerID = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.mainConnectionString, CommandType.StoredProcedure, "AddCustomer", paramArray));

            }

            catch (Exception e)

            {

                throw new Exception("Error Adding Customer", e);

            }

            return customer.CustomerID;

        }


A friend of mine was using this function today. He was converting a system from NHibernate to ObjectHelper and needed something I was missing. He needed to skip some fields when creating the SqlParameter array. He was executing an Insert sproc and he didn’t need the object ID as a parameter. It was a field that was auto generated by the Insert statement. He then asked me for a way to hide object fields at will so that the function will only return the parameters he needed and suggested NHibernate like attributes on the fields he needed to hide. This is a great idea and I immediately went to work on implementing it.
First I added a new custom attribute. I created a new class that derived from System. Attribute:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

namespace InvertedSoftware.ShoppingCart.DataLayer.DataAttributes

{

    [Flags]

    public enum CrudFieldType

    {

        Create = 0x01,

        Read = 0x02,

        Update = 0x04,

        Delete = 0x08,

        DontUse = 0x10

    }

 

    public class CrudField : Attribute

    {

        public CrudFieldType UsedFor { get; set; }

    }

}

I used a bit enum to allow multiple operations on the same field and simply left the ones I didn’t need behind. Then I added an attribute to my Data Object

 public class Customer

    {

        [CrudField(UsedFor = CrudFieldType.Delete | CrudFieldType.Read | CrudFieldType.Update)]

        public int CustomerID { get; set; }

    }


Finally I re wrote the function to ignore a field based on the operation type we specify:

public static SqlParameter[] GetSQLParametersFromPublicProperties(object dataObject, CrudFieldType usedFor)

        {

            Type type = dataObject.GetType();

            PropertyInfo[] props = type.GetProperties();

            List<SqlParameter> paramList = new List<SqlParameter>();

            for (int i = 0; i < props.Length; i++)

            {

                if (props[i].PropertyType.IsValueType || props[i].PropertyType.Name == "String")

                {

                    object fieldValue = type.InvokeMember(props[i].Name, BindingFlags.GetProperty, null, dataObject, null);

                    CrudField usedForAttr = Attribute.GetCustomAttribute(props[i], typeof(CrudField)) as CrudField;

                    if (usedForAttr != null && (usedForAttr.UsedFor & usedFor) == usedFor)

                    {

                        SqlParameter sqlParameter = new SqlParameter("@" + props[i].Name, fieldValue);

                        paramList.Add(sqlParameter);

                    }

                    else if (usedForAttr == null)

                    {

                        SqlParameter sqlParameter = new SqlParameter("@" + props[i].Name, fieldValue);

                        paramList.Add(sqlParameter);

                    }

                }

            }

            return paramList.ToArray();

        }


The end result was the ability to have granular control over what parameters we send into the sproc and was still backward compatible with older versions of ObjectHelper. Data objects without the CrudField attribute will be handled in a similar way to the old version and will always be included in the returned array.
Thank you Jason for the idea! You can find Jason Brown here and he is always available for consulting.
I have attached the new ObjectHelper to this post. I hope you all find it as useful as I have.

 

Shout it


Posted 24 Feb 2010 6:35 AM by Gal Ratner
Attachment: ObjectHelper.cs

Powered by Community Server (Non-Commercial Edition), by Telligent Systems