Example #1
0
        /// <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);
        }
Example #2
0
        /// <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);
        }
Example #3
0
        /// <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);
        }
Example #4
0
        /// <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);
        }
Example #5
0
        /// <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);
        }
Example #6
0
        /// <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);
        }
Example #7
0
        /// <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);
        }