DevTrain - Identitys mit ADO .NET und SQL Server

Auch wenn das Konzept von ADO .NET nicht ideal dafür ist, ist es möglich von einer Datenbank generierte Identitätswerte (Identity oder Autowert) auszulesen. Das Problem besteht darin, das ein Update nur One Way funktioniert. Im zweiten Schritt müsste man dann den Datensatz wieder lesen um einen von der DB erzeugten eindeutigen Wert zu erhalten.
CREATE TABLE [Person] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NULL
) ON [PRIMARY]

Das Insert Kommando würde dann wie folgt aussehen.

INSERT INTO person ( [NAME]) Values ('Preishuber')

Was ist aber nun der erzeugte Datensatz. Der letzte? Es könnte schon jemand anderer einen Satz eingefügt haben. Oder man setzt in die Where Bedingung alle Felder um so den eingefügten Datensatz zu erhalten. Diese Methode ist relativ aber nicht 100% zuverlässig und ausserdem aufwendig zu coden.
Mit Stored Procedures lässt sich das einfach lösen und man erhält auch noch eine höhere Performance.
Der Trick ist, das über den Wert @@IDENTITIY die letzte erzeugte Identität zurückgegen wird. NewID muss explizit als Output Parameter deklariert werden.
CREATE PROCEDURE InsertPerson
@para1 varchar(50),
@NEWID Int OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO Person
    (
        [Name]
    )
    VALUES
    (
        @para1
    )
    SET NOCOUNT OFF
    SELECT NEWID = @@IDENTITY
END

Das Nocount wird benötigt, damit nicht die Anzahl der betroffenen Datensätze zurückkommt. Um die Procedure im Query Analyser zu testen, kann man diese über EXEC aufrufen.

exec insertperson @para1='ppedv',@NEWID=''

Dabei muss man beachten, das auch alle Parmeter gesetzt sind. Das gleiche gilt, wenn wir nun an die Umsetz in VB .NET Code gehen. Wie immer müssen die beiden Namespaces System.Data und SqlClient implementiert werden. Auf einem Command Objekt kann dann die Stored Procedure aufgerufen werden.
Dabei muss der Typ des Command Textes gesetzt werden, das dies auch ein SQL Kommando, Tabelle oder eben eine Stored Procedure sein.
Dann muss für jeden Parameter ein ADD ausgeführt werden um ihn ans Kommand Objekt anzufügen.
Dabei wird der Name (@ nicht vergessen) und der Datentype, bei Bedarf auch mit Dimensionierung angeführt werden. Dann wird dem Parameter ein Wert zugewiesen oder wie beim Output Parameter die Direction.
Nach dem Execute kann der Parameter @NEWID einfach ausgelesen werden und man erhält die soeben von der Datenbank erzeugte ID.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DS As DataSet
        Dim MyCmd As SqlCommand
        Dim MyConn As SqlConnection
        MyConn = New SqlConnection("server=(local);database=shops;User ID=sa")
        MyConn.Open()
        MyCmd = New SqlCommand("insertperson", MyConn)
        MyCmd.CommandType = CommandType.StoredProcedure
        MyCmd.Parameters.Add(New SqlParameter("@para1", SqlDbType.VarChar, 50))
        MyCmd.Parameters("@para1").Value = "Test"
        MyCmd.Parameters.Add(New SqlParameter("@NEWID", SqlDbType.Int))
        MyCmd.Parameters("@NEWID").Direction = ParameterDirection.Output
        MyCmd.ExecuteReader()
        Response.Write(MyCmd.Parameters("@NEWID").Value)
End Sub

Dieses Verfahren funktioniert nur mit SQL Servern von Microsoft wie 7.0 oder 2000 und der MSDE.
Die Eingangsbemerkung zur Eignung dieses Verfahrens für ADO .NET soll hier noch kurz erläutert werden. Die Idee ist, die Kommunikation zwischen dem Dataset als Container und der Datenbank zu minimieren. Updates werden für viele Datensätze in einem Rutsch per Update durchgeführt. Schon das anlegen eines Auftrages mit Positionen wird mangels Schlüssel (ID) zum Problem. ADO .NET stellt nun Offline Methoden wie NEwGUID bereit um dies ohne Roundtrip zu ermöglichen.