The SQL Column type you are looking for is either Text or nText. This is a blob field that allows any content you want.

Inorder to populate or download from this type of field, use the byte[] array and stream classes. The are ample examples on DaniWeb, CodeProject, and Google on how to do this.

If you still can't find an example, let me know some specifics about the server, database name and column name, and I can write a quick example for you using your own environment criteria (saves on confusion factor).

// Jerry

You can do this by using serialization too.
.Net has good documentation about the issue.

MSDN Linkie

I have never used this myself, but I included it in my technical analysis of my current project. I'll probably have to do something similar here.

And Jerry, also thanks for your take. I'll also consider the use of a bytestreamer. But somewhere I think that serialization is more fit.

Please tell me if otherwise.

Jens,

If you are able to find a method other than using a byte[] and memorystream to push large data (such as a file) into an SQL column, please let me know.

From what I understand, when using a stream class you are serializing data. Keep in mind that when you are pushing a file into a data column, you are using the mechanisms of the SQL Dlls as wrapped in the System.Data and System.data.SqlClient libraries.

In my project, I am serializing a multi-table dataset as XML into a SQL column. This allows me to capture large amounts of data from multiple sources (and types) for transport through an SQL database. I still have to use byte[] and stream to run the data into and outof the blob field. I do have a second column (varchar(max)) for handling smaller objects such as a generic collection class that has special metedata and some attributes seperate from the XML dataset that can be stored directly to the Sql column. It is a serializable class, so it converts easily. But the varchar column only allows char type but serialization moves data as XML anyway, so that is not a problem.

When posting PDF, Images, and other binary data, I use byte[] and stream into the blob field.

Let me know what you find.
Jerry

It'll probably take a while untill I get to that module of the code, since it has a change request pending, but I wrote it down and I'll try to send you if I manage to find something out. Don't count on it too much though, since I tend to forget stuff, and I'm already surprised I made it to day two on this forum (with over 10 posts :O )

From what I've read, the 'serialize' class also utilizes a stream. So it would most probably just be the same as your solution. Only premade in .net now.

Still thanks for your response. This thread has encouraged me to look further instead of just taking one solution for granted :p.

I am still looking for a solution to upload or download a doc. or a pdf file to the SQL server and then download them and open them in the right application, ie. word or adobe reader

rsimlote,

Files or any binary data is stored in SQl Server as a byte array.
Placing a file into SQL, means placing the file into a stream (of bytes), and then save that
to the column.

Likewise when extracting this binary data to be used by another application, it is a matter of reading the bytes from the column and writing them to disk, and finally executing the file.

This means that you will need to store more than just the bytes of the file. You will also want to save some file information such as the FileName, or as a minimum, the file extension. In a recent project, I also saved the DateTime as well so that I could reset the file to the correct name and date criteria. I also included a checksum that I could test to make sure that what I wrote to disk is exactly what I originally copied from disk to SQL.

If you want an example of saving to SQL, retrieving from SQL then executing it (something like an Excel or Word file), let me know and I can whip one up this evening after work.

Regards,
Jerry

Since this thread has been dragging on for some time, and there have been a number of members wanting to see how to do this, I went ahead and created a simple demo project in VS2005, although it should also work fine in VS2008.
It is attached to this message.
UnZip it to a new directory, and read the notes at the top of form1.cs.

// Jerry

Jerry, the Array.GetUpperBound(0) reduces the file size by one byte, this seems to handle most file types but .RTF files will not load in Word 2007 and report that they're corrupt. I changed it to be ArraySize=Data.Length; and that works fine.

JerryShaw,

SqlFileHolder sample works perfectly. Could you please let me know if the sample works with Excel Files as I am getting an error when I tried to view the uploaded excel file. 'The file cannot be accessed. The file may be read-only or you may be trying to access a read-only location. Or, the server the document is stored may not be responding.'
I am using Visual Studio 2005, C#, Sql Server Express Edition.

Thank you.
Kiran.