Storing PDF files in SQL server and retrieving them using ASP.net

Can you guys give me any specific classes I should read about to implement the above? Any specific methods?

Any webpage I should read?
Thanks for your direction/help!
Visit me at: www.kkarthik.info
0
kumarakn
2/18/2003 3:35:13 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

22 Replies
740 Views

Similar Articles

[PageSpeed] 7

You'll pretty much want to store any file types as BLOBs in a relational dB, providing you don't expect the file to exceed the size that a BLOB can in whatever product you are using (since this is in the SQL Server discussion, I'll assume you are using SQL Server).

So basically, when you store them, you store them simply as binary data in a BLOB column, along with the file name or file type in the same row. Might want to be careful with your schema design, as some products only support one BLOB column per table.
At any rate, inserting the PDF file is simple using a file chooser control to upload the file to the server, and inserting it binary into a row, along with other attributes like file name, file type, maybe. When you retrieve it from the database, you'll examine the file name or file type (necessary if you are storing more than just PDFs, not if you arent), set the HTTP headers to the appropriate content type, and stream the binary data back to whatever client that requests it.
This type of thing ultimately has little to do with ASP.NET, much more to do with ADO.NET, since whether you use ASP or any other web presentation technology, the method is the same.
Classes you might want to read up on are the same ones you use to insert and retrieve any kind of data from SQL Server w/ ADO.NET....DataReader, SqlCommand, etc. Heres a fine example straight from the .NET documentation. It writes an Image to SQL Server BLOB column, but you get the idea.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("5/21/99");
AddEmployee("Jones", "Mary", "Sales Representative", hireDate, 5, "jones.bmp");
}
public static void AddEmployee(string lastName, string firstName, string title, DateTime hireDate , int reportsTo, string photoFilePath)
{
byte[] photo = GetPhoto(photoFilePath);
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;");
SqlCommand addEmp = new SqlCommand("INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, @Photo)", nwindConn);
addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo;
addEmp.Parameters.Add("@Photo", SqlDbType.Image, photo.Length).Value = photo;
nwindConn.Open();
addEmp.ExecuteNonQuery();
nwindConn.Close();
}
public static byte[] GetPhoto(string filePath)
{
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
byte[] photo = br.ReadBytes((int)fs.Length);
br.Close();
fs.Close();
return photo;
}
}
0
alexkr
2/28/2003 6:44:15 PM
Are you sure you actually want to store the binary file data in the DB?

Why not store a pointer to the file, and some config info about the file?
see also:
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=160489#161136
0
rox
3/3/2003 5:25:58 PM
Hi rox.scott,

Have you used BLOBs in SS2K lately? I used to be against binary data in a relational database, but it seems like MS made it work better now. With stuff I've done recently, it seems to work without the perf penalty that existed before.
I'd love to hear about how it's worked or hasn't worked for you. I'm curious if my apps have just used the binary data in a way that worked. Since I was so against it for so long, I'm having trouble believing that storing binary data in SQL Server really is a good thing!
Thanks!
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
3/4/2003 12:43:07 AM
1. My most basic concern is just theoretical:  databases are not designed to be file systems, file systems *are* designed to be file systems.

2. BLOB-based code may be getting easier to write, but so is file IO.
3. BLOBs are the most un-portable thing in our universe. They not only have varying implementations between DBs, but also completely different infrastructures at the application programming level--e.g. between C++, C#, Delphi, etc.--all using ADO.
4. Then there are size limitations. I am not sure what it is for SS2K, but to be robust you would need to account for blocking of your blobs should the limit be exceeded (see point #1).
5. BLOBs are not readily administered--i.e. an Admin of the OS could easily open up files, to see what's in them, view date/time creation, etc.
6. My success with file upload/storage using .NET and the model of pointers in the db has been very good. If ease of code is the concern, you'll be happy to know that such a solution can be completed in very little code.
0
rox
3/4/2003 3:37:02 AM
I agree with this. Since you're never going to join on a blob field, or do LIKEs etc. there is NO good reason for keeping PDF files in the database. Keep it in your file system and just save pointers to them. Otherwise you may quickly find your database growing out of control with all the PDFs in it. At least with seperate files you can move them to other devices / split them up. With a DB it's not that easy.

Blobs sucks. I don't like anything about them.
0
Pierre
3/5/2003 1:14:16 AM
Ok, I've read this commentary, and I agree that storing a link might be the better way.

So how do you store a link in a SQL server table and access it using ADO.NET?
I have a stack of pdfs that the client wants scanned and indexed to be searchable, then stored in some way. The new forms will be input into a dB on SS7. I'd like to store a link to the old ones in the same dB.
-Dawn
0
djoyn
4/1/2003 3:26:10 PM
I certainly agree that BLOBs suck. Big time.

But I'd be careful with this statement: "At least with seperate files you can move them to other devices / split them up. With a DB it's not that easy." Sure, it's easy to move the files in the file system, but keeping that coordinated with the path you've stored in the database can be a nightmare, particularly in a heavily used system. And splitting them would be even way worse.
But as with most things, a carefule design that takes into account most reasonable future conditions will keep all this manageable.
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
4/1/2003 3:34:26 PM
If you store full paths in your DB, it's as easy as writing a little maintenance program to copy some files to a new device and updating your DB accordingly. I don't see what the problem is. You can even do this on-the-fly provided updates to the actual files are rare (as it will be with PDFs)

I mean:
1) Copy file to new drive
2) Update database link
3) Delete old file
0 downtime.
0
Pierre
4/1/2003 8:56:03 PM
The "problem" is that it is yet more code to write, test, and deploy. Code that is a bit tricky to get right and reliable, supporting transactions in case something goes wrong in copying the file.

But you're right. It's not rocket science and a very acceptable way to go.
Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
0
donkiely
4/7/2003 6:31:05 PM
Hi

I know this is an old thread but I was just about to post a new one along the same lines.
I have to store various documents (up to 5mb) which can be uploaded/downloaded via a web app, therefor I either have to reference them or store them as blobs.
The problem I have is that site will be hosted on a web farm so I cannot reference a path (well in theory I can but the there's seems to be little point with regards to resillience, the app relies on the documents being available).
Therefore I was thinking of using Blobs, the sql server is clustered (not something I've dealt with before).
So considering this are blobs really that bad? I'm not really talking about preffered methods but I need to know if this project is achievable via blobs.
Will there be a hit on performance? What are the downsides? As the documents are stored as binary what does this means in terms of filesize on the server?
Thanks
Rich
0
shincello
9/8/2003 12:36:14 PM
For me, I'd say that blobs are bad, don't use them, you'll thank your lucky stars later. Performance will suck, filesize won't be optimal but probably won't matter, and it will be a lot of extra hassle to implement.

But yes, they will work for what you want to do.
I'd personally just make the files part of the app deployment, and so they'll just get copied on to all the web servers. Easy peasy.
0
Pierre
9/8/2003 1:06:01 PM
People will be uploading the documents all the time, prehaps up to 50 documents a day, we cannot stored them on every single server! The extra disk space required would be huge.

What do you mean filesize will not be optimal?
0
shincello
9/8/2003 1:10:28 PM
On a clustered SQL Server the files will ALSO be stored on every single server (SQL server in that case) and since it's stored in a database there's some overhead, but like I said, that probably won't matter. HDD space is cheap. Very cheap. And if you have the same number of web-servers as SQL servers, the usage will be roughly the same. So that's not an issue for me. But do what you like, it sounds like you've made up your mind already. Both ways will work.
0
Pierre
9/8/2003 1:17:11 PM
Thanks Pierre.

I haven't made my mind up yet!
I am being driven by my client, they already have the web farm set up and they have more disk space on their SQL server machines than the web servers so they have asked for the documents to be stored in the database.
My job now is to work out if it's best to:
a) Establish that storing the documents as Blobs is going to be a complete headache with a loss in performance and kick up a fuss and try to get them to change their mind.
or
b) Just to accept that every project is not perfect (especially when you are relying on external hosting) and get on with it.
The purpose of my post is to allow me to make an informed decision, so any feedback is very useful.
If you (or anyone else) could go into further details of why you would not store them in the database then it is something for me to consider.
Thanks again!
rich
0
shincello
9/8/2003 1:30:35 PM
Pierre

Also could you give me a brief description of a clustered SQL Server:
Does this mean that their are separate servers each with a SQL server running?
or
That SQL server is running on one server and writing to many disks?
thanks.
0
shincello
9/8/2003 1:40:25 PM
From Roy's post above:

1. My most basic concern is just theoretical: databases are not designed to be file systems, file systems *are* designed to be file systems.
2. BLOB-based code may be getting easier to write, but so is file IO.
3. BLOBs are the most un-portable thing in our universe. They not only have varying implementations between DBs, but also completely different infrastructures at the application programming level--e.g. between C++, C#, Delphi, etc.--all using ADO.
4. Then there are size limitations. I am not sure what it is for SS2K, but to be robust you would need to account for blocking of your blobs should the limit be exceeded (see point #1).
5. BLOBs are not readily administered--i.e. an Admin of the OS could easily open up files, to see what's in them, view date/time creation, etc.
6. My success with file upload/storage using .NET and the model of pointers in the db has been very good. If ease of code is the concern, you'll be happy to know that such a solution can be completed in very little code.
And for me, the main thing is point #1. Databases are not designed as file systems. If you are designing a system big enough to warrent a SQL Server cluster (@#$% expensive last time I checked) then you can convince your client to invest $50 for some extra HDD space. That way your SQL Servers are not bogged down shoving files around. Adding another web-server or file server is MUCH CHEAPER that adding another SQL Server.
With clustered servers, it depends on the exact setup, but normally the servers will each run SQL Server and have a copy of the entire database on each of them, so they can do queries independantly (to some extent) of the others. If you now start shoving 5meg files into these servers, you'll suck up their memory and waste the most expensive resource in your setup (SQL Server).
0
Pierre
9/8/2003 2:20:00 PM
I'll throw in my 2 cents here.  I posted quite a while ago, and due to the responses, decided to use the OS to be my file manager.  I now have a directory on our server full of pdfs.  These pdfs are not named anything significant, are simply numerically named and sorted.  The file names are stored in a table along with their associated information.  Just the file name is stored, not the path.

My program has a .ini file associated with it which gives the UNC path to the directory. The program then appends the file name it finds from a SQL database.
It's super fast, works wonderfully, the files are easily backed up and restored without having to deal with SQL restorations (or the dba) and can be viewed and monitored using Adobe Acrobat reader, which is free. I also don't have to do anything special to display them, just send them to Internet Explorer and the free plug-in does it all for me.
I'd really advocate using the OS instead of BLOBs. My job is much easier, and it's far easier for my dba to handle if something does go wrong. They're also easily transferable and it's a simple change to the ini file to point at the new location.
:)
Dawn
0
djoyn
9/8/2003 3:01:45 PM
ya rich, is there a reason why you can't store all files to a shared network folder?
0
rox
9/8/2003 3:17:14 PM
This post really interested me as I want to upload Word docs but let users do keyword searches against them all to return documents which interest them.

I'm investigating BLOBs and the Full-Text Index on SQL Server 2000 but I'm not yet decided.
Can you keyword searches on Word docs if you hold them on the filesystem?
0
calcium
10/9/2003 11:10:32 AM
You need to understand that Word docs are not text-based but binary.

I think the only way to properly search them is to use Word automation to start the Word application up and then use it to search a Word doc.
view post 356715 for a link on Word automation.
In this case, I still think the best solution would be to store a pointer to the file in your database rather than the binary file itself.
0
rox
10/9/2003 2:45:11 PM
Thanks for the reply.

Just to fill you in with the specifics....my app will have 400 Word docs being uploaded per year by a rotating set of users and my wish is that future users will be able to search across all of the documents to find a number of which they are interested in, not just accessing one document at a time and searching through each one (although maybe that's not what you mean). The link you gave me implied client side functionality on single files (I think!). This link here on full-text indexes suggests you can save word documents as BLOBs and use SQL Server's filters and a full-text index to perform such a search server-side.
I haven't tried if it works yet but would welcome any feedback from attempts successful or otherwise?? I'm particularly confused about the SQL syntax for inserting the file into the database which is detailed at the link above.
Re: You need to understand that Word docs are not text-based but binary.
I'm assuming that uploading files just to the filesystem would limit you to searches on the directories and filenames under which they are stored?
As a relative newcomer, I'm just looking for a uncomplicated solution which will scale.
0
calcium
10/9/2003 3:22:40 PM

Rich,

  Here is the link which shows how to store and retrieve file from Sql Server.

http://chiragrdarji.wordpress.com/2007/08/31/storing-and-retrieving-docpdfxls-files-in-sql-server/

 

 

0
chirag_darji
9/28/2007 11:11:21 AM
Reply:

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

Sql Server Script File(.sql) Execution by Vb.net Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

creating ssis packagte for sql server to sql serer using C#.net
Hi friendsdoes any one has code or urls which can help me to create the ssis pacakage which will transfer data from sql server to sql server databaseswith some manipulationsthanks so much Lad LaxmikantPune You are probably in the wrong forum.  This forum is for questions relating to ASP.NET Dynamic Data. thanks,David...

Storing Excel File in SQL DataBase Using .NET
Please any one tell me how to Store excel sheet in DataBase using our .NET Thanks in ADVANCE Try this article by Steve Orr:http://steveorr.net/articles/EasyUploads.aspxDarrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you!...

How to store / retrieve PDF or Image Files in Sql Server
Here is sample code on how to store and retrieve image files using SQL Server. There are a couple things you need to know. 1. If storing PDF files and you wish to allow the end user to view them using the browser you must have them open adobe and click Edit>Preferences>options and uncheck open in browser. This will allow the application to start running and interpret the binary information. Failure to do this will result in a blank web page. 2. Using standard gif, jpeg and such will open directly in the browser no problems. 3. I suggest you store you file type in the dat...

How to read Transaction Log files from SQL Server using .NET
Hi all,Is anyone of you knowing about how to read .ldf (Transaction Log) files from SQL Server using .NET.e.g, suppose i m having following Database structure:database : testtable : tbltestfield : fldtestNow, i m inserting new record in blank table of fldtest using front-end application and now i want to write this event of inserted record in file or eventlog..with all necessary details contained in Transaction Log files - like  table name,time range,descriptions,field values etc.How to do that for specific to handle SQL Server events of Insert,Update,Delete? only.Awaiting for your...

Writing Stored Procedure in .NET 1.1 and using in SQL Server 2005
Hi, I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0. Please refer some good tutorial for it. Regards,Imran GhaniImran Abdul Ghani SQL Server requires 2.0. The .net team made a number of changes to the memory manager and gc so .net could be hosted in SQL Server....

How to insert Video Files into sql server and retrieve the them and play in aps.net page?
How to insert Video Files into sql server and retrieve the them and play in aps.net page? thank you guys This is a pretty complicated question with a long answer.  I think the first question is "Are you sure / why do you really want to store the files themselves in SQL Server?"  The easiest and more common method is to just store the location of the file on the disk, then render the page and just write out the link (in one way or another) to the page....

This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers
When I connect SQL Server 2005 to a remote database i get the error: "This version of Microsoft Sql Server Management Studio can only be used to connect to Sql Server 2000 and Sql Server 2005 servers". Can I confirm it is because: the remote db is of SQL Server 2008? There is a soln mentioned in http://forums.asp.net/t/1324047.aspx Did anybody try this (installing 2008 express: http://www.microsoft.com/express/sql/download/)? I am just checking again, whether it will disturb my existing SQL Server installation, in any means...? Though it is mentioned that it will g...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

Storing AES encrypted files in SQL Server and retrieving these files to decrypt them.
Hi folks,Ive been working on a little app that lets u choose a file from your PC and encrypts it using AES encryption(Rijndael). The encrypted bytes are stored in Sql Server. I wanted to be able to retrieve the encrypted bytes so I can decrypt them. The process works fine. However when i open the decrypted file I have a ton of gibberish looking characters appended file but the decrypted text is  fine. Can my goals even be achieved with this method? I have heard there are issues with doing this due to how SQLServer stores binary and padding issues with AES. Here is the code.  &...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...

Using Sql Server with VB.net
I am trying to create an asp.net application that will delete an item from inventory when the itemid is entered into a textbox and a button is pushed I only wnat it to delete the item if the QOH is 0 and then return the corresponding item name, and return an error if the item is still in stock. Could anyone help me on the stored procedure. The only table involved is the inventory table. "DELETE FROM <TableName> WHERE ItemID = @ID AND QOH=0" if records affected > 0, woop woop. else, errormessage="Who you talkin' bout willis?"; like my psuedo-code?Ka...

Web resources about - Storing PDF files in SQL server and retrieving them using ASP.net - asp.net.sql-datasource

Mother of three killed landlord after he tried to stop her retrieving her son's shirt
A mother of three has been found guilty of killing her landlord after he tried to stop her retrieving a red polo shirt her son needed for school ...

Retrieving bodies a 'horrifying' experience (01:43)
Anti-jump measures on the Story Bridge should be "fast tracked" and water police funding increased so CityCats don't have to pick up bodies. ...

Man drowns retrieving toy boat
A man has drowned after entering the Wimmera River at Horsham to retrieve a remote-controlled boat.

Bishop says there is no time limit on retrieving MH17 remains
THE Australian Federal Police have warned that it is too dangerous to approach the MH17 crash site in war-torn eastern Ukraine until security ...

Search underway for man swept away in Brisbane River while retrieving toy boat
A search is underway for a man swept away in the Brisbane River while retrieving a toy boat.

DPRK blames US for suspension of retrieving American soldiers' remains
DPRK blames US for suspension of retrieving American soldiers' remains People's Daily Online PYONGYANG, Oct. 13 The Democratic People's Republic ...

Father killed retrieving Christmas gifts from broken-down car
Richard Perrin, 57, was struck and killed by another driver early Saturday along Hwy 170 in Roanoke.

Yankee retrieving a big Canada
Yankee retrieving a big Canada

iPhone dev: Retrieving user phone numbers
... a secret to recovering user phone numbers that does not involve the … One of the most common questions I get these days has to do with retrieving ...

Girl stuck in Dover storm drain retrieving phone
A teenage girl gets stuck in a storm drain in Dover while trying to retrieve her mobile phone which had fallen into it.

Resources last updated: 1/15/2016 3:08:23 AM