public void EliminarDato(string sNombreSP, string sNombreTabla, DataTable dtParametros,
ref string sMsjError)
{
try
{
Cls_DataBase_BLL OBJ_DataBase_BLL = new Cls_DataBase_BLL();
Cls_DataBase_DAL OBJ_DataBase_DAL = new Cls_DataBase_DAL();
OBJ_DataBase_DAL.SSP_Nombre = sNombreSP;
OBJ_DataBase_DAL.SNombreTabla = sNombreTabla;
OBJ_DataBase_BLL.Crear_Parametros(ref OBJ_DataBase_DAL);
OBJ_DataBase_DAL.dt_Parametros = dtParametros;
OBJ_DataBase_BLL.Execute_NonQuery(ref OBJ_DataBase_DAL);
if (OBJ_DataBase_DAL.SError == string.Empty)
{
sMsjError = string.Empty;
}
else
{
sMsjError = OBJ_DataBase_DAL.SError;
}
}
catch (Exception Error)
{
sMsjError = Error.Message.ToString();
}
}
public DataTable FiltrarDatos(string sNombreSP, string sNombreTabla, DataTable dtParametros,
ref string sMsjError)
{
try
{
Cls_DataBase_BLL OBJ_DataBase_BLL = new Cls_DataBase_BLL();
Cls_DataBase_DAL OBJ_DataBase_DAL = new Cls_DataBase_DAL();
OBJ_DataBase_DAL.SSP_Nombre = sNombreSP;
OBJ_DataBase_DAL.SNombreTabla = sNombreTabla;
OBJ_DataBase_BLL.Crear_Parametros(ref OBJ_DataBase_DAL);
OBJ_DataBase_DAL.dt_Parametros = dtParametros;
OBJ_DataBase_BLL.Execute_DataAdapter(ref OBJ_DataBase_DAL);
if (OBJ_DataBase_DAL.SError == string.Empty)
{
sMsjError = string.Empty;
return(OBJ_DataBase_DAL.OBJ_DataSet.Tables[0]);
}
else
{
sMsjError = OBJ_DataBase_DAL.SError;
return(null);
}
}
catch (Exception Error)
{
sMsjError = Error.Message.ToString();
return(null);
}
}
//METODO DE CONECXION A LA BASE DE DATOS SQL SERVER
public void CrearConx(ref Cls_DataBase_DAL Obj_DB_DAL)
{
try
{
Obj_DB_DAL.sCxCadena = ConfigurationManager.ConnectionStrings[1].ConnectionString;
Obj_DB_DAL.Obj_Connec_DB = new SqlConnection(Obj_DB_DAL.sCxCadena);
Obj_DB_DAL.sMsjError = string.Empty;
}
catch (Exception ex)
{
Obj_DB_DAL.sMsjError = ex.Message.ToString();
Obj_DB_DAL.Obj_Connec_DB = null;
Obj_DB_DAL.sCxCadena = string.Empty;;
}
}
public void CrearCnx(ref Cls_DataBase_DAL OBJ_DataBase_DAL)
{
try
{
OBJ_DataBase_DAL.SCxCadena = ConfigurationManager.ConnectionStrings["Windows_AUT"].ConnectionString;
OBJ_DataBase_DAL.OBJ_Connection_DataBase = new SqlConnection(OBJ_DataBase_DAL.SCxCadena);
OBJ_DataBase_DAL.SError = string.Empty;
}
catch (Exception ex)
{
OBJ_DataBase_DAL.SError = ex.Message.ToString();
OBJ_DataBase_DAL.OBJ_Connection_DataBase = null;
OBJ_DataBase_DAL.SCxCadena = string.Empty;
}
}
//METODO DE CREACION DE PARAMETROS, QUE RELACIONAN LAS VARIABLES
//DE LA BASE DE DATOS CON LA DE LA PLATAFORMA DE VISUAL(DAL)
public void CrearParametros(ref Cls_DataBase_DAL Obj_DB_DAL)
{
try
{
Obj_DB_DAL.DT_Parametros = new DataTable("Parametros");
Obj_DB_DAL.DT_Parametros.Columns.Add("Nombre");
Obj_DB_DAL.DT_Parametros.Columns.Add("Tipo");
Obj_DB_DAL.DT_Parametros.Columns.Add("Valor");
Obj_DB_DAL.sMsjError = string.Empty;
}
catch (Exception ex)
{
Obj_DB_DAL.sMsjError = ex.Message.ToString();
Obj_DB_DAL.DT_Parametros = null;
}
}
public void Crear_Parametros(ref Cls_DataBase_DAL OBJ_DataBase_DAL)
{
try
{
OBJ_DataBase_DAL.dt_Parametros = new DataTable("Parametros");
OBJ_DataBase_DAL.dt_Parametros.Columns.Add("Nombre");
OBJ_DataBase_DAL.dt_Parametros.Columns.Add("Tipo");
OBJ_DataBase_DAL.dt_Parametros.Columns.Add("Valor");
OBJ_DataBase_DAL.SError = string.Empty;
}
catch (Exception Error)
{
OBJ_DataBase_DAL.SError = Error.Message.ToString();
OBJ_DataBase_DAL.dt_Parametros = null;
}
}
public DataTable Listar_Estados(ref string sMsjError)
{
Cls_DataBase_DAL Obj_DAL = new Cls_DataBase_DAL();
Cls_DataBase_BLL Obj_BLL = new Cls_DataBase_BLL();
Obj_DAL.sTableName = "Estados";
Obj_DAL.sSP_Name = ConfigurationManager.AppSettings["Listar_Estados"].ToString().Trim();
Obj_BLL.Ejec_Scalar(ref Obj_DAL);
if (Obj_DAL.sMsjError == string.Empty)
{
sMsjError = string.Empty;
return(Obj_DAL.Obj_DSet.Tables[0]);
}
else
{
sMsjError = Obj_DAL.sMsjError;
return(null);
}
}
public string InsertarDato(string sNombreSP, string sNombreTabla, DataTable dtParametros,
ref char cAccion, ref string sMsjError)
{
try
{
Cls_DataBase_BLL OBJ_DataBase_BLL = new Cls_DataBase_BLL();
Cls_DataBase_DAL OBJ_DataBase_DAL = new Cls_DataBase_DAL();
OBJ_DataBase_DAL.SSP_Nombre = sNombreSP;
OBJ_DataBase_DAL.SNombreTabla = sNombreTabla;
OBJ_DataBase_BLL.Crear_Parametros(ref OBJ_DataBase_DAL);
OBJ_DataBase_DAL.dt_Parametros = dtParametros;
OBJ_DataBase_BLL.Execute_Scalar(ref OBJ_DataBase_DAL);
if (OBJ_DataBase_DAL.SError == string.Empty)
{
sMsjError = string.Empty;
cAccion = 'U';
return(OBJ_DataBase_DAL.SScalarValue);
}
else
{
sMsjError = OBJ_DataBase_DAL.SError;
cAccion = 'I';
return(string.Empty);
}
}
catch (Exception Error)
{
sMsjError = Error.Message.ToString();
cAccion = 'I';
return(string.Empty);
}
}
public DataTable Filtrar_Estados(ref string sMsjError, string sFiltro)
{
Cls_DataBase_DAL Obj_DAL = new Cls_DataBase_DAL();
Cls_DataBase_BLL Obj_BLL = new Cls_DataBase_BLL();
Obj_BLL.CrearParametros(ref Obj_DAL);
Obj_DAL.DT_Parametros.Rows.Add("@Nombre", 3, sFiltro);
Obj_DAL.sTableName = "Estados";
Obj_DAL.sSP_Name = ConfigurationManager.AppSettings["Filtrar_Estados"].ToString().Trim();
Obj_BLL.Execute_DataAdapter(ref Obj_DAL);
if (Obj_DAL.sMsjError == string.Empty)
{
sMsjError = string.Empty;
return(Obj_DAL.Obj_DSet.Tables[0]);
}
else
{
sMsjError = Obj_DAL.sMsjError;
return(null);
}
}
//METODO EJECUTABLE DE LAS ACCIONES DE LISTAR Y FILTRAR
public void Execute_DataAdapter(ref Cls_DataBase_DAL Obj_DB_DAL)
{
try
{
//SE CREA LA CONECXION A LA BD SQL
CrearConx(ref Obj_DB_DAL);
//COMPROBAR SI HAY CONECXION A LA BD SQL
if ((Obj_DB_DAL.Obj_Connec_DB != null) && (Obj_DB_DAL.sMsjError == string.Empty))
{
//ABRIR LA ENTRADA DE DATOS
if (Obj_DB_DAL.Obj_Connec_DB.State == ConnectionState.Closed)
{
Obj_DB_DAL.Obj_Connec_DB.Open();
}
//INSTANCIAR EL DATA ADAPTER CON LOS PARAMETROS QUE RECIBE SP
Obj_DB_DAL.Obj_DAdapter = new SqlDataAdapter(Obj_DB_DAL.sSP_Name, Obj_DB_DAL.Obj_Connec_DB);
//Ejecutar Stored Procedure
Obj_DB_DAL.Obj_DAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
//DEFINICION DEL VALOR DEL PARAMETRO (VARIABLES)
if (Obj_DB_DAL.DT_Parametros != null)
{
foreach (DataRow DR in Obj_DB_DAL.DT_Parametros.Rows)
{
SqlDbType DBType = SqlDbType.VarChar;
switch (DR[1].ToString())
{
case "1":
{
DBType = SqlDbType.Decimal;
break;
}
case "2":
{
DBType = SqlDbType.NVarChar;
break;
}
case "3":
{
DBType = SqlDbType.VarChar;
break;
}
case "4":
{
DBType = SqlDbType.NChar;
break;
}
case "5":
{
DBType = SqlDbType.Char;
break;
}
case "6":
{
DBType = SqlDbType.Int;
break;
}
case "7":
{
DBType = SqlDbType.DateTime;
break;
}
default:
DBType = SqlDbType.VarChar;
break;
}
Obj_DB_DAL.Obj_DAdapter.SelectCommand.Parameters.Add(DR["Nombre"].ToString(), DBType).Value = DR["Valor"].ToString();
}
}
Obj_DB_DAL.Obj_DSet = new DataSet();
Obj_DB_DAL.Obj_DAdapter.Fill(Obj_DB_DAL.Obj_DSet, Obj_DB_DAL.sTableName);
Obj_DB_DAL.sMsjError = string.Empty;
}
}
catch (Exception error)
{
Obj_DB_DAL.sMsjError = error.Message.ToString();
}
finally
{
//CIERRE DE LA ENTRADA DE DATOS
if (Obj_DB_DAL.Obj_Connec_DB != null)
{
if (Obj_DB_DAL.Obj_Connec_DB.State == ConnectionState.Open)
{
Obj_DB_DAL.Obj_Connec_DB.Close();
}
//DESTRUCCION DE LA CONECXION PARA NO CONSUMIR
Obj_DB_DAL.Obj_Connec_DB.Dispose();
}
}
}
//Metodo para modificar tablas con variables identity
public void Ejec_Scalar(ref Cls_DataBase_DAL Obj_DB_DAL)
{
try
{
CrearConx(ref Obj_DB_DAL);
if ((Obj_DB_DAL.Obj_Connec_DB != null) && (Obj_DB_DAL.sMsjError == string.Empty))
{
}
{
if (Obj_DB_DAL.Obj_Connec_DB.State == ConnectionState.Closed)
{
Obj_DB_DAL.Obj_Connec_DB.Open();
}
Obj_DB_DAL.Obj_Command = new SqlCommand(Obj_DB_DAL.sSP_Name, Obj_DB_DAL.Obj_Connec_DB);
Obj_DB_DAL.Obj_Command.CommandType = CommandType.StoredProcedure;
if (Obj_DB_DAL.DT_Parametros.Rows.Count >= 1)
{
foreach (DataRow DR in Obj_DB_DAL.DT_Parametros.Rows)
{
SqlDbType DBType = SqlDbType.VarChar;
switch (DR[1].ToString())
{
case "1":
{
DBType = SqlDbType.Decimal;
break;
}
case "2":
{
DBType = SqlDbType.NVarChar;
break;
}
case "3":
{
DBType = SqlDbType.VarChar;
break;
}
case "4":
{
DBType = SqlDbType.NChar;
break;
}
case "5":
{
DBType = SqlDbType.Char;
break;
}
case "6":
{
DBType = SqlDbType.Int;
break;
}
case "7":
{
DBType = SqlDbType.DateTime;
break;
}
default:
DBType = SqlDbType.VarChar;
break;
}
Obj_DB_DAL.Obj_Command.Parameters.Add(DR["Nombre"].ToString(), DBType).Value = DR["Valor"].ToString();
}
}
Obj_DB_DAL.iValorScalar = Convert.ToInt32(Obj_DB_DAL.Obj_Command.ExecuteScalar());
Obj_DB_DAL.sMsjError = string.Empty;
}
Obj_DB_DAL.sMsjError = string.Empty;
}
catch (Exception error)
{
Obj_DB_DAL.sMsjError = error.Message.ToString();
}
finally
{
if (Obj_DB_DAL.Obj_Connec_DB != null)
{
if (Obj_DB_DAL.Obj_Connec_DB.State == ConnectionState.Open)
{
Obj_DB_DAL.Obj_Connec_DB.Close();
}
Obj_DB_DAL.Obj_Connec_DB.Dispose();
}
}
}
public void Execute_DataAdapter(ref Cls_DataBase_DAL OBJ_DataBase_DAL)
{
try
{
CrearCnx(ref OBJ_DataBase_DAL);
if ((OBJ_DataBase_DAL.OBJ_Connection_DataBase != null) && (OBJ_DataBase_DAL.SError == string.Empty))
{
if (OBJ_DataBase_DAL.OBJ_Connection_DataBase.State == ConnectionState.Closed)
{
OBJ_DataBase_DAL.OBJ_Connection_DataBase.Open();
}
OBJ_DataBase_DAL.OBJ_DataAdapter = new SqlDataAdapter(OBJ_DataBase_DAL.SSP_Nombre, OBJ_DataBase_DAL.OBJ_Connection_DataBase);
if (OBJ_DataBase_DAL.dt_Parametros.Rows.Count >= 1)
{
foreach (DataRow DR in OBJ_DataBase_DAL.dt_Parametros.Rows)
{
SqlDbType DBType = SqlDbType.VarChar;
switch (DR[1].ToString())
{
case "1":
{
DBType = SqlDbType.Int;
break;
}
case "2":
{
DBType = SqlDbType.VarChar;
break;
}
case "3":
{
DBType = SqlDbType.NVarChar;
break;
}
case "4":
{
DBType = SqlDbType.Char;
break;
}
case "5":
{
DBType = SqlDbType.NChar;
break;
}
case "6":
{
DBType = SqlDbType.Decimal;
break;
}
case "7":
{
DBType = SqlDbType.DateTime;
break;
}
case "8":
{
DBType = SqlDbType.Bit;
break;
}
case "9":
{
DBType = SqlDbType.Time;
break;
}
default:
DBType = SqlDbType.VarChar;
break;
}
OBJ_DataBase_DAL.OBJ_DataAdapter.SelectCommand.Parameters.Add(DR[0].ToString(), DBType).Value = DR[2].ToString();
}
}
OBJ_DataBase_DAL.OBJ_DataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
OBJ_DataBase_DAL.OBJ_DataAdapter.Fill(OBJ_DataBase_DAL.OBJ_DataSet, OBJ_DataBase_DAL.SNombreTabla);
OBJ_DataBase_DAL.SError = string.Empty;
}
OBJ_DataBase_DAL.SError = string.Empty;
}
catch (Exception Error)
{
OBJ_DataBase_DAL.SError = Error.Message.ToString();
}
finally
{
if (OBJ_DataBase_DAL.OBJ_Connection_DataBase != null)
{
if (OBJ_DataBase_DAL.OBJ_Connection_DataBase.State == ConnectionState.Open)
{
OBJ_DataBase_DAL.OBJ_Connection_DataBase.Close();
}
OBJ_DataBase_DAL.OBJ_Connection_DataBase.Dispose();
}
}
}
