mssql helpers

MsSqlHelpers

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

엔터티 프레임워크 사용법:

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