DatabaseContext

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

namespace DAL
{
    /// <summary>
    /// Classe padrão de acesso ao banco de dados.
    /// </summary>
    internal class DatabaseContext
    {

        //#if (DEBUG)
        //    private const string CONNECTION_STRING_NAME = "DefaultConnection";
        //#else
        //        private const string CONNECTION_STRING_NAME = "DefaultConnection";
        //#endif

        private string _idString;

        public DatabaseContext(string idString)
        {
            _idString = idString;
        }

        internal string GetConnectionString(out Exception erro)
        {
            erro = null;

            string connString = "";
            try
            {
                connString = ConfigurationManager.ConnectionStrings[_idString].ConnectionString;

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connString);

#if (DEBUG)
                builder.Password = "helpdesk";
#else
                builder.Password = "helpdesk";
#endif
                return builder.ConnectionString;

            }
            catch (Exception ex)
            {
                erro = ex;
                Debug.WriteLine(ex.Message);
                return string.Empty;
            }

        }
        /// <summary>
        /// Método que pesquisa e retorna dados em um DataTable na base:
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>

        public DataTable ExecuteReader(ref SqlCommand cmd, out Exception erro)
        {
            return (DataTable)Execute(ref cmd, EnuDMLExecute.Reader, out erro);
        }
        /// <summary>
        /// Método que pesquisa e retorna um único valor na base:
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public string ExecuteScalar(ref SqlCommand cmd, out Exception erro)
        {
            return Execute(ref cmd, EnuDMLExecute.Scalar, out erro).ToString();
        }

        /// <summary>
        /// Método que altera e retorna a quantidade de registros afetados na base:
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns>int</returns>
        public int ExecuteNonQuery(ref SqlCommand cmd, out Exception erro)
        {
            return (int)Execute(ref cmd, EnuDMLExecute.NonQuery, out erro);
        }
        /// <summary>
        /// Método que executa a conexão com o banco de dados e retorna, dependendo de <dml>:
        /// - EnuDMLExecute.Reader   => DataTable
        /// - EnuDMLExecute.NonQuery => int
        /// - EnuDMLExecute.Scalar   => objeto
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dml"></param>
        /// <param name="tr"></param>
        /// <returns></returns>
        public object Execute(ref SqlCommand cmd, EnuDMLExecute dml, out Exception erro)
        {

            object ret = null;

            try
            {
                using (SqlConnection cnn = new SqlConnection(GetConnectionString(out erro)))
                {
                    cnn.Open();
                    using (SqlCommand comm = cmd)
                    {
                        comm.Connection = cnn;
#if (DEBUG)
                        comm.CommandTimeout = 100;
#else
                        comm.CommandTimeout = 30;
#endif
                        switch (dml)
                        {
                            case EnuDMLExecute.Reader:

                                using (DataTable dt = new DataTable())
                                {
                                    dt.Load(comm.ExecuteReader());
                                    ret = dt;
                                }
                                break;

                            case EnuDMLExecute.NonQuery:
                                ret = comm.ExecuteNonQuery();
                                break;

                            case EnuDMLExecute.Scalar:
                                ret = comm.ExecuteScalar();
                                break;

                            default:
                                return null;
                        }

                    }
                }

                return ret;
            }
            catch (Exception ex)
            {
                erro = ex;
                Debug.WriteLine(ex.Message);
                return null;
            }

        }

        /// <summary>
        /// Método que executa a conexão com o banco de dados e retorna, dependendo de <dml>:
        /// - EnuDMLExecute.Reader   => DataTable
        /// - EnuDMLExecute.NonQuery => int
        /// - EnuDMLExecute.Scalar   => objeto
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="dml"></param>
        /// <param name="tr"></param>
        /// <returns></returns>
        public int InsertRowList(string table, List<object[]> rows, List<DBColumn> cols, out Exception erro)
        {

            int rowsAffected = 0;
            string pars;
            string colnames;

            try
            {
                using (SqlConnection cnn = new SqlConnection(GetConnectionString(out erro)))
                {
                    cnn.Open();

                    using (SqlCommand cmd = new SqlCommand("", cnn))
                    {
                        cmd.CommandText = "INSERT INTO " + table + " ";

                        colnames = ""; pars = "";
                        cmd.Parameters.Clear();

                        for (int i = 0; i < cols.Count; i++)
                        {
                            colnames += cols[i].Name + ",";
                            pars += "@p" + i + ",";
                            cmd.Parameters.Add("@p" + i, cols[i].Type);
                        }
                        colnames = colnames.Substring(0, colnames.Length - 1);
                        pars = pars.Substring(0, pars.Length - 1);

                        cmd.CommandText += " (" + colnames + ") VALUES (" + pars + ")";

                        cmd.Transaction = cnn.BeginTransaction();

                        try
                        {
                            for (int i = 0; i < rows.Count(); i++)
                            {
                                for (int j = 0; j < rows[i].Count(); j++)
                                {
                                    switch (cols[j].Type)
                                    {
                                        case SqlDbType.SmallDateTime:
                                        case SqlDbType.Date:
                                            if (DateTime.TryParse(rows[i][j].ToString(), out DateTime result))
                                            {
                                                if (rows[i][j].ToString() != "00:00:00")
                                                    cmd.Parameters["@p" + j].Value = rows[i][j];
                                            }
                                            else
                                            {
                                                cmd.Parameters["@p" + j].Value = DBNull.Value;
                                            }
                                            break;
                                        // Inserir novos casos:
                                        // case SqlDbType.???
                                        //  break;
                                        default:
                                            cmd.Parameters["@p" + j].Value = rows[i][j];
                                            break;
                                    }
                                }
                                rowsAffected += cmd.ExecuteNonQuery();
                            }
                            cmd.Transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            rowsAffected = 0;
                            cmd.Transaction.Rollback();
                            erro = ex;
                            Debug.WriteLine(ex.Message);
                        }
                    }
                }
                return rowsAffected;
            }
            catch (Exception ex)
            {
                erro = ex;
                Debug.WriteLine(ex.Message);
                return -1;
            }
        }
    }

    public class DBColumn
    {
        public string Name { get; set; }
        public SqlDbType Type { get; set; }
        public int Size { get; set; }
        //    public DBColumn() : this("",SqlDbType.VarChar,0) { }
        public DBColumn(string name, SqlDbType type) : this(name, type, 0) { }
        public DBColumn(string name, SqlDbType type, int size)
        {
            Name = name;
            Type = type;
            Size = size;
        }

    }
    public enum EnuDMLExecute : int
    {
        Reader = 0,
        NonQuery = 1,
        Scalar = 2
    }
}