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