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