//METODO DE CONEXION A LA BASE DE DATOS SQL SERVER public void CrearConx(ref cls_VariablesConexBD_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.Obj_Connec_DB.Open(); 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;; } }
//METODO DE CREACION DE PARAMETROS, QUE RELACIONAN LAS VARIABLES //DE LA BASE DE DATOS CON LA DE LA PLATAFORMA DE VISUAL public void CrearParametros(ref cls_VariablesConexBD_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; } }
//METODO EJECUTABLE DE LAS ACCIONES DE LISTAR Y FILTRAR public void Execute_DataAdapter(ref cls_VariablesConexBD_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.TinyInt; break; } case "2": { DBType = SqlDbType.Int; break; } case "3": { DBType = SqlDbType.VarChar; break; } case "4": { DBType = SqlDbType.NVarChar; break; } case "5": { DBType = SqlDbType.SmallInt; break; } case "6": { DBType = SqlDbType.Char; break; } case "7": { DBType = SqlDbType.DateTime; 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_VariablesConexBD_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.TinyInt; break; } case "2": { DBType = SqlDbType.Int; break; } case "3": { DBType = SqlDbType.VarChar; break; } case "4": { DBType = SqlDbType.NVarChar; break; } case "5": { DBType = SqlDbType.SmallInt; break; } case "6": { DBType = SqlDbType.Char; break; } case "7": { DBType = SqlDbType.DateTime; 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(); } } }