Các thao tác cơ bản với FileTable trong SQLserver 2012

I. TẠO CƠ SỞ DỮ LIỆU (DATABASE) SỬ DỤNG FILESTREAM

1. Tạo cơ sở dữ liệu sử dụng FILESTREAM

- Khởi động SQL Server Management Studio > New Query

-  Chạy truy vấn có cấu trúc dưới đây. Tuy nhiên đường dẫn đến thư mục chính phải tồn tại . Ở đây là 'E:\DATABASE_FILETABLE’

CREATE DATABASE TEN_CO_SO_DU_LIEU

ON

PRIMARY(Name = FileTableDB,FILENAME = 'E:\DATABASE_FILETABLE\FileTableDB.mdf'),

FILEGROUP FileTableFileGroup CONTAINS FILESTREAM

            (NAME = FileTableFS, FILENAME='E:\DATABASE_FILETABLE\FileStream')

LOG ON (Name = FileTableDBLog, FILENAME = 'E:\DATABASE_FILETABLE\FileTableLOG.ldf')

GO

- Sau khi chạy truy vấn.  Các File FileTableDB.mdf và FileTableLOG.ldf' và thư mục FileStream sẽ được tạo ra theo đường dẫn trên.

2. Cho phép truy cập CSDL (None-Transactional Access at the Database)

a. Kiểm tra trạng thái của None-Transactional Access at the Database

- Cách 1 : Trong SQL Management Studio , ấn chuột phải vào CSDL vừa tạo

   Chọn Properties > Options.

   Xem phần FileStream None-Transactional Access at the Database (Full/Off/ReadOnly)

- Cách 2 : Chạy truy vấn có cấu trúc sau đây  :

SELECT DB_NAME(database_id), non_transacted_access_desc

                                FROM sys.database_filestream_options;

GO

           

b. Đặt None-Transactional Access at the Database với chế độ Full và thiết lập tên thư mục gốc của FILESTREAM

- Cách 1 : Trong SQL Management Studio , ấn chuột phải vào CSDL vừa tạo

   Chọn Properties > Options.

   Trong phần FileStream None-Transactional Access at the Database chọn Full

   Trong phần FileStream Directory Name : nhập tên thư mục gốc sẽ lưu trữ các file của   CSDL

- Cách 2 : Chạy truy vấn có cấu trúc sau đây  :

ALTER DATABASE FILETABLEDATABASETEST

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL,

DIRECTORY_NAME = N'FileTableDataBase');

GO

                FileTableDataBase : tên thư mục gốc sẽ chứa các file sẽ đưa vào CSDL

Cũng có thể set chế độ này ngay từ khi tạo DATABASE

II. LÀM VIỆC VỚI FILETABLE SQL

1. Cách tạo FileTable

Chạy truy vấn có cấu trúc sau đây  :

USE [FILETABLEDATABASETEST] -- Ten CSDL

GO

CREATE TABLE FileTableDB AS FILETABLE -- Ten FileTable

  WITH

  (

    FILETABLE_DIRECTORY = 'FileTableDIR', -- Thu muc luu tru cac file trong FileTable

    FILETABLE_COLLATE_FILENAME = database_default

  );

GO

- Sau khi chạy truy vấn này , SQL Server sẽ tự động tạo 1 Thư mục trong Network :

“DIRECTORY_NAME\FILETABLE_DIRECTORY”

Cụ thể ở đây là  “FileTableDataBase\ FileTableDIR”

- Đồng thời 1 bảng gồm các cột lưu trữ thông tin File sẽ được tạo ra :

·         creation_time              : Thời gian tạo File

·         name                            : Tên file

·         is_archive

·         is_directory

·         is_hidden                                 : Trạng thái file có bị ẩn không

·         is_offline

·         is_readonly                              : Trạng thái file có ở chế độ Readonly không

·         is_system                     : Là file hệ thống

·         is_temporary

·         last_access_time          :Thời gian truy cập cuối cùng

·         last_write_time                        : Thời gian ghi cuối cùng

·         path_locator    

·         stream_id                     : Stream này là duy nhất (khóa chính)

- Ta có thể xem danh sách file trong Filetable bằng truy vấn sau :

USE [FILETABLEDATABASETEST]

GO

SELECT *  FROM FileTableDB

GO

Lấy đường dẫn đầy đủ này bằng cách chạy truy vấn (Quan trọng trong việc làm việc với System.IO trong NET)

USE [FILETABLEDATABASETEST]

GO

SELECT FileTableRootPath('FileTableDB') AS FileTableRootPath

2. Thao tác với FileTable trong SQL

*** Sử dụng giao diện Windows ***

- Trong SQL Management Studio > Chọn CSDL >  Tables > File Tables      

- Ấn chuột phải vào Bảng vừa tạo chọn “Explore FileTable Directory”

- Khi đó SQL Server sẽ mở đường dẫn bạn đã tạo ở bước trước bằng Windows Explorer. Ta có thể thực hiện các thao tác Copy/Paste/Delete như trên các thư mục của máy tính

***Sử dụng truy vấn ***

a. Tải File lên FileTable

Chạy truy vấn có cấu trúc sau đây  :

USE [FILETABLEDATABASETEST] -- Ten CSDL

GO

INSERT INTO [dbo].[FileTableDB] -- Ten Bang

([name],[file_stream])           

SELECT

'ExampleFileTable.doc', * FROM OPENROWSET(BULK N'E:\Example.doc’, SINGLE_BLOB) AS FileData

GO

Khi chạy truy vấn : File “Example.doc” trong ổ đĩa E (E:\Example.doc) sẽ được tải lên FileTable với tên

“ExampleFileTable”

Chú ý : File Upload lên CSDL không được là file rỗng và tên file không có dấu tiếng việt và các kí tự đặc biệt

b. Xóa file trên FileTable

Chạy truy vấn có cấu trúc sau đây  :

USE [FILETABLEDATABASETEST] -- Ten CSDL

GO

DELETE FROM [dbo].[FileTableDB] WHERE name = 'ExampleFileTable.doc';

Khi đó file có tên 'ExampleFileTable.doc'  sẽ bị xóa khởi FileTable

3. Thao tác với FileTable bằng thư viện trong System.IO trong .NET

Chú ý : Có thể sử dụng thư viện System.Data.SqlClient để tương tác trực tiếp với SQL . Sau đó sử dụng những truy vấn ở phần 2

Trên trang chủ của Microsoft có viết “Có thể sử dụng thư viện IO trong C#.NET hoặc VB.NET” để tương tác trực tiếp với các File/Directory trong FileTable”

Việc quan trọng ở đây ta vẫn phải sử dụng System.Data.SqlClient để chạy truy vấn :

SELECT FileTableRootPath('FileTableDB') AS FileTableRootPath

Từ đó lấy được đường dẫn tới thư mục chứa các file trong FileTable.

Chú ý : Ta thường lấy được đường dẫn dạng :

\\IPSERVER \sqlexpress\FileTableDataBase\FileTableDIR

Tưởng như đường dẫn này không tồn tại nhưng thực tế khi kết nối với SQL server . SQL sẽ tự động tạo một đường dẫn ảo như vậy. Khi ngắt kết nối với Server đường dẫn này sẽ biến mất

Sau khi lấy được đường dẫn của “FileTable_Directory”

Ta có thể sử System.IO để Copy/Paste/Delete/Rename bằng các phương thức khá cơ bản để tương tác với Folder này như :

System.IO.File.Copy

System.IO.File.Delete

Ta cũng có thể mở các file này một cách rất cơ bản :

System.Diagnostics.Process.Start(DUONG_DAN);