[Sql Server] FileStream Column Type

[abhijit]

This is very basic code to store data into a filestream column in SQL Server. FileStream requires additional configuration on your Windows server.

Once SQL Server has created a windows share, you will need to grant additional privileges to the service account to allow it to write / read from that folder.

You can read this to enable the filestream object on your SQL Server R2 Database. You will need a filestream enabled database to get this code to work.

USE [SchoolMgmt]
GO
 
/****** Object:  Table [dbo].[FileBackUp]    Script Date: 08/08/2011 12:16:00 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[FileBackUp](
    [FileId] [int] IDENTITY(1,1) NOT NULL,
    [FileData] [varbinary](max) FILESTREAM  NULL,
    [RowGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FilePath] [nchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
    [FileId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY] FILESTREAM_ON [BIRTHCERTIFICATES],
UNIQUE NONCLUSTERED 
(
    [RowGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [BIRTHCERTIFICATES]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [dbo].[FileBackUp] ADD  DEFAULT (newid()) FOR [RowGuid]
GO

namespace FileStream_Sql
{
    class WriteStreamData
    {
        private static SqlConnectionStringBuilder ConnStringBuilder { get; set; }
        static void Main(string[] args)
        {
            ConnStringBuilder = new SqlConnectionStringBuilder
                                    {
                                        ConnectionString = {Your Connection String}
                                    };
 
            var directoryInfo = new DirectoryInfo(Environment.CurrentDirectory);
            var listofFiles = directoryInfo.EnumerateFiles("*.jpg");
            foreach (var file in listofFiles)
            {
                SaveThisFile(file.Name);    
            }
            
        }
        static void SaveThisFile(string fileName)
        {
            var connection = new SqlConnection{ConnectionString = ConnStringBuilder.ConnectionString}; 
            connection.Open();
            var command = new SqlCommand
                               {
                                   CommandText =
                                       "INSERT INTO FileBackUp ([FilePath], [FileData], [RowGuid]) VALUES (@FilePath, @Data, @RowGuid)",
                                   CommandType = CommandType.Text, 
                                   Connection = connection
                                   
                               };
 
            var fileInfo = new FileInfo(fileName);
 
 
            var filePath = new SqlParameter("@FilePath", SqlDbType.NVarChar) { Value = fileInfo.Name };
            command.Parameters.Add(filePath);
 
            var fileData = new SqlParameter("@Data", SqlDbType.VarBinary)
                          {Value = File.ReadAllBytes(fileName)};
            command.Parameters.Add(fileData);
 
            var rowGuid = new SqlParameter("@RowGuid", SqlDbType.UniqueIdentifier) {Value =Guid.NewGuid()};
            command.Parameters.Add(rowGuid);
 
      command.Transaction = connection.BeginTransaction();
      command.ExecuteNonQuery();
      command.Transaction.Commit();
      connection.Close();
        }
    }
}

The C# code enables you to write to the database. The enumeration lets me filter out files.

In some cases the filestream is an overhead and you're better off storing data to regular blobs.