private void CreateTableIfNotExists()
{
var config = _config ?? ConfigurationFactory.Get(_entityType);
var connectionName = _connectionName ?? config.DefaultConnectionName;
var dialect = DialectFactory.GetProvider(connectionName, _config);
using (var connection = DbFactory.CreateConnection(connectionName, _entityType, _config))
{
connection.Open();
using (var tx = connection.BeginTransaction(IsolationLevel.Serializable))
{
using (var command = connection.CreateCommand())
{
command.Transaction = tx;
command.CommandText = dialect.CreateTableIfNotExists(config.HiLoTableName,
new Dictionary <string, Tuple <DbType, int> > {
{ "next_hi", Tuple.Create(DbType.Int64, 0) }, { "entity_type", Tuple.Create(DbType.AnsiString, 128) }
});
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
tx.Commit();
}
connection.Close();
}
}
}
/// <summary>
/// Deletes instances of the passed type that match the conditions passed
/// </summary>
/// <param name="type">Type</param>
/// <param name="conditions">Conditions</param>
/// <param name="transaction">Transaction, in case the operation must belong to one</param>
public static void Delete(Type type, ConditionCollection conditions, Transaction transaction)
{
if (type == null)
{
throw new ArgumentNullException("type");
}
if (!type.IsSubclassOf(typeof(EntityBase)))
{
throw new InvalidTypeException(type, "type");
}
TableInfo table = TableInfo.CreateTableInfo(type);
ConnectionStringSettings connection = table.GetConnection();
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
Join[] joins = GetPlainJoins(conditions, type);
TenorParameter[] parameters = null;
string query = dialect.CreateDeleteSql(type, conditions, joins, out parameters);
if (transaction != null && transaction.dbTransaction != null)
{
Helper.ExecuteQuery(query, parameters, transaction.dbTransaction, dialect);
}
else
{
Helper.UpdateData(connection, query, parameters);
}
}
internal static IEnumerable <T> Select <T, T1, T2, T3>(Expression <Func <T, T1, bool> > join1, Expression <Func <T1, T2, bool> > join2, Expression <Func <T2, T3, bool> > join3,
Expression <Func <T, bool> > predicate = null, string connectionName = null, DbConnection connection = null, DialectProvider provider = null, int page = 0, int pageSize = 0, int skipCount = 0, bool?cached = null,
SelectOption selectOption = SelectOption.All, IConfiguration config = null, params Sorting <T>[] orderBy)
where T : class
where T1 : class
where T2 : class
where T3 : class
{
if (provider == null)
{
string providerName = null;
if (connection == null)
{
providerName = DbFactory.GetProviderInvariantName(connectionName, typeof(T), config);
connection = DbFactory.CreateConnection(connectionName, typeof(T), config);
}
provider = DialectFactory.GetProvider(connection, providerName);
}
var sqlRoot = SqlBuilder.GetSelectStatement(predicate, page, pageSize, skipCount, selectOption != SelectOption.All, provider, orderBy);
var sqlJoin1 = SqlBuilder.GetSelectStatement(predicate, join1, 0, 0, 0, false, provider, orderBy);
var sqlJoin2 = SqlBuilder.GetSelectStatement(predicate, join1, join2, 0, 0, 0, false, provider, orderBy);
var sqlJoin3 = SqlBuilder.GetSelectStatement(predicate, join1, join2, join3, 0, 0, 0, false, provider, orderBy);
var result = new EagerLoadEnumerable <T>(new[] { sqlRoot, sqlJoin1, sqlJoin2, sqlJoin3 }, new[] { typeof(T), typeof(T1), typeof(T2), typeof(T3) },
(s, t) => ((IMultiResult)RetrieveImplemenation <T>(s, OperationType.Sql, null, OperationReturnType.MultiResult, connectionName, connection, types: t, cached: cached, config: config)).Aggregate <T>(config), predicate, provider, selectOption, connectionName, connection, page, pageSize, skipCount, config);
return(result);
}
private static IAsyncEnumerable <T> SelectAsync <T, T1>(Expression <Func <T, T1, bool> > join, Expression <Func <T, bool> > predicate = null, string connectionName = null, DbConnection connection = null, DialectProvider provider = null, int page = 0, int pageSize = 0,
bool?cached = null, SelectOption selectOption = SelectOption.All, params Sorting <T>[] orderBy)
where T : class
where T1 : class
{
if (provider == null)
{
string providerName = null;
if (connection == null)
{
providerName = DbFactory.GetProviderInvariantName(connectionName, typeof(T));
connection = DbFactory.CreateConnection(connectionName, typeof(T));
}
provider = DialectFactory.GetProvider(connection, providerName);
}
var sqlRoot = SqlBuilder.GetSelectStatement(predicate, page, pageSize, selectOption != SelectOption.All, provider, orderBy);
var sqlJoin = SqlBuilder.GetSelectStatement(predicate, join, 0, 0, false, provider, orderBy);
var result = new EagerLoadEnumerableAsync <T>(new[] { sqlRoot, sqlJoin }, new[] { typeof(T), typeof(T1) },
(s, t) => RetrieveImplemenationAsync <T>(s, OperationType.Sql, null, OperationReturnType.MultiResult, connectionName, connection, types: t, cached: cached), predicate, provider, selectOption);
return(result);
}
public string Generate(IConfiguration configuration, string connectionString, string providerName, IEnumerable <string> tablesToIgnore, IEnumerable <string> indexesToIgnore, IEnumerable <KeyValuePair <string, string> > extraPluralizationWords, IAnswerProvider answerProvider)
{
// set up the database connection
var dialectFactory = new DialectFactory();
var dialect = dialectFactory.Create(providerName, connectionString);
var dbProviderFactoryFactory = new DbProviderFactoryFactory();
var dbProviderFactory = dbProviderFactoryFactory.Create(providerName, connectionString);
IEnumerable <IMap> fromMaps;
if (!dbProviderFactory.DatabaseExists(connectionString, providerName, dialect))
{
fromMaps = Enumerable.Empty <IMap>();
return(this.GenerateScript(fromMaps, configuration.Maps, dialect, new NullStatisticsProvider(), answerProvider, tablesToIgnore, indexesToIgnore));
}
else
{
// get the schema from the existing database
var schemaReaderFactory = new SchemaReaderFactory();
var schemaReader = schemaReaderFactory.GetSchemaReader(providerName);
var connectionStringManipulator = new ConnectionStringManipulator(dbProviderFactory, connectionString);
using (var connection = dbProviderFactory.CreateConnection()) {
connection.ConnectionString = connectionString;
connection.Open();
var schema = schemaReader.Read(connection, connectionStringManipulator.GetDatabaseName());
// reverse engineer the maps
var engineer = new Engineer(extraPluralizationWords.Union(configuration.Maps.Select(m => new KeyValuePair <string, string>(m.Type.Name, m.Table)))); // we use our configuration to inform us as to the correct naming of tables
fromMaps = engineer.ReverseEngineer(schema, dialect, tablesToIgnore, answerProvider, false);
return(this.GenerateScript(fromMaps, configuration.Maps, dialect, new StatisticsProvider(connection, dialect), answerProvider, tablesToIgnore, indexesToIgnore));
}
}
}
public void Execute(IConfiguration configuration, string connectionString, string providerName, IEnumerable <string> tablesToIgnore, IEnumerable <string> indexesToIgnore, IEnumerable <KeyValuePair <string, string> > extraPluralizationWords, IAnswerProvider answerProvider)
{
var script = this.scriptGenerator.Generate(configuration, connectionString, providerName, tablesToIgnore, indexesToIgnore, extraPluralizationWords, answerProvider);
if (string.IsNullOrWhiteSpace(script))
{
using (new ColorContext(ConsoleColor.Green)) {
Log.Logger.Information("No migration script to run");
return;
}
}
// set up the database connection
var dialectFactory = new DialectFactory();
var dialect = dialectFactory.Create(providerName, connectionString);
var dbProviderFactoryFactory = new DbProviderFactoryFactory();
var dbProviderFactory = dbProviderFactoryFactory.Create(providerName, connectionString);
dbProviderFactory.CreateDatabaseIfNotExists(connectionString, providerName, dialect);
// run the script
using (var connection = dbProviderFactory.CreateConnection()) {
connection.ConnectionString = connectionString;
connection.Open();
connection.Execute(script);
}
}
internal static DbConnection CreateConnection(ConnectionStringSettings connection)
{
if (connection == null)
{
throw new NullReferenceException("connection");
}
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
return(CreateConnection(dialect, connection));
}
private void MoveNextHi()
{
if (!_hasTable)
{
CreateTableIfNotExists();
_hasTable = true;
}
var config = _config ?? ConfigurationFactory.Get(_entityType);
var connectionName = _connectionName ?? config.DefaultConnectionName;
var dialect = DialectFactory.GetProvider(connectionName, config);
using (var connection = DbFactory.CreateConnection(connectionName, _entityType, config))
{
connection.Open();
using (var tx = connection.BeginTransaction(IsolationLevel.Serializable))
{
using (var command = connection.CreateCommand())
{
command.Transaction = tx;
command.CommandText = string.Format(GenerateSql, config.HiLoTableName, dialect.IdentifierEscapeStartCharacter, dialect.IdentifierEscapeEndCharacter, dialect.VariablePrefix,
dialect.UseOrderedParameters ? "?" : "entityType", dialect.UseOrderedParameters ? "?" : "entityType", dialect.UseOrderedParameters ? "?" : "currentHi");
command.CommandType = CommandType.Text;
if (dialect.UseOrderedParameters)
{
var p1 = command.CreateParameter();
p1.Value = _entityType.Name;
var p2 = command.CreateParameter();
p2.Value = _entityType.Name;
var p3 = command.CreateParameter();
p3.Value = _currentHi;
command.Parameters.AddRange(new[] { p1, p2, p3 });
}
else
{
var p1 = command.CreateParameter();
p1.ParameterName = "entityType";
p1.Value = _entityType.Name;
var p2 = command.CreateParameter();
p2.ParameterName = "currentHi";
p2.Value = _entityType.Name;
command.Parameters.AddRange(new[] { p1, p2 });
}
_currentHi = (long)command.ExecuteScalar();
tx.Commit();
}
connection.Close();
}
}
}
public TestSessionWrapper Initialize()
{
var dialect = new DialectFactory().Create(this.configuration.ConnectionStringSettings);
// load the data
using (var transactionLessSession = this.configuration.BeginTransactionLessSession()) {
// create database if exists
if (!dialect.IgnoreMultipleDatabases)
{
if (transactionLessSession.Dapper.Query(dialect.CheckDatabaseExists(DatabaseName)).Any())
{
transactionLessSession.Dapper.Execute("drop database " + DatabaseName);
}
transactionLessSession.Dapper.Execute("create database " + DatabaseName);
transactionLessSession.Dapper.Execute("use " + DatabaseName);
}
var migrator = new Migrator(
dialect,
new CreateTableWriter(dialect),
new AlterTableWriter(dialect),
new DropTableWriter(dialect),
new StatisticsProvider(null, dialect));
IEnumerable <string> warnings, errors;
var createStatement = migrator.GenerateSqlDiff(
new List <IMap>(),
this.configuration.Maps,
null,
new Mock <ILogger>().Object,
new string[0],
new string[0],
out warnings,
out errors);
var statements = createStatement.Split(';');
foreach (var statement in statements.Where(s => !string.IsNullOrWhiteSpace(s.Trim())))
{
transactionLessSession.Dapper.Execute(statement);
}
}
var session = this.configuration.BeginSession();
if (!dialect.IgnoreMultipleDatabases)
{
session.Dapper.Execute("use " + DatabaseName);
}
this.InsertData(session);
return(new TestSessionWrapper(session));
}
public ISession OpenSession(string connectionString)
{
if (string.IsNullOrEmpty(connectionString))
{
throw new ArgumentNullException("connectionString", "The connection string was not supplied.");
}
var dialectFactory = new DialectFactory();
var dialect = dialectFactory.Create(connectionString);
var connection = dialect.CreateConnection(connectionString);
return(new Session(connection, this._metadataStore, dialect, _environment));
}
public static async Task <int> CountAsync <T>(Expression <Func <T, bool> > predicate = null, string connectionName = null, DbConnection connection = null)
where T : class
{
string providerName = null;
if (connection == null)
{
providerName = DbFactory.GetProviderInvariantName(connectionName, typeof(T));
connection = DbFactory.CreateConnection(connectionName, typeof(T));
}
var sql = SqlBuilder.GetSelectCountStatement(predicate, DialectFactory.GetProvider(connection, providerName));
return(await RetrieveScalarAsync <int>(sql, connection : connection));
}
internal static TResult Count <T, TResult>(Expression <Func <T, bool> > predicate = null, string connectionName = null, DbConnection connection = null, IConfiguration config = null)
where T : class
where TResult : struct
{
string providerName = null;
if (connection == null)
{
providerName = DbFactory.GetProviderInvariantName(connectionName, typeof(T), config);
connection = DbFactory.CreateConnection(connectionName, typeof(T), config);
}
var sql = SqlBuilder.GetSelectCountStatement(predicate, DialectFactory.GetProvider(connection, providerName));
return(RetrieveScalar <TResult>(sql, connection: connection, config: config));
}
public static IAsyncEnumerable <T> SelectAsync <T>(Expression <Func <T, bool> > predicate = null, string connectionName = null, DbConnection connection = null, int page = 0, int pageSize = 0, int skipCount = 0, bool?cached = null,
SelectOption selectOption = SelectOption.All, IConfiguration config = null, params Sorting <T>[] orderBy)
where T : class
{
string providerName = null;
if (connection == null)
{
providerName = DbFactory.GetProviderInvariantName(connectionName, typeof(T), config);
connection = DbFactory.CreateConnection(connectionName, typeof(T), config);
}
var provider = DialectFactory.GetProvider(connection, providerName);
var sql = SqlBuilder.GetSelectStatement(predicate, page, pageSize, skipCount, selectOption != SelectOption.All, provider, orderBy);
var result = new EagerLoadEnumerableAsync <T>(new[] { sql }, new[] { typeof(T) },
(s, t) => RetrieveImplemenationAsync <T>(s, OperationType.Sql, null, OperationReturnType.SingleResult, connectionName, connection, types: t, cached: cached, config: config), predicate, provider, selectOption, connectionName, connection, page, pageSize, skipCount, config);
return(result);
}
/*
* /// <summary>
* /// Executes a query on the database.
* /// </summary>
* /// <param name="query">A SQL query to execute.</param>
* /// <param name="parameters">Parameters collection.</param>
* /// <param name="connection">The connection.</param>
* public static object UpdateData(string query, TenorParameter[] parameters, ConnectionStringSettings connection)
* {
* return UpdateData(query, parameters, connection, (string)null);
* }
*/
/// <summary>
/// Executes a query on the database.
/// </summary>
/// <param name="query">A SQL query to execute.</param>
/// <param name="parameters">Parameters collection.</param>
/// <param name="connection">The connection.</param>
public static object UpdateData(ConnectionStringSettings connection, string query, params TenorParameter[] parameters)
{
if (connection == null)
{
connection = EntityBase.SystemConnection;
}
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
DbConnection conn = CreateConnection(dialect, connection);
DbTransaction transaction = null;
try
{
OpenConnection(conn, dialect);
transaction =
conn.BeginTransaction();
object retVal = ExecuteQuery(query, parameters, transaction, dialect);
transaction.Commit();
return(retVal);
}
catch
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
}
public void Execute(ISeeder seeder, IConfiguration configuration, string connectionString, string providerName)
{
// set up the database connection
var dialectFactory = new DialectFactory();
var dialect = dialectFactory.Create(providerName, connectionString);
var dbProviderFactoryFactory = new DbProviderFactoryFactory();
var dbProviderFactory = dbProviderFactoryFactory.Create(providerName, connectionString);
if (!dbProviderFactory.DatabaseExists(connectionString, providerName, dialect))
{
Log.Logger.Error("Database doesn't exist");
return;
}
// run the script
var sqlSessionCreator = new SqlDatabase(configuration, dbProviderFactory, connectionString, dialect);
using (var session = sqlSessionCreator.BeginSession()) {
seeder.Seed(session);
session.Complete();
}
}
public static async Task <long> InsertAsync <T>(IEnumerable <T> items, string connectionName = null, DbConnection connection = null, DbTransaction transaction = null, bool captureException = false, IConfiguration config = null)
where T : class
{
var count = 0L;
var connectionOpenedHere = false;
var externalTransaction = transaction != null;
var externalConnection = externalTransaction || connection != null;
if (config == null)
{
config = ConfigurationFactory.Get <T>();
}
if (externalTransaction)
{
connection = transaction.Connection;
}
if (!externalConnection)
{
connection = DbFactory.CreateConnection(connectionName ?? config.DefaultConnectionName, config);
}
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
connectionOpenedHere = true;
}
if (transaction == null)
{
transaction = connection.BeginTransaction();
}
var propertyMap = Reflector.GetPropertyMap <T>();
var provider = DialectFactory.GetProvider(transaction.Connection);
var requests = BuildBatchInsert(items, transaction, captureException, propertyMap, provider, config);
foreach (var request in requests)
{
var response = await ExecuteAsync <T>(request).ConfigureAwait(false);
if (!response.HasErrors)
{
count += response.RecordsAffected;
}
}
transaction.Commit();
return(count);
}
catch (Exception ex)
{
transaction?.Rollback();
throw;
}
finally
{
if (connectionOpenedHere)
{
connection.Clone();
}
if (!externalConnection)
{
connection.Dispose();
}
}
}
public static async Task <OperationResponse> DestroyAsync <T>(Param[] parameters, string connectionName = null, bool captureException = false, string schema = null, DbConnection connection = null, IConfiguration config = null)
where T : class
{
config ??= ConfigurationFactory.Get <T>();
var request = new OperationRequest {
Parameters = parameters, ReturnType = OperationReturnType.NonQuery, ConnectionName = connectionName, Connection = connection, CaptureException = captureException, Configuration = config
};
if (config.GenerateDeleteSql)
{
request.Operation = SqlBuilder.GetDeleteStatement(typeof(T), parameters, request.Connection != null ? DialectFactory.GetProvider(request.Connection) : DialectFactory.GetProvider(request.ConnectionName ?? config.DefaultConnectionName, config));
request.OperationType = OperationType.Sql;
}
else
{
request.Operation = OperationDestroy;
request.OperationType = OperationType.StoredProcedure;
request.SchemaName = schema;
}
var response = await ExecuteAsync <T>(request).ConfigureAwait(false);
return(response);
}
public static async Task <OperationResponse> DeleteAsync <T>(Param[] parameters, string connectionName = null, bool captureException = false, string schema = null, DbConnection connection = null, IConfiguration config = null)
where T : class
{
config ??= ConfigurationFactory.Get <T>();
var request = new OperationRequest {
Parameters = parameters, ReturnType = OperationReturnType.NonQuery, ConnectionName = connectionName, Connection = connection, CaptureException = captureException, Configuration = config
};
if (config.GenerateDeleteSql)
{
string softDeleteColumn = null;
var map = MappingFactory.GetEntityMap <T>();
if (map != null)
{
softDeleteColumn = map.SoftDeleteColumnName;
}
if (softDeleteColumn == null)
{
var attr = Reflector.GetAttribute <T, TableAttribute>();
if (attr != null)
{
softDeleteColumn = attr.SoftDeleteColumn;
}
}
var partition = parameters.Partition(p => p.IsPrimaryKey);
// if p.IsPrimaryKey is not set then
// we need to infer it from reflected property
if (partition.Item1.Count == 0)
{
var propertyMap = Reflector.GetPropertyMap <T>();
var pimaryKeySet = propertyMap.Values.Where(p => p.IsPrimaryKey).ToDictionary(p => p.ParameterName ?? p.PropertyName, p => p.MappedColumnName);
partition = parameters.Partition(p =>
{
if (pimaryKeySet.TryGetValue(p.Name, out var column))
{
p.Source = column;
p.IsPrimaryKey = true;
return(true);
}
return(false);
});
}
request.Operation = SqlBuilder.GetDeleteStatement(typeof(T), partition.Item1, request.Connection != null ? DialectFactory.GetProvider(request.Connection) : DialectFactory.GetProvider(request.ConnectionName ?? config.DefaultConnectionName, config), softDeleteColumn);
request.OperationType = OperationType.Sql;
}
else
{
request.Operation = OperationDelete;
request.OperationType = OperationType.StoredProcedure;
request.SchemaName = schema;
}
var response = await ExecuteAsync <T>(request).ConfigureAwait(false);
return(response);
}
protected IDbDialectProvider GetProvider()
{
return(DialectFactory.Create(Schema));
}
/// <summary>
/// Creates the SQL query based on conditions using the current dialect.
/// Generally, you can call this method for debugging reasons.
/// </summary>
/// <param name="searchOptions">The search definitions.</param>
/// <param name="connection">The Connection.</param>
/// <param name="justCount">Indicates that this is a COUNT query.</param>
/// <param name="skip">Number of rows to skip when paging</param>
/// <param name="take">Number of rows to take when paging</param>
/// <param name="parameters">Outputs the generated parameters.</param>
/// <returns>A SQL query.</returns>
public static string GetSearchSql(SearchOptions searchOptions, bool justCount, int?skip, int?take, ConnectionStringSettings connection, out TenorParameter[] parameters)
{
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
if (searchOptions == null)
{
throw (new ArgumentNullException("searchOptions", "You must specify a SearchOptions instance."));
}
TableInfo table = TableInfo.CreateTableInfo(searchOptions.baseType);
if (connection == null)
{
connection = table.GetConnection();
}
//Read Projections
List <Projection> projections = new List <Projection>();
foreach (Projection p in searchOptions.Projections)
{
projections.Add(p);
}
//Read Joins
List <Join> joins = new List <Join>();
joins.AddRange(GetPlainJoins(searchOptions, dialect));
//Get necessary fields to create the select statement.
StringBuilder sqlFields = new StringBuilder();
List <FieldInfo> fieldInfos = new List <FieldInfo>();
List <SpecialFieldInfo> spFields = new List <SpecialFieldInfo>();
List <FieldInfo> lenFields = new List <FieldInfo>();
FieldInfo[] allFields;
if (justCount)
{
allFields = EntityBase.GetFields(searchOptions.baseType, true); //lets count using distinct subquery
}
else if (searchOptions.Projections.Count > 0)
{
allFields = ReadProjections <FieldInfo>(projections, null, EntityBase.GetFields(searchOptions.baseType));
}
else
{
allFields = EntityBase.GetFields(searchOptions.baseType); //lets get all fields
}
foreach (FieldInfo f in allFields)
{
if (f.PrimaryKey || (!f.LazyLoading && !justCount)) //primary keys and eager fields must be loaded
{
fieldInfos.Add(f);
}
else if (!justCount && f.LazyLoading && f.FieldType.IsAssignableFrom(typeof(BinaryStream)))
{
lenFields.Add(f);
}
// when paging, at least one sorting criterion is needed
if (skip.HasValue && take.HasValue && searchOptions.Sorting.Count == 0 && f.PrimaryKey)
{
searchOptions.Sorting.Add(f.RelatedProperty.Name);
}
}
if (!justCount) //we don't need it on counting
{
SpecialFieldInfo[] fields = EntityBase.GetSpecialFields(searchOptions.baseType);
if (searchOptions.Projections.Count > 0)
{
fields = ReadProjections <SpecialFieldInfo>(projections, null, fields);
}
spFields.AddRange(fields);
}
sqlFields.Append(dialect.CreateSelectSql(table.RelatedTable, null, fieldInfos.ToArray(), spFields.ToArray(), lenFields.ToArray()));
//adding values from eager loading types
foreach (ForeignKeyInfo fkInfo in searchOptions.eagerLoading.Keys)
{
fieldInfos = new List <FieldInfo>();
spFields = new List <SpecialFieldInfo>();
lenFields = new List <FieldInfo>();
//select all fields, or only the projection.
FieldInfo[] allEagerFields = EntityBase.GetFields(fkInfo.ElementType);
if (searchOptions.Projections.Count > 0)
{
allEagerFields = ReadProjections <FieldInfo>(projections, fkInfo.RelatedProperty.Name, allEagerFields);
}
foreach (FieldInfo f in allEagerFields)
{
if (f.PrimaryKey || !f.LazyLoading)
{
fieldInfos.Add(f);
}
else if (f.LazyLoading && f.FieldType.IsAssignableFrom(typeof(BinaryStream)))
{
lenFields.Add(f);
}
}
//spfields
SpecialFieldInfo[] allSpFields = EntityBase.GetSpecialFields(fkInfo.ElementType);
if (searchOptions.Projections.Count > 0)
{
allSpFields = ReadProjections <SpecialFieldInfo>(projections, fkInfo.RelatedProperty.Name, allSpFields);
}
spFields.AddRange(allSpFields);
//joins: joins was made on GetPlainJoins.
sqlFields.Append(", ");
sqlFields.Append(dialect.CreateSelectSql(fkInfo.ElementType, searchOptions.eagerLoading[fkInfo], fieldInfos.ToArray(), spFields.ToArray(), lenFields.ToArray()));
}
//Sorting (not necessary for count queries)
string sqlSort = string.Empty;
if (!justCount)
{
string appendToSelect = null;
sqlSort = dialect.CreateSortSql(searchOptions.Sorting, table.RelatedTable, joins.ToArray(), searchOptions.Distinct, skip.HasValue && take.HasValue, out appendToSelect);
if (!string.IsNullOrEmpty(appendToSelect))
{
sqlFields.Append(appendToSelect);
}
}
//Check if we found all projections:
if (projections.Count > 0)
{
throw new InvalidProjectionException(projections[0]);
}
//Creates the where part
string sqlWhere = dialect.CreateWhereSql(searchOptions.Conditions, searchOptions.baseType, joins.ToArray(), out parameters);
// Creates the join parts
string sqlJoins = dialect.CreateJoinsSql(joins.ToArray());
// Creates the entire sql string
string sql = dialect.CreateFullSql(searchOptions.baseType, searchOptions.Distinct, justCount, searchOptions.Top, skip, take, sqlFields.ToString(), sqlJoins, sqlSort, sqlWhere);
Tenor.Diagnostics.Debug.DebugSQL("GetSearchSql()", sql, parameters, connection);
#if DEBUG
LastSearches.Push(sql);
#endif
return(sql);
}
/// <summary>
/// Gets the value of a lazy tagged property.
/// This call can do a database access.
/// </summary>
internal virtual object GetPropertyValue(string propertyName, bool forceGetBinary)
{
//only loads if not loaded yet.
//TODO: provide a way to reload from database.
lock (propertyData)
{
if (forceGetBinary || !propertyData.ContainsKey(propertyName))
{
//Getting class metadata.
TableInfo table = TableInfo.CreateTableInfo(this.GetType());
System.Reflection.PropertyInfo fieldP = this.GetType().GetProperty(propertyName);
if (fieldP == null)
{
throw new Tenor.Data.MissingFieldException(this.GetType(), propertyName);
}
ForeignKeyInfo fkInfo = null;
fkInfo = ForeignKeyInfo.Create(fieldP);
if (fkInfo != null)
{
// this is an FK, so, route to fk loading
return(LoadForeign(fieldP, null));
}
else
{
//Continue to the lazy property (lazy fields like byte[]) loading
FieldInfo field = FieldInfo.Create(fieldP);
if (field == null)
{
throw new Tenor.Data.MissingFieldException(this.GetType(), fieldP.Name);
}
if (!forceGetBinary && (field.FieldType == typeof(BinaryStream) || field.FieldType == typeof(BinaryStream).BaseType))
{
propertyData[propertyName] = new BinaryStream(this, propertyName);
}
else
{
GeneralDialect dialect = DialectFactory.CreateDialect(table.GetConnection());
ConditionCollection conditions = new ConditionCollection();
foreach (FieldInfo f in GetFields(this.GetType(), true))
{
conditions.Add(f.RelatedProperty.Name, f.PropertyValue(this));
}
if (conditions.Count == 0)
{
throw (new Tenor.Data.MissingPrimaryKeyException(this.GetType()));
}
TenorParameter[] parameters = null;
string fieldsPart = dialect.CreateSelectSql(table.RelatedTable, null, new FieldInfo[] { field }, null, null);
string wherePart = dialect.CreateWhereSql(conditions, table.RelatedTable, null, out parameters);
string sql = dialect.CreateFullSql(table.RelatedTable,
false, false,
0, fieldsPart, null, null, wherePart);
Tenor.Diagnostics.Debug.DebugSQL("GetPropertyValue()", sql, parameters, table.GetConnection());
#if DEBUG
LastSearches.Push(sql);
#endif
Tenor.Data.DataTable rs = new Tenor.Data.DataTable(sql, parameters, table.GetConnection());
rs.Bind();
if (rs.Rows.Count == 0)
{
throw (new RecordNotFoundException());
}
else if (rs.Rows.Count > 1)
{
throw new ManyRecordsFoundException();
}
else
{
var obj = rs.Rows[0][field.DataFieldName];
if (obj == DBNull.Value)
{
obj = null;
}
if (forceGetBinary)
{
if (obj == null)
{
return new byte[] { }
}
;
else
{
return(obj);
}
}
else
{
propertyData[propertyName] = obj;
}
}
}
}
}
return(propertyData[propertyName]);
}
}
internal static void OpenConnection(DbConnection connection)
{
OpenConnection(connection, DialectFactory.CreateDialect(connection));
}
internal static string GetParameterPrefix(ConnectionStringSettings connection)
{
return(GetParameterPrefix(DialectFactory.CreateDialect(connection).Factory));
}
/// <summary>
/// Executes a query on the database and them returns a datatable.
/// </summary>
/// <param name="connectionString">The connection parameters.</param>
/// <param name="sqlSelect">A SQL query to execute.</param>
/// <param name="parameters">Parameters collection.</param>
/// <returns>A <see cref="System.Data.DataTable">DataTable</see> with results of the query.</returns>
public static DataTable QueryData(ConnectionStringSettings connectionString, string sqlSelect, params TenorParameter[] parameters)
{
if (connectionString == null)
{
throw new ArgumentNullException("connectionString");
}
GeneralDialect dialect = DialectFactory.CreateDialect(connectionString);
DataTable dtRetorno = null;
DbConnection conn = CreateConnection(dialect, connectionString);
DbCommand cmd = null;
DbDataReader reader;
try
{
if (System.Diagnostics.Debugger.IsAttached)
{
Tenor.Diagnostics.Debug.DebugSQL("Helper: ConsultarBanco()", sqlSelect, parameters, connectionString);
}
}
catch (Exception ex)
{
Diagnostics.Debug.HandleError(ex);
}
try
{
cmd = conn.CreateCommand();
cmd.CommandText = sqlSelect;
cmd.CommandTimeout = Helper.DefaultTimeout;
if (parameters != null)
{
foreach (TenorParameter param in parameters)
{
cmd.Parameters.Add(param.ToDbParameter(dialect.Factory));
}
}
OpenConnection(conn, dialect);
reader = cmd.ExecuteReader();
if (reader != null)
{
dtRetorno = new DataTable();
dtRetorno.Load(reader);
}
}
catch (Exception up)
{
if (cmd != null && !up.Data.Contains("CommandText"))
{
up.Data.Add("CommandText", cmd.CommandText);
}
throw (up);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
return(dtRetorno);
}
private static ISqlDialect GetDialect(string providerName, string connectionString)
{
var dialectFactory = new DialectFactory();
return(dialectFactory.Create(providerName, connectionString));
}
/// <summary>
/// Creates an update query of this entity data.
/// </summary>
/// <param name="isUpdate">Determines whether to update an existing record or create a new record.</param>
/// <param name="parameters">Outputs an array of TenorParameter with required parameters.</param>
/// <param name="autoKeyField">Outputs the autonumber FieldInfo.</param>
/// <param name="specialValues">The special values can contains sql sentences/sequences/etc</param>
/// <param name="connection">The Connection.</param>
/// <param name="dialect"></param>
/// <returns>Return a SQL query string.</returns>
internal string GetSaveSql(bool isUpdate, ConnectionStringSettings connection, System.Collections.Specialized.NameValueCollection specialValues, out FieldInfo autoKeyField, out TenorParameter[] parameters, out GeneralDialect dialect)
{
Dictionary <FieldInfo, object> data = new Dictionary <FieldInfo, object>();
TableInfo table = TableInfo.CreateTableInfo(this.GetType());
if (connection == null)
{
connection = table.GetConnection();
}
dialect = DialectFactory.CreateDialect(connection);
autoKeyField = null;
ConditionCollection conditions = new ConditionCollection();
List <FieldInfo> fieldInfos = new List <FieldInfo>(GetFields(this.GetType()));
for (int i = fieldInfos.Count - 1; i >= 0; i--)
{
if (isUpdate && fieldInfos[i].PrimaryKey)
{
if (conditions.Count != 0)
{
conditions.Add(LogicalOperator.And);
}
conditions.Add(fieldInfos[i].RelatedProperty.Name, fieldInfos[i].PropertyValue(this));
if (fieldInfos[i].AutoNumber)
{
autoKeyField = fieldInfos[i];
data.Add(fieldInfos[i], null);
}
else
{
fieldInfos.RemoveAt(i);
}
}
else if (fieldInfos[i].AutoNumber)
{
autoKeyField = fieldInfos[i];
data.Add(fieldInfos[i], null);
}
else if (fieldInfos[i].LazyLoading && !propertyData.ContainsKey(fieldInfos[i].RelatedProperty.Name))
{
fieldInfos.RemoveAt(i);
}
else
{
var lazyValue = fieldInfos[i].PropertyValue(this);
if (lazyValue != null && lazyValue.GetType() == typeof(BinaryStream))
{
//binary stream will never change
fieldInfos.RemoveAt(i);
}
else
{
data.Add(fieldInfos[i], lazyValue);
}
}
}
string sql = dialect.CreateSaveSql(this.GetType(), data, specialValues, conditions, out parameters) + dialect.LineEnding;
if (dialect.GetIdentityOnSameCommand && !isUpdate && autoKeyField != null)
{
string queryFormat = @"{0}
{1}
{2}";
string before = string.Empty;
if (!string.IsNullOrEmpty(dialect.IdentityBeforeQuery))
{
before = dialect.IdentityBeforeQuery + dialect.LineEnding;
}
string after = string.Empty;
if (!string.IsNullOrEmpty(dialect.IdentityAfterQuery))
{
after = string.Format(dialect.IdentityAfterQuery, autoKeyField.InsertSQL) + dialect.LineEnding;
}
sql = string.Format(queryFormat, before, sql, after);
}
return(sql);
}
private static string GenerateMigrationScript(
ConnectionStringSettings connectionStringSettings,
ReverseEngineerSettings reverseEngineerSettings,
IConfiguration configuration,
bool naive,
out IEnumerable <string> warnings,
out IEnumerable <string> errors)
{
// fetch the from state
var dialectFactory = new DialectFactory();
var dialect = dialectFactory.Create(connectionStringSettings.ToSystem());
var factory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
// create database if not exists
CreateDatabaseIfNotExists(connectionStringSettings, factory, dialect);
DatabaseSchema schema;
using (new TimedOperation("-- Reading database contents...")) {
var databaseReader = new DatabaseReader(connectionStringSettings.ConnectionString, connectionStringSettings.ProviderName);
schema = databaseReader.ReadAll();
}
IEnumerable <IMap> fromMaps;
using (new TimedOperation("-- Reverse engineering...")) {
Console.WriteLine();
var engineer = new Engineer(reverseEngineerSettings.ExtraPluralizationWords);
fromMaps = engineer.ReverseEngineer(schema, dialect, reverseEngineerSettings.GetTablesToIgnore(), consoleAnswerProvider, false);
Console.Write("-- ");
}
using (var connection = factory.CreateConnection()) {
connection.ConnectionString = connectionStringSettings.ConnectionString;
// set up migrator
IMigrator migrator;
if (naive)
{
throw new NotSupportedException("The Naive Migrator is no longer supported");
}
migrator = new Migrator(
dialect,
new CreateTableWriter(dialect),
new AlterTableWriter(dialect),
new DropTableWriter(dialect),
new StatisticsProvider(connection, dialect));
// run the migrator
string script;
using (new TimedOperation("-- Generating diff...")) {
script = migrator.GenerateSqlDiff(
fromMaps,
configuration.Maps,
consoleAnswerProvider,
new ConsoleLogger(isVerbose),
reverseEngineerSettings.GetIndexesToIgnore(),
out warnings,
out errors);
// TODO: do things with warnings and errors
return(script);
}
}
}
/// <summary>
/// Persists a list on the database.
/// </summary>
/// <param name="propertyName">The name of a many-to-many property on this class.</param>
public virtual void SaveList(string propertyName)
{
if (propertyName == null)
{
throw new ArgumentNullException("propertyName");
}
System.Reflection.PropertyInfo prop = this.GetType().GetProperty(propertyName);
if (prop == null)
{
throw new ArgumentException(string.Format("The property '{0}' was not found on '{1}'.", propertyName, this.GetType().FullName), "propertyName");
}
ForeignKeyInfo fkInfo = ForeignKeyInfo.Create(prop);
if (fkInfo == null)
{
throw new InvalidMappingException(this.GetType());
}
if (!fkInfo.IsManyToMany)
{
throw new TenorException("Currently, only many-to-many relations are supported");
}
TableInfo table = TableInfo.CreateTableInfo(this.GetType());
if (table == null)
{
throw new InvalidMappingException(this.GetType());
}
ConnectionStringSettings connection = (tenorTransaction == null ? table.GetConnection() : tenorTransaction.Connection);
GeneralDialect dialect = DialectFactory.CreateDialect(connection);
TenorParameter[] parameters;
DbTransaction t = (tenorTransaction == null ? null : tenorTransaction.dbTransaction);
//if (dialect.GetType() == typeof(Tenor.Data.Dialects.TSql.TSql))
//{
// //oh god! do you have a better idea on where to write this code?
// System.Data.SqlClient.SqlBulkCopy bulk;
// if (t == null)
// bulk = new System.Data.SqlClient.SqlBulkCopy(tenorTransaction.Connection.ConnectionString);
// else
// bulk = new System.Data.SqlClient.SqlBulkCopy((System.Data.SqlClient.SqlConnection)t.Connection, System.Data.SqlClient.SqlBulkCopyOptions.Default, (System.Data.SqlClient.SqlTransaction)t);
// bulk.DestinationTableName = dialect.GetPrefixAndTable(fkInfo.ManyToManyTablePrefix, fkInfo.ManyToManyTable);
// System.Data.DataTable data;
// string sql = dialect.CreateSaveList(table, fkInfo, this, out parameters, out data);
// foreach (DataColumn col in data.Columns)
// {
// bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
// }
// Helper.ExecuteQuery(sql, parameters, t, dialect);
// bulk.WriteToServer(data);
// bulk.Close();
//}
//else
//{
string sql = dialect.CreateSaveListSql(table, fkInfo, this, out parameters);
Helper.ExecuteQuery(sql, parameters, t, dialect);
//}
}
private static Tuple <string, Param[], List <object> > GetCommitStatement(ChangeNode rootNode, DbConnection connection)
{
var dialect = DialectFactory.GetProvider(connection);
var dataEntities = new List <object>();
var sql = new StringBuilder();
var statementId = 0;
// Inserts
var newNodes = GetChanges(rootNode, ObjectState.New);
var tableCreated = false;
var tempTableName = dialect.GetTemporaryTableName("ID");
var allParameters = new List <Param>();
foreach (var newNode in newNodes.Where(n => n.IsObject))
{
var item = newNode.Value;
if (item != null)
{
dataEntities.Add(item);
}
else
{
continue;
}
var objectType = newNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType);
var autoGenProperty = propertyMap.Where(p => p.Key.CanWrite && p.Value != null && p.Value.IsAutoGenerated).Select(p => p.Key).FirstOrDefault();
var autoGenType = DbType.String;
if (!tableCreated)
{
if (autoGenProperty != null)
{
autoGenType = Reflector.ClrToDbType(autoGenProperty.PropertyType);
}
sql.AppendFormat(dialect.CreateTemporaryTable("ID", new Dictionary <string, DbType> {
{ "StatementId", DbType.Int32 }, { "GeneratedId", autoGenType }, { "ParameterName", DbType.AnsiString }, { "PropertyName", DbType.AnsiString }
})).AppendLine();
tableCreated = true;
}
var parameters = ObjectExtensions.GetInsertParameters(newNode.Value, propertyMap, statementId);
allParameters.AddRange(parameters);
if (parameters.Length > 0)
{
string commandName = SqlBuilder.GetInsertStatement(objectType, parameters, dialect);
var autoGenParameter = parameters.FirstOrDefault(p => p.IsAutoGenerated);
if (autoGenParameter != null)
{
sql.AppendFormat(dialect.DeclareVariable(autoGenParameter.Name, autoGenType)).AppendLine();
sql.AppendFormat(dialect.AssignVariable(autoGenParameter.Name, autoGenParameter.Type.GetDefault())).AppendLine();
}
sql.AppendLine(commandName);
if (autoGenParameter != null)
{
sql.AppendFormat(dialect.ComputeAutoIncrement(autoGenParameter.Name, () => SqlBuilder.GetTableNameForSql(objectType, dialect))).AppendLine();
sql.AppendFormat("INSERT INTO " + tempTableName + " ({4}StatementId{5}, {4}GeneratedId{5}, {4}ParameterName{5}, {4}PropertyName{5}) VALUES ({0}, {1}, '{2}', '{3}')", statementId, dialect.EvaluateVariable(dialect.ParameterPrefix + autoGenParameter.Name), autoGenParameter.Name, autoGenProperty.Name, dialect.IdentifierEscapeStartCharacter, dialect.IdentifierEscapeEndCharacter).AppendLine();
}
statementId++;
}
}
if (newNodes.Count > 0)
{
sql.AppendLine("SELECT * FROM " + tempTableName);
}
if (tableCreated && !dialect.SupportsTemporaryTables)
{
sql.AppendLine("DROP TABLE " + tempTableName);
}
// Updates
var dirtyNodes = GetChanges(rootNode, ObjectState.Dirty);
var dirtyNodeParents = dirtyNodes.Where(n => n.IsSimpleLeaf).Select(n => n.Parent).Distinct();
foreach (var dirtyNode in dirtyNodeParents)
{
var objectType = dirtyNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType).ToDictionary(p => p.Key.Name, p => p);
var parameters = new List <Param>();
foreach (var change in dirtyNode.Nodes.Where(n => n.IsSimpleLeaf))
{
KeyValuePair <PropertyInfo, ReflectedProperty> map;
if (propertyMap.TryGetValue(change.PropertyName, out map))
{
var property = map.Key;
var parameterName = change.PropertyName;
if (map.Value != null && !string.IsNullOrEmpty(map.Value.ParameterName))
{
parameterName = map.Value.ParameterName;
}
parameters.Add(new Param {
Name = parameterName + "_" + statementId, Value = change.Value, Source = MapColumnAttribute.GetMappedColumnName(property)
});
}
}
var primaryKey = new List <Param>();
foreach (var primaryKeyMap in propertyMap.Values.Where(p => p.Value.IsPrimaryKey))
{
var value = dirtyNode.Value.Property(primaryKeyMap.Key.Name);
var parameterName = primaryKeyMap.Key.Name;
if (primaryKeyMap.Value != null && !string.IsNullOrEmpty(primaryKeyMap.Value.ParameterName))
{
parameterName = primaryKeyMap.Value.ParameterName;
}
primaryKey.Add(new Param {
Name = parameterName + "_" + statementId, Value = value, Source = MapColumnAttribute.GetMappedColumnName(primaryKeyMap.Key)
});
}
var commandName = SqlBuilder.GetUpdateStatement(objectType, parameters, primaryKey, dialect);
allParameters.AddRange(parameters);
allParameters.AddRange(primaryKey);
sql.Append(commandName).AppendLine();
statementId++;
}
// Deletes
var deletedNodes = GetChanges(rootNode, ObjectState.Deleted);
foreach (var deletedNode in deletedNodes)
{
var objectType = deletedNode.Value.GetType();
var propertyMap = Reflector.GetPropertyMap(objectType);
var parameters = ObjectExtensions.GetDeleteParameters(deletedNode.Value, propertyMap, statementId);
var commandName = SqlBuilder.GetDeleteStatement(objectType, parameters, dialect);
allParameters.AddRange(parameters);
sql.Append(commandName).AppendLine();
statementId++;
}
return(Tuple.Create(sql.ToString(), allParameters.ToArray(), dataEntities));
}
