Solved

How to store a retrieve a PDF in SQL Server

Posted on 2013-02-06
4
544 Views
Last Modified: 2013-02-07
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
Comment
Question by:kpbarem
4 Comments
 
LVL 18

Assisted Solution

by:John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou earned 100 total points
ID: 38860141
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38860171
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 400 total points
ID: 38862951
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
 

Author Closing Comment

by:kpbarem
ID: 38866075
Thank you!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now