Publikoval Michal Kočí dňa 20.9.2004 o 11:26 v kategórii .Net
Medzi nové funkcie Microsoft SQL Serveru 2005 patrí aj môžnosť tvorby určitých databázových objektov v Microsoft Common Language Runtime-e (CLR). Jedným z týchto objektov sú aj uložené procedúry (Stored Procedures).
Uložené procedúry sú zjednodušene povedané často používané časti kódu. Kým v Microsoft SQL Servery 2000 sa musel na ich vytvorenie použiť jazyk T-SQL, v Microsoft SQL Servery 2005 sa jednak môže použiť T-SQL, druhak môže byť uložená procedúra vytvorená v ľubovoľnom programovacom jazyku, v ktorom sa dajú programovať assembly pre platformu .Net. Ja budem všetko demonštrovať na jazyku C# 2.0.
Postup pre naprogramovanie a používanie uložených procedúr sa dá zhrnúť do nasledujúcich bodov:
Poznámka: Vytváranej assembly musíme pridať referenciu (reference) na assembly sqlaccess.dll nacházajúcu sa v adresári c:\program files\microsoft sql server\mssql.1\mssql\binn. Táto referencia Vám nepôjde pridať vo Visual Studio-u 2003.
Ako si ukážeme, metódy, ktoré budeme programovať nemusia ale môžu prijímať parametre, parametre môžu byť aj výstupné. Metódy môžu vracať ako výsledok vo forme tabuľky (tabular results), tak aj správy (messages), alebo hodnoty už spomenutými výstupnými parametrami.
Demonštráciu začnem jednoduchou metódou, ktorej jedinou akciou bude vrátenie správy obsahujúcej moju prezývku. Začnem ukážkou C# kódu:
[SqlProcedure]
public static void ShowMyName()
{
SqlPipe sqlPipe =
SqlContext.GetPipe();
sqlPipe.Send("mifko");
}
Verejnú statickú metódu označíme atribútom SqlProcedure. V našej metóde získame statickou metódou GetPipe triedy SqlContext objekt triedy SqlPipe a jeho metódu Send vrátime správu obsahujúcu moju prezývku. Triviálny príklad bez hlbšieho významu, ale niekde začať treba.
Ďaľším krokom bude zaregistrovanie nami vytvorenej assembly v SQL Servery. Na zaregistrovanie slúži T-SQL príkaz CREATE ASSEMBLY, ktorého syntax (len nami potrebná časť syntaxe) je:
CREATE ASSEMBLY nazov_assembly FROM subor_assembly WITH PERMISSION_SET = SAFE | EXTERNAL_ACCESS | UNSAFE
Napríklad registrácia našej assembly bude nasledovná:
CREATE ASSEMBLY Mifko FROM 'c:\mifko\Mifko.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
Čiže, preložené do ľudskej reči, vytvárame assembly s názvom Mifko, ktorej súbor sa volá Mifko.dll a nachádza sa v adresári mifko v roote disku c. Danej assembly poskytujeme práva EXTERNAL_ACCESS. Názov assembly budeme používať pri registrácii uložených procedúr, názov súboru je jasná vec, takže ostáva už len popis možných poskytnutých práv:
V tomto momente si dovolím poznamenať jednu maličkosť. Ak na vytvorenie assembly použijete Visual Studio 2005 Express, pravdepodobne sa pri registrácii assembly stretnete s chybovou hláškou podobnou tejto:
CREATE ASSEMBLY failed because method 'get_Value' on type 'Sql2005StoredProcedures.Properties.Settings' in safe assembly 'Mifko' is storing to a static field. Storing to a static field is not allowed in safe assemblies.
Ak budete assembly vytvárať kombináciou Notepad/C# compiler, chybovej hláške by ste sa mali vyhnúť. Popis odstránenia problému som našiel v MrDave's WebLog v poste Sql2005 Managed Stored Proc Error (rada v komentároch od Rushi Desai)
Keď už máme zaregistrovanú assembly, je potrebné ešte zaregistrovať uloženú procedúru. Skrátená syntax registrácie:
CREATE PROCEDURE meno_procedury AS EXTERNAL NAME
meno_assembly.trieda_vratane_namespace.nazov_metody
Treba však dať pozor, ak meno assembly, meno triedy vrátane namespace alebo názov metódy obsahujú nepovolené znaky (napríklad bodku), treba ich uzavrieť do hranatých zátvoriek, alebo do úvodzoviek. Príklad registrácie vyššie uvedenej metódy ShowMyName:
CREATE PROCEDURE ShowMyName AS EXTERNAL NAME [Mifko].[Mifko.StoredProcedures.SqlServer2005StoredProcedures].[ShowMyName]
Poznámka: Mnou vytvorená metóda sa nachádza v triede SqlServer2005StoredProcedures v namespace Mifko.StoredProcedures.
Dobré je tiež vedieť syntax na odregistrovanie procedúry, alebo assembly, uvádzam už len príklad, tentokrát na odregistrovanie vyššie uvedenej procedúry a assembly:
DROP PROCEDURE ShowMyName GO DROP ASSEMBLY Mifko GO
Poznámka: Je dobré vedieť, že ak zaregistrujete assembly a procedúru, následne zmeníte kód a celú assembly prekompilujete, zmeny sa neprejavia. Treba ich odregistrovať a znovu zaregistrovať aby sa zmeny prejavili. Bližšie som dôvod neskúmal, ale zrejme si SQL Server assembly odloží niekam bokom, keby si ju totiž držal, potom by Visual Studio nepovolilo kompiláciu assembly, lebo by nevedelo prepísať držaný súbor.
Spománal som, že procedúra môže prijímať vstupné parametre. Ukážkou bude procedúra, ktorá príjme jedným parametrom názov tabuľky a druhým počet záznamov, ktoré z nej chceme zobraziť. Metóda si pripraví SELECT príkaz, ktorý bude tvoriť výsledok procedúry:
[SqlProcedure]
public static void SelectTopFrom(string tableName, int topN) { SqlPipe sqlPipe = SqlContext.GetPipe(); SqlCommand sqlCmd = SqlContext.GetCommand(); sqlCmd.CommandText = string.Format("SELECT TOP {0} * FROM {1}", topN, tableName); ISqlReader slqReader = sqlCmd.ExecuteReader(); sqlPipe.Send(slqReader); }
Vidíme volanie ďaľšej statickej metódy triedy SqlContext, metódy GetCommand. Tá nám vráti objekt typu SqlCommand, pomocou ktorého môžeme nad databázou púšťať SQL dotazy. V našom prípade to bude SQL, ktorý vráti tabuľkový výsledok - požadovaný počet záznamov z požadovanej tabuľky. Ako v predcházajúcej metóde, aj v tejto voláme metódu Send objektu typu SqlPipe. Tentoraz však nevraciame správu (parameter typu String), ale tabuľkový výsledok (tabular result; parameter typu ISqlReader).
Túto metódu v SQL Servery zaregistrujeme nasledovne:
CREATE PROCEDURE SelectTopFrom( @tableName nvarchar(255), @topN int) AS EXTERNAL NAME [Mifko].[Mifko.StoredProcedures.SqlServer2005StoredProcedures].[SelectTopFrom]
Použiť ju môžeme štandardným volaním príkazov EXECUTE (alebo skrátenou formou EXEC).
EXECUTE SelectTopFrom 'Sales.Currency', 10
Veľmi podobná metóda je aj nasledovná s tým rozdielom, že jeden z jej parametrov bude výstupný (output). Procedúre predáme názov tabuľky a ona nám spočíta SQL dotazom počet záznamov. Tento počet nám vráti vo výstupnom parametri. Výstupný parameter označíme v C# tak ako sme zvyknutý - klúčovým slovom out:
[SqlProcedure]
public static void CountOfRecords(
string tableName, out int count)
{
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand sqlCmd = SqlContext.GetCommand();
sqlCmd.CommandText = string.Format(
"SELECT COUNT(*) FROM {0}", tableName);
count = (int) sqlCmd.ExecuteScalar();
}
Registrácia takejto metódy v SQL Servery bude:
CREATE PROCEDURE CountOfRecords( @tableName nvarchar(255), @count int output) AS EXTERNAL NAME [Mifko].[Mifko.StoredProcedures.SqlServer2005StoredProcedures].[CountOfRecords]
A použitie s testom - výpisom do správ:
DECLARE @pocet int EXEC CountOfRecords 'Sales.Currency', @pocet output PRINT @pocet
Všetky doposiaľ uvedené príklady sa ovšem dajú docela jednoducho naprogramovať aj v T-SQL. Nasledujúci príklad by v T-SQL nešiel vôbec, alebo s docela veľkými problémami. Vytvoríme si metódu, ktorá nám bude vracať tabuľkový výsledok obsahujúci názvy súborov a ich veľkosti v nami požadovanom adresári. Kód je trochu dlhší, ale demonštruje, ako v metóde za behu zostrojiť tabuľkovú štruktúru a odoslať ju z metódy:
[SqlProcedure]
public
static void FilesInDirectory(string path) {
SqlMetaData[] rowSchema = new SqlMetaData[]
{
new SqlMetaData("filename", SqlDbType.NVarChar, 1024),
new SqlMetaData("filesize", SqlDbType.Int)
};
SqlMetaData tableSchema = new SqlMetaData(
"row", SqlDbType.Row, rowSchema);
DirectoryInfo dirInfo = new DirectoryInfo(path);
FileInfo[] files = dirInfo.GetFiles();
SqlContext.GetPipe().SendResultsStart(
SqlContext.GetConnection().CreateRecord(tableSchema), false);
foreach(FileInfo file in files)
{
SqlDataRecord newRow =
SqlContext.GetConnection().CreateRecord(tableSchema);
Int32 fileSize = (Int32) file.Length;
newRow.SetSqlString(0, file.Name);
newRow.SetSqlInt32(1, fileSize);
SqlContext.GetPipe().SendResultsRow(newRow);
}
SqlContext.GetPipe().SendResultsEnd();
}
V metóde začneme tým, že vytvoríme schému riadku popis jednotlivých stĺpcov - rowSchema - objet typu SqlMetaData[]. Ďalej vytvoríme schému tabuľky - tableSchema - objekt typu SqlMetaData, ktorá bude obsahovať schému stĺpcov. Odosielanie začneme metódou SendResultsStart, jednotlivé riadky budeme odosielať metódou SendResultsRow a odosielanie ukončíme volaním metódy SendResultsEnd. Všetko sú to opäť metódy objektu typu SqlPipe.
Túto metódu by sme v SQL Servery zaregistrovali nasledovne:
CREATE PROCEDURE FilesInDirectory( @path nvarchar(255) ) AS EXTERNAL NAME [Mifko].[Mifko.StoredProcedures.SqlServer2005StoredProcedures].[FilesInDirectory]
Zoznam súborov v roote disku c by sme mohli získať nasledovne:
EXEC FilesInDirectory 'c:\'
Práve kvôli tejto metóde sme museli assembly prideliť práva na úrovni EXTERNAL_ACCESS. Ostatné metódy by si vystačili s právami na úrovni SAFE. Bohužial nejde v SQL Servery zaregistrovať tá istá assembly dva krát, vždy pod iným menom s inými pridelenými právami. Najlepšie by asi bolo izolovať poslednú metódu od ostatných do inej assembly.
Musím skonštatovať, že T-SQL plnilo svoj účel celkom dostatočne, avšak možnosť spúšťať z SQL Servera de facto ľubovoľný kód je skvelá. Samozrejme, niektoré veci sa budú naďalej riešiť v T-SQL (napríklad okrem poslednej metódy všetky uvedené - majú slúžiť len ako príklad, použiť na implementáciu jednoduchého SQL dotazu C# by bolo asi ako brať kanón na vrabce), ale veci, ktoré sa doteraz nedali riešiť v T-SQL sa budú dať riešiť docela jednoducho. Klobúk dole, platforma .Net ma úplne dostáva zo dňa na deň viac a viac :)
Stiahnite si zdrojové a binárne kódy a ukážky (4 kB).
Ak nechceš premeškať príspevky ako je tento, sleduj ma na Twitteri, alebo ak máš RSS čítačku, môžeš sledovať môj RSS kanál.