Saving data quickly to a database

[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...

[Marc Gravell]

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

[sloan]

Bulk Insert using Xml is my favorite tool of choice.

See

http://groups.google.com/group/micro...91c0640ddf9680
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.

"Jon" <.wrote in message news:u$**************@TK2MSFTNGP02.phx.gbl...

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...

[Marc Gravell]

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

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

[Marc Gravell]

btw, the SqlBulkCopy code is *something* like [untested]:

using (SqlBulkCopy sbc = new
SqlBulkCopy(connectionString))
{
sbc.DestinationTableName = "YOUR_TABLE";
sbc.WriteToServer(yourDataReader);
sbc.Close();
}

Marc

[sloan]

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.

"Marc Gravell" <ma**********@gmail.comwrote in message
news:f1**********************************@l32g2000 hse.googlegroups.com...

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

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

[Marc Gravell]

(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

[Jon]

That's very helpful, thanks for all of your replies.

Jon

"Jon" <.wrote in message news:u$**************@TK2MSFTNGP02.phx.gbl...
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...