Reusable Data Layer In C#

The goal here is to take advantage of C# 2.0 features as generics and create a reusable data layer capable of calling any stored procedure, with any sets of parameters and returning different sets of results.

The DataUtility class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

///
/// Summary description for DataUtility
///
namespace Data
{
    public class DataUtility : Common
    {
        private string conStr;

        private enum ExecuteType
        {
            NonQuery,
            Scalar,
            Reader,
            DataRow,
            DataTable,
            DataSet
        }

        public DataUtility(string conStr)
        {
            this.conStr = conStr;
        }

        public Result ExecuteNonQuery(string storedProcedureName, Hashtable parameters)
        {
            object returnValue;
            return Execute(out returnValue, storedProcedureName, parameters, ExecuteType.NonQuery);
        }

        public Result ExecuteScalar(out object scalar, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out scalar, storedProcedureName, parameters, ExecuteType.Scalar);
        }

        public Result ExecuteReader(out SqlDataReader reader, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out reader, storedProcedureName, parameters, ExecuteType.Reader);
        }

        public Result ExecuteDataRow(out DataRow dataRow, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataRow, storedProcedureName, parameters, ExecuteType.DataRow);
        }

        public Result ExecuteDataTable(out DataTable dataTable, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataTable, storedProcedureName, parameters, ExecuteType.DataTable);
        }

        public Result ExecuteDataSet(out DataSet dataSet, string storedProcedureName, Hashtable parameters)
        {
            return Execute(out dataSet, storedProcedureName, parameters, ExecuteType.DataSet);
        }

        private Result Execute(out T returnValue, string storedProcedureName, Hashtable parameters, ExecuteType executeType) where T : class
        {
            Result result = new Result();
            returnValue = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    SqlCommand command = new SqlCommand(storedProcedureName, connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Connection.Open();

                    foreach (string name in parameters.Keys)
                    {
                        command.Parameters.AddWithValue(name, parameters[name]);
                    }

                    SqlDataAdapter adapter;
                    DataTable dataTable;

                    switch (executeType)
                    {
                        case ExecuteType.NonQuery:
                            command.ExecuteNonQuery();
                            break;

                        case ExecuteType.Scalar:
                            returnValue = command.ExecuteScalar() as T;
                            break;

                        case ExecuteType.Reader:
                            returnValue = command.ExecuteReader() as T;
                            break;

                        case ExecuteType.DataRow:
                            adapter = new SqlDataAdapter(command);
                            dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            if (dataTable.Rows.Count > 0)
                            {
                                returnValue = dataTable.Rows[0] as T;
                            }

                            break;

                        case ExecuteType.DataTable:
                            adapter = new SqlDataAdapter(command);
                            dataTable = new DataTable();
                            adapter.Fill(dataTable);
                            returnValue = dataTable as T;
                            break;

                        case ExecuteType.DataSet:
                            adapter = new SqlDataAdapter(command);
                            DataSet dataSet = new DataSet();
                            adapter.Fill(dataSet);
                            returnValue = dataSet as T;
                            break;
                    }

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                result.ResultType = ResultType.Failure;
                result.Exception = exception;
            }

            return result;
        }
    }
}

The Result class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

///
/// Summary description for Result
///
namespace Data
{
    public class Result : Common
    {
        private ResultType resultType = ResultType.Success;
        private Exception exception = null;

        public Result()
        {
        }

        public Result(ResultType resultType, Exception exception)
        {
            this.resultType = resultType;
            this.exception = exception;
        }

        public Result(ResultType resultType)
        {
            this.resultType = resultType;
        }

        public ResultType ResultType
        {
            get { return resultType; }
            set { resultType = value; }
        }

        public Exception Exception
        {
            get { return exception; }
            set { exception = value; }
        }
    }
}

The Common class

using System;
using System.Collections.Generic;
using System.Text;

namespace Data
{
    public class Common
    {
        public enum ResultType
        {
            Success,
            Failure
        }
    }
}

Example of use:

        public User SignIn(string username, string password)
        {
            User user = null;
            Hashtable parameters = new Hashtable();
            parameters.Add("Name", username);
            parameters.Add("Password", password);
            DataRow dr;

            if ((dataUtility.ExecuteDataRow(out dr, "UserSelect", parameters).ResultType == ResultType.Success) && (dr != null))
            {
                user = new User
                    (
                        Convert.ToInt32(dr["ID"]),
                        dr["Name"].ToString(),
                        dr["Password"].ToString(),
                        dr["Email"].ToString(),
                        dr["Question"].ToString(),
                        dr["Answer"].ToString()
                    );
            }

            return user;
        }