SqlClient.CopyDataToServer Method

SqlClient CopyDataToServer Method

Nemiro.Data.dll

Transferring the specified tables from the instance of DataSet to the database. Used SqlBulkCopy. The fastest and most economical way to move large amounts of data in the database.

Namespace: Nemiro.Data.Sql
Assembly: Nemiro.Data (in Nemiro.Data.dll) Version: 2.11.4.126 (2.11.4.126)

Syntax

C++

Copy to Clipboard Print

public void CopyDataToServer(
	DataSet value
)

public void CopyDataToServer(
	DataSet value
)

Public Sub CopyDataToServer ( 
	value As DataSet
)

Public Sub CopyDataToServer ( 
	value As DataSet
)

Parameters

value: Type: System.Data DataSet
Instance the DataSet class, which you want to transfer to the SQL Server.

Exceptions

Exception	Condition
SqlException	The exception that is thrown when SQL Server returns a warning or error.
StringOrBinaryDataWouldBeTruncatedException	An exception occurs if some of the fields of the table are added values that exceed the allowable size of a table field.

Remarks

Each instance of the collection Tables must contain the name of the target table in the property TableName.

Fields list of the tables must match to columns of the target tables.

Caching options are ignored.

Examples

C++

Copy to Clipboard Print

using (SqlClient client = new SqlClient())
{
  // Create DataTable instance for hotels. 
  // Table name is [hotels]. 
  // In SQL Server database should have a table named [hotels].
  DataTable hotels = new DataTable("hotels");
  // Add fields in the table.
  hotels.Columns.Add("id_hotels", typeof(Guid));
  hotels.Columns.Add("hotel_code", typeof(string));
  hotels.Columns.Add("hotel_name", typeof(string));
  hotels.Columns.Add("hotel_stars", typeof(int));
  hotels.Columns.Add("date_created", typeof(DateTime));

  // Create DataTable instance for photos. 
  // Table name is [hotels_images]. 
  // In SQL Server database should have a table named [hotels_images].
  DataTable hotelsImages = new DataTable("hotels_images");
  // Add fields in the table.
  hotelsImages.Columns.Add("id_hotels", typeof(Guid)); // linked with hotels
  hotelsImages.Columns.Add("image_url", typeof(string));

  // Random data generator, for example.
  Random rnd = new Random(DateTime.Now.Millisecond);

  for (int i = 0; i <= 1000; i++)
  {
    // Hotel ID.
    Guid newHotelId = Guid.NewId();
    // Hotel data.
    hotels.Rows.Add(newHotelId, Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now);
    // Generate photos data. 
    int cnt = rnd.Next(0, 9);
    for (int j = 0; j <= cnt; j++)
    {
      // Set newHotelId to id_hotels, 
      // for linking photos with hotels.
      hotelsImages.Rows.Add(newHotelId, String.Format("http://example.org/{0}", Guid.NewGuid()));
    }
  }
  // Create DataSet instance.
  DataSet data = new DataSet();
  // Add tables to DataSet.
  data.Tables.Add(hotels);
  data.Tables.Add(hotelsImages);
  // Transfer data to SQL Server.
  client.CopyDataToServer(data);
}

using (SqlClient client = new SqlClient())
{
  // Create DataTable instance for hotels. 
  // Table name is [hotels]. 
  // In SQL Server database should have a table named [hotels].
  DataTable hotels = new DataTable("hotels");
  // Add fields in the table.
  hotels.Columns.Add("id_hotels", typeof(Guid));
  hotels.Columns.Add("hotel_code", typeof(string));
  hotels.Columns.Add("hotel_name", typeof(string));
  hotels.Columns.Add("hotel_stars", typeof(int));
  hotels.Columns.Add("date_created", typeof(DateTime));

  // Create DataTable instance for photos. 
  // Table name is [hotels_images]. 
  // In SQL Server database should have a table named [hotels_images].
  DataTable hotelsImages = new DataTable("hotels_images");
  // Add fields in the table.
  hotelsImages.Columns.Add("id_hotels", typeof(Guid)); // linked with hotels
  hotelsImages.Columns.Add("image_url", typeof(string));

  // Random data generator, for example.
  Random rnd = new Random(DateTime.Now.Millisecond);

  for (int i = 0; i <= 1000; i++)
  {
    // Hotel ID.
    Guid newHotelId = Guid.NewId();
    // Hotel data.
    hotels.Rows.Add(newHotelId, Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now);
    // Generate photos data. 
    int cnt = rnd.Next(0, 9);
    for (int j = 0; j <= cnt; j++)
    {
      // Set newHotelId to id_hotels, 
      // for linking photos with hotels.
      hotelsImages.Rows.Add(newHotelId, String.Format("http://example.org/{0}", Guid.NewGuid()));
    }
  }
  // Create DataSet instance.
  DataSet data = new DataSet();
  // Add tables to DataSet.
  data.Tables.Add(hotels);
  data.Tables.Add(hotelsImages);
  // Transfer data to SQL Server.
  client.CopyDataToServer(data);
}

Using client As New SqlClient()
  ' Create DataTable instance for hotels. 
  ' Table name is [hotels]. 
  ' In SQL Server database should have a table named [hotels]. 
  Dim hotels As New DataTable("hotels")
  ' Add fields in the table.
  hotels.Columns.Add("id_hotels", GetType(Guid))
  hotels.Columns.Add("hotel_code", GetType(String))
  hotels.Columns.Add("hotel_name", GetType(String))
  hotels.Columns.Add("hotel_stars", GetType(Integer))
  hotels.Columns.Add("date_created", GetType(DateTime))

  ' Create DataTable instance for photos. 
  ' Table name is [hotels_images]. 
  ' In SQL Server database should have a table named [hotels_images]. 
  Dim hotelsImages As New DataTable("hotels_images")
  ' Add fields in the table.
  hotelsImages.Columns.Add("id_hotels", GetType(Guid)) ' поле для связи с hotels
  hotelsImages.Columns.Add("image_url", GetType(String))

  ' Random data generator, for example. 
  Dim rnd As New Random(DateTime.Now.Millisecond)

  For i As Integer = 0 To 1000 
    ' Hotel ID. 
    Dim newHotelId As Guid = Guid.NewId()
    ' Hotel data.
    hotels.Rows.Add(newHotelId, Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now)
    ' Generate photos data. 
    Dim cnt As Integer = rnd.Next(0, 9)
    For j As Integer = 0 To cnt
      ' Set newHotelId to id_hotels, 
      ' for linking photos with hotels.
      hotelsImages.Rows.Add(newHotelId, String.Format("http://example.org/{0}", Guid.NewGuid()))
    Next 
  Next 
  ' Create DataSet instance. 
  Dim data As New DataSet()
  ' Add tables to DataSet.
  data.Tables.Add(hotels)
  data.Tables.Add(hotelsImages)
  ' Transfer data to SQL Server.
  client.CopyDataToServer(data)
End Using

Using client As New SqlClient()
  ' Create DataTable instance for hotels. 
  ' Table name is [hotels]. 
  ' In SQL Server database should have a table named [hotels]. 
  Dim hotels As New DataTable("hotels")
  ' Add fields in the table.
  hotels.Columns.Add("id_hotels", GetType(Guid))
  hotels.Columns.Add("hotel_code", GetType(String))
  hotels.Columns.Add("hotel_name", GetType(String))
  hotels.Columns.Add("hotel_stars", GetType(Integer))
  hotels.Columns.Add("date_created", GetType(DateTime))

  ' Create DataTable instance for photos. 
  ' Table name is [hotels_images]. 
  ' In SQL Server database should have a table named [hotels_images]. 
  Dim hotelsImages As New DataTable("hotels_images")
  ' Add fields in the table.
  hotelsImages.Columns.Add("id_hotels", GetType(Guid)) ' поле для связи с hotels
  hotelsImages.Columns.Add("image_url", GetType(String))

  ' Random data generator, for example. 
  Dim rnd As New Random(DateTime.Now.Millisecond)

  For i As Integer = 0 To 1000 
    ' Hotel ID. 
    Dim newHotelId As Guid = Guid.NewId()
    ' Hotel data.
    hotels.Rows.Add(newHotelId, Guid.NewGuid().ToString().Substring(0, 4), Guid.NewGuid().ToString().Replace("-", ""), rnd.Next(1, 5), DateTime.Now)
    ' Generate photos data. 
    Dim cnt As Integer = rnd.Next(0, 9)
    For j As Integer = 0 To cnt
      ' Set newHotelId to id_hotels, 
      ' for linking photos with hotels.
      hotelsImages.Rows.Add(newHotelId, String.Format("http://example.org/{0}", Guid.NewGuid()))
    Next 
  Next 
  ' Create DataSet instance. 
  Dim data As New DataSet()
  ' Add tables to DataSet.
  data.Tables.Add(hotels)
  data.Tables.Add(hotelsImages)
  ' Transfer data to SQL Server.
  client.CopyDataToServer(data)
End Using