HOWTO insert a file (100KB) in a database

  • Thread starter Thread starter mac
  • Start date Start date
i am not sure exactly what you want but i guess you want
to read data from a file and insert it into a data base.
that should be quite easy and straight forward.
use a stream reader object to read the data from the file
and use ado.net objects to insert each data (preferrably
each line) into the database. you will use a connection
object, and a command object and you will have to create
some adhoc query string. it is easy trust me.
cheers!
 
If you are using SQLServer, you will probably want to take a look at the BCP
utility. Check it out in your SQL BOL (books on-line documentation).
 
*WHAT* database??

And more importantly: *WHY* do you want to do that in the first
place??

MS SQL Server stores its data in 8K pages, so a 100K file would take
up at least 13 pages or more - and it's not really a very fast, quick
operation to store or retrieve a file from a SQL Server database.

My tip: think real hard about why you want to store the file INSIDE
the database - is that REALLY necessary?? If ever possible, DON'T -
use some other means to link the database and the file.

Current relational databases usually don't handle larger chunks of
data too well...... no matter how you do it.

Marc
 
*WHAT* database??

And more importantly: *WHY* do you want to do that in the first
place??
Click to expand...

Makes for a nice document repository that is easy to back up. All files
inside the database file. Both Microsoft Content Management Server and
Sharepoint server technologies store binary objects in the database.
MS SQL Server stores its data in 8K pages, so a 100K file would take
up at least 13 pages or more - and it's not really a very fast, quick
operation to store or retrieve a file from a SQL Server database.
Click to expand...

Ummm, in SQL 6.5 that may have been true as it had to scan the page
chain but in SQL2K that is not really the case. While the data is
stored in multiple pages, it is organized in a very efficient B-tree
structure that it can navigate very quickly.

My tip: think real hard about why you want to store the file INSIDE
the database - is that REALLY necessary?? If ever possible, DON'T -
use some other means to link the database and the file.
Click to expand...

It all depends on the situation. Maintaining the file's location in the
file system can be a real hassle and you're counting on nobody moving or
deleting the file. Saving the file in the database also allows you to
track changes to the file and implement versioning control. (although
writing this on your own is crazy with so many products out there that
already do this)
Current relational databases usually don't handle larger chunks of
data too well...... no matter how you do it.
Click to expand...

Some do, some don't. SQL2000 does a very good job of it.

Check out this kb article on msdn for some examples of how to do this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317043

Dave
 
Ummm, in SQL 6.5 that may have been true as it had to scan the page
chain but in SQL2K that is not really the case. While the data is
stored in multiple pages, it is organized in a very efficient B-tree
structure that it can navigate very quickly.
Click to expand...

Well, maybe so - we haven't had much success with it yet. Also, the
TEXT, NTEXT, VARBINARY and IMAGE columns tend to force certain
restrictions on tables (like you can't replicate and some triggers
won't work), which is still a nuisance. I'm hoping SQL Server 2005
will alleviate these problems, and make storing BLOBs inside tables a
"natural" way of doing business!

Marc
 
Back
Top