Store And Retrieve Using FILESTREAM From SQL Database

Today I will explain regarding storing and retrieving images, videos and other document files from SQL server using the FILESTREAM feature. If you are new to FILESTREAM then read my previous article FILESTREAM Feature In SQL Server.

We are taking one example with WPF application and SQL server 2008 for understanding.

First we are creating one database with FILESTREAM. Enable option then create one table with FILESTREAM column. Script for creating table is given below.

We are creating one WPF window application with image control, buttons and text box as shown in below image.



In this application you have to browse image file using browse button and then give some name to this image in text box and click on “Save photo To Database” button this will insert new records in database. After inserting record you have to click on “Load Photo from Database” button. This button will take last inserted image data from database and display in below image control

Below I have given XAML design code for creating above UI in WPF application.

MainWindow.xaml

<Window x:Class="FILESTREAM_WPF.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" WindowState="Maximized">
    <Grid>
        <Grid HorizontalAlignment="Center" VerticalAlignment="Center">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="Auto"/>
            </Grid.RowDefinitions>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="Auto"/>
            <ColumnDefinition Width="Auto"/>
        </Grid.ColumnDefinitions>
            <TextBox Name="txtFilePath" Grid.Column="0" Grid.Row="0" Width="280" Height="30" HorizontalAlignment="Left" Margin="5"></TextBox>
            <Button Name="btnBrowse" Grid.Column="1" Grid.Row="0" Width="150" Content="Browse Photo" Height="30" HorizontalAlignment="Right" Margin="5" Click="btnBrowse_Click"></Button>
            <TextBox Name="txtIamgeName" Grid.Column="0" Grid.Row="1" Width="280" Height="30" HorizontalAlignment="Left" Margin="5"></TextBox>
            <Button Name="btnSave" Grid.Column="1" Grid.Row="1" Width="150" Content="Save Photo To Database" Height="30" HorizontalAlignment="Right" Margin="5" Click="btnSave_Click"></Button>
            <Border Grid.Column="0" Grid.Row="3" Grid.ColumnSpan="2" BorderBrush="Black" BorderThickness="1" CornerRadius="3">
                <Image Name="imImages" Grid.Column="0" Grid.Row="3" Grid.ColumnSpan="2" Width="400" Height="400"></Image>
            </Border>
            <Button Name="btnLoadFromDB" Grid.Column="0" Grid.ColumnSpan="2" Grid.Row="2" Width="160" Content="Load Photo From Databse" Height="30" HorizontalAlignment="Center" Margin="5" Click="btnLoadFromDB_Click"></Button>
    </Grid>
    </Grid>
</Window>

For browsing image you have write the below code on button click event.

private void btnBrowse_Click(object sender, RoutedEventArgs e)
{
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            Nullable<bool> result = dlg.ShowDialog();
            if (result == true)
            {
                string filename = dlg.FileName;
                txtFilePath.Text = filename;
            }
}

Now for storing an image file in database, first we are creating table with file stream enable column. Script for creating Table is given below.

CREATE TABLE TblPhotos(
   ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   Name VARCHAR(20),
   Images VARBINARY(MAX) FILESTREAM NULL,
   CreatedDate datetime default getdate()
)

Now for storing the image we have to use below insert query.

-- Declare a variable to store the image data
DECLARE @tImage AS VARBINARY(MAX)

-- Load the image data
SELECT @tImage = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK
            'C:\temp\MicrosoftMouse.jpg',
            SINGLE_BLOB ) AS x

-- Insert data in table
INSERT INTO TblPhotos (ID, Name, Images) SELECT NEWID(), 'Image-1',@tImage

In above query you have to pass local image path. If your SQL server has installed on different PC and your image is on local path then you have to give network path in above insert query. 

For inserting a record in database we have write below code on “Save Photo To Database” button click event.

private void btnSave_Click(object sender, RoutedEventArgs e)
{
            SqlConnection conn = new SqlConnection("Your SQL Connection String");
            conn.Open();
            StringBuilder str = new StringBuilder();
            str.AppendLine("DECLARE @tImage AS VARBINARY(MAX)");
            str.AppendLine("SELECT @tImage = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK '" + txtFilePath.Text + "', SINGLE_BLOB ) AS x");
            str.AppendLine("INSERT INTO TblPhotos (ID, Name, Images) SELECT NEWID(), '" + txtIamgeName.Text + "',@tImage");

            SqlCommand cmd = new SqlCommand(str.ToString(), conn);
            cmd.ExecuteNonQuery();
            MessageBox.Show("Image Saved Successfully...");
}

After successfully inserting an image in database, we have to retrieve image data from the database and display it in image control in WPF application.

Whatever data you have retrieved from the database it in byte array format so first you have to convert this byte array data to memory stream and then you have to pass this memory stream to the image control shown in below.

private void btnLoadFromDB_Click(object sender, RoutedEventArgs e)
{
            SqlConnection conn = new SqlConnection("Your SQL Connection String ");
            conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter("SELECT TOP 1 * FROM TBLPHOTOS ORDER BY CreatedDate desc", conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            DataTable dt = ds.Tables[0];

            if (dt!=null && dt.Rows.Count>0)
            {
                byte[] btImage = dt.Rows[0]["Images"] as byte[];

                using (MemoryStream ms = new MemoryStream(btImage, 0, btImage.Length))
                {
                    var imageSource = new BitmapImage();
                    ms.Position = 0;
                    imageSource.BeginInit();
                    imageSource.CreateOptions = BitmapCreateOptions.PreservePixelFormat;
                    imageSource.CacheOption = BitmapCacheOption.OnLoad;
                    imageSource.UriSource = null;
                    imageSource.StreamSource = ms;
                    imageSource.EndInit();
                    // Assign the Source property of your image
                    imImages.Source = imageSource;
                }
            }
}

As you seen in above code first we are fetching record from database using select query and then we are assigning database content to byte array and then we are creating memory stream from this byte array.

Then create BitmapImage from the memory stream and give this BitmapImage to image control.

In the above example we have shown the storing and retrieving image from the database but If you have any video file or any other document file then inserting of video or PFD file are same as image file, so you have to give path of that file. But when you want to retrieve any file from database then you have to create file from byte array using below code.

public void ByteArrayToFile(string fileName, byte[] byteArray)
{
     try
     {
                using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(byteArray, 0, byteArray.Length);
                }
     }
     catch (Exception)
     {
     }
}

Using this above function you can create file from your byte array. You can use the above function  for creating a PDF file, video file, or any document files.

In the above function you have to provide path where you have to save your file.