C Sharp - C# - SQLServer - Ejecuta un procedimiento almacenado y retorna el resultado como una lista de objetos o como una Data Table

Conector.cs

/*
    Create by Jsn  jason.scz@gmail.com
 */

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

namespace Acceso_a_datos
{
    public class Conector
    {
        private SqlConnection myConnection;
        private string serverName;
        private string userServer;
        private string passwordServer;
        private string databaseServer;

        /// <summary>
        /// Establece la conexion con el servidor
        /// </summary>
        /// <param name="serverName">Nombre del servidor o direccion ip.</param>
        /// <param name="userServer">Nombre de usuario</param>
        /// <param name="passwordServer">Password del usuario</param>
        public Conector(string serverName, string userServer, string passwordServer, string databaseServer)
        {
            this.serverName = serverName;
            this.userServer = userServer;
            this.passwordServer = passwordServer;
            this.databaseServer = databaseServer;
            TryConnect();
        }

        public Conector()
        {
            this.databaseServer = Configuracion.db_name;
            this.serverName = Configuracion.db_server;
            this.userServer = Configuracion.db_user;
            this.passwordServer = Configuracion.db_password;
            TryConnect();
        }

        private bool TryConnect()
        {
            try
            {
                myConnection = new SqlConnection(
                    "user id=" + userServer + ";" +
                    "password=" + passwordServer + ";" +
                    "server=" + serverName + ";" +
                    "Persist Security Info=True;" +
                    "database=" + databaseServer + "; "
                    );
                myConnection.Open();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Retorna el estado de la conexion.
        /// </summary>
        /// <returns>true: Si la conexion esta establecida, false: Si la conexion no esta establecida.</returns>
        private bool IsConnected()
        {
            return (myConnection.State == ConnectionState.Open);
        }



        /// <summary>
        /// Ejecuta un procedimiento almacenado y retorna una lista de objetos, Si la ejecucion del procedimiento falla retorna null.
        /// </summary>
        /// <param name="procedure">Nombre del procedimiento almacenado</param>
        /// <param name="param">Parametros del procedimiento separados por coma</param>
        /// <returns>Lista de objetos dinamicos</returns>
        public List<dynamic> Exec(String procedure, params object[] param)
        {
            try
            {
                if (!IsConnected())
                {
                    if (!TryConnect()) throw new Exception();
                }
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = procedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;
                if (param.Length > 0)
                {
                    SqlCommandBuilder.DeriveParameters(cmd);
                    if (param.Length > cmd.Parameters.Count - 1) return null;
                    var pos = -1;
                    foreach (SqlParameter p in cmd.Parameters)
                    {
                        if (pos == -1) { pos++; continue; }
                        p.Value = param[pos];
                        pos++;
                    }
                }

                SqlDataReader reader = cmd.ExecuteReader();

                // Total columns
                int columns = reader.FieldCount;
                List<dynamic> _list = new List<dynamic>();

                while (reader.Read())
                {
                    // create a dinamic object
                    ExpandoObject eo = new ExpandoObject();
                    var ieo = eo as IDictionary<String, Object>;
                    for (int column = 0; column < columns; column++)
                    {
                        ieo.Add( reader.GetName(column) , reader[column]);
                    }

                    _list.Add(eo);
                }

                reader.Close();
                return _list;
            }
            catch (Exception e)
            {
                if (Configuracion.db_debug) {
                    List<dynamic> _list = new List<dynamic>();
                    dynamic o = new ExpandoObject();
                    o.ERROR = e.Message;
                    _list.Add(o);
                    return _list;
                }
                else
                {
                    return null;
                }
            }
        }

        public DataTable Exec2(String procedure, params object[] param)
        {
            try
            {
                if (!IsConnected())
                {
                    if (!TryConnect()) throw new Exception();
                }
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = procedure;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;
                if (param.Length > 0)
                {
                    SqlCommandBuilder.DeriveParameters(cmd);
                    if (param.Length > cmd.Parameters.Count - 1) return null;
                    var pos = -1;
                    foreach (SqlParameter p in cmd.Parameters)
                    {
                        if (pos == -1) { pos++; continue; }
                        p.Value = param[pos];
                        pos++;
                    }
                }

                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                return dt;

            }
            catch (Exception e)
            {
                return null;
            }
        }

    }
}