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]

Re: Saving data quickly to a database

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]

Re: Saving data quickly to a database


Bulk Insert using Xml is my favorite tool of choice.

See

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5491c0640ddf9680

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$$GobtUIH A.1168@TK2MSFTN GP02.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]

Re: Saving data quickly to a database

Bulk Insert using Xml is my favorite tool of choice.
100Mb? yikes!

Anyway, the fake IDataReader is SimpleDataReade r 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

[Marc Gravell]

Re: Saving data quickly to a database

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

using (SqlBulkCopy sbc = new
SqlBulkCopy(con nectionString))
{
sbc.Destination TableName = "YOUR_TABLE ";
sbc.WriteToServ er(yourDataRead er);
sbc.Close();
}

Marc

[sloan]

Re: Saving data quickly to a database


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" <marc.gravell@g mail.comwrote in message
news:f1f39cc0-4fc9-48f8-a62b-c3a1f4bf1956@l3 2g2000hse.googl egroups.com...

>Bulk Insert using Xml is my favorite tool of choice.

100Mb? yikes!
>
Anyway, the fake IDataReader is SimpleDataReade r 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

[Marc Gravell]

Re: Saving data quickly to a database

(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]

Re: Saving data quickly to a database

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

Jon

"Jon" <.wrote in message news:u$$GobtUIH A.1168@TK2MSFTN GP02.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...