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