Results 1 to 2 of 2

Thread: [Sql Server] FileStream Column Type

  1. #1

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    [Sql Server] FileStream Column Type

    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:
    1. USE [SchoolMgmt]
    2. GO
    3.  
    4. /****** Object:  Table [dbo].[FileBackUp]    Script Date: 08/08/2011 12:16:00 ******/
    5. SET ANSI_NULLS ON
    6. GO
    7.  
    8. SET QUOTED_IDENTIFIER ON
    9. GO
    10.  
    11. SET ANSI_PADDING ON
    12. GO
    13.  
    14. CREATE TABLE [dbo].[FileBackUp](
    15.     [FileId] [int] IDENTITY(1,1) NOT NULL,
    16.     [FileData] [varbinary](max) FILESTREAM  NULL,
    17.     [RowGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    18.     [FilePath] [nchar](100) NULL,
    19. PRIMARY KEY CLUSTERED
    20. (
    21.     [FileId] ASC
    22. )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],
    23. UNIQUE NONCLUSTERED
    24. (
    25.     [RowGuid] ASC
    26. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
    27. ) ON [PRIMARY] FILESTREAM_ON [BIRTHCERTIFICATES]
    28.  
    29. GO
    30.  
    31. SET ANSI_PADDING OFF
    32. GO
    33.  
    34. ALTER TABLE [dbo].[FileBackUp] ADD  DEFAULT (newid()) FOR [RowGuid]
    35. GO
    c# Code:
    1. namespace FileStream_Sql
    2. {
    3.     class WriteStreamData
    4.     {
    5.         private static SqlConnectionStringBuilder ConnStringBuilder { get; set; }
    6.         static void Main(string[] args)
    7.         {
    8.             ConnStringBuilder = new SqlConnectionStringBuilder
    9.                                     {
    10.                                         ConnectionString = {Your Connection String}
    11.                                     };
    12.  
    13.             var directoryInfo = new DirectoryInfo(Environment.CurrentDirectory);
    14.             var listofFiles = directoryInfo.EnumerateFiles("*.jpg");
    15.             foreach (var file in listofFiles)
    16.             {
    17.                 SaveThisFile(file.Name);    
    18.             }
    19.            
    20.         }
    21.         static void SaveThisFile(string fileName)
    22.         {
    23.             var connection = new SqlConnection{ConnectionString = ConnStringBuilder.ConnectionString};
    24.             connection.Open();
    25.             var command = new SqlCommand
    26.                                {
    27.                                    CommandText =
    28.                                        "INSERT INTO FileBackUp ([FilePath], [FileData], [RowGuid]) VALUES (@FilePath, @Data, @RowGuid)",
    29.                                    CommandType = CommandType.Text,
    30.                                    Connection = connection
    31.                                    
    32.                                };
    33.  
    34.             var fileInfo = new FileInfo(fileName);
    35.  
    36.  
    37.             var filePath = new SqlParameter("@FilePath", SqlDbType.NVarChar) { Value = fileInfo.Name };
    38.             command.Parameters.Add(filePath);
    39.  
    40.             var fileData = new SqlParameter("@Data", SqlDbType.VarBinary)
    41.                           {Value = File.ReadAllBytes(fileName)};
    42.             command.Parameters.Add(fileData);
    43.  
    44.             var rowGuid = new SqlParameter("@RowGuid", SqlDbType.UniqueIdentifier) {Value =Guid.NewGuid()};
    45.             command.Parameters.Add(rowGuid);
    46.  
    47.       command.Transaction = connection.BeginTransaction();
    48.       command.ExecuteNonQuery();
    49.       command.Transaction.Commit();
    50.       connection.Close();
    51.         }
    52.     }
    53. }

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  2. #2

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: [Sql Server] FileStream Column Type

    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:
    1. static void Main(string[] args)
    2.         {
    3.             ConnStringBuilder = new SqlConnectionStringBuilder
    4.                                     {
    5.                                         ConnectionString =
    6.                                             {YourConnectionString};
    7.                                     };
    8.  
    9.  
    10.  
    11.             var directoryInfo = new DirectoryInfo(Environment.GetFolderPath(Environment.SpecialFolder.MyPictures));
    12.             var startTime = DateTime.Now;
    13.             foreach (var subFolder in directoryInfo.EnumerateDirectories())
    14.             {
    15.  
    16.  
    17.                 var listofFiles = subFolder.EnumerateFiles("*.jpg");
    18.                 foreach (var file in listofFiles)
    19.                 {
    20.                     Console.WriteLine(string.Format("Now Saving file {0}", file.FullName));
    21.                     SaveThisFile(file.FullName);
    22.                 }
    23.             }
    24.             var endTime = DateTime.Now;
    25.             Console.WriteLine(String.Format("Start Time {0} To  End time {1} = {2}", startTime, endTime, endTime - startTime));
    26.            
    27.         }
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width