# VBForums CodeBank > CodeBank - Other >  [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.




sql Code:
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

c# Code:
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.

----------


## abhijit

A slight change in the above code has enabled me to save all the files from MyPictures folder on my hard-disk. There were more than 150 files and it took a minute to save these files. In my next code snippet, I will write the part where you can extract this file. In this case, the file extension does not matter. As I pointed out earlier, you can store all your word documents, pdf files, excel spreadsheets to this column.

The change is in the Main function of the WriteStreamData class.


c# Code:
static void Main(string[] args)
        {
            ConnStringBuilder = new SqlConnectionStringBuilder
                                    {
                                        ConnectionString =
                                            {YourConnectionString};
                                    };
               var directoryInfo = new DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.MyPictures));
            var startTime = DateTime.Now;
            foreach (var subFolder in directoryInfo.EnumerateDirectories())
            {
                  var listofFiles = subFolder.EnumerateFiles("*.jpg");
                foreach (var file in listofFiles)
                {
                    Console.WriteLine(string.Format("Now Saving file {0}", file.FullName));
                    SaveThisFile(file.FullName);
                }
            }
            var endTime = DateTime.Now;
            Console.WriteLine(String.Format("Start Time {0} To  End time {1} = {2}", startTime, endTime, endTime - startTime));
            
        }

----------

