• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

How to store a retrieve a PDF in SQL Server

I have an application that i've written in asp.net that allows a user to select a 1 page PDF file from their computer and upload it to a SQL Server database.  A second application I've written retrieves the PDF file and displays it to a user.  

There is a lot more to this application but these are the basic concepts of this problem.  Ideally when the user of the first application hits the upload button I will call a stored procedure that passes in the PDF from their computer and stores it to the database.  

Then when the user of the second application clicks a link I can retrieve the PDF file from the database and display it to the user.  I've read that you can use VARBINARY data type to store the PDF but i'm not sure how to do this.

Has anyone done anything like this and could provide me some sample code?  Any help you could offer would be appreciated.

Thanks experts!
0
kpbarem
Asked:
kpbarem
2 Solutions
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
0
 
Barry CunneyCommented:
If you are using SQL Server 2008 it may be worth looking into FILESTREAM
http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/
0
 
Alan WarrenApplications DeveloperCommented:
You'll need a table in a SQL Server catalog:
USE [YourDbCatalog]
GO

/****** Object:  Table [dbo].[tblPDF]    Script Date: 02/07/2013 15:44:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblPDF](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](250) NOT NULL,
	[Data] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_tblPDF] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Open in new window

You'll need a stored procedure to insert data into the SQL Table:
USE [SQL2005_383580_akojo]
GO
/****** Object:  StoredProcedure [dbo].[alz_PDF_Ins_or_Upd]    Script Date: 02/07/2013 13:41:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[alz_PDF_Ins_or_Upd] 
   @Name varchar(250)
  ,@Data varbinary(MAX)
  ,@ID int = null output
AS
BEGIN

    DECLARE @TranStarted BIT
    DECLARE @ErrorCode INT

    SET @ErrorCode = 0
    SET @TranStarted = 0

	BEGIN TRANSACTION
	SET @TranStarted = 1

	IF NOT EXISTS(	SELECT * FROM [dbo].[tblPDF] WHERE ID = @ID)
	
	BEGIN
		-- do an insert
		INSERT INTO [dbo].[tblPDF] ([Name],[Data]) VALUES (@Name,@Data)
	END
	ELSE
	BEGIN
		-- do an update
		UPDATE [dbo].[tblPDF] SET [Name]=@Name,[Data]=@Data WHERE ID=@ID
	END
		
	IF( @@ERROR <> 0 )
	BEGIN
		SET @ErrorCode = -1
		GOTO Cleanup
	END

	IF (@TranStarted = 1)
	BEGIN
		SET @TranStarted = 0
		
		-- If no ID was provided, we would have done an insert, so return the ID of the inserted record
		IF ISNULL(@ID,0) = 0 
		BEGIN
			SELECT @ID = SCOPE_IDENTITY()
		END 
		COMMIT TRANSACTION
	END
	SET @TranStarted = 0
	-- Debugging if we have an ID?
	-- SELECT @ID as ID
	RETURN 0
END

Cleanup:
    IF (@TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END


-- Testing
-- alz_PDF_Ins_or_Upd

Open in new window

You'll need a page with an asp:FileUpload control to upload the file and parse the binary to the SQL Sored Procedure
<%@ Page Language="VB" AutoEventWireup="false" Title="PDF upload to SQL Server Example" %>

<!DOCTYPE html>
<script runat="server">
    
    Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim strErrMsg As String = ""
        Dim strPostedFilename As String = ""

        Dim intSize As Int32 = 0

        Dim intID As Integer


        ' Before attempting to save the file, verify
        ' that the FileUpload control contains a file.
        If (FileUpload1.HasFile) Then

            ' check the extension is valid
            Dim strExt As String = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower()

            If strExt <> ".pdf" Then
                strErrMsg = "Oops! " & strExt & " is not an acceptable PDF file type."
                GoTo ExitProcedure
            End If

            ' Check the file is not bigger than 2 meg
            strPostedFilename = FileUpload1.PostedFile.FileName
            intSize = FileUpload1.PostedFile.ContentLength

            If intSize > 2048000 Then
                strErrMsg = strPostedFilename _
                + " is too large (" _
                + Format(Convert.ToInt32(intSize.ToString), "###,##0") & ") bytes" _
                + " The largest acceptable file size is 2,048,000 bytes (2mb)."

                GoTo ExitProcedure
            End If


            ' Read the input stream into a Byte array
            Dim PostedFileBytes(FileUpload1.PostedFile.InputStream.Length) As Byte
            FileUpload1.PostedFile.InputStream.Read(PostedFileBytes, 0, PostedFileBytes.Length)


            ' Create Instance of Connection and Command Object
            Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("cn").ConnectionString)
                Using myCommand As New System.Data.SqlClient.SqlCommand("alz_PDF_Ins_or_Upd", myConnection)

                    ' Mark the Command as a SPROC
                    myCommand.CommandType = System.Data.CommandType.StoredProcedure

                    ' Add parameters to the command object


                    ' Populate the Name parameter
                    Dim prmName As New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar, 250)
                    prmName.Value = strPostedFilename
                    myCommand.Parameters.Add(prmName)

                    ' Populate the Data parameter
                    Dim prmData As New System.Data.SqlClient.SqlParameter("@Data", System.Data.SqlDbType.VarBinary, intSize)
                    prmData.Value = PostedFileBytes
                    myCommand.Parameters.Add(prmData)

                    ' Populate the record ID parameter
                    Dim prmID As New System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Int)
                    prmID.Direction = Data.ParameterDirection.InputOutput
                    prmID.Value = 0
                    myCommand.Parameters.Add(prmID)

                    Try
                        ' Open the connection
                        myConnection.Open()

                        ' Execute the stored procedure
                        myCommand.ExecuteNonQuery()

                        'Grab the @ID output parameter's value...
                        If Not Convert.IsDBNull(prmID.Value) Then
                            intID = Convert.ToInt32(prmID.Value)
                        End If


                    Catch SQLexc As System.Data.SqlClient.SqlException
                        strErrMsg = SQLexc.ToString
                        GoTo ReportError
                    End Try

                End Using
            End Using

        Else
            ' Notify the user that a file was not uploaded.
            strErrMsg = "Oops! You did not specify a file to upload."
            GoTo ReportError
        End If


ExitProcedure:

        FileUpload1.Dispose()

        If strErrMsg = "" Then
            HyperLink1.NavigateUrl = "~/pdf-view.aspx?id=" & intID.ToString
            HyperLink1.Text = strPostedFilename
        End If

        Exit Sub

ReportError:

        If strErrMsg <> "" Then
            strErrMsg = "Error in ~/pdf-upload.aspx.vb_UploadButton_Click()<br />" & strErrMsg
        Else
            strErrMsg = "Error in ~/pdf-upload.aspx.vb_UploadButton_Click()<br />" _
              & "<br />" & "Error number " & CStr(Err.Number) _
              & "<br />" & " was generated by " & Err.Source _
              & "<br /><br />" & Err.Description
        End If

        Me.lblErrorMessage.Text = strErrMsg
        Me.lblErrorMessage.Visible = True

        GoTo ExitProcedure


    End Sub

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:FileUpload ID="FileUpload1" runat="server" Width="80%" />&nbsp;<asp:Button id="UploadButton" Text="Upload" OnClick="UploadButton_Click" runat="server" />
    <br />
        <asp:HyperLink ID="HyperLink1" runat="server" Text="" NavigateUrl="#" />
        <br /><br />
    <span style="color:#8B0000;text-align:center;"><asp:Label ID="lblErrorMessage" runat="server" EnableViewState="False" Text="" Visible="false" /></span>

    </div>
    </form>
</body>
</html>

Open in new window

And you'll need another page to Get the Binary Data from the SQL Server and render it as PDF to the screen:
<%@ Page Language="VB" Title="PDF extract from SQL Server Example" %>

<!DOCTYPE html>

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim strErrMsg As String = ""
        Dim ID As Integer = 0
        Dim objNameValueCollection As System.Collections.Specialized.NameValueCollection = Nothing

        If Context.Request.QueryString IsNot Nothing Then
            objNameValueCollection = Context.Request.QueryString
            If objNameValueCollection IsNot Nothing Then
                Try

                    If objNameValueCollection("id") IsNot Nothing Then
                        ID = objNameValueCollection("id").ToString
                    End If

                Catch ex As Exception
                    strErrMsg = ex.ToString
                    GoTo ReportError
                End Try
            End If
        End If


        If ID = 0 Then
            Exit Sub
        End If

        'Connect to the database and bring back the pdf contents
        Using myConnection As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("cn").ConnectionString)
            
            Using myCommand As New System.Data.SqlClient.SqlCommand("SELECT * FROM [dbo].[tblPDF] WHERE [ID] = @ID", myConnection)

                ' Dim myCommand As New SqlCommand(SQL, myConnection)
                myCommand.Parameters.AddWithValue("@ID", ID)

                myConnection.Open()

                Dim myReader As System.Data.SqlClient.SqlDataReader = myCommand.ExecuteReader
                If myReader.Read Then
                    Response.ContentType = "application/pdf"
                    Response.BinaryWrite(myReader("Data"))
                End If
                myReader.Close()
                myConnection.Close()
            End Using
        End Using


ExitProcedure:

        Exit Sub

ReportError:

        If strErrMsg <> "" Then
            strErrMsg = "Error in ~/pdf-view.aspx.vb.Page_Load()<br />" & strErrMsg
        Else
            strErrMsg = "Error in ~/pdf-view.aspx.vb.Page_Load()<br />" _
              & "<br />" & "Error number " & CStr(Err.Number) _
              & "<br />" & " was generated by " & Err.Source _
              & "<br /><br />" & Err.Description
        End If

        Me.lblErrorMessage.Text = strErrMsg
        Me.lblErrorMessage.Visible = True

        GoTo ExitProcedure
    End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
      <span style="color:#8B0000;text-align:center;"><asp:Label ID="lblErrorMessage" runat="server" EnableViewState="False" Text="" Visible="false" /></span>

    </div>
    </form>
</body>
</html>

Open in new window

Alan ";0)
0
 
kpbaremAuthor Commented:
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now