Tuesday, April 27, 2010

AAPL Part 6: Building a DataMapper and DataMapperFactory

I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net.  I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result.  Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview

In this post, we’ll get into the DataMapper.  If you’ve been waiting to see some real ADO.Net code, this is the class you’ve been waiting for.

Where the DataMapper classes fit in

Let’s review our solution structure.  Core is our main business layer project (Note: in previous posts and in the sample code Core is actually the BAL project.  I never liked the name BAL so I’ve renamed it Core.  Same code, just a different name.  Sorry for any confusion.).  Core contains Service classes which are our one stop shop for all persistence (see AAPL Part 3 and AAPL Part 4 for more on how the service classes work). The Service classes use the SqlDao for all communication with the database.  The SqlDao uses the DataMapper classes to handle all mapping between the DataReader returned by executing a query, and the desired data shape.  Here’s what our project looks like:

image

Last post we covered the SqlDao class and saw how it’s data access methods take a query wrapped in an SqlCommand, execute that query, then return either a Single<DTO> (where DTO is our desired data shape), List<DTO>, DataPage<DTO>,or a scalar value.  The data access methods in SqlDao handle all the boilerplate logic for getting a connection, executing a query, stuff like that, but the logic that is specific to a data shape, things like get the value from this column in my reader put it in this property on my returned data shape, that stuff get’s delegated to a DataMapper class. This is what that SqlDao code looks like:

SqlDataReader reader = command.ExecuteReader();

if (reader.HasRows)

{

    reader.Read();

    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

    dto = (T)mapper.GetData(reader);

    reader.Close();

}

What does the DataMapperFactory do?

You’ll notice in the code above that we get our IDataMapper object from a DataMapperFactory.  If we need to get the DataMapper for type User, we just call the DataMapperFactory.GetMapper method, pass type(User) as a parameter, and GetMapper returns the appropriate concrete DataMapper (Note that the code example above uses generics, so we use generic T instead of a specific type like User, but the logic is the same). DataMapperFactory is a simple factory class with one method, GetMapper. The code looks like this: 

class DataMapperFactory

{

    public IDataMapper GetMapper(Type dtoType)

    {

        switch(dtoType.Name)

        {

            case "ListItemDTO":

                return new ListItemDTOMapper();

            case "User":

                return new UserMapper();

            default:

                return new GenericMapper(dtoType);

        }      

    }

}

Whenever we create a new IDataMapper for a specific type, like User, we need to add it to this factory method.  The method is just a switch statement that uses the type.Name to look for a type-specific implementation of IDataMapper.  If it finds one, it returns it.  If it doesn’t find a type specific IDataMapper, it returns an instance of GenericMapper, which is our reflection based DataMapper that can be used to handle mappings for any query / data shape combination.  We’ll cover GenericMapper in the next post.

What does the DataMapper do?

The behavior of a DataMapper class is defined by the IDataMapper interface.  The interface requires DataMappers to implement two methods.

interface IDataMapper

{

    // Main method that populates dto with data

    Object GetData(IDataReader reader);

    // Gets the num results returned. Needed for data paging.

    int GetRecordCount(IDataReader reader);

}

The GetData method is the important one.  That’s the method that our SqlDao calls whenever it needs to map the current record in an SqlDataReader to a specific data shape.  Remember, this method doesn’t do any mapping of an SqlDataReader to a List, or DataPage, or anything like that. That’s handled by the SqlDao data access methods. The purpose of the DataMapper GetData function is to take an SqlDataReader that’s already open and on a record, read that record, put the column data into an object (DTO) of the desired data shape, then return that single DTO.

Note that GetData returns an Object, so the SqlDao has to cast the GetData result to the proper data shape.  I tried all kinds of designs to get around this cast, I used interfaces, generics, base classes, but in the end all of these options added complexity to the code that I just couldn’t accept.  So, I opted to stick with GetData returning an object.  I think it was the right tradeoff, but if anyone has a better solution please let me know.

Here is a diagram of our IDataMapper interface and a couple of concrete DataMapper implementations.   

image

 

Inside a DataMapper class

So let’s take a look inside the UserMapper class and see what an IDataMapper implementation looks like. The user class is a data shape that represents, wait for it… a user.  The class looks like this.

image

So the job of UserDataMapper is to take a SqlDataReader, get the values from it’s columns, and put those values into and instance of the  User class above.  The DataMapper implementation that I use accomplishes this task in a very structured way that is easy to copy and reproduce for new data shapes. 

Before we look at the code, I should mention that I write a fair amount of extra code to get the ordinals for each data field instead of just using the string field names.  The reason for this is that using the ordinals allows me to use strongly typed methods on the SqlDataReader to get each column.  This allows me to avoid the cast to and from Object that occurs when you get a column by name which results in boxing and unboxing for value types.  It also gives me a little type safety.  If the query is returning a column called UserGuid that has a type of Int32, when I call reader.GetGuid() on that column, my code will throw an error and alert me that there is a problem with my query.  For more on the benefits of using ordinals take a look at High Performance Data Access Layer Architecture Part 3

Now, with no further ado, here’s the UserMapper implementation.

class UserMapper : IDataMapper

{

    private bool _isInitialized = false;

    private int _ordinal_UserGuid;

    private int _ordinal_UserRole;

    private int _ordinal_CreatedUtc;

    private int _ordinal_ModifiedUtc;

    private int _ordinal_CompanyGuid;

    private int _ordinal_FirstName;

    private int _ordinal_LastName;

    private int _ordinal_Email;

    private int _ordinal_Password;

    private int _ordinal_PhoneWork;

    private int _ordinal_PhoneMobile;

 

    private void InitializeMapper(IDataReader reader)

    {

        PopulateOrdinals(reader);

        _isInitialized = true;

    }

 

    public void PopulateOrdinals(IDataReader reader)

    {

        _ordinal_UserGuid = reader.GetOrdinal("UserGuid");

        _ordinal_UserRole = reader.GetOrdinal("UserRole");

        _ordinal_CreatedUtc = reader.GetOrdinal("CreatedUtc");

        _ordinal_ModifiedUtc = reader.GetOrdinal("ModifiedUtc");

        _ordinal_CompanyGuid = reader.GetOrdinal("CompanyGuid");

        _ordinal_FirstName = reader.GetOrdinal("FirstName");

        _ordinal_LastName = reader.GetOrdinal("LastName");

        _ordinal_Email = reader.GetOrdinal("Email");

        _ordinal_Password = reader.GetOrdinal("Password");

        _ordinal_PhoneWork = reader.GetOrdinal("PhoneWork");

        _ordinal_PhoneMobile = reader.GetOrdinal("PhoneMobile");

    }

 

    public Object GetData(IDataReader reader)

    {

        // This is where we define the mapping between the object properties and the

        // data columns. The convention that should be used is that the object property

        // names are exactly the same as the column names. However if there is some

        // compelling reason for the names to be different the mapping can be defined here.

 

        // We assume the reader has data and is already on the row that contains the data

        //we need. We don't need to call read. As a general rule, assume that every field must

        //be null  checked. If a field is null then the nullvalue for that  field has already

        //been set by the DTO constructor, we don't have to change it. 

        if (!_isInitialized) { InitializeMapper(reader); }

        User dto = new User();

        // Now we can load the data

        if (!reader.IsDBNull(_ordinal_UserGuid)) { dto.UserGuid = reader.GetGuid(_ordinal_UserGuid); }

        if (!reader.IsDBNull(_ordinal_UserRole)) { dto.UserRole = (UserRole)reader.GetInt32(_ordinal_UserRole); }

        if (!reader.IsDBNull(_ordinal_CreatedUtc)) { dto.CreatedUtc = reader.GetDateTime(_ordinal_CreatedUtc); }

        if (!reader.IsDBNull(_ordinal_ModifiedUtc)) { dto.ModifiedUtc = reader.GetDateTime(_ordinal_ModifiedUtc); }

        if (!reader.IsDBNull(_ordinal_CompanyGuid)) { dto.CompanyGuid = reader.GetGuid(_ordinal_CompanyGuid); }

        if (!reader.IsDBNull(_ordinal_FirstName)) { dto.FirstName = reader.GetString(_ordinal_FirstName); }

        if (!reader.IsDBNull(_ordinal_LastName)) { dto.LastName = reader.GetString(_ordinal_LastName); }

        if (!reader.IsDBNull(_ordinal_Email)) { dto.Email = reader.GetString(_ordinal_Email); }

        if (!reader.IsDBNull(_ordinal_Password)) { dto.Password = reader.GetString(_ordinal_Password); }

        if (!reader.IsDBNull(_ordinal_PhoneWork)) { dto.PhoneWork = reader.GetString(_ordinal_PhoneWork); }

        if (!reader.IsDBNull(_ordinal_PhoneMobile)) { dto.PhoneMobile = reader.GetString(_ordinal_PhoneMobile); }

        return dto;

    }

 

    public int GetRecordCount(IDataReader reader)

    {

        Object count = reader["RecordCount"];

        return count == null ? 0 : Convert.ToInt32(count);

    }

}

You’ll notice that the first thing we do is declare an _ordinal_FieldName member for each property in our User class.  These local members are used to store the ordinal location for the column in the SqlDataReader that maps to that User field.  We also create an _isInitialized flag that let’s us know whether we’ve set our _ordinal_FieldName values yet.  This is important when the UserMapper is being used to get more than one User from a DataReader.  The SqlDao code will get a single IDataMapper instance and will then call that mapper over and over for each record that exists in the SqlDataReader. We only want to find the ordinals once, so we create our _isInitialized flag that can be set to true after we set all the ordinal values.

Next is the InitializeMapper method.  This just calls our PopulateOrdinals method and then sets that _isInitialized flag, that we just discussed, to true.

PopulateOrdinals is the method that takes our SqlDataReader, finds the ordinal location of each field we need, then stores those ordinal values in our local _ordinal_FieldName members.

Finally we arrive at GetData, the heart of our class.  We’ve structured our code in such a way that GetData is actually pretty simple.  We check to make sure the _isInitialized flag has been set. If it hasn’t we call PopulateOrdinals.  We then create a new instance of our User class called dto. After that it’s just a matter of repeating the same line of code for each field.

if (!reader.IsDBNull(_ordinal_UserGuid)) { dto.UserGuid = reader.GetGuid(_ordinal_UserGuid); }  

This one line of code performs a null check on the ordinal, if the column exists it uses a strongly typed Get method to get the value of that column and assign it to the dto, if the column is null then we do nothing because the constructor for User already set each property to it’s null value on creation.  And that’s it, after setting each field value (or not setting it) we just return our dto and we’re done.  Simple maintainable, easy to copy and reproduce code.

The last method is GetRecordCount.  We need this method to support the DataPage<DTO> shape.  Our SqlDao needs to know the total record count if it’s returning a data page.  If it’s not returning a data page this method isn’t used.

Conclusion

That’s it.  Now that I look at the DataMapper code its a little anti-climactic because it’s really very simple stuff.  But then that’s the idea, break a complex operation into smaller pieces so that each one is easy to understand, and if needed, reproduce.  To add a new DataMapper we just copy and paste the existing UserMapper and change the field names.  Easy.  We don’t need to touch any of the code at the SqlDao level that handles getting Single<DTO>, List<DTO>, or DataPage<DTO> return types.  We also don’t touch any code that affects any other data shape.  All of our code is encapsulated in this one easy to handle class. 

Also, keep in mind that the real power of this architecture is that we don’t need to create a type specific DataMapper at all.  For the vast majority of tasks, the GenericMapper is going to work just fine.  In practice I never create any type-specific DataMappers during development because it seems like at that early stage data shapes are always in flux, and when a data shape changes, the last thing I want to do is go back and update a bunch of mapping code.  I look at the type specific DataMappers as something that I never want to use unless an app has gone into production and it’s experiencing performance issues related to the GenericMapper.  If that happens then I have a hook in the framework that I can use to create a simple DataMapper for that data shape that contains what I think is the fastest ADO.Net code possible. 

Next time we’ll go over the GenericMapper.

Monday, April 19, 2010

AAPL Part 5: How to Write a Data Access Object (DAO)

I recently decided that I needed to take a fresh look at how to build a persistence architecture that would provide the flexibility of an ORM, that would embrace change instead of resist it (making maintenance code easier), and would use ADO.Net.  I started building from the ground up, threw away any best practices that resulted in friction, and Agile ADO.Net Persistence Layer is the result.  Part 1 in the series can be found here: Agile ADO.Net Persistence Layer Overview

In this post, we’ll get into what is probably the most useful class in the framework, the Data Access Object (DAO).

Where does the DAO fit in?

Let’s backtrack a bit and take a quick look at the design of core.  Below we see a snapshot of Core’s folder structure.  We have a Persistence folder that contains our SqlDAO (a DAO written specifically for SqlServer), as well as our DataMappers and Persisters (we’ll go into these later).  The Rules folder contains business rules and validation rules for different entities / data shapes.  Security has a little plumbing code for business layer authorization.  Finally, the Services folder contains the service classes that we’ve focused on during the previous few posts. 

image

The service classes are the one stop shop that our UI code uses to get and save data.  A typical data access method looks like this.

public User GetUserByEmail(string email)

{

    string sql = @"SELECT *

                  FROM [User]

                  WHERE [Email] = @Email";

    SqlDao dao = SharedSqlDao;

    SqlCommand command = dao.GetSqlCommand(sql);

    command.Parameters.Add(dao.CreateParameter("@Email", email, 100));

    return dao.GetSingle<User>(command);

}

You’ll notice that this is a pretty simple method.  There’s no real Ado.Net code. We’re just defining a query, wrapping it in an SqlCommand, and then passing both the SqlCommand and our desired data shape <User> to our DAO.  This mystery DAO object does all the heavy lifting for us and even helps us create our query parameter in very efficient, single line of code.  This is a very intentional design.  After writing data access code for a few years, I finally noticed that the only things I really change from one data access method to the next are: the query; the parameters; and the return data shape. Everything else is just boilerplate plumbing code that gets repeated over and over again.  So, I took all that data access boilerplate code and extracted it out to a helper class called SqlDao.  The main functions of the DAO are:

  • The DAO is the single access point to our database. All communication with the database goes through the DAO.  No code goes around it.
  • The DAO encapsulates common ADO.Net tasks like getting a connection, creating query parameters, executing queries, etc.
  • The DAO defines methods that take an SqlCommand as a parameter, execute it, and return the data in one of our standard formats, a single DTO, as List<DTO>, a DataPage<DTO>, or a string value.     

There’s a lot of good stuff in there that makes writing data access method almost frictionless.  I can’t go over everything so I included a complete listing for the SqlDao at the end of this post.

The GetSingle<T> and GetList<T> methods

Now let’s look at something a little more interesting. The SqlDao.GetSingle<T> method takes an SqlCommand as a parameter and returns the restults of that command as a single T where T is the class of our desired datashape.  So, the line dao.GetSingle<User>(command); will execute command and then return an object of type User.  If no User was found then the method just returns null.  Here’s how it works.

// GetSingle

public T GetSingle<T>(SqlCommand command) where T : class

{

    T dto = null;

    try

    {

        if (command.Connection.State != ConnectionState.Open)

        {

            command.Connection.Open();

        }               

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)

        {

            reader.Read();

            IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

            dto = (T)mapper.GetData(reader);

            reader.Close();

        }

    }

    catch (Exception e)

    {

        throw new Exception("Error populating data", e);

    }

    finally

    {

        command.Connection.Close();

    }

    // return the DTO, it's either populated with data or null.

    return dto;

}

The code is pretty simple.  We declare a new object of type T called dto (Data Transfer Object) and initialize it to null. We then check the connection on our command and make sure it’s open.  We call ExecuteReader on the command which gets us an SqlDataReader.  If the reader has any rows then we go to our DataMapperFactory and get an IDataMapper for the type T.  A cool thing that is happening behind the scenes is that the DataMapperFactory checks to see if we have an IDataMapper that has been created specifically for type T, if we don’t it returns a GenericMapper that uses reflection to map SqlDataReader fields to fields on our type T.  Once we have a mapper it’s just a matter of calling the mapper.GetData(reader) method and returning the dto. 

Now let’s take a quick look at GetList<T>.  This method is very similar to GetSingle<T>, but it returns a generic list of objects of type T instead of a single object of type T.  If no results are returned when we execute the SqlCommand passed to GetList<T>, we don’t return null, instead we return an empty list.  Here’s the code.                   

// GetList

public List<T> GetList<T>(SqlCommand command) where T : class

{

    List<T> dtoList = new List<T>();

    try

    {

        if (command.Connection.State != ConnectionState.Open)

        {

            command.Connection.Open();

        }  

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)

        {

            IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

            while (reader.Read())

            {

                T dto = null;

                dto = (T)mapper.GetData(reader);

                dtoList.Add(dto);

            }

            reader.Close();

        }

    }

    catch (Exception e)

    {

        throw new Exception("Error populating data", e);

    }

    finally

    {

        command.Connection.Close();

    }

    // We return either the populated list if there was data,

    // or if there was no data we return an empty list.

    return dtoList;

}

It’s really the same as GetSingle<T>.  We just have a different return type List<T>, and a while loop that get’s our individual DTOs and adds them to our return dtoList. The main logic for getting an IDataMapper from the DataMapperFactory and calling mapper.GetData(reader) to parse the reader results into an object of type T remains the same.

Conclusion

At this point you may be thinking, “That’s it???, you didn’t answer anything!  All you did is get some mapper object and call the GetData method on it.”  That’s true to some extent.  Our GetSingle<T> and GetList<T> methods just contain the logic that is always going to be common to those tasks regardless of the data shape passed to them.  Anything that is data shape specific, things like how do I get data from this field in my SqlDataReader and put it in this field in my returned data shape, are delegated to the IDataMapper.  This separation of logic means that it’s very easy to maintain our code.  We can add DataMapper logic for new data shapes and never touch any of the logic for GetSingle, GetList, and GetPage.  In fact, our logic for a specific data shape is encapsulated in a single IDataMapper class that touches nothing else in the application. That means it’s easy to change and maintenance friendly.  Plus, don’t forget that we have the reflection-based GenericMapper, so we never have to create a new IDataMapper if we don’t want to.

Next time we’ll take a look at a data shape specific IDataMapper and the DataMapperFactory. In the meantime, the full source for SqlDao is included below.  Have a look at the implementation for GetDataPage<T> and see if it makes sense now that we’ve gone over GetSingle<T> and GetList<T>.  The CreateParameter methods are worth a look too.   I went with a design where I just overload the method CreateParameter for each type of parameter that I need to create, so the usage syntax for CreateParameter(Guid value) and CreateParameter(int value) are almost identical. Each CreateParameter method was designed with the target of enabling that single line syntax that I use: command.Parameters.Add(dao.CreateParameter("@Email", email, 100)); so each method contains automatic null value checking.  You’ll also see some methods that weren’t in my original spec.  While using this framework on different projects I’ve found it convenient to have methods like GetSingleInt32(command) and GetStringList(command). 

Finally you can download a small, one page sample app that uses aapl at http://aapl.codeplex.com/

 

using System;

using System.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

using hf.Common;

using hf.Common.DataShapes;

using hf.Core.Persistence.DataMappers;

 

 

namespace hf.Core.Persistence

{

    public class SqlDao

    {

 

        #region "Database Helper Methods"

 

 

        // Connection

        private SqlConnection _sharedConnection;

        public SqlConnection SharedConnection

        {

            get

            {

                if (_sharedConnection == null)

                {

                    _sharedConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["hf"].ConnectionString);

                }

                return _sharedConnection;

            }

            set

            {

                _sharedConnection = value;

            }

        }

 

 

        // Constructors

        public SqlDao() { }

        public SqlDao(SqlConnection connection)

        {

            this.SharedConnection = connection;

        }

 

 

        // GetDbSqlCommand

        public SqlCommand GetSqlCommand(string sqlQuery)

        {

            SqlCommand command = new SqlCommand();

            command.Connection = SharedConnection;

            command.CommandType = CommandType.Text;

            command.CommandText = sqlQuery;

            return command;

        }

 

 

        // GetDbSprocCommand

        public  SqlCommand GetSprocCommand(string sprocName)

        {

            SqlCommand command = new SqlCommand(sprocName);

            command.Connection = SharedConnection;

            command.CommandType = CommandType.StoredProcedure;

            return command;

        }

 

 

        // CreateNullParameter

        public  SqlParameter CreateNullParameter(string name, SqlDbType paramType)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Value = DBNull.Value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateNullParameter - with size for nvarchars

        public  SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Size = size;

            parameter.Value = DBNull.Value;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateOutputParameter

        public  SqlParameter CreateOutputParameter(string name, SqlDbType paramType)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.ParameterName = name;

            parameter.Direction = ParameterDirection.Output;

            return parameter;

        }

 

 

        // CreateOuputParameter - with size for nvarchars

        public  SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = paramType;

            parameter.Size = size;

            parameter.ParameterName = name;

            parameter.Direction = ParameterDirection.Output;

            return parameter;

        }

 

 

        // CreateParameter - uniqueidentifier

        public  SqlParameter CreateParameter(string name, Guid value)

        {

            if (value.Equals(NullValues.NullGuid))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.UniqueIdentifier);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.UniqueIdentifier;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - int

        public  SqlParameter CreateParameter(string name, int value)

        {

            if (value == NullValues.NullInt)

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.Int);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.Int;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - bool

        public SqlParameter CreateParameter(string name, bool value)

        {

            SqlParameter parameter = new SqlParameter();

            parameter.SqlDbType = SqlDbType.Bit;

            parameter.ParameterName = name;

            parameter.Value = value ? 1 : 0;

            parameter.Direction = ParameterDirection.Input;

            return parameter;

        }

 

 

        // CreateParameter - datetime

        public  SqlParameter CreateParameter(string name, DateTime value)

        {

            if (value == NullValues.NullDateTime)

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.DateTime);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.DateTime;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateParameter - nvarchar

        public  SqlParameter CreateParameter(string name, string value, int size)

        {

            if (String.IsNullOrEmpty(value))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.NVarChar);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.NVarChar;

                parameter.Size = size;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

 

        // CreateTextParameter

        public SqlParameter CreateTextParameter(string name, string value)

        {

            if (String.IsNullOrEmpty(value))

            {

                // If value is null then create a null parameter

                return CreateNullParameter(name, SqlDbType.Text);

            }

            else

            {

                SqlParameter parameter = new SqlParameter();

                parameter.SqlDbType = SqlDbType.Text;

                parameter.ParameterName = name;

                parameter.Value = value;

                parameter.Direction = ParameterDirection.Input;

                return parameter;

            }

        }

 

        #endregion

 

 

 

        #region "Data Projection Methods"

 

 

        // ExecuteNonQuery

        public void ExecuteNonQuery(SqlCommand command)

        {

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                command.ExecuteNonQuery();

            }

            catch (Exception e)

            {

                throw new Exception("Error executing query", e);

            }

            finally

            {

                command.Connection.Close();

            }

        }

 

 

        // ExecuteScalar

        public Object ExecuteScalar(SqlCommand command)

        {

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                return command.ExecuteScalar();

            }

            catch (Exception e)

            {

                throw new Exception("Error executing query", e);

            }

            finally

            {

                command.Connection.Close();

            }

        }

 

 

        // GetSingleValue

        public T GetSingleValue<T>(SqlCommand command)

        {

            T returnValue = default(T);

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetSingleInt32

        public Int32 GetSingleInt32(SqlCommand command)

        {

            Int32 returnValue = default(int);

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetSingleString

        public string GetSingleString(SqlCommand command)

        {

            string returnValue=null;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnValue;

        }

 

 

        // GetGuidList

        public List<Guid> GetGuidList(SqlCommand command)

        {

            List<Guid> returnList = new List<Guid>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    returnList = new List<Guid>();

                    while (reader.Read())

                    {

                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetGuid(0)); }

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnList;

        }

 

 

        // GetStringList

        public List<string> GetStringList(SqlCommand command)

        {

            List<string> returnList = new List<string>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    returnList = new List<string>();

                    while (reader.Read())

                    {

                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }                   

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return returnList;

        }

 

 

        // GetSingle

        public T GetSingle<T>(SqlCommand command) where T : class

        {

            T dto = null;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }               

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    reader.Read();

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    dto = (T)mapper.GetData(reader);

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            // return the DTO, it's either populated with data or null.

            return dto;

        }

 

 

        // GetList

        public List<T> GetList<T>(SqlCommand command) where T : class

        {

            List<T> dtoList = new List<T>();

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }  

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    while (reader.Read())

                    {

                        T dto = null;

                        dto = (T)mapper.GetData(reader);

                        dtoList.Add(dto);

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            // We return either the populated list if there was data,

            // or if there was no data we return an empty list.

            return dtoList;

        }

 

 

 

 

        // GetDataPage

        public DataPage<T> GetDataPage<T>(SqlCommand command, int pageIndex, int pageSize) where T : class

        {  

            DataPage<T> page = new DataPage<T>();

            page.PageIndex = pageIndex;

            page.PageSize = pageSize;

            try

            {

                if (command.Connection.State != ConnectionState.Open)

                {

                    command.Connection.Open();

                }

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)

                {

                    IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));

                    while (reader.Read())

                    {

                        // get the data for this row

                        T dto = null;

                        dto = (T)mapper.GetData(reader);

                        page.Data.Add(dto);

                        // If we haven't set the RecordCount yet then set it

                        if (page.RecordCount == 0) { page.RecordCount = mapper.GetRecordCount(reader); }

                    }

                    reader.Close();

                }

            }

            catch (Exception e)

            {

                throw new Exception("Error populating data", e);

            }

            finally

            {

                command.Connection.Close();

            }

            return page;

        }

 

        #endregion

 

    }

}