mssql helpers

MsSqlヘルパー

MsSqlHelpers は、ADO.NET、Entity Framework、Dapper などで使用されるパラメーター化された一括挿入の生成など、MS SQL Server の一般的な開発タスクを (近い将来) 改善するためのライブラリです。
IDENTITY INSERT ON を許可するには、メソッド呼び出しにオプションのパラメーターがあります (デフォルト モードは OFF)。

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

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

賢い使い方:

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