Dynamic Data Access Layer, Part 2

By Anthony Jackson. Filed in C#  |  
TOP del.icio.us digg

In Part 1 of Dynamic Data Access Layer, we created a class that was able to map between any Class and a DataRecord assuming the fields matched the properties.  In Part 2, we’re going to retrieve the data from the database and use the Mapper to populate the class.

We’ll of course start with declaring the class itself, interestingly when we actually use this class, we’ll have to not only inherit from the class, but the T will need to be the interface that the inheriting class implements.

In order for this class to do it’s work, it needs two things, a Microsoft.Practices.EnterpriseLibrary.Data.Database object and a Mapper class like the one from part 1 of this series. If the user of the class passes in a Mapper, we’ll use it, otherwise it will be created the first time the property is accessed (next code section). The user of this class has the choice to supply either the Database or the Mapper or both, this could be helpful in a Dependency Injection scenario. If they are not supplied, one will be provided for them.


public class SQLReaderWriterBase<T> where T : new()
    {
        Database db;

        #region Constructors
        public SQLReaderWriterBase(Database database, IMapper<T> mapper) : this()
        {
            this.db = database;
            Mapper = mapper;
        }
        public SQLReaderWriterBase(IMapper<T> mapper) : this()
        {
            Mapper = mapper;
        }
        public SQLReaderWriterBase(Database database) : this()
        {
            this.db = database;
        }
        public SQLReaderWriterBase()
        {
            if (db == null)
            {
                db = DatabaseFactory.CreateDatabase();
            }
        }
        #endregion

        

Next we create the properties for our class. While one of the main points of this exercise is so that the user doesn’t need to know anything about the database; their may be situations where it’s necessary to override defaults. Therefore, we allow the user to provide the name of the stored procedure to call if they need to through the GetCommandText property. We also provide a default which should handle 99.9% of the cases, in the format of spClassNameGet. Just as the Mapper could be passed into the Constructor, it can also be set through the Mapper property. However, if it’s not set through either method, we provide a default implementation using the SQLMapper we created in the first article.


#region Properties
        string getCommandText = "";
        public string GetCommandText 
        {
            get
            {
                if (getCommandText == "")
                {
                    getCommandText = "sp" + typeof(T).Name.ToString() + "Get";
                }

                return getCommandText;
            }
            set
            {
                getCommandText = value;
            }
        }

        IMapper<T> mapper;
        public IMapper<T> Mapper
        {
            get
            {
                if (mapper == null)
                {
                    mapper = new SQLMapper<T>();
                }
                return mapper;
            }
            set
            {
                mapper = value;
            }
        }

        #endregion

Now we’re starting to get down to the real fun of this class. Actually retrieving the data and populating the object. But first let’s talk about the allowed methods:

  1. Using an identity field as the key, which should only require a single integer to be passed in
  2. Unknown number of parameters passed in a comma separated format ie SQLReaderWriterBase.Get(parameter1, parameter2, …);

So let’s start with the simplest case, and create a Get that takes a single integer representing an identity field and identifies the row to retrieve and return an object for. Rather than do any real work, let’s just pass on the necessary info to another version of get to actually handle. If you notice that it passes in "ByID", that would be due to the fact that our actual implementation requires the first parameter to be the string to attach to the end of the name of the stored procedure to run. While the majority of times data will be retrieved ByID, their will be other cases where retrieval will be done on other criteria, their may be ByName or ByEmail or ByNameEmail. This design allows for unlimited variations, but puts the burden of knowledge of the database back onto the calling class.


 public ObservableCollection<T> Get(int ID)
        {
            return Get("ByID", ID);
        }

So the next version of Get we create will handle the situation of an unknown number of parameters being passed in. Fortunately, C# gives us the “params” keyword which basically means take the comma separated values and turn them into an array: SQLReaderWriterBase.Get(param1, param2, param3) becomes an array of objects that we can work with. We then convert this array of objects to a LinkedList and pass off handling it to another version of Get.


        public ObservableCollection<T> Get(params object[] parameterValues)
        {
            LinkedList<object> listParameterValues = new LinkedList<object>(parameterValues);

            return Get(listParameterValues);
        }

So we still haven’t actually done anything really, the prior two versions of Get simply provide easy ways for users of the class to call into it. Now we’re actually going to do the real work of getting the data from the database and populating the class(es).

The very first thing we’re going to do is to create our return collection and then use the first parameter to modify the command text we’ll use to discover the parameters. If you remember from above, since we potentially have many ways of retrieving data for this class, we need to some way to differentiate between them and so the first item in the parameters linked list must be a suffix for the command text which clarifies which stored procedure to use.


        public virtual ObservableCollection<T> Get(LinkedList<object> parameterValues)
        {
            ObservableCollection<T> collection = new ObservableCollection<T>();

            LinkedListNode<object> currentParameter = parameterValues.First;
            SqlCommand sqlCommand = new SqlCommand(this.GetCommandText + currentParameter.Value.ToString());
            sqlCommand.CommandType = CommandType.StoredProcedure;

            db.DiscoverParameters(sqlCommand);

Now that we have the parameters for the given stored procedure, we’ll loop through all sql parameters and set their values based on the passed in parameters, assuming that they are both in the same order.


            foreach (SqlParameter parameter in sqlCommand.Parameters)
            {
                if (parameter.ParameterName != "@RETURN_VALUE")
                {
                    currentParameter = currentParameter.Next;
                    if (currentParameter != null)
                    {
                        parameter.Value = currentParameter.Value;
                    }
                }
            }

Now that we have the parameters set, we’ll simply call ExecuteReader to get the data and then allow the Mapper class to do it’s job. Now we can return the collection of mapped objects.


            using (IDataReader reader = db.ExecuteReader(sqlCommand))
            {
                try
                {
                    collection = Mapper.MapAll(reader);
                    return collection;
                }
                catch
                {
                    throw;
                    // todo: consider handling exeption here instead of re-throwing, if graceful recovery can be accomplished
                }
                finally
                {
                    reader.Close();
                }
            }
        }
    }

Just like that we have a base class that can be inherited from and used to populate a class with data from a sql server database without worrying about writing any data access code.

In the next article, we’ll extend this class by adding in the Writer portion of SQLReaderWriterBase and allow for updates and inserts.

About Anthony Jackson

Anthony Jackson has written 10 posts in this blog.

Anthony has been developing software professionally since 1996; working primarily with Microsoft technologies such as C#, VB.NET, ASP.NET and MS SQL Server. He has a varied background working in a number of industries, on teams of different sizes including Enterprise Application Development. He often takes on varied roles for projects as well, quickly adapting to what is needed for a given project and is involved in the full life-cycle of software development. This blog and it's articles are entirely the opinion of the author Anthony Jackson and do not reflect the opinions of his employers or clients past, present or future. He currently works for Avanade as a Senior System Analyst and loves working their, but everything written is still just his opinion and does not necessary reflect on any of his employers or clients past, present or future.

Leave a Reply

You must be logged in to post a comment.