Reputation: 5869
I have a DataTable
that I want to push to the DB. I want to be able to say like
myDataTable.update();
But after reading the MSDN docs, apparently this does inserts row by row.
It should be noted that these statements are not performed as a batch process; each row is updated individually.
What are my alternatives?
Edit: I am using SQL Server 2005
Upvotes: 31
Views: 155769
Reputation: 422
string connectionString= ServerName + DatabaseName + SecurityType;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) {
connection.Open();
bulkCopy.DestinationTableName = "TableName";
try {
bulkCopy.WriteToServer(dataTableName);
} catch (Exception e) {
Console.Write(e.Message);
}
}
Please note that the structure of the database table and the table name should be the same or it will throw an exception.
Upvotes: 11
Reputation: 19012
If using SQL Server, SqlBulkCopy.WriteToServer(DataTable)
Or also with SQL Server, you can write it to a .csv and use BULK INSERT
If using MySQL, you could write it to a .csv and use LOAD DATA INFILE
If using Oracle, you can use the array binding feature of ODP.NET
If SQLite:
Upvotes: 64
Reputation: 61
Here's how I do it using a DataTable. This is a working piece of TEST code.
using (SqlConnection con = new SqlConnection(connStr))
{
con.Open();
// Create a table with some rows.
DataTable table = MakeTable();
// Get a reference to a single row in the table.
DataRow[] rowArray = table.Select();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
bulkCopy.DestinationTableName = "dbo.CarlosBulkTestTable";
try
{
// Write the array of rows to the destination.
bulkCopy.WriteToServer(rowArray);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}//using
Upvotes: 3
Reputation: 71
SqlBulkCopy class is best for SQL server,
Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C#
Upvotes: 1
Reputation: 73604
This is going to be largely dependent on the RDBMS you're using, and whether a .NET option even exists for that RDBMS.
If you're using SQL Server, use the SqlBulkCopy class.
For other database vendors, try googling for them specifically. For example a search for ".NET Bulk insert into Oracle" turned up some interesting results, including this link back to Stack Overflow: Bulk Insert to Oracle using .NET.
Upvotes: 4