Save and Retrieve PDF Files in SQL

Using ASP.net I need to be able to save and retrieve PDF Files in SQL.

I believe the best way to do this is through a BLOB datatype.

I have been searching (without luck) for a tutorial/code sample explaining how to do this.

Any help would be greatly appreciated.

Chris

p.s. I know many prefer to store the files on the server and simply store pointers to the files in SQL, but I need to store the actual files in SQL.

0
CMessineo
9/20/2006 6:48:07 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
427 Views

Similar Articles

[PageSpeed] 26

Search for storing images inside SQL Server, the idea is the same.  A blob is a blob.

The answer has been given at least 3 times in this forum alone once you know that.

0
Motley
9/20/2006 10:16:34 PM

Thanks for the response.

Unfortunately, my searching skills must not be that good.

I'm having trouble finding an answer to my question (I did find someone else asking the same questio, but you responded to him suggesting he search as well). Smile

In any case, attempting to search the web I have found conflicting advice.  In particular people are recommending different datatypes for storage (BLOB, Image, and VarBinary).

Specifically I need to store about 1,000 PDF's in my table (each pdf will be under 100k in size).

Any suggestions on the best datatype to use?  That should help me with my searching.

Thanks a lot,

Chris

0
CMessineo
9/25/2006 8:23:21 PM

The create table statement below is from AdventureWorks modify it for your use and try the thread below take your pick of code sample.  If you know the files will be 100k you can use the thumbnail but run some tests.   Hope this helps.


CREATE TABLE [ProductPhoto] (
 [ProductPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
 [ThumbNailPhoto] [image] NULL ,
 [ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [LargePhoto] [image] NULL ,
 [LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductPhoto_ModifiedDate] DEFAULT (getdate()),
 CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY  CLUSTERED
 (
  [ProductPhotoID]
 )  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


http://forums.asp.net/thread/1401879.aspx


Kind regards,
Gift Peddie
0
Caddre
9/25/2006 9:25:42 PM

Caddre,

Thanks for trying to help.  I'm not sure I understand your reply though.  I know how to create a table and records, my questions are:

1. Should I be using an Image, Blob, or VarBinary datatype to store pdf's?

2.  Once I know what type I should be using is there some sample code for storing and retrieving that data?

Thanks though,

Chris

 

0
CMessineo
9/25/2006 9:39:22 PM
Varbinary is like Varchar meaning variable length PDF is IMAGE because the file will never change and there are several codes to store you need ExecuteNonQuery, to retrieve you need ExecuteReader or ExecuteScalar and those code are in Programming .NET by Jeff Prosise or in the link I posted.  Hope this helps.
Kind regards,
Gift Peddie
0
Caddre
9/25/2006 9:47:56 PM

Image.

SQL Server does not have a "BLOB" data type.

Varbinary is limited to 8000 characters (in SQL Server 2000), so unless every PDF you want to store is less than 8k, this is a poor choice.  There are other reasons as well, but the size limit should kill the idea by itself.

If you are using SQL Server 2005 (or later), you can use varbinary(max), but I see little reason to switch from image which works in both.

0
Motley
9/25/2006 9:50:28 PM

Here's how to save: 

Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

If FileUpload1.HasFile Then

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdInsert As New SqlCommand("INSERT INTO Attachments(MimeType,Filename,Data) VALUES (@MimeType,@Filename,@Data) SELECT SCOPE_IDENTITY()", conn)

cmdInsert.Parameters.Add(New SqlParameter("@MimeType", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@Filename", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@Data", SqlDbType.Image))

Dim bArray(FileUpload1.PostedFile.ContentLength - 1) As Byte

FileUpload1.PostedFile.InputStream.Read(bArray, 0, FileUpload1.PostedFile.ContentLength)

cmdInsert.Parameters("@MimeType").Value = FileUpload1.PostedFile.ContentType

cmdInsert.Parameters("@Filename").Value = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName).ToLower

cmdInsert.Parameters("@Data").Value = bArray

End If

Dim x As Integer = cmdInsert.ExecuteScalar

conn.Close()

End If

End Sub

Here's how to retrieve:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmd As New SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@ID", conn)

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = Request.QueryString("ID")

Dim dr As SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader("Content-type", dr("MimeType"))

Response.AddHeader("Content-Disposition", "inline; filename=""" & dr("Filename") & """")

Dim buffer() As Byte = dr("Data")

Dim blen As Integer = CType(dr("Data"), Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

End Sub

0
Motley
9/25/2006 10:44:42 PM

However, storing it as blob will be very heavy for SQL to handle eventually.

Is there a c# code that will convert any file (like a pdf file) to binary?


Web developer by day My programming blog,

Musician by night Musician's blog.
0
Musikero11
1/30/2009 5:41:11 AM
Reply:

Similar Artilces:

How to save Uploaded .pdf files to an sql database filed ? Please I Nedd Help It Very Urgent
I created insertdatafilelink.aspx to Insert an Uploaded .pdf files to a filed then when I run insertdatafilelink.aspx it inserts all data to all filed and Uploads the .pdf files to the folder Documents put it doesn't save the .pdf files to the fied file_link. This is the code I used Please I want to know where I made the mistake. 1-This Is the insertdatafilelink.vbPartial Class Insertdatatfilelink Inherits System.Web.UI.PageProtected Sub New_mail_system_ItemInserting(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _ Handles New_mail_system.ItemInserting 'Refere...

How to save pdf files to SQL database?
Hi all, Can someone please brief me on how to save 'pdf' files(there are about 15 files) into a SQL database and retrieve/open it from a datagrid? I have about 15 pdf files on my webserver and need to save it in the SQL database, so every pdf file saved in the database will have a primary key and the file will be saved either as 'ntext' or 'binary' type...I just have this rough idea. If someone has a ready code in VB.net then nothing better( i know thats a shortcut :) but it will help me like a tutorial. C# code will do too, I can then convert the code to Vb.net Thanks a ton.Aartee...we ...

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

File saving/retrieving to SQL server ..weird
Hi all i am trying to save a file to sql server and than retrieve it and save it to web server to get some info. code for uploading file to sql server is. i have debugged this code and it seems to work just fine as size of binaryCert is always the same as size of file i am uploading and Try Dim strm As FileStream = Nothingstrm = New FileStream(fileUpload.PostedFile.FileName, FileMode.Open) ReDim binaryCert(CType(strm.Length, Integer) - 1)strm.Read(binaryCert, 0, CType(strm.Length, Integer)) strm.Close() Return True Catch ex As Exception  i am adding this byte() as parameter t...

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

Upload and Save a PDF file into MS SQL Databse
Hi guys,  I want to update a PDF file and store it in the MSSQL database. (I know it is better if I save the file on server and just store the link to it, but I have to store the file on the database "Project Requirements") I would probably use FileUpload control to upload the file. but would I upload it to server temporarly and then save it or would I just upload it to DB. I appreciate your help and suggestions and any tips & tricks or issue that I have to consider. Regards,MehdiMehdi Entezary~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Please click “Mark as Answer” on the p...

Writing and retrieving image file from Sql 2005 with .net
Hi, i have been looking around and can not find and example that i can follow to make this work. I am using an fileupload control to retrieve a file and than load that file into the sql database. Seems simple but i can not get this to work. I then need to retrieve the image and display it with their other information. If anybody can help me i would be much appreciative. I have some code put together but it is bulky and of course WRONG but if you want me to post it i will. Thank you in advance for your help. kashwmu: Hi, i have been looking around and can not find and example that i can ...

is it possible to save PDF file in sql 2005 database table?
Hi:Just want to know: "is it possible to save PDF file in sql 2005 database table?", so far I know there is no problem save files such as: .doc; .txt, .jpg.....But I don't find any info to save PDF file.ThanksJT Yes, is actually same like what you mentioned. Which mean same like save .doc; .txt; .jpg method. Just you database structure need to create a filecontent with binary data type will do.Regards,Yeoh Eik DenSoftware Engineer  Hi! JT, here is the link that I used to store pdf. http://aspnet.4guysfromrolla.com/articles/120606-1.aspx Once you downlo...

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

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

Sql 2005 mdf files uprage to Sql 2008 mdf files ?
hiwhat is different between SQL 2008 Server and SQL 2005 Server ?SQL 2008 Server is more fast for searching ?i created my *.mdf at Sql 2005. does it work at SQL 2008 ? does it work same performance with *.mdf that created with SQL 2008 ? can i uprage my *.mdf for sql 2008 ? what should i do ? ThanksMark as me if my question or my answer can be helpful for you :) I posted this link before http://www.angryhacker.com/blog/archive/2008/06/20/10-reasons-why-sql-server-2008-is-going-to-rock.aspx You can upgrade your MDF to SQL Server 2008, but you would not be able to downgrade.Beware of bu...

Reading Fixed length file to save data into SQL datatable using VB.NET
Hello all,   I have a SQL 2000 database table named tblRoutingNumbers which has the following design:   ColumName -DataType -Length ABANumber-    varchar      - 20 BankName        -varchar      -50   And I have a text file: (fixed length) which looks like exactly like this:   011000015O0110000150020802000000000FEDERAL RESERVE BANK                1000 PEACHTREE ST N.E 011000028O0...

Is is possible to run a .SQL file on SQL Server without converting file contents to a string?
Is it possible to run a .SQL file without converting the contents of the file to a string and then executing the string? Is it possible to run or execute the file? Thanks You can use the .sql file as an input file to ISQL using the -i switch "Kevin Wiebe" <kevin.wiebe@riseinc.com> wrote in message news:BjSh5G7GCHA.196@forums.sybase.com... > Is it possible to run a .SQL file without converting the contents of the > file to a string and then executing the string? Is it possible to run or > execute the file? > Thanks > > ...

how to save file (stored on SQL server) to file/web server?
Hi Experts, I got files stored on SQL server db. I would like to be able to save them onto file/web server. I know how to retrieve them, but not sure how to save to file/web server. Please help. Thanks so much. Below are codes for retrieving file info from sql server:Dim strFileName As String = Quote.GetQuoteFileByQuoteFileID(CInt(selectedQuoteFileID)).Tables(0).Rows(0).Item("QuoteFileName")Dim strFileType As String = Quote.GetQuoteFileByQuoteFileID(CInt(selectedQuoteFileID)).Tables(0).Rows(0).Item("QuoteFileType")Dim strFileSize As Integer = Quote.GetQuoteFileByQuoteFileID(CInt(selectedQ...

Web resources about - Save and Retrieve PDF Files in SQL - asp.net.sql-datasource

Facebook Developers Can Retrieve Users’ Profile Pictures In Different Sizes
Facebook introduced a way for developers to retrieve users’ profile pictures for use within their applications in different sizes, rather than ...

Winston retrieves the news - Flickr - Photo Sharing!
... food and losing weight. Three months ago, we were told he had lymphosarcoma of the GI tract. On March 10, 2008, Winston was called to go retrieve ...

Dolphin retrieves phone for a lady after it fell in the ocean - YouTube
Dolphin retrieves phone for a lady after it fell in the ocean

Man killed by train after jumping on tracks to retrieve something
A man has been killed by a train after jumping onto the tracks to retrieve something at Wentworthville Station.

People Are Willing To Go To Extreme Lengths To Retrieve Their Stolen Smartphones
People are willing to pay a ton of money and potentially put themselves in danger to retrieve their stolen smartphones, a new survey has found. ...

NSA surveillance program can retrieve, replay phone calls
The NSA has built a voice interception program capable of recording 100 per cent of a foreign country's calls and replaying voices from calls ...

Divers retrieve body from NSW floodwaters
A woman's body has been retrieved from a submerged car in a creek in Maitland.

Tourist plunges to death from Potts Point rooftop park trying to retrieve football: police
A French tourist who fell to his death from a rooftop park in Potts Point in inner Sydney was attempting to retrieve a football that had gone ...

Rescuers retrieve bodies after Brazilian tour bus crash kills 54
At least 54 people have been killed after a tour bus plunged hundreds of metres into a densely wooded ravine in southern Brazil, authorities ...

Investigators retrieve more human remains at MH17 crash site in eastern Ukraine but wreckage cannot yet ...
Dutch forensic experts recover further human remains at the crash site of downed flight MH17.

Resources last updated: 12/5/2015 4:32:51 AM