How to save a PDF file to SQL Server 2008 using C#

Question

The database is prepared to store the PDF in a field of type VarBinary(MAX).

How to read this field later?

Answer 1

You can even do it and I will teach how, but I would like to warn you that this is not a good practice, the best to be done is to save this file in the Server (encrypted preference) and save only the way for it in the database.

Since you did not specify the format of your table, I will use the following:

CREATE TABLE [dbo].[File](
    [FileGUID] [uniqueidentifier] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [Extension] [varchar](10) NOT NULL,
    [CreationTime] [datetime2](2) NOT NULL,
    [LastAccessTime] [datetime2](2) NOT NULL,
    [LastWriteTime] [datetime2](2) NOT NULL,
    [Length] [bigint] NOT NULL,
    [FileStream] [varbinary](max) NULL,
CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
    [FileGUID] ASC
)

Below the template for the table above

Model

public partial class File
{
    public System.Guid FileGUID { get; set; }
    public string Name { get; set; }
    public string Extension { get; set; }
    public System.DateTime CreationTime { get; set; }
    public System.DateTime LastAccessTime { get; set; }
    public System.DateTime LastWriteTime { get; set; }
    public long Length { get; set; }
    public byte[] FileStream { get; set; }
}

Filling in the Model

var sample = new FileInfo(@"D:\Sample.pdf");
if (sample.Exists) {
    var fileBinary = new byte[sample.Length];
    using (var fileStream = sample.OpenRead()) {
        fileStream.Read(fileBinary, 0, (int)sample.Length);
    }

    var fileName = sample.Name.Substring(0, sample.Name.Length - sample.Extension.Length);
    var fileExtension = sample.Extension.Substring(1);
    var fileSample = new File {
        FileGUID = Guid.NewGuid(),
        Name = fileName,
        Extension = fileExtension,
        CreationTime = sample.CreationTime,
        LastAccessTime = sample.LastAccessTime,
        LastWriteTime = sample.LastWriteTime,
        Length = sample.Length,
        FileStream = fileBinary
    };

    //Logica para inserir no Banco
}

As you also did not specify the technology being used in your project, I will post an example of how to save this entity in the Database using Entity Framework and ADO.NET

Entity Framework

using (var context = new FileManagerEntities()) {
    context.Files.Add(fileSample);
    context.SaveChanges();
}

ADO.NET

var sqlCommandString = @"
    INSERT INTO [File]
    VALUES (
        @FileGUID, 
        @Name, 
        @Extension, 
        @CreationTime, 
        @LastAccessTime, 
        @LastWriteTime, 
        @Length, 
        @FileStream
    );
";

var transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    var sqlConnectionString = ConfigurationManager.ConnectionStrings["FileManagerConn"].ConnectionString;
    using (var sqlConnection = new SqlConnection(sqlConnectionString))
    {
        sqlConnection.Open();
        using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
        {
            sqlCommand.Parameters.Add("@FileGUID", SqlDbType.UniqueIdentifier, 16).Value = fileSample.FileGUID;
            sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 255).Value = fileSample.Name;
            sqlCommand.Parameters.Add("@Extension", SqlDbType.VarChar, 10).Value = fileSample.Extension;
            sqlCommand.Parameters.Add("@CreationTime", SqlDbType.DateTime2, 6).Value = fileSample.CreationTime;
            sqlCommand.Parameters.Add("@LastAccessTime", SqlDbType.DateTime2, 6).Value = fileSample.LastAccessTime;
            sqlCommand.Parameters.Add("@LastWriteTime", SqlDbType.DateTime2, 6).Value = fileSample.LastWriteTime;
            sqlCommand.Parameters.Add("@Length", SqlDbType.BigInt, 8).Value = fileSample.Length;
            sqlCommand.Parameters.Add("@FileStream", SqlDbType.VarBinary, -1).Value = fileSample.FileStream;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.ExecuteNonQuery();
        }
    }
    scope.Complete();
}

Finally the part that really interests you, read the previously saved file:

Entity Framework

var fileSample = default(File);
var fileGUID = new Guid("0C0281E5-0FCD-4324-B25A-77F5544CA106");

using (var context = new FileManagerEntities()) {
    fileSample = context.Files.Find(fileGUID);
    context.SaveChanges();
}

ADO.NET

var fileSample = default(File);
var fileGUID = new Guid("0C0281E5-0FCD-4324-B25A-77F5544CA106");

var sqlCommandString = @"
    SELECT 
        FileGUID,
        Name,
        Extension,
        CreationTime,
        LastAccessTime,
        LastWriteTime,
        Length,
        FileStream
    FROM [File]
    WHERE FileGUID = @FileGUID
";

var transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    var sqlConnectionString = ConfigurationManager.ConnectionStrings["FileManagerConn"].ConnectionString;
    using (var sqlConnection = new SqlConnection(sqlConnectionString))
    {
        sqlConnection.Open();
        using (var sqlCommand = new SqlCommand(sqlCommandString, sqlConnection))
        {
            sqlCommand.Parameters.Add("@FileGUID", SqlDbType.UniqueIdentifier, 16).Value = fileGUID;
            sqlCommand.CommandType = CommandType.Text;

            using (var sqlReader = sqlCommand.ExecuteReader())
            {
                if (sqlReader.HasRows)
                {
                    sqlReader.Read();
                    fileSample = new File();
                    fileSample.FileGUID = sqlReader.GetGuid(0);
                    fileSample.Name = sqlReader.GetString(1);
                    fileSample.Extension = sqlReader.GetString(2);
                    fileSample.CreationTime = sqlReader.GetDateTime(3);
                    fileSample.LastAccessTime = sqlReader.GetDateTime(4);
                    fileSample.LastWriteTime = sqlReader.GetDateTime(5);
                    fileSample.Length = sqlReader.GetInt64(6);
                    fileSample.FileStream = sqlReader.GetValue(7) as byte[];
                }
            }
        }
    }
    scope.Complete();
}

Now all you need to do is manipulate the bytes in fileSample.FileStream, for this you can use a Memorystream to send the file via a Webservice or a Filestream if you want to save it to disk.