FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.  https://msdn.microsoft.com/en-us/library/gg471497.aspx

To learn more about sql file streams I decided to create simple image gallery.

image

 

 

First of all you need to enable File stream for sql server instance.

Then create new file stream for database:

image

 

Specify where to save file stream data by adding new file:

image

 

Table that has file stream column also must have not null, unique rowguidcol column and varbinary column for file content. File content column data will be saved separately and fetched from file during query execution.  Sample table that also has title and primary key:

CREATE TABLE [dbo].[Files](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [IdFile] [uniqueidentifier] unique ROWGUIDCOL  NOT NULL,
    [Title] [nvarchar](max) NULL,
    [File] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
))

GO

ALTER TABLE [dbo].[Files] ADD  CONSTRAINT [DF_Files_IdFile]  DEFAULT (newid()) FOR [IdFile]
GO

 

Entity framework model:

[Table("Files")]
public class FileModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public byte[] File { get; set; }
}

Note that IdFile column is not present, it has default value and is of no use for us, it is used by sql server.

 

View model used for creating files:

public class FileViewModel
{
    public string Title { get; set; }
    public HttpPostedFileBase File { get; set; }
}

 

To save files with entity framework we don’t need any specific code, just read all bytes of received file:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "Title,File")] FileViewModel fileModel)
{
    if (ModelState.IsValid)
    {
        var fileData = new MemoryStream();
        fileModel.File.InputStream.CopyTo(fileData);

        var file = new FileModel { Title = fileModel.Title, File = fileData.ToArray() };
        db.FileModels.Add(file);
        db.SaveChanges();

        return RedirectToAction("Index");
    }

    return View(fileModel);
}

 

To display images we can simply convert bytes to base64 string:

<td>
    @{
        var base64 = Convert.ToBase64String(item.File);
        var imgSrc = String.Format("data:image/gif;base64,{0}", base64);
    }
    <img src="data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==" data-src="@imgSrc" style="max-height:250px"/>
</td>

 

After uploading several files, go to file stream location that you specified.

image

You should see one or several folders with guid names, in one of these folders should be files without extensions also with guid names:

image

These are uploaded images, you can open it with any image viewer, even paint will do the trick.

image

 

Source code.

Database back up included in source (FilesTable.bak).

Comments


Comments are closed