C# - Identity oszlop értékének kiolvasása Insert into esetén

forráskód letöltése
Ha adott egy tárolt eljárás, mely képes egy új sor felvételére az adattáblába és ennek a táblának van identity típusú oszlopa, akkor problémánk adódik abból, hogy ennek az oszlopnak a megjelenítése az alkalmazásba nem történik meg automatikusan, így az érték rejtve marad a felhasználónk elől. Sok esetben lehet, hogy épp ez a kívánatos. Azonban ha alkalmazásunknak az identity oszlop értékét is meg kell mutatnia a felhasználónak, akkor ezt a problémát valahogy meg kell oldanunk.
A példából megtudhatjuk továbbá azt is, hogy miként tudunk programból adatbázist, táblát, tárolt eljárást létrehozni.
A probléma leküzdéséhez elő kell állítanunk azt: ehhez szükségünk lesz egy adatbázisra, benne egy táblára, melybe új sorokat vehetünk fel, valamint kell egy tárolt eljárás, mely elvégzi az új sor felvitelét. A teljes tábla tartalmát szeretnénk egy DataGrid-ben mindvégig látni, még a tábla első oszlopát is, mely egy identity értéket tartalmaz, melyet az MS SQL szerver fog kiosztani, így az csak a tárolt eljárás futása után válik elérhetővé.
Adatbázis létrehozása
Az adatbázis létrehozását egy SQL create database utasítás lefuttatásával végezzük el. Ehhez szükségünk lesz egy kapcsolatra az adatbázissal, melyet az SqlConnection osztály segítségével hozunk létre. Ezt követően összeállítjuk az SQL utasítást, mely létrehozza az adatbázist, melynek állományai az EXE mellé kerülnek.
Az elkészült SQL utasítás futtatásához egy SqlCommand osztályt használunk, melynek ExecuteNonQuery függvénye lefuttatja azt, úgy hogy visszatérési adathalmazt nem kapunk. Ennek nyilván nem is lenne értelme, hiszen a create database ilyet nem ad vissza.
    private void button1_Click(object sender, System.EventArgs e)
    {
      SqlConnection connection = new SqlConnection("initial catalog=master;data source="+textBox1.Text+";user id="+textBox2.Text+";password="+textBox3.Text);    
      connection.Open();
      string s="";
      s+="create database UpdateSPData\n";
      s+="on\n";
      s+="(\n";
      s+="  name=UpdateSPData,\n";
      s+="  filename='"+Application.StartupPath+"\\UpdateSPData.mdf'\n";
      s+=")\n";
      SqlCommand command = new SqlCommand(s, connection);            
      command.ExecuteNonQuery();
      ...
    }
Tábla, tárolt eljárás létrehozása
Következő lépés egy adattábla és tárolt eljárás létrehozása lesz. A módszer ugyanaz: adatbázis kapcsolat, sql sztring és SqlCommand-al futtatás.
A tábla létrehozásánál a create table utasítást használjuk. A tábla két oszlopot fog tartalmazni ID névvel készül az identity típusú oszlop és Value01 névvel egy szöveges mező, melybe tetszőleges adatot tárolhatunk.
    private void button2_Click(object sender, System.EventArgs e)
    {
      ...      
      string s="";
      s+="create table Table01\n";
      s+="(\n";
      s+="  ID int identity(1, 1),\n";
      s+="  Value01 varchar(30)\n";
      s+=")\n";
      SqlCommand command = new SqlCommand(s, connection);            
      command.ExecuteNonQuery();
A tárolt eljárásnál lesz a felmerült problémánk megoldásának egyik kulcsa: tudjuk, hogy miután a tárolt eljárás lefutott az új sor ID mezője az SQL szervertől megkapta az értékét. Ezt felhasználva a tárolt eljárás végén meghatározhatjuk ezt a számot és a tárolt eljárás egy paraméterében visszaadhatjuk azt alkalmazásunk számára.
Két paramétert hozunk tehát létre a tárolt eljáráshoz: az egyiket v01 névvel, ez lesz majd a Value01 oszlop értéke, és egy retval nevű paramétert, melynél az output kulcsszó megadásával lehetővé tesszük, hogy visszaadjon egy értéket a hívó alkalmazásnak.
Az új sor beszúrását az insert into utasítás végzi. Ezt követően az @@identity változó tartalmazni fogja a számunkra szükséges értéket, melyet most egyszerűen értékül adunk a retval paraméternek.
      s="";
      s+="create procedure Proc01\n";
      s+="  @v01 varchar(30), @retval int output\n";
      s+="AS\n";
      s+="  insert into Table01 (Value01)\n";
      s+="  values (@v01)\n\n";
      s+="set @retval = @@identity\n";
      command.CommandText=s;
      command.ExecuteNonQuery();
      ...
    }
Új sor felvétele a tárolt eljáráson keresztül
Ennyi előkészület után nézzük most miként történik az új sor felvétele a táblába és hogyan jelenik meg a DataGrid-ben az identity típusú oszlop értéke is.
Két SQL utasításra is szükségünk lesz: az egyik az insert műveletet elvégző tárolt eljárás hívása, a másik egy select utasítás futtatása, mellyel lekérdezhetjük az új adatokat. Ezek összefogására egy SqlDataAdapter osztályt hozunk létre. Ennek InsertCommand property-jébe fogjuk megadni a tárolt eljárás hívásával kapcsolatos dolgokat, míg a lekérdezés futtatásához a SelectCommand property-t használjuk.
Mivel a tárolt eljárás paraméterezett, így az InsertCommand Parameters property-jének Add függvényével fel kell vennünk a szükséges paramétereket. Nézzük meg mit is tud ez az Add függvény.
Add
Osztály: System.Data.SqlClient.SqlParameterCollection
public SqlParameter Add(
string parameterName,
SqlDbType sqlDbType,
int size,
string sourceColumn
);
Az SqlParameterCollection kollekcióba vehetünk fel egy új SqlParameter osztályt.
Paraméterek
string parameterName
A paraméter neve.
SqlDbType sqlDbType
A paraméter típusa. Lehetséges értékeit lásd alább.
int size
A paraméter mérete. Nem minden esetben használatos. Pl.: int típus esetén a méret adott, de varchar típusnál itt adhatjuk meg, hogy maximum hány karakteres legyen.
string sourceColumn
Forrás oszlop megnevezése.
Visszatérési érték
Visszatérési értékként kapunk egy referenciát a létrejött új SqlParameter osztályról.
Megjegyzés
Az sqlDbType paraméter lehetséges értékei az SqlDbType felsorolt típus elemei közül kerülhet ki.
Név Leírás
BigInt Int64 típusnak felel meg.
Binary Bájtokat tartalmazó tömb, melynek hossza 1 és 8000 között lehet.
Bit Boolean típusnak felel meg, de numerikusan tárolva. Lehetséges értéke 0, 1, vagy null.
Char String típusnak felel meg. Unicode karaktereket nem tartalmazó szöveg, melynek hossza 1 és 8000 karakter közötti.
DateTime DateTime típusnak felel meg. 1753. január 1 és 9999 december 31. közötti időtartományban 3.33 ezredmásodperces időközzel.
Decimal Decimal típusnak felel meg.
Float Double típusnak felel meg.
Image Bájtokat tartalmazó tömb, melynek hossza 0 és 2 147 483 647 között lehet.
Int Int32 típusnak felel meg.
Money Decimal típusnak felel meg. Értéke -922 337 203 685 477.5808 és 922 337 203 685 477.5807 között lehet.
NChar String típusnak felel meg. Unicode karaktereket tartalmazhat, melynek hossza 1 és 4000 karakter közötti.
NText String típusnak felel meg. Unicode karaktereket tartalmazhat, melynek hossza 1 és 1 073 741 823 karakter közötti.
NVarChar String típusnak felel meg. Unicode karaktereket tartalmazhat, melynek hossza 1 és 4000 karakter közötti.
Real Single típusnak felel meg.
SmallDateTime DateTime típusnak felel meg. 1900. január 1 és 2079. június 6. közötti időtartományban 1 perces időközzel.
SmallInt Int16 típusnak felel meg.
SmallMoney Decimal típusnak felel meg. Értéke -214 748.3648 és 214 748.3647 között lehet.
Text String típusnak felel meg. Unicode karaktereket nem tartalmazó szöveg, melynek hossza 1 és 2 147 483 647 karakter közötti.
Timestamp DateTime típusnak felel meg, ahol a formátum a következő: yyyymmddhhmmss
TinyInt Byte típusnak felel meg.
UniqueIdentifier GUID típusnak felel meg.
VarBinary Bájtokat tartalmazó tömb, melynek hossza 1 és 8000 között lehet.
VarChar String típusnak felel meg. Unicode karaktereket nem tartalmazó szöveg, melynek hossza 1 és 8000 karakter közötti.
Variant Object típusnak felel meg.
A lényeg tehát az Add függvény negyedik paraméterében van. Itt adhatjuk meg a forrás oszlopot. Ha megfigyeljük a forráskódot a tárolt eljárás paramétereinél, mikor a retval paramétert hozzuk létre, ott a forrás oszlopnak az ID oszlopot jelöljük meg. Ezzel tudjuk a visszatérési értéket összekötni a DataGrid egy oszlopával.
    private void button3_Click(object sender, System.EventArgs e)
    {
      ...
      SqlDataAdapter da = new SqlDataAdapter();
      da.InsertCommand = new SqlCommand("Proc01", connection);
      da.InsertCommand.CommandType = CommandType.StoredProcedure;
      da.InsertCommand.Parameters.Add("@v01", SqlDbType.VarChar, 30, "Value01");
      da.InsertCommand.Parameters[0].Value="Software Online";
      da.InsertCommand.Parameters.Add("@retval", SqlDbType.Int, 0, "ID");
      da.InsertCommand.Parameters[1].Direction = ParameterDirection.Output;
      da.InsertCommand.ExecuteNonQuery();
Az új sor beszúrása után futtatjuk a select-et, mely visszaadja a tábla aktuális tartalmát.
      da.SelectCommand = new SqlCommand("select * from Table01", connection);
      da.SelectCommand.ExecuteNonQuery();
      ...
    }