C# - Extended Stored Procedure létrehozása

forráskód letöltése
MS SQL adatbázis szervert üzemeltetve lehetőségünk nyílik ún. Extended Stored Procedure létrehozására és használatára. Ez abban különbözik a hagyományos tárolt eljáráshoz képest, hogy itt nem egy Transact SQL-ben megfogalmazott parancs halmazról van szó, hanem egy DLL állományról, melyben tetszőleges kódot futtathatunk és így tetszőleges eredmény halmazt generálhatunk a tárolt eljárásunk eredményeként.
Ezt kihasználva szinte korlátlan lehetőségünk nyílik az adatszolgáltatásra, melynek további nagy előnye, hogy az adatbázisunkhoz forduló ügyfél ugyanúgy használhatja ezt a fajta speciális tárolt eljárást, mint a hagyományost.
A mellékelt példaprogramunk kissé kilóg a sorból, mivel most nem a C# nyelvet használtuk a megvalósításhoz, hanem a C++-t. Ennek oka igen egyszerű: a C++-t használva kapunk egy varázslót, mely képes egy ilyen speciális tárolt eljárást tartalmazó DLL keretprogramjának létrehozására, így egyszerűen nekiláthatunk a feladatnak.
Az új project létrehozásához válasszuk a File – New – Project menüpontot. A megjelenő ablakban pedig a Visual C++ kategórián belül az Extended Stored Procedure Dll elemet.
A project név megadása után elindul egy egyszerű varázsló, melynek Application Settings oldalán csupán egyetlen kérdéses paramétert kell megadnunk, mégpedig azt, hogy mi legyen a speciális tárolt eljárásunk neve. Ezt a nevet célszerű az xp_ karakterekkel kezdeni, ezzel is utalva a tárolt eljárás milyenségére.
A varázsló bezárása után létrejönnek a szükséges forráskódok. Ezek közül a proc.cpp lesz számunkra az érdekes. Ebben találunk egy komplett, működőképes Extended Stored Procedure megvalósítást, mely három oszlopban, három sornyi adatot ad vissza.
Mielőtt megnéznénk, hogy miként is működik, próbáljuk ki a tárolt eljárást ebben a formájában. Ehhez fordítsuk le a DLL-t. Következő lépésként installálnunk kell a többi tárolt eljárás közé. Ehhez két módszer is rendelkezésünkre áll.
Az egyik módszer a következő: indítsuk el az SQL Enterprise Manager-t. Tudnunk kell, hogy az Extended Stored Procedure csak a master adatbázisban tárolható, így válasszuk ki ezt, majd ezen belül az Extended Stored Procedures elemet. Itt jobb gombbal kattintva és a New Extended Stored Procedure menüpontot választva megjelenik egy ablak, melyben két adatot kell megadnunk. A Name mezőbe írjuk a tárolt eljárásunk nevét (xp_softwareonline), vagyis azt a nevet, amely függvényt a proc.cpp-ben létrehoztunk. A másik kitöltendő mező pedig a Path, ahol a DLL nevét teljes elérési úttal kell megadnunk.
A másik módszer, mellyel felvehetjük a tárolt eljárás DLL-ünket a következő lépésekből áll:
  • Másoljuk a DLL-t az SQL szerver Binn könyvtárába.
  • Futtassuk a következő tárolt eljárást az SQL szerverben: sp_addextendedproc 'xp_softwareonline', 'ExtendSP.DLL' (Itt az első paraméter a tárolt eljárásunk neve, a második pedig a DLL neve, mely tartalmazz azt.)
Lehetőség van arra is, hogy egy másik tárolt eljárás használatával eltávolítsuk a miénket:
  sp_dropextendedproc 'xp_softwareonline'
További fontos tudnivaló az, hogy amikor a DLL-ünk betöltésre kerül egy hívás miatt, akkor a DLL-hez nem férünk hozzá, mivel az a memóriában van. Ekkor nem tudjuk lecserélni a DLL-t, illetve törölni sem. Ahhoz, hogy a memóriából eltávolításra kerüljön, az alábbi SQL utasítás kiadására van szükség:
  DBCC xp_softwareonline(FREE)
Regisztrálás után nézzük, miként tudjuk kipróbálni a tárolt eljárást: indítsuk el az SQL Query Analyzer-t, majd futtassuk az alábbi utasítást.
exec xp_softwareonline
Az eredményben megjelenik a DLL által generált három oszlop és három sor.
Térjünk vissza most a proc.cpp-hez és nézzük miként működik az xp_softwareonline függvény. Annyit már tudunk, hogy amikor a tárolt eljárás aktivizálásra kerül, akkor valójában ez a függvény lesz az, melyet az SQL szerver meghív, és amelytől az adatokat várja.
RETCODE __declspec(dllexport) xp_softwareonline(SRV_PROC *srvproc)
{
    DBSMALLINT i = 0;
    DBCHAR colname[MAXCOLNAME];
    DBCHAR spName[MAXNAME];
    DBCHAR spText[MAXTEXT];
Szükségünk lesz két DBCHAR típusú szövegre. Az egyiket az spName változóba, a másikat az spText-be másoljuk. Az spName-be a tárolt eljárás neve kell hogy kerüljön, míg az spText-be egy tetszőleges, rövid leírás a tárolt eljárásunkról.
  _snprintf(spName, MAXNAME, "xp_softwareonline");
  _snprintf(spText, MAXTEXT, "%s Sample Extended Stored Procedure", spName);
Ha ez adott, akkor az srv_sendmsg függvény hívásával küldünk egy információs üzenetet a hívó félnek. Itt első paraméterként a függvény paraméterében kapott SRV_PROC típusú paramétert adjuk meg. A második paraméterben az üzenet küldés típusa állítható be. Ennek két értéke lehet:
  • SRV_MSG_INFO – információ küldés esetén
  • SRV_MSG_ERROR – hibaüzenet küldés esetén
A további paraméterekre most nincs szükségünk, kivéve még az utolsó kettőre. Az utolsó előtti paraméterben adható meg az üzenet szövege, míg az utolsóban annak hossza. Abban az esetben, ha null terminált sztringet adunk meg, akkor az utolsó paraméterben használhatjuk a SRV_NULLTERM konstanst.
  srv_sendmsg(
    srvproc,
    SRV_MSG_INFO,
    0,
    (DBTINYINT)0,
    (DBTINYINT)0,
    NULL,
    0,
    0,
    spText,
    SRV_NULLTERM);
Következő lépés lesz az oszlopok megadása. Ehhez az srv_describe függvényt használhatjuk, melyet annyiszor hívunk meg, ahány oszlopot szeretnénk létrehozni az eredmény halmazban. A függvény első paraméterében ismét a kapott SRV_PROC típusú paramétert adjuk át, majd ezt követően az oszlop sorszámát és nevét. A negyedik paraméterben a név hosszát adjuk meg bájtokban, vagy ismét használhatjuk az SRV_NULLTERM konstanst a null terminált sztring használatának jelzésére.
Az ötödik paraméterben az oszlopban elhelyezendő adataink típusát adjuk meg. A cikk végén lévő táblázat összefoglalja, hogy milyen konstans milyen típust takar.
A hatodik paraméterben az adatunk hosszúságát kell megadnunk bájtban mérve. Olyan típusoknál melyek fix méretűek, ott egyszerűen használjuk a SizeOf-ot az érték meghatározásánál, míg egy sztring típus esetén mi adhatjuk meg annak hosszát.
Ezt követő két paraméterben a forrás adat típusát és hosszát, míg végül a forrás adatot adhatjuk meg.
    _snprintf(colname, MAXCOLNAME, "ID");
    srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT2, sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);
    _snprintf(colname, MAXCOLNAME, "spName");
    srv_describe(srvproc, 2, colname, SRV_NULLTERM, SRVCHAR, MAXNAME, SRVCHAR, 0, NULL);
    _snprintf(colname, MAXTEXT, "Text");
    srv_describe(srvproc, 3, colname, SRV_NULLTERM, SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);
Ezt követően a generált függvény a srv_setcoldata függvényt hívja meg. Ennek segítségével egy lépésben beállíthatjuk egy adott mező összes sorában lévő adatot, vagyis alapértelmezett értéket adhatunk, melyet később tetszés szerint még felülbírálhatunk. Első paraméter itt is a szokásos SRV_PROC típusú adat. A másodikban az oszlop sorszámát kell megadnunk, míg végül annak értékét. Ezzel a hívással a második oszlop minden sorába az spName változó értéke kerül.
    srv_setcoldata(srvproc, 2, spName);
Az srv_setcollen függvényt használva megadhatjuk/módosíthatjuk, hogy egy adott oszlopnál hány bájtos adatot tároljon.
    srv_setcollen(srvproc, 2, static_cast<int>(strlen(spName)));
A következőben a sorok létrehozásával foglalkozunk, melyhez egy for ciklust használva hozunk létre hármat.
    for (i = 0; i < 3; i++) {
Az első oszlopba a ciklus változó aktuális értékét írjuk.
      srv_setcoldata(srvproc, 1, &i);
A harmadik oszlopba egy olyan szöveget adunk meg, melyben szintén szerepel a ciklus változó értéke.
    _snprintf(spText, MAXTEXT, "%d) Sample rowset generated by the %s extended stored procedure", i, spName);
    srv_setcoldata(srvproc, 3, spText);
    srv_setcollen(srvproc, 3, static_cast<int>(strlen(spText)));
Az adott sor tényleges elküldéséhez az srv_sendrow függvény hívása szükséges. Most a ciklus magon belül nem adtunk értéket a második oszlopnak, de emlékezzünk vissza, hogy ennek beállítottunk egy alapértéket, így ez kerül átadásra.
    srv_sendrow(srvproc);
  }
Az oszlopok és sorok létrehozása után tudatnunk kell az SQL szerverrel, hogy tárolt eljárásunk befejezte ténykedését, végzett az adatküldéssel. Ehhez az srv_senddone függvényt kell meghívnunk. Első paraméter a szokásos srvproc. A másodikban az alábbi konstansok kombinációjából származó értéket adhatjuk meg:
  • SRV_DONE_FINAL – az elküldött eredmény halmaz a végleges volt
  • SRV_DONE_MORE – az elküldött eredmény halmaz még nem az összes volt (MS SQL szerver esetén, ha ténylegesen végeztünk az adatküldéssel, akkor is ezt kell használnunk, mivel az SRV_DONE_FINAL jelzést maga az MS SQL szerver küldi majd)
  • SRV_DONE_COUNT – a negyedik paraméter valós adatot tartalmaz
  • SRV_DONE_ERROR – hiba történt
A harmadik paraméter fenntartott, nem használt.
A negyedikbe adjuk meg az elküldött sorok számát, ha a második paraméterben szerepel a SRV_DONE_COUNT konstans.
  srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);
  return XP_NOERROR ;
}
Extended Stored Procedure-ben használható típusok:
Megnevezés Típus
SRVBIGBINARY binary
SRVBIGCHAR char
SRVBIGVARBINARY varbinary
SRVBIGVARCHAR varchar
SRVBINARY binary
SRVBIT bit
SRVBITN bit null
SRVCHAR char
SRVDATETIME datetime
SRVDATETIM4 smalldatetime
SRVDATETIMN datetime null
SRVDECIMAL decimal
SRVDECIMALN decimal null
SRVFLT4 real
SRVFLT8 float
SRVFLTN real | float null
SRVIMAGE image
SRVINT1 tinyint
SRVINT2 smallint
SRVINT4 Int
SRVINTN tinyint | smallint | int null
SRVMONEY4 smallmoney
SRVMONEY money
SRVMONEYN money | smallmoney null
SRVNCHAR nchar
SRVNTEXT ntext
SRVNUMERIC numeric
SRVNUMERICN numeric null
SRVNVARCHAR nvarchar
SRVTEXT text
SRVVARBINARY varbinary
SRVVARCHAR varchar