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