SQL Server 2012 : Working with FileTables

-
Hi Guys,

 I had a requirement to store files in the database. But had an issue with storage as the files were growing, so my project leader asked me to come up with a solution. Then I found about FileStream and FileTables. So thought of sharing my experience.

 1) Enable FileStream in your instance
Go to Configuration Manager, select the instance -> Properties -> FileStream Tab

2) Enable FileStream on your Server
Go to SQL Server Management Studio, select server -> Properties -> Advanced ->FileStream -> FileStream Access Level -> Full Access Enabled 




3) Create Database with FileStream

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'd:\FileStorage\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'd:\FileStorage\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'd:\FileStorage\archlog1.ldf')
GO


4) Specify FileStream Directory Name
Go to SQL Server Management Studio, select database -> Properties -> Options ->FileStream -> FileStream Directory Name  -> Provide Any Name



5) Create FileTable 

USE Archive

IF OBJECT_ID('dbo.TestFileTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestFileTable
GO

CREATE TABLE dbo.TestFileTable AS FILETABLE
  WITH
  (
    FILETABLE_DIRECTORY = 'ArchiveFileTable', --This name must be the name given in 3rd Step
    FILETABLE_COLLATE_FILENAME =  database_default
  )
GO

6) Add Files to your FileTable

There are three ways you can do this.

  • Drag and drop files from the source folders to the new FileTable folder in Windows Explorer.
  • Use command line options such as MOVE, COPY, XCOPY, or ROBOCOPY from the command prompt or in a batch file or script.
  • Write a custom application in C# or Visual Basic.NET that uses methods from the System.IO namespace to move or copy the files.




7) Insert Files to your FileTable using TSQL

INSERT INTO [dbo].[TestFileTable]([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB)AS FileData
GO


Comments

Post a Comment

Popular posts from this blog

Secure .NET Core Api Server and Client

-
Image
 Hi Guys, Recently I had a requirement to create a JWT token based WebApi server and a WebApi Client to consume it using .NET Core. So first I created two .NET Core Web Api projects as shown below. In the appsettings.json file of both the WebApi Server and the Client. I added the following values. "JwtConfig": { "Key": "dsadsagfaqrergsdsfdffdsfdsffdsfdsfdsfdsfdfdsfsfdsfd", //Some Secure Key longer the better "AudienceId": 7895, //Some Secure Audience Id "HostUrl": "https://somesecureurl.com" //Some Host Url } Now we will first look at implementation of the WebApi Server. 1) First we must create code to Generate the JWT token, this can be achieved by creating JWTTokenManager.cs file and adding following code public interface IJwtTokenManager { string Authenticate(string userName, string password); } public class JwtTokenManager : IJwtTokenManager { private readonly IConfiguration _c...
Read more

Calling ChatGPT using .NET Core

-
Image
  Hi Guys, With the immense hype created by ChatGPT every technology is adopting ways to integrate ChatGPT to their frameworks. Hence, a brilliant team led by Hassan Habib  has managed to give .NET Developers a way to call ChatGPT programmatically and get the responses. Remember this is still a new project you might encounter some issues, but the Standard.AI.OpenAI team is ready to help us developers in this journey. The source code can be found here. 1) First you must register yourself at https://platform.openai.com 2) Then you must generate an API Key as shown below. 3) Now you can start integrating your .NET Core applications to use openai. For this purpose I use a console application (.NET 7).  4) Install the Nuget package Standard.AI.OpenAI 5) Copy paste the below code using Microsoft.AspNetCore.Mvc; using Standard.AI.OpenAI.Brokers.DateTimes; using Standard.AI.OpenAI.Clients.OpenAIs; using Standard.AI.OpenAI.Models.Configurations; using Standard.AI.OpenAI.Model...
Read more

Windows Desktop Watermark with username

-
Image
 Hi Guys, I recently had a requirement from a client to implement a way to protect sensitive documents from being photographed from mobile devices and shared online. To mitigate this issue I made a windows application using C#.  When the exe runs it will read current logged in users' domain user account and display his username throughout the screen. I have given options to change the displayed text to username, hostname of the computer or any custom texts. You can get this application from the following url  Desktop watermark Note: This is works only in Windows Operating Systems
Read more