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