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