CPStorage.cs

/*
    Create by Jsn  jason.scz@gmail.com
 */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Acceso_a_datos
{
    public class CPStorage
    {
        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 CPStorage(string serverName, string userServer, string passwordServer, string databaseServer)
        {
            this.serverName = serverName;
            this.userServer = userServer;
            this.passwordServer = passwordServer;
            this.databaseServer = databaseServer;
            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</returns>
        public List<object[]> 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++;
                    }
                }
                var reader = cmd.ExecuteReader();
                var result = new List<object[]>();
                var max = reader.FieldCount;
                if (max > 0)
                {
                    var title = new object[max];
                    for (int i = 0; i < max; i++)
                    {
                        title[i] = reader.GetName(i);
                    }
                    result.Add(title);

                    while (reader.Read())
                    {
                        var o = new Object[max];
                        for (int i = 0; i < max; i++)
                        {
                            o[i] = reader[i];
                        }
                        result.Add(o);
                    }
                }
                reader.Close();
                return result;
            }
            catch
            {
                return null;
            }
        }
    }
}