Update database without SQL???
-
First of all, I'm not an expert on data access. But in my trials and tribulations with ADO.Net and ADO I've yet to find a way to update a database without it using SQL to do its work. It seems to me that if I have a large recordset that I create which I want to add to a database, it should be doable without the overhead of converting to SQL and then having the DBMS unconvert it on its side. Is this possible with ADO.Net, and if so, how? Let's say that I have a simple table in the database with 200 double-precision floating point fields. If at some point, I have 4,000 rows to add to the table (6,400,000 bytes of data) how can I add this data to the database as efficiently as possible? It seems to me that converting everything to a text command in SQL when I already have the data formatted as a 4000x200 table is inefficient.
-
First of all, I'm not an expert on data access. But in my trials and tribulations with ADO.Net and ADO I've yet to find a way to update a database without it using SQL to do its work. It seems to me that if I have a large recordset that I create which I want to add to a database, it should be doable without the overhead of converting to SQL and then having the DBMS unconvert it on its side. Is this possible with ADO.Net, and if so, how? Let's say that I have a simple table in the database with 200 double-precision floating point fields. If at some point, I have 4,000 rows to add to the table (6,400,000 bytes of data) how can I add this data to the database as efficiently as possible? It seems to me that converting everything to a text command in SQL when I already have the data formatted as a 4000x200 table is inefficient.
Convert everything to a text file and use the BCP (bulk copy) utility. This is the fastest way to upload to a SQL Server database (in reasonable servers, I got about 100,000 rows/second). The second fastest way, much slower, but much more flexible is using DTS. Concussus surgo. When struck I rise.