How can save a PDF file to a SQL table using an ASP.NET webpage?

Question

This looks like a huge pile, but it's actually a very focused question. This looks bigger than it is because I am providing context and what I have been able to work out so far.

Let me start with the question in more precise terms: "Using an ASP.NET webpage, how can I: (a) "attach" a saved PDF file to a database table using a Formview, and (b) allow the user to view that saved PDF file when the database table row is selected in a Gridview?"

While I can easily store the path and filename of the PDF file, if the PDF file is renamed, moved, or deleted, the database record now has a broken link. My client has requested that I "attach" the actual PDF file to the database record to prevent broken links.

That answers the why: because my client requested it. Now it's a matter of figuring out how.

Here is what I have done up to now in my research:

1. I learned how to enable Filestream for a SQL Server 2012 database.
2. I created a table where one of the columns is a varbinary(max). (Table definition language shown below in "Code Block #1".)
3. Using available online examples, I was able to test and verify a working T-SQL script -- however, I have not yet succeeded in making this a stored procedure because I do not know how to make the filename a variable in an "Openrowset" statement . (Script shown below in "Code Block #2".)

Where I'm drawing the big blank is the ASP.NET side of the equatiion. Here is the system I hope to set up. I'm not as restricted in terms of the details so long as they work along these lines.

1. User uses the Formview (connected to the database via SqlDataSource) to type in the values entered on the paper form, and finally "attach" the saved PDF file to the "Scanned_PDF_File" field.
2. A gridview immediately refreshes, showing the results from the "Scanned_PDFs" table, allowing the user to select a row and view the saved PDF file.

Is this approach possible? Any directions of further research would be greatly appreciated. Thank you for your help.

---

Code Block #1: Here is the definition of the SQL database table.

USE [IncidentReport_v3]
GO

/****** Object:  Table [dbo].[Scanned_PDFs]    Script Date: 1/13/2015 11:56:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Scanned_PDFs](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DateEntered] [date] NOT NULL,
    [Scanned_PDF_File] [varbinary](max) NOT NULL,
    CONSTRAINT [PK_Scanned_PDFs] 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] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

---

Code Block #2: This is the T-Sql Script I used to test the ability to insert a row w/a PDF file. It works great as a proof of concept if I hand-type the PDF file name and path, but I will need to make that filename a variable that the user supplies. I envision using this as a stored procedure -- or perhaps I could use this code on the client side? Not sure yet.

USE IncidentReport_v3;
GO

DECLARE @pdf AS VARBINARY(max)

SELECT @pdf = cast(bulkcolumn AS VARBINARY(max))
FROM openrowset(BULK '\\wales\e$\test\test.pdf', SINGLE_BLOB) AS x

INSERT INTO dbo.Scanned_PDFs (
    DateEntered,
    Scanned_PDF_File
    )
SELECT cast('1/12/2015' AS DATE),
    @pdf;
GO

Answer 1

You'll want to convert the PDF to a byte array before getting to the data layer. The [Scanned_PDF_File] gets set to the result. You can parse the file name or take it from some other value.

This link here, might give you everything you need.