/// <summary> /// Carga los datos de un elemento /// </summary> public virtual TypeData LoadObject(string strSQL, ParametersDBCollection objColParametersDB, CommandType intCommandType = CommandType.Text) { TypeData objData = default(TypeData); lock (objLock) { // Abre la conexión Provider.Open(); // Lee los datos using (IDataReader rdoData = Provider.ExecuteReader(strSQL, objColParametersDB, intCommandType)) { // Lee los datos if (rdoData.Read()) { objData = (TypeData)AssignData(rdoData); } else { objData = (TypeData)AssignData(null); } // Cierra el recordset rdoData.Close(); } // Cierra la conexión Provider.Close(); } // Devuelve el objeto return(objData); }
/// <summary> /// Lee los parámetros de salida /// </summary> private ParametersDBCollection ReadOutputParameters(IDataParameterCollection objColOutputParameters) { ParametersDBCollection objColParameters = new ParametersDBCollection(); // Recupera los parámetros foreach (IDataParameter objOutputParameter in objColOutputParameters) { ParameterDB objParameter = new ParameterDB(); // Asigna los datos objParameter.Name = objOutputParameter.ParameterName; objParameter.Direction = objOutputParameter.Direction; if (objOutputParameter.Value == DBNull.Value) { objParameter.Value = null; } else { objParameter.Value = objOutputParameter.Value; } // Añade el parámetro a la colección objColParameters.Add(objParameter); } // Devuelve la colección de parámetros return(objColParameters); }
/// <summary> /// Carga las columnas de la vista /// </summary> private void LoadColumns(SQLServerProvider objConnection, SchemaView objView) { ParametersDBCollection objColParameters = new ParametersDBCollection(); string strSQL; // Asigna lo parámetros objColParameters.Add("@View_Catalog", objView.Catalog); objColParameters.Add("@View_Schema", objView.Schema); objColParameters.Add("@View_Name", objView.Name); // Crea la cadena SQL strSQL = @"SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name FROM Information_Schema.View_Column_Usage WHERE View_Catalog = @View_Catalog AND View_Schema = @View_Schema AND View_Name = @View_Name" ; // Carga las columnas using (IDataReader rdoColumns = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text)) { // Lee los registros while (rdoColumns.Read()) { SchemaColumn objColumn = new SchemaColumn(objView.Parent); // Carga los datos de la columna objColumn.Catalog = (string)rdoColumns.IisNull("Table_Catalog"); objColumn.Schema = (string)rdoColumns.IisNull("Table_Schema"); objColumn.Table = (string)rdoColumns.IisNull("Table_Name"); objColumn.Name = (string)rdoColumns.IisNull("Column_Name"); // Añade la columna a la colección objView.Columns.Add(objColumn); } // Cierra el recordset rdoColumns.Close(); } }
/// <summary> /// Ejecuta un INSERT sobre la base de datos y obtiene el valor de identidad /// </summary> public override int?ExecuteGetIdentity(string strText, ParametersDBCollection objColParametersDB, CommandType intCommandType = CommandType.Text) { int?intIdentity = null; // Abre la conexión Open(); // Ejecuta sobre la conexión switch (intCommandType) { case CommandType.Text: intIdentity = (int?)((decimal?)ExecuteScalar(NormalizeSqlInsert(strText), objColParametersDB, intCommandType)); break; case CommandType.StoredProcedure: Execute(strText, objColParametersDB, intCommandType); intIdentity = (int?)objColParametersDB["@return_code"].Value; break; default: Execute(strText, objColParametersDB, intCommandType); intIdentity = null; break; } // Cierra la conexión Close(); // Devuelve el valor identidad return(intIdentity); }
/// <summary> /// Ejecuta una cadena SQL sobre la base de datos y devuelve un escalar /// </summary> protected object ExecuteScalar(string strSQL, string strParameterName, int?intParameterValue, CommandType intCommandType = CommandType.Text) { ParametersDBCollection objColParametersDB = new ParametersDBCollection(); // Asigna los parámetros objColParametersDB.Add(strParameterName, intParameterValue); // Ejecuta la cadena SQL return(ExecuteScalar(strSQL, objColParametersDB, intCommandType)); }
/// <summary> /// Carga los datos de un objeto /// </summary> public virtual TypeData LoadObject(string strSQL, string strParameterName, string strParameterValue, int intLength, CommandType intCommandType = CommandType.Text) { ParametersDBCollection objColParametersDB = new ParametersDBCollection(); // Asigna los parámetros objColParametersDB.Add(strParameterName, strParameterValue, intLength); // Carga los datos return(LoadObject(strSQL, objColParametersDB, intCommandType)); }
/// <summary> /// Carga una colección /// </summary> public void LoadCollection(IList <TypeData> objColData, string strSQL, string strFieldName, string strFieldValue, int intLength, CommandType intCommandType = CommandType.Text) { ParametersDBCollection objColParametersDB = new ParametersDBCollection(); // Asigna los parámetros objColParametersDB.Add(strFieldName, strFieldValue, intLength); // Carga los datos LoadCollection(objColData, strSQL, objColParametersDB, intCommandType); }
/// <summary> /// Ejecuta una cadena SQL sobre la base de datos /// </summary> protected void Execute(string strSQL, ParametersDBCollection objColParametersDB, CommandType intCommandType = CommandType.Text) { lock (objLock) { // Abre el proveedor Provider.Open(); // Ejecuta la cadena SQL Provider.Execute(strSQL, objColParametersDB, intCommandType); // Cierra el proveedor Provider.Close(); } }
/// <summary> /// Añade a un comando los parámetros de una clase <see cref="ParametersDBCollection"/> /// </summary> protected void AddParameters(IDbCommand objCommand, ParametersDBCollection objColParameters) { // Limpia los parámetros antiguos objCommand.Parameters.Clear(); // Añade los parámetros nuevos if (objColParameters != null) { for (int intIndex = 0; intIndex < objColParameters.Count; intIndex++) { objCommand.Parameters.Add(GetSQLParameter(objColParameters[intIndex])); } } }
/// <summary> /// Carga una colección /// </summary> public void LoadCollection(IList <TypeData> objColData, string strSQL, string strFieldName, int?intFieldValue, CommandType intCommandType = CommandType.Text, bool blnCheckNullsBefore = false) { if (!blnCheckNullsBefore || (blnCheckNullsBefore && intFieldValue != null)) { ParametersDBCollection objColParametersDB = new ParametersDBCollection(); // Asigna los parámetros objColParametersDB.Add(strFieldName, intFieldValue); // Carga los datos LoadCollection(objColData, strSQL, objColParametersDB, intCommandType); } }
/// <summary> /// Ejecuta una cadena SQL sobre la base de datos /// </summary> protected void Execute(string strSQL, string strParameterName, int?intParameterValue, bool blnCheckNullsBefore = true, CommandType intCommandType = CommandType.Text) { if (!blnCheckNullsBefore || (blnCheckNullsBefore && intParameterValue != null)) { ParametersDBCollection objColParametersDB = new ParametersDBCollection(); // Asigna los parámetros objColParametersDB.Add(strParameterName, intParameterValue); // Ejecuta la cadena SQL Execute(strSQL, objColParametersDB, intCommandType); } }
/// <summary> /// Ejecuta una sentencia o un procedimiento sobre la base de datos /// </summary> public void Execute(string strText, ParametersDBCollection objColParameters, CommandType intCommandType) { lock (objLock) { using (IDbCommand objCommand = GetCommand(strText)) { // Indica el tipo del comando objCommand.CommandType = intCommandType; // Añade los parámetros al comando AddParameters(objCommand, objColParameters); // Ejecuta la consulta objCommand.ExecuteNonQuery(); // Pasa los valores de salida de los parámetros del comando a la colección de parámetros de entrada objColParameters = ReadOutputParameters(objCommand.Parameters); } } }
/// <summary> /// Carga las restricciones de una tabla /// </summary> private void LoadConstraints(SQLServerProvider objConnection, SchemaTable objTable) { ParametersDBCollection objColParameters = new ParametersDBCollection(); string strSQL; // Añade los parámetros objColParameters.Add("@Table_Catalog", objTable.Catalog); objColParameters.Add("@Table_Schema", objTable.Schema); objColParameters.Add("@Table_Name", objTable.Name); // Crea la cadena SQL strSQL = @"SELECT TableConstraints.Table_Catalog, TableConstraints.Table_Schema, TableConstraints.Table_Name, ColumnConstraint.Column_Name, ColumnConstraint.Constraint_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position FROM Information_Schema.Table_Constraints AS TableConstraints INNER JOIN Information_Schema.Constraint_Column_Usage AS ColumnConstraint ON TableConstraints.Constraint_Catalog = ColumnConstraint.Constraint_Catalog AND TableConstraints.Constraint_Schema = ColumnConstraint.Constraint_Schema AND TableConstraints.Constraint_Name = ColumnConstraint.Constraint_Name INNER JOIN Information_Schema.Key_Column_Usage AS Key_Column ON ColumnConstraint.Constraint_Catalog = Key_Column.Constraint_Catalog AND ColumnConstraint.Constraint_Schema = Key_Column.Constraint_Schema AND ColumnConstraint.Constraint_Name = Key_Column.Constraint_Name AND ColumnConstraint.Column_Name = Key_Column.Column_Name WHERE TableConstraints.Table_Catalog = @Table_Catalog AND TableConstraints.Table_Schema = @Table_Schema AND TableConstraints.Table_Name = @Table_Name ORDER BY TableConstraints.Table_Name, TableConstraints.Constraint_Type, Key_Column.Ordinal_Position" ; // Carga los datos using (IDataReader rdoConstraint = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text)) { // Lee los datos while (rdoConstraint.Read()) { SchemaConstraint objConstraint = new SchemaConstraint(objTable.Parent); // Asigna los datos del registro objConstraint.Catalog = (string)rdoConstraint.IisNull("Table_Catalog"); objConstraint.Schema = (string)rdoConstraint.IisNull("Table_Schema"); objConstraint.Table = (string)rdoConstraint.IisNull("Table_Name"); objConstraint.Column = (string)rdoConstraint.IisNull("Column_Name"); objConstraint.Name = (string)rdoConstraint.IisNull("Constraint_Name"); objConstraint.Type = GetConstraintType((string)rdoConstraint.IisNull("Constraint_Type")); objConstraint.Position = (int)rdoConstraint.IisNull("Ordinal_Position"); // Añade la restricción a la colección objTable.Constraints.Add(objConstraint); } // Cierra el recordset rdoConstraint.Close(); } }
/// <summary> /// Graba los datos de un registro /// </summary> public virtual void Save(TypeData objData, ParametersDBCollection objColParametersDB, ref int?intObjDataId) { lock (objLock) { string strSQLUpdate = GetSQLUpdate(); // Graba los datos if (intObjDataId == null || string.IsNullOrWhiteSpace(strSQLUpdate)) { intObjDataId = Provider.ExecuteGetIdentity(GetSQLInsert(), objColParametersDB, CommandType.Text); } else { Provider.Execute(strSQLUpdate, objColParametersDB, CommandType.Text); } } }
protected object ExecuteScalar(string strSQL, ParametersDBCollection objColParametersDB, CommandType intCommandType = CommandType.Text) { object objScalar = null; // Bloquea antes de ejecutar lock (objLock) { // Abre el proveedor Provider.Open(); // Ejecuta objScalar = Provider.ExecuteScalar(strSQL, objColParametersDB, intCommandType); // Cierra el proveedor Provider.Close(); } // Devuelve el escalar return(objScalar); }
/// <summary> /// Obtiene un dataTable a partir de un nombre de una sentencia o procedimiento y sus parámetros /// </summary> public DataTable GetDataTable(string strText, ParametersDBCollection objColParameters, CommandType intCommandType) { DataTable dtTable = new DataTable(); // Crea el comando SQL Server lock (objLock) { using (IDbCommand objCommand = GetCommand(strText)) { // Inicializa el tipo de comando objCommand.CommandType = intCommandType; // Pasa los parámetros al comando AddParameters(objCommand, objColParameters); // Rellena la tabla con los datos dtTable = FillDataTable(objCommand); } } // Devuelve la tabla return(dtTable); }
/// <summary> /// Obtiene un DataReader /// </summary> public IDataReader ExecuteReader(string strText, ParametersDBCollection objParametersDB, CommandType intCommandType) { IDataReader objReader = null; // ... supone que no se puede abrir el dataReader // Ejecuta el lector lock (objLock) { using (IDbCommand objCommand = GetCommand(strText)) { // Indica el tipo de comando objCommand.CommandType = intCommandType; // Añade los parámetros AddParameters(objCommand, objParametersDB); // Obtiene el dataReader objReader = objCommand.ExecuteReader(); } } // Devuelve el dataReader return(objReader); }
/// <summary> /// Ejecuta una sentencia o procedimiento sobre la base de datos y devuelve un escalar /// </summary> public object ExecuteScalar(string strText, ParametersDBCollection objColParameters, CommandType intCommandType) { object objResult = null; // Ejecuta el comando lock (objLock) { using (IDbCommand objCommand = GetCommand(strText)) { // Indica el tipo de comando objCommand.CommandType = intCommandType; // Añade los parámetros al comando AddParameters(objCommand, objColParameters); // Ejecuta la consulta objResult = objCommand.ExecuteScalar(); } } // Devuelve el resultado return(objResult); }
/// <summary> /// Carga una colección /// </summary> public void LoadCollection(IList <TypeData> objColData, string strSQL, ParametersDBCollection objColParametersDB, CommandType intCommandType = CommandType.Text) { lock (objLock) { // Abre la conexión Provider.Open(); // Carga los datos using (IDataReader rdoData = Provider.ExecuteReader(strSQL, objColParametersDB, intCommandType)) { // Lee los datos while (rdoData.Read()) { objColData.Add((TypeData)AssignData(rdoData)); } // Cierra el recordset rdoData.Close(); } // Cierra la conexión Provider.Close(); } }
/// <summary> /// Ejecuta un INSERT sobre la base de datos y obtiene el valor de identidad /// </summary> public abstract int?ExecuteGetIdentity(string strText, ParametersDBCollection objColParametersDB, CommandType intCommandType);
/// <summary> /// Carga las columnas de una tabla /// </summary> private void LoadColumns(SQLServerProvider objConnection, SchemaTable objTable) { ParametersDBCollection objColParameters = new ParametersDBCollection(); string strSQL; // Añade los parámetros objColParameters.Add("@Table_Catalog", objTable.Catalog); objColParameters.Add("@Table_Schema", objTable.Schema); objColParameters.Add("@Table_Name", objTable.Name); // Crea la cadena SQL strSQL = @"SELECT Columns.Column_Name, Columns.Ordinal_Position, Columns.Column_Default, Columns.Is_Nullable, Columns.Data_Type, Columns.Character_Maximum_Length, CONVERT(int, Columns.Numeric_Precision) AS Numeric_Precision, CONVERT(int, Columns.Numeric_Precision_Radix) AS Numeric_Precision_Radix, CONVERT(int, Columns.Numeric_Scale) AS Numeric_Scale, CONVERT(int, Columns.DateTime_Precision) AS DateTime_Precision, Columns.Character_Set_Name, Columns.Collation_Catalog, Columns.Collation_Schema, Columns.Collation_Name, Objects.is_identity, Properties.value AS Description FROM Information_Schema.Columns AS Columns INNER JOIN sys.all_objects AS Tables ON Columns.Table_Name = Tables.name INNER JOIN sys.columns AS Objects ON Columns.Column_Name = Objects.name AND Tables.object_id = Objects.object_id LEFT JOIN sys.extended_properties AS Properties ON Objects.object_id = Properties.major_id AND Properties.minor_id = Objects.column_id AND Properties.name = 'MS_Description' WHERE Columns.Table_Catalog = @Table_Catalog AND Columns.Table_Schema = @Table_Schema AND Columns.Table_Name = @Table_Name ORDER BY Ordinal_Position" ; // Carga los datos using (IDataReader rdoColumns = objConnection.ExecuteReader(strSQL, objColParameters, CommandType.Text)) { // Lee los datos while (rdoColumns.Read()) { SchemaColumn objColumn = new SchemaColumn(objTable.Parent); // Asigna los datos del registro objColumn.Name = (string)rdoColumns.IisNull("Column_Name") as string; objColumn.OrdinalPosition = rdoColumns.IisNull("Ordinal_Position", 0); objColumn.Default = (string)rdoColumns.IisNull("Column_Default"); objColumn.IsNullable = !(((string)rdoColumns.IisNull("Is_Nullable")).Equals("no", StringComparison.CurrentCultureIgnoreCase)); objColumn.DataType = (string)rdoColumns.IisNull("Data_Type"); objColumn.CharacterMaximumLength = rdoColumns.IisNull("Character_Maximum_Length", 0); objColumn.NumericPrecision = rdoColumns.IisNull("Numeric_Precision", 0); objColumn.NumericPrecisionRadix = rdoColumns.IisNull("Numeric_Precision_Radix", 0); objColumn.NumericScale = rdoColumns.IisNull("Numeric_Scale", 0); objColumn.DateTimePrecision = rdoColumns.IisNull("DateTime_Precision", 0); objColumn.CharacterSetName = (string)rdoColumns.IisNull("Character_Set_Name"); objColumn.CollationCatalog = (string)rdoColumns.IisNull("Collation_Catalog"); objColumn.CollationSchema = (string)rdoColumns.IisNull("Collation_Schema"); objColumn.CollationName = (string)rdoColumns.IisNull("Collation_Name"); objColumn.IsIdentity = (bool)rdoColumns.IisNull("is_identity"); objColumn.Description = (string)rdoColumns.IisNull("Description") as string; // Añade la columna a la colección objTable.Columns.Add(objColumn); } // Cierra el recordset rdoColumns.Close(); } }
/// <summary> /// Ejecuta un INSERT sobre la base de datos y devuelve el ID de IDENTITY /// </summary> public override int?ExecuteGetIdentity(string strText, ParametersDBCollection objColParametersDB, CommandType intCommandType) { throw new NotImplementedException(); }