Stored Procedures mit Rückgabewert in VB.net

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 :)

Access Migration: Stolperstein Rechtsklick

Ein weiterer Stolperstein bei der Migration von älteren Access Versionen auf die aktuelle Access 2007er Version.
Bei einigen Formularen führt ein Rechtklick auf einen Totalabsturz der Applikation. Das Verhalten tritt häufig auf, wenn Unterformulare im Spiel sind, ein eindeutiges Muster lässt sich aber nicht erkennen.

Auch hierfür hat Microsoft einen Hotfix herausgebracht, der das Problem in den Griff bekommt. Zu bekommen ist der Hotfix im KB-Artikel 952699 mit der offiziellen Beschreibung (u.a.) "On a client computer that is running a Japanese version of the operating system, Access 2007 may stop responding when you right-click the subform on a database form.".
Lassen Sie sich nicht irritieren von der japanischen Sprachversion. In unserem Umfeld löste dieser Hotfix das Problem auch auf englischen und deutschen Sprachversionen.

Access Migration: Stolperstein Performance

Eigentlich mag man glauben, dass mit einer Umstellung auf eine neuere Softwareversion alles ein wenig besser und schneller wird. Bei der Migration von Access-Datenbanken und Access-Datenbankprojekten von 2000/ 2003/ XP auf Access 2007 gibt es hierbei aber mitunter eine Überraschung:
Die Performance kann weitaus schlechter sein. Gerade bei der Migration von Access-Projekten (ADP) habe ich dieses öfters festgestellt und wurden Performanceverluste von bis zu 250% gemessen.

Der entscheidende Hinweis zum Umgehung dieser Performanceeinbuße ist in einem Knowledge-Base-Artikel bei Microsoft zu finden. Es existiert nämlich ein Hotfix der hier helfen kann. Erreichbar unter
KB 936519 und bietet laut offizieller Beschreibung die folgenden Hilfe: "When you try to run a Microsoft Access project (.adp) in Access 2007, the project may run slowly. Or, the project may take longer than expected to respond to actions that are performed on the project."

A Programmer’s Blog about VB, MSSQL and slightly different subjects