SQL Server 2012’de File Table Kullanımı(File streaming)

TÜRKÇE MAKALELER

File Table Nedir?

SQL Server 2012 ile gelen File Table, aslında tanımını isminde barındırıyor, “File Sistem verilerini tutan tablo” ya da kısaca Dosya Tablosudur. FILESTREAM teknolojisinin üzerinde temellenen bir gelişmedir. File Table’ın şeması FILESTREAM veriyi, dosya ve dizin hiyerarşisini ve dosya ile ilgili özellikleri tutacak şekilde önceden belirlenmiştir. Hiyerarşi, File Table’ı oluştururken belirteceğimiz paylaşımlı alandaki kök dizinden başlar. Ardından ekleyeceğimiz veya çıkaracağımız her dosya ve dizin, oluşturduğumuz File Table’da tutulur. File Table’daki her satır, bir dosya ya da dizini temsil eder. Başka bir deyişle Windows paylaşımındaki her dosya ve dizin File Table’da bir satıra karşılık gelir.

File Table Kullanmanın Yararları Nelerdir?

 Bu makalenin devamı 
http://www.sqlserveronculeri.com/1/Article/456/sql-server-2012%E2%80%99de-file-table-kullanimi.aspx

exec sp_configure filestream_access_level,2

reconfigure


http://www.kodyaz.com/t-sql/default-filestream-filegroup-is-not-available-in-database.aspx

https://lennilobel.wordpress.com/2011/09/11/its-a-file-system-its-a-database-table-its-sql-server-denali-filetable/


YABANCI MAKALELER

http://sql-articles.com/articles/general/working-with-filetables/

https://www.codeproject.com/Articles/128657/How-Do-I-Use-SQL-File-Stream
Örnek de var..

http://www.databasejournal.com/features/mssql/filestream-and-filetable-in-sql-server-2012.html

https://www.akadia.com/services/dotnet_read_write_blob.html
http://www.pertell.com/sqlservings/archive/2013/08/inserting-files-into-a-filetable-using-t-sql/


https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/getfilenamespacepath-transact-sql

Reading BLOBs from SQL Server and
display it in a Windows Form PictureBox

https://www.akadia.com/services/dotnet_load_blob.html

http://www.c-sharpcorner.com/UploadFile/rohatash/new-filetables-feature-in-sql-server-2012/

Klasör oluşturma

Creating Folder for DemoFileTables directory

Now create two folders for this directory, as in:

INSERT INTO DemoFileTableTb(Name, is_directory)

 values('Rohatash',1)

 INSERT INTO DemoFileTableTb(Name, is_directory)

 values('Rahul',1)

File Table

USE DemoFileTableDB

GO

CREATE TABLE DemoFileTableTb AS FileTable

WITH

(FileTable_Directory = 'FileTableTb_Dir');

GO

Default ayarlarla  filetable oluşturur.

GO
CREATE TABLE MyFirstFileTable   AS FileTable
WITH
(
      FileTable_Directory =   'MyFirstFileTable',
      FileTable_Collate_Filename = database_default
);
GO


declare @rootpath nvarchar(100);
declare @fullpath nvarchar(100);
select @rootpath=FileTableRootPath();
print @rootpath

select top 10 @fullpath=@rootpath+ file_stream.GetFileNamespacePath() from MyFileTables
print  @fullpath
select file_type as FileType,count(*) Filenumber
From [dbo].MyFileTables
group by file_type


/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [PkId]
      ,[Id]
      ,[Description]
      ,[FileSummary]
      ,[FileData]
  FROM [FileSystemDB].[dbo].[PictureTable]

  DECLARE @FileData AS VARBINARY(MAX)
  SELECT FileData FROM dbo.PictureTable

  select FileData.PathName() from dbo.PictureTable
Dosya yolunu veriyor...

http://zarez.net/?p=2427

We have created a FILESTREAM table using the following code:

CREATE TABLE [dbo].[FS_Table]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[FS_Data] [varbinary](max) FILESTREAM NULL
)

To insert data into a FILESTREAM Table use the following T-SQL code (it will insert image_001.jpg file from D:\temp\ folder to the dbo.FS_Table in the FS_Database):

Use FS_Database
GO
INSERT INTO [dbo].[FS_Table] (Id, UI, FS_Data)
VALUES (1
,NEWID()
,(SELECT * FROM OPENROWSET(BULK N'D:\temp\image_001.jpg', SINGLE_BLOB) AS Image001)
);
GO

MS SQL DE YÜKLEME YAPMAK

örnek 1

USE DemoDB;

-- Insert test images into LOB table
DECLARE @image1    VARBINARY(MAX)
DECLARE @image2 VARBINARY(MAX)

SELECT @image1 = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\File Table Demo\Photos\SF Sailboat.jpg', SINGLE_BLOB) AS x

SELECT @image2 = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(BULK 'C:\File Table Demo\Photos\Half Dome.jpg', SINGLE_BLOB) AS y

INSERT INTO dbo.Documents (name, file_stream)
SELECT 'SF Sailboat.jpg', @image1

INSERT INTO dbo.Documents (name, file_stream)
SELECT 'Half Dome.jpg', @image2;

örnek:2

Use FS_Database
GO
INSERT INTO [dbo].[FS_Table] (Id, UI, FS_Data)
VALUES (1
,NEWID()
,(SELECT * FROM OPENROWSET(BULK N'D:\temp\image_001.jpg', SINGLE_BLOB) AS Image001)
);
GO