Sicherlich wurde hierrüber viel geschrieben, es gibt ja nun auch viele Wege eine Stopred Procedure in einer VB.net-Programmierung aufzurufen. Ein oft wiederkehrender Fall ist aber bsw. eine Funktion zu haben, die einen Insert auf eine Tabelle macht und die Id des gerade eingefügten Datensatzes zurückgibt.
Das Vorgehen demonstriere ich hier in drei Schritten.
1. Tabelle anlegen
2. Stored Procedure mit Rückgabewert definieren
3. Funktion erstellen, die einen Datensatz einfügt und als Rückgabewert die neu eingefügte Id zurückliefert.
Tabelle anlegen
Wir definieren eine einfache Tabelle mit einem Feld "id", welches Identity ist. Dazu noch ein einfaches Textfeld.
CREATE TABLE [dbo].[tbl_Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[TestText] [nvarchar] (50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
In dieser Tabelle wird also bei jedem INSERT automatisch eine id hochgezählt und automatisch vergeben.
Stored Procedure mit Rückgabewert erstellen
Nun wollen wir eine Stored Procedure bauen, welche einen Parameter entgegennimmet, der den TestText repräsentiert und einen weiteren Parameter, der uns die neue ID zurückliefert.
CREATE PROCEDURE dbo.usp_Test_Insert
(
@Id INT OUTPUT,
@TestText NVARCHAR(50) = ''
INSERT INTO dbo.tbl_Test
(
TestText
) VALUES (
@TestText
)
SET @Id = @@IDENTITY
)
Man sieht, das "magische" ist die @@IDENTITY. Hier wird einfach der letzte zugefügte Wert der Identität zurückgegeben. Was dem spitzfindigen Leser direkt auffallen wird, denn damit ist der Wert strenggenommen ja nicht eindeutig, denn wenn zwischen dem INSERT und dem Abruf der @@IDENTITY ein weiterer Datensatz eingefügt wird, wird ein "falscher" Wert zurückgegeben, aber das Szenario lassen wir mal aussen vor.
Ein kleiner Exkurs sei mir gegönnt. Die Benennung erfolgt bei der Stored Procedure mit dem Präfix "usp". Es mag dem ein oder anderen bewußt sein, dass die Built-In-Procedures beim MSSQL-Server mit "sp" als Präfix benannt sind. Und genau das ist der Grund warum ich mit "usp" (User Defined Procedure) präfixe. Denn der SQL Server sucht bei Procedure-Aufrufen immer erst in den System-Prozeduren (also alle mit "sp"). Benennt man die Userdefined Procedures mit einem anderen Präfix hat man den Vorteil dass man diese Suche in den Systemprozeduren umgehen kann, was einen Performance-Vorteil sichert.
Soviel zum Exkurs.
Funktion zur Ausführung der Stored Procedure
Nehmen wir eine Funktion die TestInsert heißt und führen hierin die Execution der Stored Procedure aus, als Rückgabewert der Funktion definieren wir einen Long, der die Id beinhaltet.
Imports System.Configuration 'wenn man auf die app.config zugreifen will
Imports System.Data.SqlClient
Private Function TestInsert(ByVal TestText As String) As Long
Dim lReturn As Long 'das ist unsere neue Id
lReturn = 0
Dim sConnectionString As String
'In dem Fall kommt der ConnectionString aus der app.config
sConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString
Using oSqlConnection As SqlConnection = New SqlConnection(sConnectionString)
oSqlConnection.Open()
Dim oSqlCommand As SqlCommand
'Hier definieren wir den Output-Parameter
oSqlCommand = New SqlCommand("dbo.usp_Test_Insert", oSqlConnection)
oSqlCommand.CommandType = CommandType.StoredProcedure
Dim oSqlParameter As SqlParameter = oSqlCommand.Parameters.AddWithValue("@Id", 0)
oSqlParameter.SqlDbType = SqlDbType.Int
'Da es ein Output-Parameter ist, überschreiben wir den Direction-Default mit InputOutput
oSqlParameter.Direction = ParameterDirection.InputOutput
'Dann den Inputparameter mit Wert einfügen
oSqlCommand.Parameters.AddWithValue("@TestText", TestText)
'und "Feuer"
oSqlCommand.ExecuteNonQuery()
'In der Parameter-Collection können wir nun den Outputparamter auslesen
'und anschließend zurückgeben
lReturn= CType(oSqlCommand.Parameters("@Id").SqlValue.ToString, Long)
End Using
Return lReturn
End Sub
Ich habe bewußt auf Fehlerbehandlungen und Validierungen verzichtet, weil es das Beispiel nur aufblähen würde. Was ich zeigen wollte, wurde damit im Kern gezeigt und ist – da es doch ziemlich Straight-Forward ist – schnell ausbaufähig.
Heute mal ohne Beispiel-Solution :)