/// <summary>
/// Create a new instance into database.
/// </summary>
/// <param name="instance">Instance to create.</param>
/// <returns>The instance unique identifier.</returns>
private static long InsertDatabaseRecord(T instance)
{
object value;
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetInsertCommand();
// Connecto to database
ORMEntity <T> .Connect();
// Set command parameters
foreach (ORMEntityMember param in cmd.Parameters)
{
value = param.GetValue(instance);
ORMEntity <T> .SetParameter(param, value is null?DBNull.Value : value);
}
// Execute the SELECT sentence to retrieve the instance properties
ORMEntity <T> .ExecuteNonQuery(cmd.SqlCommand);
long id = ExecuteScalar(SqlDialect.GetNewIDCommand().SqlCommand);
// Set the generated new record unique identifier to the current instance
ORMEntity <T> .ORMStructure.PrimaryKey.SetValue(instance, id);
// Close the connection to database
ORMEntity <T> .Disconnect();
// Created project is added to in-memory table
ORMEntity <T> .AddInMemoryTable(id, instance);
return(((ORMIdentifiableEntity)instance).ID);
}
/// <summary>
/// Delete an existing instance from database.
/// </summary>
/// <param name="instance">Instance unique identifier.</param>
private static int DeleteDatabaseRecord(long id)
{
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetDeleteCommand();
// Delete foreign records
ORMEntity <T> .DeleteDatabaseForeignRecords(id);
// Connecto to database
ORMEntity <T> .Connect();
// Set command parameters
ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, id);
// Execute the SQL command
int rowsAffected = ExecuteNonQuery(cmd.SqlCommand);
// Close the connection to database
ORMEntity <T> .Disconnect();
// Delete the instance from in-memory database
if (ORMEntity <T> .InMemoryTable.ContainsKey(id))
{
ORMEntity <T> .InMemoryTable.Remove(id);
}
return(rowsAffected);
}
/// <summary>
/// Update an existing database record.
/// </summary>
/// <param name="instance">Instance to update.</param>
/// <returns>The instance unique identifier.</returns>
private static long UpdateDatabaseRecord(T instance)
{
object value;
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetUpdateCommand();
// Connecto to database
ORMEntity <T> .Connect();
// Set command parameters
ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, cmd.PrimaryKeyName.GetValue(instance));
foreach (ORMEntityMember param in cmd.Parameters)
{
value = param.GetValue(instance);
ORMEntity <T> .SetParameter(param, value is null?DBNull.Value : value);
}
// Execute the SELECT sentence to retrieve the instance properties
ORMEntity <T> .ExecuteNonQuery(cmd.SqlCommand);
// Close the connection to database
ORMEntity <T> .Disconnect();
return(((ORMIdentifiableEntity)instance).ID);
}
/// <summary>
/// Get the specified instance of the type.
/// </summary>
/// <param name="id">Instance primary key.</param>
/// <returns>The specified instance.</returns>
private static T ReadFromDatabase(long id, bool closeConnection = true)
{
T instance = default;
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetSelectCommand();
// Connecto to database
ORMEntity <T> .Connect();
// Set command parameters
ORMEntity <T> .SetParameter(cmd.PrimaryKeyName, id);
// Execute the SELECT sentence to retrieve the instance properties
using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
{
if (reader.Read())
{
instance = ORMEntity <T> .MapData(reader);
}
}
// Close the connection to database
if (closeConnection)
{
ORMEntity <T> .Disconnect();
}
return(instance);
}
public ORMSqlCommand GetSelectByQuery(string whereClause)
{
try
{
ORMSqlCommand cmd = new ORMSqlCommand();
// Generate SELECT clause
cmd.SqlCommand += " select ";
cmd.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
cmd.SqlCommand += " as ID ";
// Generate FROM clause
cmd.SqlCommand += " from ";
cmd.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName + " ";
// Generate WHERE clause
whereClause = whereClause.Trim();
if (!whereClause.StartsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
whereClause = "WHERE " + whereClause;
}
cmd.SqlCommand += whereClause;
return(cmd);
}
catch (Exception ex)
{
Logger.LogError(this, ex);
throw ex;
}
}
public ORMSqlCommand GetSelectByFieldCommand(string propertyName)
{
try
{
ORMSqlCommand cmd = new ORMSqlCommand();
ORMEntityMember member = ORMEntity <T> .ORMStructure.GetByPropertyName(propertyName);
// Generate SELECT clause
cmd.SqlCommand += " select ";
cmd.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
cmd.SqlCommand += " as ID ";
// Generate FROM clause
cmd.SqlCommand += " from ";
cmd.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName;
// Generate WHERE clause
cmd.SqlCommand += " where ";
cmd.SqlCommand += member.Attribute.FieldName + "=@" + member.Attribute.FieldName;
cmd.Parameters.Add(member);
return(cmd);
}
catch (Exception ex)
{
Logger.LogError(this, ex);
throw ex;
}
}
/// <summary>
/// Gets the SQL sentence to get all entities.
/// </summary>
/// <returns>A string containing the requested SQL sentence.</returns>
public ORMSqlCommand GetSelectAllCommand(ICollection <String> sortProperties = null)
{
try
{
if (this.SelectAllCommand != null)
{
return(this.SelectAllCommand);
}
else
{
this.SelectAllCommand = new ORMSqlCommand();
}
// Generate SELECT clause
this.SelectAllCommand.SqlCommand += " select ";
this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.PrimaryKey.Attribute.FieldName;
this.SelectAllCommand.SqlCommand += " as ID ";
// Generate FROM clause
this.SelectAllCommand.SqlCommand += " from ";
this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.Table.TableName;
// Generate ORDER BY clause
if (sortProperties != null && sortProperties.Count > 0)
{
this.SelectAllCommand.SqlCommand += " order by ";
bool isFirst = true;
foreach (string propName in sortProperties)
{
this.SelectAllCommand.SqlCommand += (isFirst ? string.Empty : ", ");
this.SelectAllCommand.SqlCommand += ORMEntity <T> .ORMStructure.GetByPropertyName(propName)?.Attribute.FieldName;
isFirst = false;
}
}
return(this.SelectAllCommand);
}
catch (Exception ex)
{
Logger.LogError(this, ex);
throw ex;
}
}
/// <summary>
/// Get the specified instance of the type.
/// </summary>
/// <param name="id">Instance primary key.</param>
/// <returns>The specified instance.</returns>
private static ICollection <T> ReadRecords(string propertyName, object value, bool closeConnection = true)
{
List <long> ids = new List <long>();
List <T> list = new List <T>();
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetSelectByFieldCommand(propertyName);
// Connecto to database
ORMEntity <T> .Connect();
// Set filter parameter
ORMEntity <T> .SetParameter(cmd.Parameters[0], value);
// Execute the SELECT sentence to retrieve the instance properties
using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
{
while (reader.Read())
{
ids.Add((long)reader[0]);
}
}
// Close the connection to database
if (closeConnection)
{
ORMEntity <T> .Disconnect();
}
// Get all instances
foreach (long id in ids)
{
if (!ORMEntity <T> .InMemoryTable.ContainsKey(id))
{
T instance = ORMEntity <T> .Get(id, closeConnection);
list.Add(instance);
}
else
{
list.Add(ORMEntity <T> .InMemoryTable[id]);
}
}
return(list);
}
/// <summary>
/// Get the specified instance of the type.
/// </summary>
/// <param name="id">Instance primary key.</param>
/// <returns>The specified instance.</returns>
private static ICollection <T> ReadAllRecords(ICollection <String> sortProperties = null)
{
List <long> ids = new List <long>();
List <T> list = new List <T>();
ORMSqlCommand cmd = ORMEntity <T> .SqlDialect.GetSelectAllCommand(sortProperties);
// Connecto to database
ORMEntity <T> .Connect();
// Execute the SELECT sentence to retrieve the instance properties
using (DbDataReader reader = ORMEntity <T> .ExecuteReader(cmd.SqlCommand))
{
while (reader.Read())
{
ids.Add((long)reader[0]);
}
}
// Close the connection to database
ORMEntity <T> .Disconnect();
// Get all instances
foreach (long id in ids)
{
if (!ORMEntity <T> .InMemoryTable.ContainsKey(id))
{
T instance = ORMEntity <T> .Get(id);
list.Add(instance);
}
else
{
list.Add(ORMEntity <T> .InMemoryTable[id]);
}
}
return(list);
}
