Ejemplo n.º 1
0
        /// <summary>
        /// Create a new instance into database.
        /// </summary>
        /// <param name="instance">Instance to create.</param>
        /// <returns>The instance unique identifier.</returns>
        private static long InsertDatabaseRecord(T instance)
        {
            object        value;
            ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetInsertCommand();

            // Connecto to database
            ORMEntity <T> .Connect();

            // Set command parameters
            foreach (ORMEntityMember param in cmd.Parameters)
            {
                value = param.GetValue(instance);
                ORMEntity <T> .SetParameter(param, value is null?DBNull.Value : value);
            }

            // Execute the SELECT sentence to retrieve the instance properties
            ORMEntity <T> .ExecuteNonQuery(cmd.SqlCommand);

            long id = ExecuteScalar(SqlDialect.GetNewIDCommand().SqlCommand);

            // Set the generated new record unique identifier to the current instance
            ORMEntity <T> .ORMStructure.PrimaryKey.SetValue(instance, id);

            // Close the connection to database
            ORMEntity <T> .Disconnect();

            // Created project is added to in-memory table
            ORMEntity <T> .AddInMemoryTable(id, instance);

            return(((ORMIdentifiableEntity)instance).ID);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Delete an existing instance from database.
        /// </summary>
        /// <param name="instance">Instance unique identifier.</param>
        private static int DeleteDatabaseRecord(long id)
        {
            ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetDeleteCommand();

            // Delete foreign records
            ORMEntity <T> .DeleteDatabaseForeignRecords(id);

            // Connecto to database
            ORMEntity <T> .Connect();

            // Set command parameters
            ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, id);

            // Execute the SQL command
            int rowsAffected = ExecuteNonQuery(cmd.SqlCommand);

            // Close the connection to database
            ORMEntity <T> .Disconnect();

            // Delete the instance from in-memory database
            if (ORMEntity <T> .InMemoryTable.ContainsKey(id))
            {
                ORMEntity <T> .InMemoryTable.Remove(id);
            }

            return(rowsAffected);
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Update an existing database record.
        /// </summary>
        /// <param name="instance">Instance to update.</param>
        /// <returns>The instance unique identifier.</returns>
        private static long UpdateDatabaseRecord(T instance)
        {
            object        value;
            ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetUpdateCommand();

            // Connecto to database
            ORMEntity <T> .Connect();

            // Set command parameters
            ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, cmd.PrimaryKeyName.GetValue(instance));

            foreach (ORMEntityMember param in cmd.Parameters)
            {
                value = param.GetValue(instance);
                ORMEntity <T> .SetParameter(param, value is null?DBNull.Value : value);
            }

            // Execute the SELECT sentence to retrieve the instance properties
            ORMEntity <T> .ExecuteNonQuery(cmd.SqlCommand);

            // Close the connection to database
            ORMEntity <T> .Disconnect();

            return(((ORMIdentifiableEntity)instance).ID);
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Get the specified instance of the type.
        /// </summary>
        /// <param name="id">Instance primary key.</param>
        /// <returns>The specified instance.</returns>
        private static T ReadFromDatabase(long id, bool closeConnection = true)
        {
            T             instance = default;
            ORMSqlCommand cmd      = ORMEntity <T> .SqlDialect.GetSelectCommand();

            // Connecto to database
            ORMEntity <T> .Connect();

            // Set command parameters
            ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, id);

            // Execute the SELECT sentence to retrieve the instance properties
            using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
            {
                if (reader.Read())
                {
                    instance = ORMEntity <T> .MapData(reader);
                }
            }

            // Close the connection to database
            if (closeConnection)
            {
                ORMEntity <T> .Disconnect();
            }

            return(instance);
        }
Ejemplo n.º 5
0
        public ORMSqlCommand GetSelectByQuery(string whereClause)
        {
            try
            {
                ORMSqlCommand cmd = new ORMSqlCommand();

                // Generate SELECT clause
                cmd.SqlCommand += " select ";
                cmd.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
                cmd.SqlCommand += " as ID ";

                // Generate FROM clause
                cmd.SqlCommand += " from ";
                cmd.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName + " ";

                // Generate WHERE clause
                whereClause = whereClause.Trim();
                if (!whereClause.StartsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
                {
                    whereClause = "WHERE " + whereClause;
                }
                cmd.SqlCommand += whereClause;

                return(cmd);
            }
            catch (Exception ex)
            {
                Logger.LogError(this, ex);
                throw ex;
            }
        }
Ejemplo n.º 6
0
        public ORMSqlCommand GetSelectByFieldCommand(string propertyName)
        {
            try
            {
                ORMSqlCommand   cmd    = new ORMSqlCommand();
                ORMEntityMember member = ORMEntity <T> .ORMStructure.GetByPropertyName(propertyName);

                // Generate SELECT clause
                cmd.SqlCommand += " select ";
                cmd.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
                cmd.SqlCommand += " as ID ";

                // Generate FROM clause
                cmd.SqlCommand += " from ";
                cmd.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName;

                // Generate WHERE clause
                cmd.SqlCommand += " where ";
                cmd.SqlCommand += member.Attribute.FieldName + "=@" + member.Attribute.FieldName;
                cmd.Parameters.Add(member);

                return(cmd);
            }
            catch (Exception ex)
            {
                Logger.LogError(this, ex);
                throw ex;
            }
        }
Ejemplo n.º 7
0
        /// <summary>
        /// Gets the SQL sentence to get all entities.
        /// </summary>
        /// <returns>A string containing the requested SQL sentence.</returns>
        public ORMSqlCommand GetSelectAllCommand(ICollection <String> sortProperties = null)
        {
            try
            {
                if (this.SelectAllCommand != null)
                {
                    return(this.SelectAllCommand);
                }
                else
                {
                    this.SelectAllCommand = new ORMSqlCommand();
                }

                // Generate SELECT clause
                this.SelectAllCommand.SqlCommand += " select ";
                this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
                this.SelectAllCommand.SqlCommand += " as ID ";

                // Generate FROM clause
                this.SelectAllCommand.SqlCommand += " from ";
                this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName;

                // Generate ORDER BY clause
                if (sortProperties != null && sortProperties.Count > 0)
                {
                    this.SelectAllCommand.SqlCommand += " order by ";

                    bool isFirst = true;
                    foreach (string propName in sortProperties)
                    {
                        this.SelectAllCommand.SqlCommand += (isFirst ? string.Empty :  ", ");
                        this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.GetByPropertyName(propName)?.Attribute.FieldName;

                        isFirst = false;
                    }
                }

                return(this.SelectAllCommand);
            }
            catch (Exception ex)
            {
                Logger.LogError(this, ex);
                throw ex;
            }
        }
Ejemplo n.º 8
0
        /// <summary>
        /// Get the specified instance of the type.
        /// </summary>
        /// <param name="id">Instance primary key.</param>
        /// <returns>The specified instance.</returns>
        private static ICollection <T> ReadRecords(string propertyName, object value, bool closeConnection = true)
        {
            List <long>   ids  = new List <long>();
            List <T>      list = new List <T>();
            ORMSqlCommand cmd  = ORMEntity <T> .SqlDialect.GetSelectByFieldCommand(propertyName);

            // Connecto to database
            ORMEntity <T> .Connect();

            // Set filter parameter
            ORMEntity <T> .SetParameter(cmd.Parameters[0], value);

            // Execute the SELECT sentence to retrieve the instance properties
            using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
            {
                while (reader.Read())
                {
                    ids.Add((long)reader[0]);
                }
            }

            // Close the connection to database
            if (closeConnection)
            {
                ORMEntity <T> .Disconnect();
            }

            // Get all instances
            foreach (long id in ids)
            {
                if (!ORMEntity <T> .InMemoryTable.ContainsKey(id))
                {
                    T instance = ORMEntity <T> .Get(id, closeConnection);

                    list.Add(instance);
                }
                else
                {
                    list.Add(ORMEntity <T> .InMemoryTable[id]);
                }
            }

            return(list);
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Get the specified instance of the type.
        /// </summary>
        /// <param name="id">Instance primary key.</param>
        /// <returns>The specified instance.</returns>
        private static ICollection <T> ReadAllRecords(ICollection <String> sortProperties = null)
        {
            List <long>   ids  = new List <long>();
            List <T>      list = new List <T>();
            ORMSqlCommand cmd  = ORMEntity <T> .SqlDialect.GetSelectAllCommand(sortProperties);

            // Connecto to database
            ORMEntity <T> .Connect();

            // Execute the SELECT sentence to retrieve the instance properties
            using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
            {
                while (reader.Read())
                {
                    ids.Add((long)reader[0]);
                }
            }

            // Close the connection to database
            ORMEntity <T> .Disconnect();

            // Get all instances
            foreach (long id in ids)
            {
                if (!ORMEntity <T> .InMemoryTable.ContainsKey(id))
                {
                    T instance = ORMEntity <T> .Get(id);

                    list.Add(instance);
                }
                else
                {
                    list.Add(ORMEntity <T> .InMemoryTable[id]);
                }
            }

            return(list);
        }