public void TestSchemaLoaderCacheWithLazyLoading() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); List <ISqlBulkHelpersDBSchemaLoader> schemaLoadersList = new List <ISqlBulkHelpersDBSchemaLoader> { SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlConnectionProvider), SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlConnectionProvider), SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlConnectionProvider) }; Assert.IsNotNull(schemaLoadersList[0]); Assert.IsNotNull(schemaLoadersList[1]); Assert.IsNotNull(schemaLoadersList[2]); Assert.AreEqual(schemaLoadersList[0], schemaLoadersList[1]); Assert.AreEqual(schemaLoadersList[1], schemaLoadersList[2]); Assert.AreEqual(schemaLoadersList[0], schemaLoadersList[2]); //NOTE: We can't actually test this since it's Cached and the TestFramework may have already // initialized the Schema Definitions for the connection from other tests! //Assert.IsFalse(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[0]).IsInitialized); schemaLoadersList[1].InitializeSchemaDefinitions(); Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[0]).IsInitialized); Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[1]).IsInitialized); Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[2]).IsInitialized); }
public async Task TestSchemaLoaderCacheWithExistingConnectionAndImmediateLoadingAsync() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); List <ISqlBulkHelpersDBSchemaLoader> schemaLoadersList = new List <ISqlBulkHelpersDBSchemaLoader>(); using (var conn = await sqlConnectionProvider.NewConnectionAsync()) { schemaLoadersList.Add(SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(conn)); } using (var conn = await sqlConnectionProvider.NewConnectionAsync()) { schemaLoadersList.Add(SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(conn)); } using (var conn = await sqlConnectionProvider.NewConnectionAsync()) { schemaLoadersList.Add(SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(conn)); } Assert.IsNotNull(schemaLoadersList[0]); Assert.IsNotNull(schemaLoadersList[1]); Assert.IsNotNull(schemaLoadersList[2]); Assert.AreEqual(schemaLoadersList[0], schemaLoadersList[1]); Assert.AreEqual(schemaLoadersList[1], schemaLoadersList[2]); Assert.AreEqual(schemaLoadersList[0], schemaLoadersList[2]); //ALL should already be initialized since used existing connections to construct them! Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[0]).IsInitialized); Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[1]).IsInitialized); Assert.IsTrue(((SqlBulkHelpersDBSchemaStaticLoader)schemaLoadersList[2]).IsInitialized); }
public async Task TestBulkInsertConstructorWithExistingConnectionAndTransactionAsync() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); using (var conn = await sqlConnectionProvider.NewConnectionAsync()) using (SqlTransaction transaction = conn.BeginTransaction()) { ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(conn, transaction); await DoInsertOrUpdateTestAsync(sqlBulkIdentityHelper, transaction); } }
public async Task TestBulkInsertConstructorWithDBSchemaLoaderInstanceDeferred() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); var sqlBulkDbSchemaLoader = SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlConnectionProvider); using (var conn = await sqlConnectionProvider.NewConnectionAsync()) using (SqlTransaction transaction = conn.BeginTransaction()) { ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(sqlBulkDbSchemaLoader); await DoInsertOrUpdateTestAsync(sqlBulkIdentityHelper, transaction); } }
/// <summary> /// This is the preferred way to initialize the Schema Loader. This will retrieve a DB Schema Loader using the /// SqlConnection Provider specified. With an Sql Connection Provider, we can defer (e.g. lazy load) /// the loading of the Schema Definitions until it's actually needed. This may speed up startup time if /// this initialization is part of a static element at startup and/or if it is never needed based on execution logic. /// </summary> /// <param name="sqlConnectionProvider"></param> /// <returns></returns> public static ISqlBulkHelpersDBSchemaLoader GetSchemaLoader(ISqlBulkHelpersConnectionProvider sqlConnectionProvider) { //Validate arg is a Static Schema Loader... var sqlConnProvider = sqlConnectionProvider.AssertArgumentIsNotNull(nameof(sqlConnectionProvider)); //Init cached version if it exists; which may already be initialized! var resultLoader = SchemaLoaderLazyCache.GetOrAdd( sqlConnProvider.GetDbConnectionUniqueIdentifier(), new Lazy <SqlBulkHelpersDBSchemaStaticLoader>(() => new SqlBulkHelpersDBSchemaStaticLoader(sqlConnectionProvider)) ); //Unwrap the Lazy<> to get, or construct, a valid Schema Loader... return(resultLoader.Value); }
public async Task TestBulkInsertConstructorWithDBSchemaLoaderInstanceFromExistingConnectionAndTransaction() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); using (var conn = await sqlConnectionProvider.NewConnectionAsync()) using (SqlTransaction transaction = conn.BeginTransaction()) { //TEST the code flow where the DB Schema Loader is initialized from existing //Connection + Transaction and immediately initialized var sqlBulkDbSchemaLoader = SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(conn, transaction, true); ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(sqlBulkDbSchemaLoader); await DoInsertOrUpdateTestAsync(sqlBulkIdentityHelper, transaction); } }
public async Task TestBulkInsertConstructorWithExistingConnectionOnlyAsync() { ISqlBulkHelpersConnectionProvider sqlConnectionProvider = SqlConnectionHelper.GetConnectionProvider(); using (var conn = await sqlConnectionProvider.NewConnectionAsync()) { //NOTE: IN THIS CASE we must initialize BEFORE the transaction is created or an error may occur // when initializing the DB Schema Definitions because we are intentionally not passing // in the Transaction to test this code flow. ISqlBulkHelper <TestElement> sqlBulkIdentityHelper = new SqlBulkIdentityHelper <TestElement>(conn); using (SqlTransaction transaction = conn.BeginTransaction()) { await DoInsertOrUpdateTestAsync(sqlBulkIdentityHelper, transaction); } } }
public SqlBulkHelpersDBSchemaStaticLoader(ISqlBulkHelpersConnectionProvider sqlConnectionProvider) { sqlConnectionProvider.AssertArgumentIsNotNull(nameof(sqlConnectionProvider)); //Safely initialize the Lazy<> loader for Table Definition Schemas. //NOTE: We use a Lazy<> here so that our manual locking does as little work as possible and simply initializes the Lazy<> reference, // leaving the optimized locking for execution of the long-running logic to the underlying Lazy<> object to manage with // maximum efficiency _tableDefinitionsLookupLazy = new Lazy <ILookup <string, SqlBulkHelpersTableDefinition> >(() => { //Get a local reference so that it's scoping will be preserved... var localScopeSqlConnectionProviderRef = sqlConnectionProvider; var dbSchemaResults = LoadSqlBulkHelpersDBSchemaHelper(localScopeSqlConnectionProviderRef); //Set the initialization flag (mainly for reference/debugging) IsInitialized = true; return(dbSchemaResults); }); }
public SqlBulkHelpersDBSchemaStaticLoader(ISqlBulkHelpersConnectionProvider sqlConnectionProvider) { sqlConnectionProvider.AssertArgumentNotNull(nameof(sqlConnectionProvider)); //Lock the padlock to safely initialize the Lazy<> loader for Table Definition Schemas, but only if it hasn't yet been initialized! //NOTE: We use a Lazy<> here so that our manual locking does as little work as possible and simply initializes the Lazy<> reference, // leaving the optimized locking for execution of the long-running logic to the underlying Lazy<> object to manage with // maximum efficiency //NOTE: Once initialized we will only have a null check before the lock can be released making this completely safe but still very lightweight. lock (_padlock) { if (_tableDefinitionsLookupLazy != null) { _tableDefinitionsLookupLazy = new Lazy <ILookup <string, SqlBulkHelpersTableDefinition> >(() => { //Get a local reference so that it's scoping will be preserved... var localScopeSqlConnectionProviderRef = sqlConnectionProvider; var dbSchemaResults = LoadSqlBulkHelpersDBSchemaHelper(localScopeSqlConnectionProviderRef); return(dbSchemaResults); }); } } }
/// <inheritdoc/> public SqlBulkIdentityHelper(ISqlBulkHelpersConnectionProvider sqlBulkHelpersConnectionProvider, int timeoutSeconds = DefaultBulkOperationTimeoutSeconds) : base(sqlBulkHelpersConnectionProvider, timeoutSeconds) { }
/// <summary> /// BBernard /// Add all table and their columns from the database into the dictionary in a fully Thread Safe manner using /// the Static Constructor! /// </summary> private ILookup <string, SqlBulkHelpersTableDefinition> LoadSqlBulkHelpersDBSchemaHelper(ISqlBulkHelpersConnectionProvider sqlConnectionProvider) { var tableSchemaSql = @" SELECT [TABLE_SCHEMA] as TableSchema, [TABLE_NAME] as TableName, [Columns] = ( SELECT COLUMN_NAME as ColumnName, ORDINAL_POSITION as OrdinalPosition, DATA_TYPE as DataType, COLUMNPROPERTY(OBJECT_ID(table_schema+'.'+table_name), COLUMN_NAME, 'IsIdentity') as IsIdentityColumn FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = t.TABLE_NAME and c.TABLE_SCHEMA = t.TABLE_SCHEMA and c.TABLE_CATALOG = t.TABLE_CATALOG ORDER BY c.ORDINAL_POSITION FOR JSON PATH ) FROM INFORMATION_SCHEMA.TABLES t ORDER BY t.TABLE_NAME FOR JSON PATH " ; SqlConnection sqlConn = sqlConnectionProvider.NewConnection(); //Determine if our connection provider created a new Connection or if it is proxy-ing for an Existing Sql Connection. //NOTE: If we are proxy-ing an existing Connection then we also need to handle a potentially associated Transaction. bool isNewConnectionInitialized = true; SqlTransaction sqlTransaction = null; if (sqlConnectionProvider is SqlBulkHelpersConnectionProxyExistingProvider sqlConnProxyProvider) { isNewConnectionInitialized = false; sqlTransaction = sqlConnProxyProvider.GetTransaction(); } try { using (SqlCommand sqlCmd = new SqlCommand(tableSchemaSql, sqlConn, sqlTransaction)) { var tableDefinitionsList = sqlCmd.ExecuteForJson <List <SqlBulkHelpersTableDefinition> >(); //Dynamically convert to a Lookup for immutable cache of data. //NOTE: Lookup is immutable (vs Dictionary which is not) and performance for lookups is just as fast. var tableDefinitionsLookup = tableDefinitionsList.Where(t => t != null).ToLookup( t => $"[{t.TableSchema.ToLowerInvariant()}].[{t.TableName.ToLowerInvariant()}]" ); return(tableDefinitionsLookup); } } finally { //Cleanup the Sql Connection IF it was newly created it... if (isNewConnectionInitialized && sqlConn != null) { sqlConn.Close(); sqlConn.Dispose(); } } }
/// <summary> /// Constructor that support passing in an SqlConnection Provider which will enable deferred (lazy) initialization of the /// Sql DB Schema Loader and Schema Definitions internally. the Sql DB Schema Loader will be resolved internally using /// the SqlBulkHelpersSchemaLoaderCache manager for performance. /// NOTE: With this overload the resolve ISqlBulkHelpersDBSchemaLoader will be resolved for this unique Connection, /// as an internally managed cached resource for performance. /// </summary> /// <param name="sqlBulkHelpersConnectionProvider"></param> /// <param name="timeoutSeconds"></param> protected BaseSqlBulkHelper(ISqlBulkHelpersConnectionProvider sqlBulkHelpersConnectionProvider, int timeoutSeconds = DefaultBulkOperationTimeoutSeconds) { this.SqlDbSchemaLoader = SqlBulkHelpersSchemaLoaderCache.GetSchemaLoader(sqlBulkHelpersConnectionProvider); this.BulkOperationTimeoutSeconds = timeoutSeconds; }
/// <inheritdoc/> public SqlBulkNaturalKeyHelper(ISqlBulkHelpersConnectionProvider sqlBulkHelpersConnectionProvider, int timeoutSeconds = DefaultBulkOperationTimeoutSeconds) : base(sqlBulkHelpersConnectionProvider, timeoutSeconds) { throw new NotImplementedException(NOT_IMPLEMENTED_MESSAGE); }
/// <summary> /// BBernard /// Add all table and their columns from the database into the dictionary in a fully Thread Safe manner using /// the Static Constructor! /// </summary> private ILookup <String, SqlBulkHelpersTableDefinition> LoadSqlBulkHelpersDBSchemaHelper(ISqlBulkHelpersConnectionProvider sqlConnectionProvider) { var tableSchemaSql = @" SELECT [TABLE_SCHEMA] as TableSchema, [TABLE_NAME] as TableName, [Columns] = ( SELECT COLUMN_NAME as ColumnName, ORDINAL_POSITION as OrdinalPosition, DATA_TYPE as DataType, COLUMNPROPERTY(OBJECT_ID(table_schema+'.'+table_name), COLUMN_NAME, 'IsIdentity') as IsIdentityColumn FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME = t.TABLE_NAME and c.TABLE_SCHEMA = t.TABLE_SCHEMA and c.TABLE_CATALOG = t.TABLE_CATALOG ORDER BY c.ORDINAL_POSITION FOR JSON PATH ) FROM INFORMATION_SCHEMA.TABLES t ORDER BY t.TABLE_NAME FOR JSON PATH "; using (SqlConnection sqlConn = sqlConnectionProvider.NewConnection()) using (SqlCommand sqlCmd = new SqlCommand(tableSchemaSql, sqlConn)) { var tableDefinitionsList = sqlCmd.ExecuteForJson <List <SqlBulkHelpersTableDefinition> >(); //Dynamically convert to a Lookup for immutable cache of data. //NOTE: Lookup is immutable (vs Dictionary which is not) and performance for lookups is just as fast. var tableDefinitionsLookup = tableDefinitionsList.Where(t => t != null).ToLookup(t => t.TableName.ToLowerInvariant()); return(tableDefinitionsLookup); } }