BulkInserts without SQLBulkCopy

Al Wilkerson

Does anyone know an alternate efficient way of doing bulk/batch inserts (from a DataSet/DataTable) into a database without using SQLBulkCopy and without using SQLDataAdpater object ?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

daniel13y ago
Check Valentina Database. It has local royalty free engine and Valentina Server. You can use any or both of these via "Valentina NET" ADK.
I should say this is fastest database I have see ever (it is columar). This is SQL and in the same time NON-SQL DB. Can be disk-only or RAM-only or mixed. Besides, it works on mac and linux, and can be used with other languages easy.
http://www.valentina-db.com
0
Sam Hobbs15y ago
A DataTable has a Rows Property. A DataTable exists only in memory. You can add to the DataTable's Rows collection then update the database in a single call. I don't know what ADO does but if it does not do a bulk oeration in the manner you are looking for, hopefully it will be enhanced later. So perhaps you can research that. If you are not familiar with TableAdapters, then it might help to become familiar with that. A TableAdapter is code generated by Visual Studio that does mnay of the lower-level things such as the details of updating the database. If you use a TableAdapter then it is possible that the details will be done for you, including the most efficient way to get and update the database.
0
Al Wilkerson15y ago
Yep, that is correct VistaDB does not support yet and already posted to their support forum. I was just asking a general question if there was a way of doing it without SQLBulkCopy and DataAdapter objects.

I am using one of VistaDB's proprietary Data Access model (DDA) that the company suggested, just have to remove indexes before insert and re-add them afterwards.

Not as fast as SQLBulkCopy, but appears nothing probably is.

Thanks
0
Suthish Nair15y ago
I somewhere read, not sure. In VistaDB there is no bulk upload you need to insert each row one at a time.

Also read and post your query here, might get a better reply.
0
Al Wilkerson15y ago
I'm using VistaDB. They have ADO.Net wrapper but don't have SQLBulkCopy or Batch processing with their DataApater. So, I was wondering if anyone knew of another general way of doing bulk/batch inserts without DataAdapter and besides dropping keys before insert and then re-adding them after insert.

I don't believe there is another way, but just throwing the question out there. Thanks.
0
Mike Gold15y ago
what database are you using? Different databases have different ways of doing bulk copy in their api without using ADO.NET
0
Al Wilkerson15y ago
Forgot to mention I am using a different database than SQL Server and they don't have the SQLBulkCopy functionality or DataAdapter.UpdateBatchSize functionality. They have their own DataAdapter similar to SQLDataAdapter except without the two above mentioned functionality.
0
Suthish Nair15y ago
SQLBulkCopy is more efficient than other ways from code behind.
0