/// <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>
/// 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 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>Result of operation.</returns>
public bool SetToTable(Type tableType, object data, out string error)
{
// Generate command
using (DbCommand command = GenerateSetToTableCommand(tableType, data, out error))
{
// Drop if error has been occured.
if (!string.IsNullOrEmpty(error))
{
return(false);
}
#region Execute command
// Opening connection to DB srver.
if (!Active.OpenConnection(out error))
{
return(false);
}
// Executing command.
command.Connection = Active.Connection;
int affectedRowsCount;
try
{
affectedRowsCount = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception("Query not exeuted. Query:\n" + command.CommandText + "\n\nDetails:\n" + ex.Message);
}
// Closing connection.
Active.CloseConnection();
#endregion
// Retrun true if query was success, false if rows not affected.
return(affectedRowsCount > 0);
}
}
/// <summary>
/// Trying to set schema to databases server in case if schema not exist.
/// </summary>
/// <param name="schemaName">Name of the schema that would be used\created.</param>
/// <param name="error">Error faces during operation.</param>
/// <returns></returns>
public bool ActivateSchema(string schemaName, out string error)
{
// Check is SQL operator exist.
if (Active == null)
{
throw new NullReferenceException("Active 'ISQLOperator' not exist. Select it before managing of database.");
}
// Variable that would contain SQL comand.
string command = "";
// Creating schema if not exist.
command += "CREATE SCHEMA IF NOT EXISTS `" + schemaName + "` DEFAULT CHARACTER SET utf8 ;\n";
// Setting schema as target.
command += "USE `" + schemaName + "` ;";
// Tring to open connection to the server.
if (!Active.OpenConnection(out error))
{
// Inform about fail.
return(false);
}
// Instiniating a new command based on the query.
using (var dCommand = Active.NewCommand(command))
{
// Executing the query.
dCommand.ExecuteNonQuery();
// Closing the connection after finish executing.
Active.CloseConnection();
}
// Confirm success.
return(true);
}
/// <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);
}
