/// <summary>
/// Crea el procedimiento "CreateNextId" para la tabla especificada.
/// </summary>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="idColumnName">Nombre de la columna clave.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateNextIdStoredProcedure(string tableName, string idColumnName)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentException("Must provide a name for the table.", "tableName");
}
// Crear el comando para crear el procedimiento
string cmdText = "CREATE PROCEDURE NextID" + tableName + "( @" + idColumnName + " int OUT)" +
"AS IF (SELECT COUNT(*) FROM [" + tableName + "]) = 0 " +
"BEGIN " +
" SET @" + idColumnName + " = 1 " +
"END " +
"ELSE " +
"BEGIN " +
" SET @" + idColumnName + " = (SELECT (MAX(" + idColumnName + ")+1) FROM [" + tableName + "]) " +
"END";
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Ejecutar el procedimiento y cerrar la conexión
IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
return(true);
}
/// <summary>
/// Controla que el procedimiento exista en la base actual.
/// </summary>
/// <param name="procedureName">Nombre del procedimiento.</param>
/// <returns>True si tiene éxito.</returns>
public static bool DBCheckedStoredProcedure(string procedureName)
{
bool result = false;
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Construir el comando para controlar la existencia del procedimiento
string cmdText = "SELECT name FROM sysObjects WHERE name = @procedureName AND type = 'P'";
IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection);
IDbDataParameter param = dataAccess.GetNewDataParameter();
param.ParameterName = "@procedureName";
param.DbType = DbType.AnsiString;
param.Size = procedureName.Length;
param.Value = procedureName;
command.Parameters.Add(param);
// Controlar la existencia de la tabla y cerrar la conexión
IDataReader reader = command.ExecuteReader();
if (reader.Read())
{
result = true;
}
reader.Close();
dbConnection.Close();
return(result);
}
/// <summary>
/// Crea un procedimiento de grabación para la tabla indicada usando clave autonumerica.
/// </summary>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="fieldsName">Nombres de los campos de la tabla.</param>
/// <param name="fieldsType">Tipos .NET de los campos en el mismo orden que los nombres.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateSaveStoredProcedure(string tableName, string[] fieldsName, Type[] fieldsType)
{
if (fieldsName.Length == 0)
{
throw new ArgumentException("Must provide the table's field names.", "fieldsName");
}
if (fieldsType.Length == 0)
{
throw new ArgumentException("Must provide the table's field types.", "fieldsType");
}
// Crea el comando SQL para insertar un registro en la tabla
string cmdText = "INSERT INTO [" + tableName + "] VALUES(";
for (int i = 1; i < fieldsName.Length; i++)
{
if (i == 1)
{
cmdText += "@" + fieldsName[i];
}
else
{
cmdText += ",@" + fieldsName[i];
}
}
cmdText += ", GETDATE())";
// Crear el comando para crear el procedimiento de inserción
string procedure = "CREATE PROCEDURE Save" + tableName + "(";
for (int i = 1; i < fieldsName.Length; i++)
{
if (i == 1)
{
procedure += "@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]);
}
else
{
procedure += ",@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]);
}
}
procedure += ") AS " + cmdText;
procedure += " RETURN SCOPE_IDENTITY()";
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Ejecutar el procedimiento y cerrar la conexión
IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
return(true);
}
/// <summary>
/// Crea un procedimiento de actualización para la tabla indicada.
/// </summary>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="idColumnName">The name of the id column</param>
/// <param name="fieldsName">Nombres de los campos de la tabla.</param>
/// <param name="fieldsType">Tipos .NET de los campos en el mismo orden que los nombres.</param>
/// <returns>True if successful</returns>
public static bool CreateUpdateStoredProcedure(string tableName, string idColumnName, string[] fieldsName, Type[] fieldsType)
{
if (fieldsName.Length == 0)
{
throw new ArgumentException("Must provide the table's field names.", "fieldsName");
}
if (fieldsType.Length == 0)
{
throw new ArgumentException("Must provide the table's field types.", "fieldsType");
}
// Crea el comando para actualizar un registro
string cmdText = "UPDATE [" + tableName + "] SET ";
for (int i = 1; i < fieldsName.Length; i++)
{
if (i == 1)
{
cmdText += fieldsName[i] + " = @" + fieldsName[i];
}
else
{
cmdText += ", [" + fieldsName[i] + "] = @" + fieldsName[i];
}
}
cmdText += " , timestamp=GETDATE() WHERE [" + idColumnName + "] = @" + idColumnName;
// Crear el comando para crear el procedimiento de actualización
string procedure = "CREATE PROCEDURE Update" + tableName + "(";
for (int i = 0; i < fieldsName.Length; i++)
{
if (i == 0)
{
procedure += "@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]);
}
else
{
procedure += ",@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]);
}
}
procedure += ")AS " + cmdText;
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Ejecutar el procedimiento y cerrar la conexión
IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
return(true);
}
/// <summary>
/// Crea un procedimiento de eliminación para una tabla determinada
/// </summary>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="idColumnName">Nombre de la columna clave.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateDeleteStoredProcedure(string tableName, string idColumnName)
{
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Crear el comando para crear el procedimiento
string procedure = "CREATE PROCEDURE Delete" + tableName + "( @" + idColumnName + " int) AS DELETE FROM [" + tableName + "] WHERE " + idColumnName + " = @" + idColumnName;
IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
return(true);
}
/// <summary>
/// Function to load a CampaignEntity from database.
/// </summary>
/// <param name="id">The ID of the record to load</param>
/// <param name="loadRelation">if is true load the relation</param>
/// <param name="scope">Internal structure used to avoid circular reference locks, must be provided if calling from other data access object</param>
/// <returns>The entity instance</returns>
/// <exception cref="UtnEmallDataAccessException">
/// If a DbException occurs while accessing the database.
/// </exception>
public CampaignEntity Load(int id, bool loadRelation, Dictionary <string, IEntity> scope)
{
// Build a key for internal scope object
string scopeKey = id.ToString(NumberFormatInfo.InvariantInfo) + "Campaign";
if (scope != null)
{
// If scope contains the object it was already loaded,
// return it to avoid circular references
if (scope.ContainsKey(scopeKey))
{
return((CampaignEntity)scope[scopeKey]);
}
}
else
{
// If there isn't a current scope create one
scope = new Dictionary <string, IEntity>();
}
CampaignEntity campaign = null;
// Check if the entity was already loaded by current data access object
// and return it if that is the case
if (inMemoryEntities.ContainsKey(id))
{
campaign = inMemoryEntities[id];
// Add current object to current load scope
scope.Add(scopeKey, campaign);
}
else
{
bool closeConnection = false;
try
{
// Open a new connection if it isn't on a transaction
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string cmdText = "SELECT idCampaign, description, name, startDate, stopDate, idUser, timestamp FROM [Campaign] WHERE idCampaign = @idCampaign";
// Create the command
IDbCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Create the Id parameter for the query
IDbDataParameter parameter = dataAccess.GetNewDataParameter("@idCampaign", DbType.Int32);
parameter.Value = id;
sqlCommand.Parameters.Add(parameter);
// Use a DataReader to get data from db
IDataReader reader = sqlCommand.ExecuteReader();
campaign = new CampaignEntity();
if (reader.Read())
{
// Load fields of entity
campaign.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
campaign.Description = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
campaign.Name = reader.GetString(2);
}
campaign.StartDate = reader.GetDateTime(3);
campaign.StopDate = reader.GetDateTime(4);
campaign.IdUser = reader.GetInt32(5);
// Add current object to the scope
scope.Add(scopeKey, campaign);
// Add current object to cache of loaded entities
inMemoryEntities.Add(campaign.Id, campaign);
// Read the timestamp and set new and changed properties
campaign.Timestamp = reader.GetDateTime(6);
campaign.IsNew = false;
campaign.Changed = false;
// Close the reader
reader.Close();
// Load related objects if required
if (loadRelation)
{
LoadRelationUser(campaign, scope);
}
}
else
{
reader.Close();
}
}
catch (DbException dbException)
{
// Catch DBException and rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was opened by ourself
if (closeConnection)
{
dbConnection.Close();
}
}
}
// Return the loaded entity
return(campaign);
}
/// <summary>
/// Crea una nueva tabla con indices de clave foranea
/// </summary>
/// <param name="table">El nombre de la tabla a crear.</param>
/// <param name="fieldsName">Los nombres de los campos.</param>
/// <param name="fieldsType">Los tipos de los campos.</param>
/// <param name="indexColumns">Los nombres de los campos a indizar.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateTable(string table, string[] fieldsName, bool isIdIdentity, Type[] fieldsType, string[] indexColumns)
{
if (String.IsNullOrEmpty(table))
{
throw new ArgumentException("Must provide a name for the table.", "table");
}
if (fieldsName.Length == 0)
{
throw new ArgumentException("Must provide at least one field name.", "table");
}
if (fieldsType.Length == 0)
{
throw new ArgumentException("Must provide at least one field type.", "fieldsType");
}
if (fieldsName.Length != fieldsType.Length)
{
throw new ArgumentException("Quantity oif fields names and types must be equal.", "fieldsType");
}
// Agrega el campo timestamp
string[] fields = new string[fieldsName.Length + 1];
fieldsName.CopyTo(fields, 0);
fields[fields.Length - 1] = "timestamp";
fieldsName = fields;
// Agrega el tipo del campo timestamp
Type[] fieldsNewTypes = new Type[fieldsType.Length + 1];
fieldsType.CopyTo(fieldsNewTypes, 0);
fieldsNewTypes[fieldsNewTypes.Length - 1] = Type.GetType("System.DateTime");
fieldsType = fieldsNewTypes;
// Crear el comando SQL para la creación de la tabla
string cmdText = "CREATE TABLE [" + table + "] (";
cmdText += "" + fieldsName[0] + " " + GetSQLTypeName(fieldsType[0]) + " PRIMARY KEY";
if (isIdIdentity)
{
cmdText += " IDENTITY";
}
for (int i = 1; i < fieldsName.Length; i++)
{
cmdText += ",[" + fieldsName[i] + "] " + GetSQLTypeName(fieldsType[i]);
}
cmdText += ")";
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Ejecutar el comando
IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection);
command.ExecuteNonQuery();
// Crear los índices
if (indexColumns != null && indexColumns.Length > 0)
{
string cmdIndex;
for (int i = 0; i < indexColumns.Length; i++)
{
cmdIndex = "CREATE INDEX IDX_" + table + "_" + indexColumns[i] +
" ON [" + table + "] (" + indexColumns[i] + ");";
command.CommandText = cmdIndex;
command.ExecuteNonQuery();
}
}
// Cerrar la conexión
dbConnection.Close();
return(true);
}
/// <summary>
/// Function to load a StoreEntity from database.
/// </summary>
/// <param name="id">The ID of the record to load</param>
/// <param name="loadRelation">if is true load the relation</param>
/// <param name="scope">Internal structure used to avoid circular reference locks, must be provided if calling from other data access object</param>
/// <returns>The entity instance</returns>
/// <exception cref="UtnEmallDataAccessException">
/// If a DbException occurs while accessing the database.
/// </exception>
public StoreEntity Load(int id, bool loadRelation, Dictionary <string, IEntity> scope)
{
// Build a key for internal scope object
string scopeKey = id.ToString(NumberFormatInfo.InvariantInfo) + "Store";
if (scope != null)
{
// If scope contains the object it was already loaded,
// return it to avoid circular references
if (scope.ContainsKey(scopeKey))
{
return((StoreEntity)scope[scopeKey]);
}
}
else
{
// If there isn't a current scope create one
scope = new Dictionary <string, IEntity>();
}
StoreEntity store = null;
// Check if the entity was already loaded by current data access object
// and return it if that is the case
if (inMemoryEntities.ContainsKey(id))
{
store = inMemoryEntities[id];
// Add current object to current load scope
scope.Add(scopeKey, store);
}
else
{
bool closeConnection = false;
try
{
// Open a new connection if it isn't on a transaction
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string cmdText = "SELECT idStore, name, telephoneNumber, internalPhoneNumber, contactName, ownerName, email, webAddress, localNumber, idMall, idDataModel, timestamp FROM [Store] WHERE idStore = @idStore";
// Create the command
IDbCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Create the Id parameter for the query
IDbDataParameter parameter = dataAccess.GetNewDataParameter("@idStore", DbType.Int32);
parameter.Value = id;
sqlCommand.Parameters.Add(parameter);
// Use a DataReader to get data from db
IDataReader reader = sqlCommand.ExecuteReader();
store = new StoreEntity();
if (reader.Read())
{
// Load fields of entity
store.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
store.Name = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
store.TelephoneNumber = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
store.InternalPhoneNumber = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
store.ContactName = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
store.OwnerName = reader.GetString(5);
}
if (!reader.IsDBNull(6))
{
store.Email = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
store.WebAddress = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
store.LocalNumber = reader.GetString(8);
}
store.IdMall = reader.GetInt32(9);
store.IdDataModel = reader.GetInt32(10);
// Add current object to the scope
scope.Add(scopeKey, store);
// Add current object to cache of loaded entities
inMemoryEntities.Add(store.Id, store);
// Read the timestamp and set new and changed properties
store.Timestamp = reader.GetDateTime(11);
store.IsNew = false;
store.Changed = false;
// Close the reader
reader.Close();
// Load related objects if required
if (loadRelation)
{
LoadRelationStoreCategory(store, scope);
}
}
else
{
reader.Close();
}
}
catch (DbException dbException)
{
// Catch DBException and rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was opened by ourself
if (closeConnection)
{
dbConnection.Close();
}
}
}
// Return the loaded entity
return(store);
}