Inserting a file into a FILESTREAM table from C#. And how to get it back as well.
Here is my take on how to insert files into a SQL Server 2008 FILESTREAM (in short, storing files on disk rather than in the database).
From "FILESTREAM Overview"
https://msdn.microsoft.com/en-us/library/bb933993(SQL.100).aspx
“Much of the data that is created every day is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database,
separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage,
the file streaming capabilities and performance can be limited.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system.
Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance.
The SQL Server buffer pool is not used; therefore, this memory is available for query processing.”
First we need a database with a table in where to store the data. In this example you need to have a directory called C:\Data, so create that if you do not have it.
Then simply execute the following from SSMS:
CREATE DATABASE MyFsDb
ON
PRIMARY ( NAME = MyFsDat,
FILENAME = 'c:\data\myfsdat.mdf'),
FILEGROUP MyFsGroup CONTAINS FILESTREAM( NAME = MyFs,
FILENAME = 'c:\data\myfs1')
LOG ON ( NAME = MyFsLog,
FILENAME = 'c:\data\myfslog.ldf')
GO
USE MyFsDb
GO
CREATE TABLE MyFsTable
(
fId INT IDENTITY PRIMARY KEY,
fData VARBINARY(MAX) FILESTREAM NULL,
fName NVARCHAR(300),
RowGuid UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL UNIQUE DEFAULT NEWID()
)
GO
This creates the database and the table for the storage. I have included a column for storing the filename.
Create a standard Window Form application. Put a button (btnInsert) onto the form and add the following code in the button clicked event.
private void btnInsert_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDlg = new OpenFileDialog();
openFileDlg.InitialDirectory = Directory.GetCurrentDirectory();
if (openFileDlg.ShowDialog() == DialogResult.OK)
{
FileInfo fi = new FileInfo(openFileDlg.FileName);
FileStream fs = new FileStream(fi.FullName, FileMode.Open, FileAccess.Read);
BinaryReader rdr = new BinaryReader(fs);
byte[] fileData = rdr.ReadBytes((int)fs.Length);
rdr.Close();
fs.Close();
string cs = @"Data Source=<your server>;Initial Catalog=MyFsDb;Integrated Security=TRUE";
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
string sql = "INSERT INTO MyFsTable VALUES (@fData, @fName, default)";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.Add("@fData", SqlDbType.Image, fileData.Length).Value = fileData;
cmd.Parameters.Add("@fName", SqlDbType.NVarChar).Value = fi.Name;
cmd.ExecuteNonQuery();
con.Close();
}
MessageBox.Show(fi.FullName, "File Inserted!", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
Then put a button (btnOpen) onto the form and add the following code in the button clicked event.
This will simply write the binary data from the table to the C:\Temp directory using the filename that was provided when inserting the file.
This most likely not the scenario that you would use, instead you probably would present a list and then let the use select the file and download\open
it based on that selection. But again, this is just an example on how you can do this.
private void btnOpen_Click(object sender, EventArgs e)
{
string cs = @"Data Source=<your server>;Initial Catalog=MyFsDb;Integrated Security=TRUE";
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlTransaction txn = con.BeginTransaction();
string sql = "SELECT fData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), fName FROM MyFsTable";
SqlCommand cmd = new SqlCommand(sql, con, txn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string filePath = rdr[0].ToString();
byte[] objContext = (byte[])rdr[1];
string fName = rdr[2].ToString();
SqlFileStream sfs = new SqlFileStream(filePath, objContext, System.IO.FileAccess.Read);
byte[] buffer = new byte[(int)sfs.Length];
sfs.Read(buffer, 0, buffer.Length);
sfs.Close();
// Just write all files in the table to the temp direcotory.
// This is probably not how you would do it in the real world. But this is just an example.
string filename = @"C:\Temp\" + fName;
System.IO.FileStream fs = new System.IO.FileStream(filename, FileMode.Create, FileAccess.Write, FileShare.Write);
fs.Write(buffer, 0, buffer.Length);
fs.Flush();
fs.Close();
}
rdr.Close();
txn.Commit();
con.Close();
}
}
Comments
Anonymous
May 14, 2013
This is an excellent FILESTREAM sample, in my case I added some extra code to open the file after the download: //Open file System.Diagnostics.Process prc = new System.Diagnostics.Process(); prc.StartInfo.FileName = filename; prc.Start(); prc.Close();Anonymous
October 28, 2013
The comment has been removedAnonymous
July 02, 2014
Do you know how to save a .VSD file into a FileTable in SQL Server 2012? And also need to retrieve and display the .VSD file on a web page?Anonymous
December 02, 2014
i really like ur code but i have a problem when i click button submit it's take a long time charging and nthg happened help mee please