mssql helpers

MsSqlHelpers

MsSqlHelpers est une bibliothèque destinée à améliorer les tâches de développement courantes de MS SQL Server, telles que la génération d'insertions groupées paramétrées à utiliser avec ADO.NET, Entity Framework et Dapper, et bien plus encore (dans un avenir proche).
Pour autoriser IDENTITY INSERT ON, il existe un paramètre facultatif dans l'appel de méthode (mode par défaut OFF).

Utilisation d'ADO.NET :

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

Utilisation d'Entity Framework :

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

Utilisation pimpante :

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