HOWTO insert a file (100KB) in a database

[mac]

thanks,

mac

 

[tryers]

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!

 

[John Mark Howell]

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

 

[Marc Scheuner [MVP ADSI]]

*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

 

[David Totzke (.NET/C# MVP)]

*WHAT* database??

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

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.

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.

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.

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

 

[Marc Scheuner [MVP ADSI]]

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.

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