SqlBulkCopy.WriteToServer Method
Definition
Important
Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.
Overloads
| WriteToServer(DbDataReader) |
Copies all rows from the supplied DbDataReader array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. |
| WriteToServer(DataRow[]) |
Copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. |
| WriteToServer(DataTable) |
Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. |
| WriteToServer(IDataReader) |
Copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. |
| WriteToServer(DataTable, DataRowState) |
Copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object. |
WriteToServer(DbDataReader)
Copies all rows from the supplied DbDataReader array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
public:
void WriteToServer(System::Data::Common::DbDataReader ^ reader);public void WriteToServer (System.Data.Common.DbDataReader reader);member this.WriteToServer : System.Data.Common.DbDataReader -> unitPublic Sub WriteToServer (reader As DbDataReader)Parameters
- reader
- DbDataReader
A DbDataReader whose rows will be copied to the destination table.
Exceptions
A SqlBulkCopyColumnOrderHint did not specify a valid destination column name.
Applies to
WriteToServer(DataRow[])
Copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
public:
void WriteToServer(cli::array <System::Data::DataRow ^> ^ rows);public void WriteToServer (System.Data.DataRow[] rows);member this.WriteToServer : System.Data.DataRow[] -> unitPublic Sub WriteToServer (rows As DataRow())Parameters
Exceptions
A SqlBulkCopyColumnOrderHint did not specify a valid destination column name.
Examples
The following console application demonstrates how to bulk load data from a DataRow array. The destination table is a table in the AdventureWorks database.
In this example, a DataTable is created at run time. A single row is selected from the DataTable to copy to the destination table.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
Remarks
While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.
The ColumnMappings collection maps from the DataRow columns to the destination database table.
Applies to
WriteToServer(DataTable)
Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
public:
void WriteToServer(System::Data::DataTable ^ table);public void WriteToServer (System.Data.DataTable table);member this.WriteToServer : System.Data.DataTable -> unitPublic Sub WriteToServer (table As DataTable)Parameters
Exceptions
A SqlBulkCopyColumnOrderHint did not specify a valid destination column name.
Examples
The following Console application demonstrates how to bulk load data from a DataTable. The destination table is a table in the AdventureWorks database.
In this example, a DataTable is created at run time and is the source of the SqlBulkCopy operation.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup.
This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(newProducts);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static DataTable MakeTable()
// Create a new DataTable named NewProducts.
{
DataTable newProducts = new DataTable("NewProducts");
// Add three column objects to the table.
DataColumn productID = new DataColumn();
productID.DataType = System.Type.GetType("System.Int32");
productID.ColumnName = "ProductID";
productID.AutoIncrement = true;
newProducts.Columns.Add(productID);
DataColumn productName = new DataColumn();
productName.DataType = System.Type.GetType("System.String");
productName.ColumnName = "Name";
newProducts.Columns.Add(productName);
DataColumn productNumber = new DataColumn();
productNumber.DataType = System.Type.GetType("System.String");
productNumber.ColumnName = "ProductNumber";
newProducts.Columns.Add(productNumber);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = productID;
newProducts.PrimaryKey = keys;
// Add some new rows to the collection.
DataRow row = newProducts.NewRow();
row["Name"] = "CC-101-WH";
row["ProductNumber"] = "Cyclocomputer - White";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-BK";
row["ProductNumber"] = "Cyclocomputer - Black";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-ST";
row["ProductNumber"] = "Cyclocomputer - Stainless";
newProducts.Rows.Add(row);
newProducts.AcceptChanges();
// Return the new DataTable.
return newProducts;
}
private static string GetConnectionString()
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Remarks
All rows in the DataTable are copied to the destination table except those that have been deleted.
While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.
The ColumnMappings collection maps from the DataTable columns to the destination database table.
See also
Applies to
WriteToServer(IDataReader)
Copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
public:
void WriteToServer(System::Data::IDataReader ^ reader);public void WriteToServer (System.Data.IDataReader reader);member this.WriteToServer : System.Data.IDataReader -> unitPublic Sub WriteToServer (reader As IDataReader)Parameters
- reader
- IDataReader
A IDataReader whose rows will be copied to the destination table.
Exceptions
A SqlBulkCopyColumnOrderHint did not specify a valid destination column name.
Examples
The following console application demonstrates how to bulk load data from a SqlDataReader. The destination table is a table in the AdventureWorks database.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is provided
to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a
Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();
// Set up the bulk copy object using a connection string.
// In the real world you would not use SqlBulkCopy to move
// data from one table to the other in the same database.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Remarks
The copy operation starts at the next available row in the reader. Most of the time, the reader was just returned by ExecuteReader() or a similar call, so the next available row is the first row. To process multiple results, call NextResult() on the data reader and call WriteToServer again.
Note that using WriteToServer modifies the state of the reader. The method will call Read() until it returns false, the operation is aborted, or an error occurs. This means that the data reader will be in a different state, probably at the end of the result set, when the WriteToServer operation is complete.
While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.
The ColumnMappings collection maps from the data reader columns to the destination database table.
Applies to
WriteToServer(DataTable, DataRowState)
Copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.
public:
void WriteToServer(System::Data::DataTable ^ table, System::Data::DataRowState rowState);public void WriteToServer (System.Data.DataTable table, System.Data.DataRowState rowState);member this.WriteToServer : System.Data.DataTable * System.Data.DataRowState -> unitPublic Sub WriteToServer (table As DataTable, rowState As DataRowState)Parameters
- rowState
- DataRowState
A value from the DataRowState enumeration. Only rows matching the row state are copied to the destination.
Exceptions
A SqlBulkCopyColumnOrderHint did not specify a valid destination column name.
Examples
The following Console application demonstrates how to bulk load only the rows in a DataTable that match a specified state. In this case, only unchanged rows are added. The destination table is a table in the AdventureWorks database.
In this example, a DataTable is created at run time and three rows are added to it. Before the WriteToServer method is executed, one of the rows is edited.
The WriteToServer method is called with a DataRowState.Unchanged rowState argument, so only the two unchanged rows are bulk copied to the destination.
Important
This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. This code is provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a connection to the AdventureWorks database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
connection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Create a table with some rows.
DataTable newProducts = MakeTable();
// Make a change to one of the rows in the DataTable.
DataRow row = newProducts.Rows[0];
row.BeginEdit();
row["Name"] = "AAA";
row.EndEdit();
// Create the SqlBulkCopy object.
// Note that the column positions in the source DataTable
// match the column positions in the destination table so
// there is no need to map columns.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write unchanged rows from the source to the destination.
bulkCopy.WriteToServer(newProducts, DataRowState.Unchanged);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static DataTable MakeTable()
// Create a new DataTable named NewProducts.
{
DataTable newProducts = new DataTable("NewProducts");
// Add three column objects to the table.
DataColumn productID = new DataColumn();
productID.DataType = System.Type.GetType("System.Int32");
productID.ColumnName = "ProductID";
productID.AutoIncrement = true;
newProducts.Columns.Add(productID);
DataColumn productName = new DataColumn();
productName.DataType = System.Type.GetType("System.String");
productName.ColumnName = "Name";
newProducts.Columns.Add(productName);
DataColumn productNumber = new DataColumn();
productNumber.DataType = System.Type.GetType("System.String");
productNumber.ColumnName = "ProductNumber";
newProducts.Columns.Add(productNumber);
// Create an array for DataColumn objects.
DataColumn[] keys = new DataColumn[1];
keys[0] = productID;
newProducts.PrimaryKey = keys;
// Add some new rows to the collection.
DataRow row = newProducts.NewRow();
row["Name"] = "CC-101-WH";
row["ProductNumber"] = "Cyclocomputer - White";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-BK";
row["ProductNumber"] = "Cyclocomputer - Black";
newProducts.Rows.Add(row);
row = newProducts.NewRow();
row["Name"] = "CC-101-ST";
row["ProductNumber"] = "Cyclocomputer - Stainless";
newProducts.Rows.Add(row);
newProducts.AcceptChanges();
// Return the new DataTable.
return newProducts;
}
private static string GetConnectionString()
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
Remarks
Only rows in the DataTable that are in the states indicated in the rowState argument and have not been deleted are copied to the destination table.
Note
If Deleted is specified, any Unchanged, Added, and Modified rows will also be copied to the server. No exception will be raised.
While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.
The ColumnMappings collection maps from the DataTable columns to the destination database table.