 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / January 2008Saving data quickly to a database
Hi, I plan to import some data from an old DOS-based programme. The data file format that this programme produced appears to be proprietary, but I've managed to reverse engineer the format and have put together a C# programme to do this. The data will be imported into a database table. Do you have any general tips on how store the data to a database (SQL Server 2005 Express) quickly? The amount of data could be up to 100Mbytes. Should I put it into a DataTable first then save this to the database, or should I send it directly to the database (eg using SQL), or is there another way? The data will overwrite anything in the database, so no merging is required. Thanks... Bulk Insert using Xml is my favorite tool of choice. See http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5491c0 640ddf9680 My example is (to me) a better thought out version and better tweaked version of the one seen here: http://support.microsoft.com/kb/315968 One key to this approach is that indexes are rebuilt ~after the bulk insert, which is contrary to the "row by row" way of doing it. > Hi, > [quoted text clipped - 18 lines] > > Thanks... SqlBulkCopy; I have posted code previously that shows how to make a fake IDataReader, essentially as a consumer of something like IEnumerable<T>. That way, you only ever need to read one row at a time. I'll see if I can dig out the old code... Marc > Bulk Insert using Xml is my favorite tool of choice. 100Mb? yikes! Anyway, the fake IDataReader is SimpleDataReader from the following: http://groups.google.com/group/microsoft.public.dotnet.languages.csharp/msg/91c7 a20056ffe8e1 You simply need to provide an implementation (just a few lines of code). In my example (XmlDataReader), it reads lines from an xml file - but instead you'd override DoRead to read the next line from your DOS file, and then call SetValues() and return true; if you find you have got to the end of the file, return false instead. (note that in the constructor, you need to tell the base-class the names and data-types of the columns) Job done ;-p Marc btw, the SqlBulkCopy code is *something* like [untested]: using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString)) { sbc.DestinationTableName = "YOUR_TABLE"; sbc.WriteToServer(yourDataReader); sbc.Close(); } Marc I see your point about the size. I didn't clearly see the "M" of "Mbyte" in the original post. ( :< ) I've done a similar thing with an IDataReader, but will check your link as well. You can always learn a different approach if you just try. Just for the record, I have done (up to 4MB) files with my approach. The "similar" thing I've mentioned, I've done an IDataReader, and every 1000 records (or whatever N Number), I create a DataSet/Xml and ship it off. I reserve this approach when I have VALIDATION business rules on the data in the IDataReader. Aka, a "non dumb" data importer. And I save off the problem records as well. But the more ways the merrier. >> Bulk Insert using Xml is my favorite tool of choice. > 100Mb? yikes! [quoted text clipped - 14 lines] > > Marc Jon, If you need to do this programatically, then the answers provided by Marc and sloan are fine. If you don't need to do this programatically (say, you need to do it once a month or once a day, and this is the only thing you need to do in the process), you might be better off creating a Data Transformation Service (google for more information) package. It would save you some code.  Signature- Nicholas Paldino [.NET/C# MVP] - mvp@spam.guard.caspershouse.com > Hi, > [quoted text clipped - 18 lines] > > Thanks... > (say, you need to do it > once a month or once a day, and this is the only thing you need to do in the > process), you might be better off creating a Data Transformation Service > (google for more information) package. True, very true; at the simplest level, you could use the C# code to write it out as CSV or TSV, which you can then get into the server just with BCP (or the similar UI tools). Marc That's very helpful, thanks for all of your replies. Hi, I plan to import some data from an old DOS-based programme. The data file format that this programme produced appears to be proprietary, but I've managed to reverse engineer the format and have put together a C# programme to do this. The data will be imported into a database table. Do you have any general tips on how store the data to a database (SQL Server 2005 Express) quickly? The amount of data could be up to 100Mbytes. Should I put it into a DataTable first then save this to the database, or should I send it directly to the database (eg using SQL), or is there another way? The data will overwrite anything in the database, so no merging is required. Thanks... Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.