SQL Server - Inserire o copiare i dati presenti in una colonna di tipo SqlBinary
Scritto da
Antonio Giglio
il
mercoledì 6 aprile 2011
Linguaggio:
•
Framework:
•
Livello: 200
La gestione delle colonne di tipo SqlBinary, e cioè le colonne
di tipo binary, varbinary ed image, è sempre alquanto difficoltosa
poichè non esistono funzioni T-SQL che ci consentono di inserire od
estrarre dati binari in una colonna prelevandoli da File
System.
Per ovviare a questo problema è possibile scrivere delle user
defined function, sviluppandole con la CLR, che accedono al File
System al posto di Sql Server. Nell'esempio che segue,
implementeremo due semplici user defined function che ci
consentiranno sia di leggere da File System e scrivere su SQL
SERVER che viceversa.
Per poter sviluppare con la CLR è necessario possedere una
versione di Visual Studio, a partire dalla 2005 in poi, diversa
dalla Express. Creiamo quindi un nuovo progetto da Visual Studio
così come illustrato nella seguente figura:
Una volta confermato il nome del progetto sarà visualizzata la
finestra che permette di selezionare la reference al database:
Se il database sul quale vogliamo aggiungere le nostre funzioni
CLR è già nella lista, selezioneremo l'item corrispondente
altrimenti possiamo creare una nuova reference al database
utilizzando il bottone Add New Reference. In questo caso comparirà
la finestra seguente che compileremo con i dati corrispondenti
alla nostra istanza di Sql Server.
Dopo aver selezionato il database corretto ci comparirà la
finestra per l'abilitazione del debug SQL/CLR alla quale sarà
opportuno rispondere affermativamente poichè, diversamente,
sarà impossibile eseguire il debug delle funzioni che andremo
ad implementare.
A questo punto comparirà, all'interno del solution
explorer, il nuovo progetto che abbiamo creato. Selezioniamo il
progetto e con il tasto desto visualizziamo le proprietà. Nel tab
Application selezioneremo il Framework 3.5 alla voce Target
Framework, mentre nel tab Database selezioneremo il valore external
alla voce permission level.
Il passo successivo consiste nel configurare correttamente il
nostro database. Le impostazioni di default di SQL Server
hanno disabilitato l'utilizzo della CLR ed in più è
necessario abilitare il nostro database all'utilizzo
di CLR che accedono al File System. Passiamo quindi alla
managment console di Sql Server ed eseguiamo lo
script seguente facendo attenzione al nome del database che
nel nostro caso è EFDemo.
USE [EFDemo]
GO
/* abilitiamo SQL per l'utilizzo delle funzionallità della CLR che di default è disabilitato */
sp_configure "clr enabled", 1
GO
RECONFIGURE
GO
/* Abilitiamo la CLR per l'accesso al File System */
ALTER DATABASE [EFDemo] SET TRUSTWORTHY ON
GO
/* Creiamo la tabella per i nostri test */
CREATE TABLE TabellaTest
(
Identificativo int IDENTITY(1,1) NOT NULL,
ValoreBinario varbinary(max) NULL,
CONSTRAINT PK_TabellaTest PRIMARY KEY CLUSTERED ( [Identificativo] ASC )
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
) ON [PRIMARY]
GO
Lo script in oggetto abilita la nostra istanza di SQL SERVER per
l'utilizzo della CLR, successivamente concede i privilegi di
accesso ad assembly CLR con permission level di tipo external ed
infine crea la nostra tabella di test nella quale effettueremo le
prove. A questo punto aggiungiamo al progetto una nuova user
defined function, selezionando il nostro progetto con il tasto
desto e scegliendo la voce Add New Item
Una volta premuto il tasto Add, sostituiamo lo script creato da
Visual Studio con il seguente:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString udfCLRSaveBinaryAs([SqlFacet(MaxSize = -1)] SqlBinary _Image, SqlString _FileName)
{
if (_FileName.IsNull) return new SqlString("FileName cannot be null.");
FileStream fStream = null;
SqlString ReturnValue = SqlString.Null;
try
{
fStream = new FileStream(_FileName.Value, FileMode.OpenOrCreate, FileAccess.Write);
}
catch (Exception EX)
{
return new SqlString(EX.Message);
}
try
{
fStream.Write(_Image.Value, 0, _Image.Length);
}
catch (Exception EX)
{
ReturnValue = new SqlString(EX.Message);
}
finally
{
fStream.Flush();
fStream.Close();
fStream.Dispose();
if (ReturnValue.IsNull) ReturnValue = _FileName;
}
return (ReturnValue);
}
};
Lo scopo della udfCLRSaveBinaryAs è quello di leggere i dati da
una colonna di tipo SqlBinary e scriverli nel File System, a tale
scopo accetta due parametri: la colonna che contiene il valore
binario ed il nome del file che andrà memorizzato nel File
System.
Il primo parametro sarà decorato con l'attributo SqlFacet,
impostando la proprietà MaxSize a -1. Questa impostazione ci
consente di superate il limite di 8000 bytes impostato di default
da Sql Server.
Effettuati i controlli preliminari sui parametri, la nostra user
defined function provvederà ad aprire/creare il file in scrittura e
ci scaricherà all'interno il contenuto della nostra colonna
binaria. Se tutto è andato a buon fine, la nostra
funzione restituirà il nome del file che gli abbiamo passato
in input, altrimenti sarà restituito un messaggio di
errore.
Aggiungiamo quindi un'altra user defined function che
chiameremo udfCLRImportBinaryFrom e sostituiamo, come prima, il
codice generato di default con il seguente:
using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1)]
public static SqlBinary udfCLRImportBinaryFrom(SqlString _FileName)
{
if (_FileName.IsNull) throw new Exception("FileName cannot be null.");
FileInfo fInfo = new FileInfo(_FileName.Value);
if (!fInfo.Exists) throw new Exception("File '" + _FileName.Value + "' does not exists.");
long numBytes = fInfo.Length;
byte[] data = new byte[numBytes];
FileStream fStream = null;
try
{
fStream = new FileStream(_FileName.Value, FileMode.Open, FileAccess.Read);
}
catch (Exception EX)
{
throw EX;
}
BinaryReader bReader = null;
String eMessage = "";
try
{
bReader = new BinaryReader(fStream);
long pos = 0;
while (pos < numBytes) data[pos++] = bReader.ReadByte();
}
catch (Exception EX)
{
eMessage = EX.Message;
}
finally
{
fStream.Flush();
bReader.Close();
fStream.Close();
fStream.Dispose();
}
if (!String.IsNullOrEmpty(eMessage)) throw new Exception(eMessage);
return (new SqlBinary(data));
}
};
In questo caso la funzione deve essere in grado di leggere
da File System, un qualsiasi file, e restituirci un
SqlBinary.
A tale scopo la funzione accetta come unico parametro il nome del
file, ritornando un SqlBinary che decoreremo oppurtamente con
l'attributo SqlFacet impostando la proprietà MaxSize a -1 sempre
per i vincoli degli 8000 bytes.
La funzione dopo aver effettuato gli opportuni controlli sul
paramentro in input, provvede a cercare il file nel File System,
dopodichè memorizza byte a byte il suo contenuto per poi restiturlo
in formato SqlBinary.
Effettuiamo il build ed il deploy della nostra soluzionene ed
avviamo la console managment di Sql Server.
Se tutto è andato a buon fine troveremo le nostre due user defined
function nel nostro database come illustrato nella seguente
figura:
Il seguente script T-SQL permette di testare la funzione di
import:
INSERT INTO TabellaTest VALUES ( dbo.udfCLRImportBinaryFrom('C:\\Test\\TestBitmap.jpg') );
-- Inseriamo adesso un file di tipo word
INSERT INTO TabellaTest VALUES ( dbo.udfCLRImportBinaryFrom('C:\\Test\\TestWord.docx') );
-- A questo punto controlliamo i risultati
SELECT * FROM TabellaTest;
e restituisce il seguente risultato:
Il seguente script T-SQL permette di testare la funzione di
export e dovrebbe generare i corrispettivi file su disco:
-- Non ci resta che provare ad estrarre su File System quello che abbiamo inserito su Sql Server
SELECT dbo.udfCLRSaveBinaryAs(ValoreBinario,'C:\\Test\\'+CONVERT(VARCHAR,Identificativo)+'.jpg') as [FileName]
FROM TabellaTest
WHERE Identificativo=1;
SELECT dbo.udfCLRSaveBinaryAs(ValoreBinario,'C:\\Test\\'+CONVERT(VARCHAR,Identificativo)+'.docx') as [FileName]
FROM TabellaTest
WHERE Identificativo=2;
-- Come risultato finale troveremo 2 nuovi files nella cartella C:\Test 1.jpg e 2.docx
Tags: sql server