mssql helpers

MsSqlHelpers

MsSqlHelpers ist eine Bibliothek zur Verbesserung allgemeiner Entwicklungsaufgaben von MS SQL Server, wie der Generierung parametrisierter Masseneinfügungen zur Verwendung mit ADO.NET, Entity Framework und Dapper und mehr (in naher Zukunft).
Um IDENTITY INSERT ON zu ermöglichen, gibt es einen optionalen Parameter im Methodenaufruf (Standardmodus OFF).

ADO.NET-Nutzung:

 using MsSqlHelpers ;

.. .

var mapper = new MapperBuilder < Person > ( )
    . SetTableName ( "People" ) // could be ommited if your table's name is the same as you entity's class name
    . AddMapping ( person => person . FirstName , columnName : "Name" )
    . AddMapping ( person => person . LastName , columnName : "Surename" )
    . AddMapping ( person => person . DateOfBirth ) // in this case property's name is the same as table column's name
    . Build ( ) ;
var people = new List < Person > ( )
{ 
    new Person ( ) { FirstName = "John" , LastName = "Lennon" , DateOfBirth = new DateTime ( 1940 , 10 , 9 ) } ,
    new Person ( ) { FirstName = "Paul" , LastName = "McCartney" , DateOfBirth = new DateTime ( 1942 , 6 , 18 ) } ,
} ;
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;" ;
var sqlQueriesAndParameters = new MsSqlQueryGenerator ( ) . GenerateParametrizedBulkInserts ( mapper , people ) ;

using ( var sqlConnection = new SqlConnection ( connectionString ) )
{
    sqlConnection . Open ( ) ;
    
    // Default batch size: 1000 rows or (2100-1) parameters per insert.
    foreach ( var ( SqlQuery , SqlParameters ) in sqlQueriesAndParameters )
    {
        using ( SqlCommand sqlCommand = new SqlCommand ( SqlQuery , sqlConnection ) )
        {
            sqlCommand . Parameters . AddRange ( SqlParameters . ToArray ( ) ) ;
            sqlCommand . ExecuteNonQuery ( ) ;
        }
    }
}

Verwendung des Entity Frameworks:

 using MsSqlHelpers ;

.. .

var mapper = new MapperBuilder < Person > ( )
    . SetTableName ( "People" ) // could be ommited if your table's name is the same as you entity's class name
    . AddMapping ( person => person . FirstName , columnName : "Name" )
    . AddMapping ( person => person . LastName , columnName : "Surename" )
    . AddMapping ( person => person . DateOfBirth ) // in this case property's name is the same as table column's name
    . Build ( ) ;
var people = new List < Person > ( )
{ 
    new Person ( ) { FirstName = "John" , LastName = "Lennon" , DateOfBirth = new DateTime ( 1940 , 10 , 9 ) } ,
    new Person ( ) { FirstName = "Paul" , LastName = "McCartney" , DateOfBirth = new DateTime ( 1942 , 6 , 18 ) } ,
} ;
var sqlQueriesAndParameters = new MsSqlQueryGenerator ( ) . GenerateParametrizedBulkInserts ( mapper , people ) ;

// Default batch size: 1000 rows or (2100-1) parameters per insert.
foreach ( var ( SqlQuery , SqlParameters ) in sqlQueriesAndParameters )
{
    _context . Database . ExecuteSqlRaw ( SqlQuery , SqlParameters ) ;
    // Depracated but still works: _context.Database.ExecuteSqlCommand(SqlQuery, SqlParameters);
}

Dapper-Nutzung:

 using MsSqlHelpers ;

.. .

var mapper = new MapperBuilder < Person > ( )
    . SetTableName ( "People" ) // could be ommited if your table's name is the same as you entity's class name
    . AddMapping ( person => person . FirstName , columnName : "Name" )
    . AddMapping ( person => person . LastName , columnName : "Surename" )
    . AddMapping ( person => person . DateOfBirth ) // in this case property's name is the same as table column's name
    . Build ( ) ;
var people = new List < Person > ( )
{ 
    new Person ( ) { FirstName = "John" , LastName = "Lennon" , DateOfBirth = new DateTime ( 1940 , 10 , 9 ) } ,
    new Person ( ) { FirstName = "Paul" , LastName = "McCartney" , DateOfBirth = new DateTime ( 1942 , 6 , 18 ) } ,
} ;
var connectionString = "Server=SERVER_ADDRESS;Database=DATABASE_NAME;User Id=USERNAME;Password=PASSWORD;" ;
var sqlQueriesAndDapperParameters = new MsSqlQueryGenerator ( ) . GenerateDapperParametrizedBulkInserts ( mapper , people ) ;

using ( var sqlConnection = new SqlConnection ( connectionString ) )
{
    // Default batch size: 1000 rows or (2100-1) parameters per insert.
    foreach ( var ( SqlQuery , DapperDynamicParameters ) in sqlQueriesAndDapperParameters )
    {
        sqlConnection . Execute ( SqlQuery , DapperDynamicParameters ) ;
    }
}