The database is prepared to save the PDF in a field of type VarBinary(MAX)
.
How do I read this field later?
The database is prepared to save the PDF in a field of type VarBinary(MAX)
.
How do I read this field later?
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.