-- Przygotuj baze dla testu dostepu przez API .NET
USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'PLSSUG_DEMO3')
DROP DATABASE [PLSSUG_DEMO3]
GO

CREATE DATABASE [PLSSUG_DEMO3]
ON PRIMARY (
	NAME		= N'PLSSUG_DEMO3',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO3.mdf',
	SIZE		= 102400KB,
	FILEGROWTH	= 51200KB,
	MAXSIZE		= UNLIMITED
),
FILEGROUP [FS_DOCS] CONTAINS FILESTREAM DEFAULT (
	NAME		= N'FS_PLSSUG_DEMO3',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\FS_PLSSUG_DEMO3',
	MAXSIZE		= UNLIMITED)
LOG ON (
	NAME		= N'PLSSUG_DEMO3_log',
	FILENAME	= N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\PLSSUG_DEMO3_log.ldf',
	SIZE		= 51200KB,
	FILEGROWTH	= 20480KB
)
GO

USE [PLSSUG_DEMO3]
GO

CREATE SCHEMA help AUTHORIZATION dbo;
GO

CREATE TABLE help.files(
	file_id		INT					NOT NULL	IDENTITY(1, 1)	CONSTRAINT PK_help_files		PRIMARY KEY,
    path		VARCHAR(256)		NOT NULL					CONSTRAINT UNQ_help_files_path	UNIQUE,
    doc_type	VARCHAR(6)										DEFAULT('.xml'),

    content		VARBINARY(MAX)		FILESTREAM	NOT NULL,
	rguid		UNIQUEIDENTIFIER	ROWGUIDCOL	NOT NULL		CONSTRAINT UNQ_rguid			UNIQUE	DEFAULT NEWID()
);
GO

-- uruchom: C:\Users\Administrator\Documents\BOL_import\import_bol_mini.bat

-- co tam w ogole jest w srodku?
SELECT
	content = CAST(content AS VARCHAR(MAX))
FROM help.files
WHERE
	1 = 1
	AND file_id = 3

-- prezentacja pliku FILESTREAM_example.cs

-- pomocnicze:
SELECT
	content.PathName(),
	GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM help.files
WHERE
	1 = 1
	AND file_id = 3

-- a w transakcji:
BEGIN TRANSACTION

SELECT
	content.PathName(),
	GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM help.files
WHERE
	1 = 1
	AND file_id = 3

ROLLBACK TRANSACTION