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