how to store files in sql server 2008

[visya]

hi friends....... i am doing a project in c#....... i need a urgent help...... i want to store all kinds of file(.doc,excel,bmp) in sql server 2008 in a single button click......the number of files to save depends upon the user...so i dont know how to create taable fr this and the datatypes for that.......please send me soon...

[deceptikon]

Simple: don't do that. To store arbitrary binary files in a table you'd need to use blobs, and I can tell you from experience to avoid blobs where possible.

A better approach is to store the files on the file system, then store the path to them in your table. Then the blob becomes just a varchar(255). From the database perspective it's much easier to work with.

[JOSheaIV]

Okay so assumning I understand this correctly, here's one way to do it I believe (note, I didn't this in SQLite, but it should be similar)

First of all you need a column of type VARBINARY

Then you take the document or whatever you have an convert it into a Byte Array, here's code I used to do it with an image

public static byte [] ImageToByte (Image image, System.Drawing.Imaging.ImageFormat format) //converts the image to a byte []
{
    using (MemoryStream ms = new MemoryStream())
    {
        image.Save(ms, format);
        byte [] imageBytes = ms.ToArray();

        return imageBytes;
    }
}

Now you have your data in a byte array (which might be very large as there isn't much if nothing, for compression)

You then add this to the database (it's type is Binary FYI). I can say exactly how as my code again is in SQLite format so it might vary.

However, as I mentioned before, the files you try to import might become larger then expected as there are no forms of compression algorithms on them. I personally stopped doing this for that reason alone and just left a link to the file