开发者

Best way to Bulk Insert from a C# DataTable

开发者 https://www.devze.com 2023-02-10 18:25 出处:网络
I h开发者_C百科ave a DataTable that I want to push to the DB. I want to be able to say like myDataTable.update();

I h开发者_C百科ave 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


If using SQL Server, SqlBulkCopy.WriteToServer(DataTable)

  • SqlBulkCopy.WriteToServer Method (DataTable)

Or also with SQL Server, you can write it to a .csv and use BULK INSERT

  • BULK INSERT (Transact-SQL)

If using MySQL, you could write it to a .csv and use LOAD DATA INFILE

  • LOAD DATA INFILE Syntax

If using Oracle, you can use the array binding feature of ODP.NET

  • Bulk Insert to Oracle using .NET

If SQLite:

  • How do I bulk insert with SQLite?
  • Faster bulk inserts in sqlite3?


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.


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.


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


SqlBulkCopy class is best for SQL server,

Doing Bulk Upload/Insert of DataTable to a Table in SQL server in C#

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号