DB LINQ and SQL Bulk Insert

>Code2Code.net Home >DB Linq Project Home

Bulk insert support in the official Microsoft Linq-to-SQL

In the Microsoft implementation (official Linq-to-SQL), bulk insert is not supported (at least not as of 2007).

Bulk insert in DB LINQ provider for MS SQL server

DB LINQ provider for MS SQL Server supports bulk insert.
This example from Northwind DB shows how to enable it on a per-table level via the 'UseBulkInsert' line:

    Vendor.UseBulkInsert[db.Shippers] = true;
    db.Shippers.Add(new Shippers(-1, "UPS", "800-800-8888"));
    db.Shippers.Add(new Shippers(-1, "Fedex", "900-900-9999"));
    db.SubmitChanges();

Limitations:

ShipperID field (the IDENTITY column) is never updated on the client, because the communication is one-way
Additional permissions are required for bulk insert (vs. regular insert)
SqlBulkCopyOptions.TableLock flag is always on
internally, we create a DataTable (containing all rows) to send - this costs extra memory. See Azamsharp, below, for alternative.

Internally, we use the SqlBulkCopy class. I use Mladen's example on weblogs.sqlteam.comqlteam.com, which internally creates a DataTable with another copy of our data. This has memory implications. Another option would be to follow Azamsharp's example on codeproject. By default, SqlBulkCopyOptions.TableLock is turned ON.

Bulk insert DB LINQ Mysql provider

In MySql, bulk insert seems to be called multiple-row insert.
Raw sql looks like this:

INSERT INTO Shippers (ShipperName,PhoneNumber) VALUES

('UPS', '800-800-8888'),

('Fedex', '900-900-9999'),

...

The one problem, when using this technique, is to choose the row multiple 'just right' to prevent the 'statement was bigger than the Max Allowed Packet size' - see Peter Freitag's post.
Here is a code snippet:

    Vendor.UseBulkInsert[db.Shippers] = 3; //specify bulk insert, three rows at a time
    db.Shippers.Add(new Shippers(-1, "UPS", "800-800-8888"));
    db.Shippers.Add(new Shippers(-1, "Fedex", "900-900-9999"));
    db.SubmitChanges();

Please have a look at unit test WriteTest_BulkInsert.

Bulk Copy DB LINQ PostgreSQL provider

The PostgreSQL command is called COPY. This is currently not exposed from DB LINQ.
Please can someone research if this can be used with anything else besides local files?

Array Binding DB LINQ Oracle provider

In the world of Oracle, there is a very powerful option of inserting arrays of objects.
Unfortunaltely, the DB LINQ Oracle provider is on hold as of 2007-Oct, because my Oracle box has died. Thus, no Array Bindings yet.