CODEWITHSUNDEEP
c#.net-2.0
Joda Maki
Joda Maki

Reputation: 5869

Best way to Bulk Insert from a C# DataTable

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

Answers (5)

Vishal Kotak
Vishal Kotak

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

JohnB
JohnB

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

Carlos A Merighe
Carlos A Merighe

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

Bala
Bala

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

David
David

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

Related Questions