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