C#.Net DAO Access class template

Something I had around there:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;


namespace Blabla.Data
{
    public interface IDataAccess
    {
        SqlDataReader GetReader(string sql, List<SqlParameter> pl = null);
        object GetScalar(string sql, List<SqlParameter> pl = null);
        DataSet GetDataset(string sql, List<SqlParameter> paramsList = null);
        int ExecuteNonQuery(string query, List<SqlParameter> paramsList = null, CommandType commandType = CommandType.Text, bool avoidTransaction = true);
    }

    public class DaoTemplate : IDataAccess
    {
        public const string DefaultConnectionString = "DefaultConn";

        private SqlConnection GetConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings[DefaultConnectionString].ConnectionString);
        }

        public SqlDataReader GetReader(string sql, List<SqlParameter> paramsList = null)
        {
            SqlConnection conex = GetConnection();
            try
            {
                conex.Open();
                SqlCommand cmd = conex.CreateCommand();

                cmd.CommandText = sql;
                cmd.CommandType = CommandType.StoredProcedure;

                if (paramsList != null)
                {
                    cmd.Parameters.AddRange(paramsList.ToArray());
                }

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (InvalidCastException e)
            {
                throw (e);
            }
        }

        public object GetScalar(string sql, List<SqlParameter> paramsList = null)
        {
            using (SqlConnection conex = GetConnection())
            {
                try
                {
                    conex.Open();
                    SqlCommand cmd = conex.CreateCommand();

                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (paramsList != null)
                    {
                        cmd.Parameters.AddRange(paramsList.ToArray());
                    }

                    object res = cmd.ExecuteScalar();
                    return res;
                }
                catch (InvalidCastException e)
                {
                    throw (e);
                }
            }
        }

        public DataSet GetDataset(string sql, List<SqlParameter> paramsList = null)
        {
            using (SqlConnection conex = GetConnection())
            {
                try
                {
                    conex.Open();
                    SqlCommand cmd = conex.CreateCommand();

                    cmd.CommandText = sql;
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (paramsList != null)
                    {
                        cmd.Parameters.AddRange(paramsList.ToArray());
                    }

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds;
                }
                catch (InvalidCastException e)
                {
                    throw (e);
                }
            }
        }

        public int ExecuteNonQuery(string query, List<SqlParameter> paramsList = null, 
                                       CommandType commandType = CommandType.Text, bool avoidTransaction = true)
        {
            if (query == null || query.Length.Equals(0)) { throw new ArgumentNullException("query"); }

            SqlTransaction trans = null;
            using (SqlConnection conex = GetConnection())
            {
                try
                {
                    conex.Open();
                    SqlCommand cmd = conex.CreateCommand();
                    
                    cmd.CommandText = query;
                    cmd.CommandType = commandType;

                    if (paramsList != null)
                    {
                        cmd.Parameters.AddRange(paramsList.ToArray());
                    }

                    if (!avoidTransaction)
                    {
                        trans = conex.BeginTransaction();
                        cmd.Transaction = trans;
                    }

                    int res = cmd.ExecuteNonQuery();

                    if (!avoidTransaction) trans.Commit();

                    return res;
                }
                catch (Exception e)
                {
                    if (!avoidTransaction) trans.Rollback();
                    throw (e);
                }
                finally
                {
                    if (!avoidTransaction) trans.Dispose();
                }
            }
        }


    }
}

Leave a Reply

Close Bitnami banner
Bitnami