Saving data quickly to a database

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

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...
 
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/91c7a20056ffe8e1

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.
 
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.
 
(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.
Click to expand...

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.

Jon

"Jon" <.> wrote in message 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...
 
Back
Top