/// <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> /// 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> /// 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> /// 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> /// 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> /// 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(); } }