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