Come noto in Sql Server le stored procedure possono essere scritte usando codice .Net, tramite l’ausilio della Sql Server CLR Integration.
In questo post esporrò la procedura pratica passo-passo per creare una stored procedure in C#, e utilizzando le funzionalità di Visual Studio 2017 che permettono di raggiungere l’obbiettivo in modo facile e intuitivo.
Quanto scritto vale perfettamente e identicamente (si può dire ?) anche per Visual Studio 2015.
Premessa
Non so Voi, ma a me spesso riesce difficile scrivere alcune stored procedure direttamente in T-SQL: infatto SQL è un linguaggio prettamente dichiarativo, e a volte molte elaborazioni necessarie per estrarre dati richiedono procedure prettamente procedurali.
Linguaggi dichiarativi->Sono linguaggi in cui si descrive il risultato che si vuole ottenere (T-SQL è uno di questi).
Linguaggi Procedurali->Si descrive come ottenere un risultato (C# per esempio).
Ora, come noto T-SQL permette anche di scrivere codice usando lo stile procedurale (vedi cursori, etc), ma per chi, come me, è abituato per il 90 % della sua giornata a usare C# e PHP, e quindi stili di programmazione prettamente procedurali, diventa difficile convincere il cervello a fare lo “switch” a codice dichiarativo.
Per tale motivo per qualche stored procedure particolarmente complessa non disdegno di usare la SQL CLR Integration, e quindi di scrivere stored procedure usando codice C#.
Devo osservare anche usare questo tipo di stored procedure può introdurre anche importanti benefici nelle perfomance: in alcune situazioni i tempi di esecuzione sono sensibilmente più brevi rispetto allo stesso codice scritto in T-Sql.
Fine Premessa (era inutile ??)
Il Common Language Runtime (CLR) è la base fondamentale di Microsoft .NET Framework ed è la parte delegata all’esecuzione di managed code ottentuta da linguaggi compatibili.
Sql Server si integra con il CLR usando la funzionalità CLR integration, che in sostanza si traduce nella possibilità di scrivere diversi elementi procedurali usando managed code, invece di T-Sql.
- Stored procedures
- Triggers
- User-defined functions (UDF)
- User-defined types
- User-defined aggregates
Iniziamo creando un nuovo progetto di tipo Sql Server Database Project.
Una volta ottenuto il progetto occorre agire sulle proprietà dello stesso, e in Project Settings selezionare la versione di Sql Server che ospiterà il codice.
Fatto questo è il momento di mettere la classe che ospiterà la stored procedure scritta in C#: con il tasto destro sul progetto selezionare Add -> New Item -> SQL CLR C# -> SQL CLR C# Stored Procedure.
L’esempio permetterà di scrivere una stored procedure che restituirà il numero di righe presenti in una tabella Documents ma filtrando per il campo DataDocumento posto sulla tabella stessa.
Il valore sarà restituito tramite un parametro out: similmente la data delegata al filtro della tabella che sarà passata come come argomento alla stored procedure stessa.
Il codice sopra usa l’oggetto SqlConnection, che rappresenta la connessione con il database ove è ospitata la stored procedure stessa, che riempie un oggetto DataTable partendo da una query parametrizzata.
Una volta riempita l’istanza della DataTable un loop sulle righe di questa permetterà di ottenere il valore richiesto.
Occorre evidenziare l’uso dell’attributo del metodo static Microsoft.SqlServer.Server.SqlProcedure, che in pratica afferma che il metodo rappresenta una stored procedure.
Chiaramente l’esempio ottiene tale valore usando un approccio completamente sbagliato: sarebbe stato molto più agile usare la proprietà Rows della DataTable stessa, e qui usare Count, o anche usare direttamente un’istruzione in T-Sql come quella nel seguito,
SELECT COUNT(*) FROM Documenti WHERE DataDocumento >=....
Comunque anche se l’esempio è oggettivamente bislacco permette di esporre come sia possibile utilizzare, all’interno di questo tipo di codice, in modo consistente e completo gli oggetti del namespace System.Data.SqlClient.
Prima di proseguire occorre verificare che l’istanza di sql server che accoglierà il codice abbia la CLR integration abilitata.
Per verificare lo stato dell’abilitazione della caratteristica usare la seguente istruzione.
EXEC sp_configure 'clr enabled';
Per eventualmente abilitarla usare le due istruzioni T-Sql di seguito, da lanciare direttamente nell’istanza destinataria.
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
Per deploiare la stored procedure sull’istanza di sql server all’interno del database selezionato agire con il tasto sul progetto e quindi selezionare Publish: verrano richiesti i dati di connessione all’istanza e il database.
Una volta completata la procedura sarà possibile usare la stored procedure direttamente usando qualcosa del genere.
DECLARE @_NumRighe int,@_dataMinimaDoc datetime set @_dataMinimaDoc = convert(datetime, '2016.10.23', 102) EXEC [dbo].[SqlStoredProcedure01] @_dataDoc = @_dataMinimaDoc, @_NumRighe = @_NumRighe OUTPUT select @_NumRighe
A questo punto scommetto che vorreste fare anche il debug !? Bene la cosa è possibile ma si complica un poco. Per iniziare occorre che veriifcar che le versioni in uso di Sql Server e di Visual Studio permettano il debug remoto….
Dopo questa verifica se l’istanza di Sql Server è sulla stessa macchina in cui si sta eseguendo il Visual Studio 2017 tutto dovrebbe andare alla grande senza grossi impedimenti.
Diversamente se le macchine sono diverse: infatti occorre che sulla macchina su cui è ospitata l’istanza di Sql Server sia installato e in esecuzione il Visual Studio Remote Debugger.
Questo software si scarica dal sito di M$ (vedere linkografia).
Fatto questo per entrambi i casi occorre abilitare il debug da Visual Studio: View -> Sql Server Object Explorer -> Aggiungere, se già non c’è, la connessione all’istanza -> Cliccare 
quindi sulla stessa connessione e con il tasto destro agire su Application Debugging e Allow SQL/CLR Debugging abiltandoli entrambi.
A questo punto, sempre usando il Sql Server Object Explorer, occorre rintracciare la stored procedure in analisi (Database -> Programmability -> Stored Procedures -> SqlStoredProcedure01) e agendo con il tasto destro selezionare la voce Execute Procedure.
Verrà presentata una nuova maschera con il codice T-Sql necessario a eseguire la stored procedure in analisi (cioè con gli argomenti già precompilati).
Modificando eventualmente il codice T-Sql proposto e agendo sui bottoni in alto a sinistra (Execute e Execute With Debugger) sarà possibile sia eseguire la stored procedure che introdurre le attività di Debug nel managed code.
Rimane ora il problema di come eseguire il deploy su altre istanze di Sql Server.
Una volta ottenuta la dll ottenuta dalla compilazione del progetto spostare questa sulla macchina che accoglie l’istanza di Sql Server.
Lanciare, quindi, le seguenti istruzioni T-Sql.
USE [DATABASE_DESTINAZIONE]<br>GO<br>CREATE ASSEMBLY InfPressapochista from 'c:\[Path alla dll]\[Nome File].dll' WITH PERMISSION_SET = SAFE;L’assembly rappresenta, all’interno di un’istanza di Sql Server, una dll scritta in managed code, al cui interno possono essere ospitate un numero arbitrario di stored procedure, funzioni, etc etc.
Una volta creato con successo l’Assembly è sufficiente usare la seguente istruzione, che crea la stored procedure sfruttando l’assembly stesso.
CREATE PROCEDURE [dbo].[SqlStoredProcedure01]
@_dataDoc DATETIME, @_NumRighe INT OUTPUT
AS EXTERNAL NAME [InfPressapochista].[StoredProcedures].[SqlStoredProcedure01]
Occorre dire che per eseguire questa attività esistono svariati modi: io ho esposto il metodo “manuale”, più lungo e macchinoso, ma che permette di comprendere altri aspetti dell’attività.
Per esempio è possibile eseguire il deploy senza dover specificare alcuna path della dll poichè il codice binario della stessa può essere passato come argomento dell’istruzione ASSEMBLY, esemplificando non poco l’attività.
Linkografia
Follow