mssql helpers

MsSqlHelper

MsSqlHelpers adalah perpustakaan untuk meningkatkan tugas pengembangan umum MS SQL Server, seperti membuat sisipan massal berparametri untuk digunakan dengan ADO.NET, Entity Framework dan Dapper, dan banyak lagi (dalam waktu dekat).
Untuk mengizinkan IDENTITY INSERT ON, ada parameter opsional dalam pemanggilan metode (mode default OFF).

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

Penggunaan Kerangka Entitas:

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

Penggunaan yang rapi:

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