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.

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.

|||

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

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

|||

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

|||

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

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

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.

|||

Here's how to save:

ProtectedSub btnSave_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnSave.Click

If FileUpload1.HasFileThen

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdInsertAsNew 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)AsByte

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

EndIf

Dim xAsInteger = cmdInsert.ExecuteScalar

conn.Close()

EndIf

EndSub

Here's how to retrieve:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdAsNew SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn)

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

Dim drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

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

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

Dim buffer()AsByte = dr("Data")

Dim blenAsInteger =CType(dr("Data"),Byte()).Length

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

Response.End()

EndSub