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

2

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

How do I read this field later?

    
asked by anonymous 14.02.2015 / 21:45

1 answer

1

You can even do this and I'll teach you how, but I would like to warn you that this is not a good practice, the best thing to do is save this file to the Server (preferably encrypted) and save only the path to it in the database.

As 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 above table

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 Template

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'll post an example of how to save this entity to the Bank 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 , so you can use a MemoryStream to send the file through a WebService or a FileStream if you want to save the file to disk.

    
15.02.2015 / 01:15