SQL stored procedure C# object mapper

21 Mar 2010

Pure ADO.NET is probably the fastest way to RDBMS data access and manipulation. On the other hand it doesn’t provide abstraction layer and mapping features of object-relational mappers like LINQ to SQL or Entity Framework. However mapping (without relations) can be easily improvised with the little use of reflection and generics.

Example below automatically maps output of MySQL stored procedure into generic collection of objects. Core functionality is written in DbManager class which provides three main methods for data access and manipulation: ExecuteSingle (returns single mapped object), ExecuteList (returns generic collection of mapped objects) and ExecuteNonQuery (used for non query stored procedures like INSERT, UPDATE or DELETE). All three methods support IN and OUT parameters. This C# example is written to work with MySQL .NET connector, but can be extended to any type of database which have .NET connector.

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data;
using MySql.Data.MySqlClient;

public class DbManager
{
    private static string ConnectionString
    {
        get
        {
            return @"server=my.sql.server;user id=youHere;password=yourPassword;database=yourTestDB";
        }
    }

    private MySqlConnection Connection { get; set; }

    private MySqlCommand Command { get; set; }

    public List<DbParameter> OutParameters { get; private set; }

    private void Open()
    {
        try
        {
            Connection = new MySqlConnection(ConnectionString);

            Connection.Open();
        }
        catch (Exception ex)
        {
            Close();
        }
    }

    private void Close()
    {
        if (Connection != null)
        {
            Connection.Close();
        }
    }

    // executes stored procedure with DB parameteres if they are passed
    private object ExecuteProcedure(string procedureName, ExecuteType executeType, List<DbParameter> parameters)
    {
        object returnObject = null;

        if (Connection != null)
        {
            if (Connection.State == ConnectionState.Open)
            {
                Command = new MySqlCommand(procedureName, Connection);
            Command.CommandType = CommandType.StoredProcedure;

                // pass stored procedure parameters to command
                if (parameters != null)
            {
                    Command.Parameters.Clear();

                foreach (DbParameter dbParameter in parameters)
                {
                        MySqlParameter parameter = new MySqlParameter();
                        parameter.ParameterName = "@" + dbParameter.Name;
                        parameter.Direction = dbParameter.Direction;
                        parameter.Value = dbParameter.Value;
                        Command.Parameters.Add(parameter);
                }
            }

                switch (executeType)
                {
                    case ExecuteType.ExecuteReader:
                        returnObject = Command.ExecuteReader();
                        break;
                    case ExecuteType.ExecuteNonQuery:
                        returnObject = Command.ExecuteNonQuery();
                        break;
                    case ExecuteType.ExecuteScalar:
                        returnObject = Command.ExecuteScalar();
                        break;
                    default:
                        break;
                }
            }
        }

        return returnObject;
    }

    // updates output parameters from stored procedure
    private void UpdateOutParameters()
    {
        if(Command.Parameters.Count > 0)
        {
            OutParameters = new List<DbParameter>();
            OutParameters.Clear();

            for (int i = 0; i < Command.Parameters.Count; i++)
            {
                if (Command.Parameters[i].Direction == ParameterDirection.Output)
                {
                    OutParameters.Add(new DbParameter(Command.Parameters[i].ParameterName, 
                                                      ParameterDirection.Output, 
                                                      Command.Parameters[i].Value));
                }
            }
        }
    }

    // executes scalar query stored procedure without parameters
    public T ExecuteSingle<T>(string procedureName) where T : new()
    {
        return ExecuteSingle<T>(procedureName, null);
    }

    // executes scalar query stored procedure and maps result to single object
    public T ExecuteSingle<T>(string procedureName, List<DbParameter> parameters) where T : new()
    {
        Open();
        IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
        T tempObject = new T();

        if (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
                propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
            }
        }

        reader.Close();

        UpdateOutParameters();

        Close();

        return tempObject;
    }

    // executes list query stored procedure without parameters
    public List<T> ExecuteList<T>(string procedureName) where T : new()
    {
        return ExecuteList<T>(procedureName, null);
    }

    // executes list query stored procedure and maps result generic list of objects
    public List<T> ExecuteList<T>(string procedureName, List<DbParameter> parameters) where T : new()
    {
        List<T> objects = new List<T>();

        Open();
        IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);

        while (reader.Read())
        {
            T tempObject = new T();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (reader.GetValue(i) != DBNull.Value)
                {
                    PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
                    propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
                }
            }

            objects.Add(tempObject);
        }

        reader.Close();

        UpdateOutParameters();

        Close();

        return objects;
    }

    // executes non query stored procedure with parameters
    public int ExecuteNonQuery(string procedureName, List<DbParameter> parameters)
    {
        int returnValue;

        Open();

        returnValue = (int)ExecuteProcedure(procedureName, ExecuteType.ExecuteNonQuery, parameters);

        UpdateOutParameters();

        Close();

        return returnValue;
    }
}

public enum ExecuteType
{
    ExecuteReader,
    ExecuteNonQuery,
    ExecuteScalar
};

public class DbParameter
{
    public string Name { get; set; }
    public ParameterDirection Direction { get; set; }
    public object Value { get; set; }

    public DbParameter(string paramName, ParameterDirection paramDirection, object paramValue)
    {
        Name = paramName;
        Direction = paramDirection;
        Value = paramValue;
    }
}

MySQL stored procedure which will be mapped:

CREATE DEFINER=`tomi`@`%` PROCEDURE `GetCategory`(
        IN categoryID TINYINT
)
BEGIN
        SELECT ID, Title
        FROM Categories
        WHERE ID = categoryID;
END

Category class to which will be mapped stored procedure output:

using System;

public class Category
{
    public byte ID { get; set; }
    public string Title { get; set; }
}

Usage:

long postID = 123;

List<DbParameter> parameters = new List<DbParameter>();
parameters.Add(new DbParameter("postID", System.Data.ParameterDirection.Input, postID));

List<Category> categories = DbManager.ExecuteList<Category>("GetCategories", parameters);

Field names of SQL stored procedure and property names of C# class have to be identical. This is prone to obvious runtime errors, but they can be easily resolved. You can checkout the project at github.