Fast movement of data into database - nemiro-net/nemiro.data.dll GitHub Wiki

SqlBulkCopy is the quickest way to transfer large amounts of data into database.

In the SqlClient class there are two methods that use SqlBulkCopy:

• CopyDataToServer;
• CopyTableToServer.

Using these methods, you can easily transfer a DataSet or DataTable into database.

C#

using (SqlClient client = new SqlClient())
{
  // Create DataTable instance. 
  // Table name is [hotels]. 
  // In SQL Server database should have a table named [hotels].
  DataTable table = new DataTable("hotels");
  // Add fields in the table.
  table.Columns.Add("hotel_code");
  table.Columns.Add("hotel_name");
  table.Columns.Add("hotel_stars");
  table.Columns.Add("date_created");
  // Random data generator, for example.
  Random rnd = new Random(DateTime.Now.Millisecond);
  for (int i = 0; i <= 1000; i++)
  {
    table.Rows.Add(Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now);
  }
  // Transfer data to SQL Server
  client.CopyTableToServer(table);
}

Visual Basic .NET

Using client As New SqlClient()
  ' Create DataTable instance. 
  ' Table name is [hotels]. 
  ' In SQL Server database should have a table named [hotels]. 
  Dim table As New DataTable("hotels")
  ' Add fields in the table.
  table.Columns.Add("hotel_code")
  table.Columns.Add("hotel_name")
  table.Columns.Add("hotel_stars")
  table.Columns.Add("date_created")
  ' Random data generator, for example. 
  Dim rnd As New Random(DateTime.Now.Millisecond)
  For i As Integer = 0 To 1000
    table.Rows.Add(Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now)
  Next 
  ' Transfer data to SQL Server
  client.CopyTableToServer(table)
End Using