예제 #1
0
        public void Rescan()
        {
            // Set default operator.
            SetDefault();

            // Activate base type to load assembly for tests.
            _ = new Table2Type();

            // Scan.
            SqlOperatorHandler.RescanDatabaseStructure();
        }
        /// <summary>
        /// Creating request that setting up data from object to database server acording to attributes.
        /// </summary>
        /// <param name="tableType">Type that has defined Table attribute
        /// Would be used as table descriptor during query building.</param>
        /// <param name="cancellationToken">Token that can terminate operation.</param>
        /// <param name="data">Object that contains fields that would be writed to database.
        /// Affected only fields and properties with defined Column attribute.</param>
        public async Task SetToTableAsync(Type tableType, CancellationToken cancellationToken, object data)
        {
            // Generate command
            using (DbCommand command = GenerateSetToTableCommand(tableType, data, out string error))
            {
                // Drop if error has been occured.
                if (!string.IsNullOrEmpty(error))
                {
                    SqlOperatorHandler.InvokeSQLErrorOccured(data, "Commnad generation failed. Details:\n" + error);
                    return;
                }

                #region Execute command
                // Opening connection to DB srver.
                if (!Active.OpenConnection(out error))
                {
                    SqlOperatorHandler.InvokeSQLErrorOccured(data, "Connection failed.\n" + error);
                    return;
                }

                // Executing command.
                command.Connection = Active.Connection;

                int affectedRowsCount;
                try
                {
                    affectedRowsCount = await command.ExecuteNonQueryAsync(cancellationToken);
                }
                catch (Exception ex)
                {
                    throw new Exception("Query not exeuted. Query:\n" + command.CommandText + "\n\nDetails:\n" + ex.Message);
                }

                // Closing connection.
                Active.CloseConnection();
                #endregion

                // Log if command failed.
                if (affectedRowsCount == 0)
                {
                    SqlOperatorHandler.InvokeSQLErrorOccured(data, "Query not affect any row.\n\n" + command.CommandText);
                }
            }
        }
        /// <summary>
        /// Generating set to table sql command from provided source data.
        /// </summary>
        /// <param name="tableType">Type that has defined Table attribute.
        /// Would be used as table descriptor during queri building.</param>
        /// <param name="data">Object that contain's fields that would be writed to database.
        /// Affected only fields and properties with defined Column attribute.</param>
        /// <param name="error">Error faces during operation.</param>
        /// <returns>Generated command or null if failed.</returns>
        public DbCommand GenerateSetToTableCommand(Type tableType, object data, out string error)
        {
            #region Validate entry data
            // Check is SQL operator exist.
            if (Active == null)
            {
                throw new NullReferenceException("Active 'ISQLOperator' not exist. Select it before managing of database.");
            }

            // Loking for table descriptor.
            if (!TableAttribute.TryToGetTableAttribute(tableType, out TableAttribute tableDescriptor, out error))
            {
                return(null);
            }
            #endregion

            #region Members detection
            // Detect memebers on objects that contain columns definition.
            List <MemberInfo> members = MembersHandler.FindMembersWithAttribute <ColumnAttribute>(data.GetType()).ToList();

            // Drop set ignore columns.
            members = MembersHandler.FindMembersWithoutAttribute <SetQueryIgnoreAttribute>(members).ToList();

            // Drop virtual generated columns.
            bool NotVirtual(MemberInfo member)
            {
                return(!(member.GetCustomAttribute <IsGeneratedAttribute>() is IsGeneratedAttribute isGenerated) ||
                       isGenerated.mode != IsGeneratedAttribute.Mode.Virual);
            };
            members = MembersHandler.FindMembersWithoutAttribute <IsGeneratedAttribute>(members, NotVirtual).ToList();

            // Trying to detect member with defined isAutoIncrement attribute that has default value.
            MemberInfo autoIncrementMember = null;
            try
            {
                autoIncrementMember = IsAutoIncrementAttribute.GetIgnorable(ref data, members);
            }
            catch (Exception ex)
            {
                error = ex.Message;
                return(null);
            }
            // Remove ignorable.
            if (autoIncrementMember != null)
            {
                members.Remove(autoIncrementMember);
            }

            // Find our not key elements.
            IEnumerable <MemberInfo> membersNK = MembersHandler.FindMembersWithoutAttribute <IsPrimaryKeyAttribute>(members);
            #endregion

            #region Generating command
            // Command that can be executed on the server.
            DbCommand command = Active.NewCommand();

            // Set values as local params.
            ColumnAttribute.MembersDataToCommand(ref data, ref command, members);

            // Getting metas.
            ColumnAttribute.MembersToMetaLists(members, out List <ColumnAttribute> membersColumns, out List <string> membersVars);
            ColumnAttribute.MembersToMetaLists(membersNK, out List <ColumnAttribute> membersNKColumns, out List <string> membersNKVars);

            string commadText = "";
            commadText += "INSERT INTO `" + tableDescriptor.schema + "`.`" + tableDescriptor.table + "`\n";
            commadText += "\t\t(" + SqlOperatorHandler.CollectionToString(membersColumns) + ")\n";
            commadText += "\tVALUES\n";
            commadText += "\t\t(" + SqlOperatorHandler.CollectionToString(membersVars) + ")\n";
            commadText += "\tON DUPLICATE KEY UPDATE\n";
            commadText += "\t\t" + SqlOperatorHandler.ConcatFormatedCollections(membersNKColumns, membersNKVars, '\0') + ";\n";

            command.CommandText = commadText;
            #endregion

            error = null;
            return(command);
        }
예제 #4
0
        /// <summary>
        /// Trying to set object data to database.
        /// </summary>
        /// <param name="tableType">Type with defined Table attribute.
        /// Contains columns\properties with defined column attributes. Using as map for collecting data.</param>
        /// <param name="obj">Instance that contains data tha with the same column sttributes as in tableType.</param>
        /// <param name="error">Occurred error. Null if operation passed success.</param>
        /// <param name="select">Array that contains columns' names that would be requested in select block.
        /// If empty then would auto select all columns.</param>
        /// <param name="where">Array that contains columns' names taht wouyld be added to Where block.</param>
        /// <returns>Result of operation.</returns>
        public bool SetToObject(
            Type tableType,
            object obj,
            out string error,
            string[] select,
            params string[] where)
        {
            #region Detecting target object
            // Detect object that contains querie's data.
            object internalObj;
            if (obj is IList objList)
            {
                // Set first element of list as target;
                internalObj = objList[0];
            }
            else
            {
                // Set input object as target.
                internalObj = obj;
            }
            #endregion

            #region Building command
            // Get coommon list of available members.
            List <MemberInfo> members = MembersAllowedToSet(tableType, internalObj, out error);

            // Generate command.
            DbCommand command = GenerateSetToObjectCommand(
                tableType,
                internalObj,
                TableAttribute.FindMembersByColumns(members, where),
                TableAttribute.FindMembersByColumns(members, select));

            // Drop if error has been occured.
            if (!string.IsNullOrEmpty(error))
            {
                error = "Commnad generation failed. Details:\n" + error;
                return(false);
            }
            #endregion

            #region Execute command
            // Opening connection to DB srver.
            if (!Active.OpenConnection(out error))
            {
                error = "Connection failed. Details:\n" + error;
                return(false);
            }
            command.Connection = SqlOperatorHandler.Active.Connection;

            // Await for reader.
            DbDataReader reader = command.ExecuteReader();

            // Drop if DbDataReader is invalid.
            if (reader == null || reader.IsClosed)
            {
                error = "DbDataReader is null or closed. Operation declined.";
                return(false);
            }

            #region Reading data
            bool result = true;
            // If collection.
            if (obj is IList)
            {
                bool readed;
                ((IList)obj).Clear();

                // Read stream till possible.
                do
                {
                    // Instiniate obect for receving.
                    object instance = Activator.CreateInstance(tableType);
                    readed = SqlOperatorHandler.DatabaseDataToObject(reader, members, instance, out _);
                    // If readed then add to output.
                    if (readed)
                    {
                        ((IList)obj).Add(instance);
                    }
                }while (readed);
            }
            else
            {
                // Try to apply data from reader to object.
                result = SqlOperatorHandler.DatabaseDataToObject(reader, members, obj, out error);
            }
            #endregion

            // Closing connection.
            Active.CloseConnection();
            #endregion

            return(result);
        }
        /// <summary>
        /// Return command that would allow to create table by descriptor.
        /// </summary>
        /// <param name="sourceType"></param>
        /// <returns></returns>
        public static string GenerateCreateTableCommand(Type sourceType)
        {
            // Loking for table descriptor.
            if (!TableAttribute.TryToGetTableAttribute(sourceType, out TableAttribute tableDescriptor, out string error))
            {
                SqlOperatorHandler.InvokeSQLErrorOccured(sourceType, error);
                return(null);
            }

            // Variable that would contain SQL comand.
            string command = "";

            command += "CREATE TABLE IF NOT EXISTS `" + tableDescriptor.schema + "`.`" + tableDescriptor.table + "` (\n";

            IEnumerable <MemberInfo> columns = MembersHandler.FindMembersWithAttribute <ColumnAttribute>(sourceType);

            #region Declere columns
            string colCommand = "";
            foreach (MemberInfo member in columns)
            {
                if (!string.IsNullOrEmpty(colCommand))
                {
                    colCommand += ",\n";
                }
                colCommand += SqlOperatorHandler.Active.ColumnDeclarationCommand(member);
            }
            command += colCommand;
            #endregion

            #region Primary keys
            // Build PKs substring string.
            string subPkCommand = "";
            foreach (MemberInfo cMeta in columns)
            {
                if (MembersHandler.TryToGetAttribute <IsPrimaryKeyAttribute>(cMeta, out IsPrimaryKeyAttribute isPrimaryKey))
                {
                    if (!string.IsNullOrEmpty(subPkCommand))
                    {
                        subPkCommand += ", ";
                    }

                    MembersHandler.TryToGetAttribute <ColumnAttribute>(cMeta, out ColumnAttribute column);
                    subPkCommand += "`" + column.title + "`";
                }
            }

            // Add to command command if pks exist.
            command += subPkCommand.Length > 0 ? ",\nPRIMARY KEY(" + subPkCommand + ")" : "";
            #endregion

            #region Unique indexes
            foreach (MemberInfo cMeta in columns)
            {
                if (MembersHandler.TryToGetAttribute <IsUniqueAttribute>(cMeta, out IsUniqueAttribute isUnique))
                {
                    command += ",\n";
                    command += isUnique.UniqueIndexDeclarationCommand(cMeta);
                }
            }
            #endregion

            #region FK indexes
            IsForeignKeyAttribute.DropIndexator();
            foreach (MemberInfo cMeta in columns)
            {
                string decleration = IsForeignKeyAttribute.FKIndexDeclarationCommand(cMeta, tableDescriptor.table);
                if (!string.IsNullOrEmpty(decleration))
                {
                    command += ",\n" + decleration;
                }
            }
            #endregion

            #region Constraints
            IsForeignKeyAttribute.DropIndexator();
            foreach (MemberInfo cMeta in columns)
            {
                string decleration = IsForeignKeyAttribute.ConstrainDeclarationCommand(cMeta, tableDescriptor.table);
                if (!string.IsNullOrEmpty(decleration))
                {
                    command += ",\n" + decleration;
                }
            }
            #endregion

            command += ")\n";

            command += "ENGINE = " + (string.IsNullOrEmpty(tableDescriptor.engine) ? "InnoDB" : tableDescriptor.engine) + ";";

            return(command);
        }