uploading files (.pdf, .docx, .xls) to SQL Server from a Windows application

nK0de

Active member
Joined
Jan 20, 2012
Messages
42
Location
Colombo, Sri Lanka
Programming Experience
1-3
I did quite a lot of research on Google on this topic but didn't come across anything useful and clear. How can I upload files such as .pdf, .docx to SQL Server 2008 from a Windows application?
A description on what datatypes to use on SQL Server and what classes to use in the VB.NET code would be great.

Thank you.
 

Herman

Well-known member
Joined
Oct 18, 2011
Messages
882
Location
Montreal, QC, CA
Programming Experience
10+
On MS SQL you will want to use the 'image' or 'varbinary(max)' datatype. They are both designed for large variable length binary data, max 2GB. You will probably need a stored procedure to receive the large chunk of data and check its integrity before committing to the database. You have to pass the data in hexadecimal in the query, like this:

INSERT INTO MyTable (FileField) VALUES (0x987aefd98fe098adfdf0798ef09da87f0e987df...)


There is also a limit on the size of a single query. To be honest it would be much more robust to use a dedicated file server and just store a link to it in the database.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,579
Location
Sydney, Australia
Programming Experience
10+
You would use type 'varbinary' in the database and you then retrieve and save data in that column exactly the same way as you would any other data. In the your .NET application, the data will be in the form of a Byte array. You can use the File.ReadAllBytes and .WriteAllBytes methods to read a file into a Byte array and write a Byte array to a file.

You might also want to look at enabling FILESTREAM on your database instance if you haven't already. That will cause binary data to be stored outside the MDF file and stop it getting to big. You should read about the pros and cons of doing so to decide whether it's appropriate for you.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,579
Location
Sydney, Australia
Programming Experience
10+
On MS SQL you will want to use the 'image' or 'varbinary(max)' datatype.
You should not be using the 'image' data type, which was deprecated long ago.
You have to pass the data in hexadecimal in the query, like this:

INSERT INTO MyTable (FileField) VALUES (0x987aefd98fe098adfdf0798ef09da87f0e987df...)
No you don't. Just like any other variable value, the data should be inserted using a parameter.
 

nK0de

Active member
Joined
Jan 20, 2012
Messages
42
Location
Colombo, Sri Lanka
Programming Experience
1-3
Thank you both for your responses.
@jmcilhinney : I managed to save the files in SQL Server database using ReadAllBytes. Thank you very much. I know this saving files in a database method is not the ideal way to go about saving files. I'm just doing this as a learning exercise. Just wanted to try it out and see :) btw one question, I don't know how to enable FILESTREAM in SQL Server. Care to explain please?
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,579
Location
Sydney, Australia
Programming Experience
10+
I don't know how to enable [/COLOR]FILESTREAM in SQL Server. Care to explain please?
What exactly do you not understand about what you found when you searched?
 
Top Bottom