23.2 Verbindungen mit dem Datenprovider

Da jeder Datenbanktyp seinen eigenen Provider hat, muss dieser zuerst festgelegt werden (siehe Abschnitt 23.1, »ADO.NET-Provider«). Die Klassen jedes .NET-Datenproviders sind jeweils in einem eigenen Namensraum in der .NET-Klassenbibliothek untergebracht.

In diesem Abschnitt bauen wir eine Verbindung zum SQL Server unter Einsatz des SqlClient-Datenproviders auf. Um einen eventuellen Providerwechsel zu vereinfachen, werden wir mit Referenzen auf allgemeinere Typen arbeiten. Sie sollten mit

Imports System.Data.Common 
Imports System.Data.SqlClient

die entsprechenden Namensräume einbinden, um nicht jedes Mal vollqualifizierte Namen tippen zu müssen.

23.2.1 DbConnection-Objekt

Die Verbindung zu einer Datenbank wird durch ein DbConnection-Objekt beschrieben. Die Klasse ist abstrakt, und das konkrete Objekt wird durch eine Klasse erzeugt, in deren Namen das Präfix Db so ersetzt wird, dass es den verwendeten .NET-Datenprovider kennzeichnet. Benutzen Sie den SqlClient-Datenprovider, heißt die Klasse SqlConnection, beim OleDb-Datenprovider heißt sie OleDbConnection. Der Einfachheit halber wird aber im Folgenden oft einfach nur vom Connection-Objekt die Rede sein. Damit wird die Allgemeingültigkeit dieses Typs unterstrichen. Die folgenden Abschnitte werden zeigen, dass sich die providerspezifischen Connection-Objekte nur geringfügig unterscheiden.

Um auf eine Datenquelle wie Microsoft SQL Server zuzugreifen, werden mehrere Informationen benötigt:

• der Name des Rechners, auf dem die SQL Server-Instanz läuft

• der Name der Datenbank, deren Dateninformationen verarbeitet werden sollen

• die Anmeldeinformationen, mit denen sich der Anwender authentifiziert

Diese Verbindungsinformationen werden nach einem bestimmten Muster in einer sogenannten Verbindungszeichenfolge zusammengefasst. Grundsätzlich haben Sie drei Möglichkeiten, die Verbindungsinformationen zu einer Datenquelle anzugeben:

• Sie rufen den parameterlosen Konstruktor der Connection-Klasse auf und übergeben dem erzeugten Objekt die Verbindungsinformationen.

• Sie rufen einen parametrisierten Konstruktor auf.

• Sie benutzen die Klasse DbConnectionStringBuilder.

23.2.2 Die Verbindungszeichenfolge

Sehen wir uns zuerst den parameterlosen Konstruktor an:

Dim con As DbConnection = new SqlConnection()

Dieses Verbindungsobjekt ist noch sehr dumm, da ihm sämtliche Informationen fehlen, die zum Aufbau einer Verbindung zu einer Datenquelle notwendig sind. Diese müssen der Eigenschaft ConnectionString des DbConnection-Objekts zugewiesen werden:

Dim con As DbConnection = New SqlConnection() 
con.ConnectionString = "<Verbindungszeichenfolge>"

Dem parametrisierten Konstruktor wird die Verbindungszeichenfolge als Argument direkt übergeben:

Dim con As DbConnection = New SqlConnection("<Verbindungszeichenfolge>")

Attribute einer Verbindungszeichenfolge

Alle Informationen, die zum Aufbau einer Verbindung zu einer Datenquelle erforderlich sind, werden in der Verbindungszeichenfolge beschrieben. Sie besteht aus einer Reihe von Attributen (bzw. Schlüsseln), denen Werte zugewiesen werden. Die Attribute sind untereinander durch ein Semikolon getrennt. Die allgemeine Syntax lässt sich wie folgt beschreiben:

Dim strCon As String = "Attribut1=Wert1;Attribut2=Wert2;..."

Die Bezeichner der einzelnen Attribute sind durch den verwendeten .NET-Datenprovider festgelegt. Weder die Groß-/Kleinschreibung noch die Reihenfolge der Attribute sind von Bedeutung. Beachten Sie, dass es meistens mehrere Attributbezeichner gibt, die gleichwertig eingesetzt werden können (siehe Tabelle 23.2).

23.2.3 Die Verbindung zu einer bestimmten SQL Server-Instanz

Befindet sich der SQL Server auf dem lokalen Rechner und wollen Sie auf die Beispieldatenbank Northwind zuzugreifen, könnte die Verbindungszeichenfolge so lauten:

Dim con As DbConnection = New SqlConnection() 
con.ConnectionString = "Data Source=(local);" & _ 
                       "Initial Catalog=Northwind;" & _ 
                       "Integrated Security=sspi"

Data Source beschreibt den Rechner, auf dem sich die laufende SQL Server-Instanz befindet. Hier können Sie den Rechnernamen oder eine TCP/IP-Adresse eintragen. Für einen lokalen Rechner dürfen Sie anstatt des Rechnernamens auch (local), localhost oder einfach nur einen Punkt angeben – die beiden Letztgenannten ohne runde Klammern.

Auf einem Computer können durchaus mehrere Instanzen von SQL Server installiert sein. Das Codefragment oben greift auf die sogenannte Standardinstanz zu. Zum Zugriff auf eine benannte Instanz geben Sie zuerst den Rechnernamen und danach einen Backslash (»\«) an. Dahinter folgt die Angabe der SQL Server-Instanz. Wollen Sie sich beispielsweise mit der Instanz SQLExpress auf der lokalen Maschine verbinden, sieht das Data Source-Attribut wie folgt aus:

Data Source=.\SQLExpress

Hinter Initial Catalog ist die Datenbank angegeben, und zum Schluss folgen noch Informationen zur Authentifizierung.

Gleichwertig können Sie auch dem parametrisierten Konstruktor des Connection-Objekts die Verbindungszeichenfolge übergeben:

Dim con As DbConnection = New SqlConnection("Data Source=.;" & _ 
  "Initial Catalog=Northwind;Integrated Security=sspi")

Sie müssen nicht zwangsläufig alle Attribute verwenden. Das Attribut Packet Size wird beispielsweise nicht benutzt. Somit werden alle Daten auf der Verbindung in 8192 Bytes großen Paketen verschickt. Wenn große Datenmengen vom Server geladen werden sollen, zum Beispiel Bilder, können größere Pakete die Leistung durchaus deutlich steigern.

Authentifizierung

Soll die Verbindung zu einer Datenbank aufgebaut werden, muss sich der Anwender bei der Datenbank anmelden. Das Connection-Objekt benutzt hierfür die Authentifizierungsinformationen, die in der Verbindungszeichenfolge enthalten sind. Diese werden vom Datenbankserver überprüft.

SQL Server kennt zwei Verfahren zur Authentifizierung:

• Integrierte Windows-Authentifizierung: Zur Authentifizierung benutzt der SQL Server das Authentifizierungssystem von Windows (NT/2000/XP/2003/…). Mit Ausnahme der Benutzer mit administrativen Rechten muss der Datenbankadministrator jeden Benutzer explizit hinzufügen.

• SQL Server-Authentifizierung: Diese nutzt die SQL Server-interne Benutzerliste, die keine Windows-Benutzer beinhaltet. Benutzer werden mithilfe des SQL Server Management Studios erstellt und konfiguriert. Den Benutzern werden die gewünschten Berechtigungen für die entsprechende Datenbank eingerichtet. (Hinweis: Bei der SQL Server Express Edition ist nur die Windows-Authentifizierung möglich.)

Die Authentifizierungsart können Sie bereits bei der Installation von SQL Server festlegen. Die SQL Server-Authentifizierung ist standardmäßig deaktiviert, Sie können aber auch einen gemischten Modus aus beiden Authentifizierungen wählen. Eine nachträgliche Änderung der Serverauthentifizierung erfolgt im SQL Server Management Studio. Markieren Sie hierzu die SQL Server-Instanz, öffnen Sie über deren Kontextmenü die Eigenschaftsliste, und wählen Sie den Reiter Sicherheit.

Bei der integrierten Windows-Authentifizierung muss weder ein Benutzername noch ein Passwort explizit gesendet werden. Mit der Angabe von Integrated Security=sspi reicht das System den Benutzernamen und das Passwort des aktuellen Windows-Benutzers an den SQL Server weiter. Vorausgesetzt der Kontoinhaber hat ausreichende Rechte, kann damit die Verbindung zur Datenbank hergestellt werden.

Die SQL Server-Authentifizierung setzt voraus, dass der Administrator des SQL Servers ein Benutzerkonto mit Passwort eingerichtet hat. Sowohl der Benutzername als auch das Passwort müssen bei diesem Authentifizierungsverfahren in der Verbindungszeichenfolge stehen, beispielsweise so:

Dim con As DbConnection = New SqlConnection(); 
con.ConnectionString = "Data Source=DBServer;Initial Catalog=Northwind;" & _ 
                       "User ID=Testuser;Password=26gf28"

SQL Server führt die Authentifizierung durch, indem er überprüft, ob ein SQL Server-Anmeldekonto mit diesem Namen eingerichtet ist und ob das angegebene Kennwort stimmt. Falls die übermittelten Anmeldeinformationen falsch sind, misslingt die Authentifizierung, und der Benutzer erhält eine Fehlermeldung.

Es ist natürlich grundsätzlich nicht empfehlenswert, die Daten zur Benutzerauthentifizierung statisch in der Verbindungszeichenfolge zu speichern. Besser ist es, in einem Dialog den Anwender zur Eingabe von Benutzernamen und Passwort aufzufordern und mit diesen Informationen zur Laufzeit die Verbindungszeichenfolge zu bilden.

23.2.4 Änderung des Passworts bei der SQL Server-Authentifizierung

Bei der SQL Server-Authentifizierung bilden Benutzername und Passwort eine Einheit, die den Zugriff auf Datenressourcen ermöglicht. Seit ADO.NET 2.0 – und auch nur im Zusammenspiel mit SQL Server 2005 – kann der Benutzer sein Passwort ändern, ohne dass der Datenbankadministrator eingreifen muss. Hier hilft die statische Methode ChangePassword der Klasse SqlConnection weiter. Aus einer aktiven Verbindung heraus kann damit unter Angabe der alten Authentifizierungsinformationen (Benutzername und Kennwort) ein neues Kennwort festgelegt werden.

Dim con As DbConnection = New SqlConnection(); 
con.ConnectionString = "Data Source=DBServer;Initial Catalog=Northwind;" & _ 
  "User ID=Testuser;Password=26gf28" 
con.Open() 
SqlConnection.ChangePassword("User ID=Testuser;PWD=26gf28", "4711password")

Diese Technik bietet sich besonders an, wenn das alte Kennwort abgelaufen ist.

23.2.5 Verbindungszeichenfolgen mit DbConnectionStringBuilder

Wenn Sie den Anwender dazu auffordern, seine Authentifizierungsinformationen, die aus Benutzername und Passwort bestehen, in einem Dialog einzutragen, besteht die Gefahr, dass »böse Buben« im Login- oder Kennwortfeld zusätzliche Parameter eintragen. In Abbildung 23.1 wird das gezeigt. Im Extremfall kann dies zu Sicherheitsproblemen führen.

Abbildung 23.1 Manipulation der Verbindungszeichenfolge

Neben der beabsichtigten Böswilligkeit könnte der Anwender aber aus Unwissenheit auch Zeichen gewählt haben, die in der Verbindungszeichenfolge eine besondere Bedeutung haben, beispielsweise »;« oder »=». Eine Eingabe dieser Zeichen würde zu einer Fehlermeldung führen.

Die Klasse DbConnectionStringBuilder vermeidet diese Probleme. Jedem Element der Verbindungszeichenfolge wird ein Wert zugeordnet. Das Ergebnis wird der Eigenschaft ConnectionString des DbConnectionStringBuilder-Objekts übergeben. Sie müssen diese Eigenschaft am Ende nur noch dem Konstruktoraufruf von Connection übergeben.

Dim conBuilder As DbConnectionStringBuilder = _ 
  New SqlConnectionStringBuilder() 
conBuilder.Add("Data Source", ".") 
conBuilder("Initial Catalog") = "Northwind" 'oder Add 
conBuilder.Add("User ID", " Testuser") 
conBuilder.Add("Password", "26gf28") 
Dim con As DbConnection = New SqlConnection(conBuilder.ConnectionString)

Die Klasse SqlConnectionStringBuilder stellt für alle Attribute der Verbindungszeichenfolge Eigenschaften zur Verfügung, denen Sie die passenden Werte zuweisen:

Dim conBuilder As SqlConnectionStringBuilder = _ 
  New SqlConnectionStringBuilder() 
conBuilder.DataSource = "." 
conBuilder.InitialCatalog = "Northwind" 
conBuilder.UserID = "Testuser" 
conBuilder.Password = "26gf28" 
Dim con As DbConnection = New SqlConnection(conBuilder.ConnectionString)

Wenn Sie sich die erzeugte Verbindungszeichenfolge im Befehlsfenster ausgeben lassen, wird Folgendes angezeigt (con.ConnectionString ohne Passwort nach con.Open()):

Data Source=.;Initial Catalog=Northwind;User ID=Testuser;Password=26gf28

23.2.6 Öffnen und Schließen einer Verbindung

Verbindung öffnen

Das Instanziieren der Klasse SqlConnection und Zuweisen der Verbindungszeichenfolge sind noch nicht ausreichend, um die Verbindung zu einer Datenbank zu öffnen und auf die in ihr enthaltenen Daten zuzugreifen. Dazu muss noch die Methode Open des DBConnection-Objekts aufgerufen werden:

Dim con As DbConnection = new SqlConnection("Data Source=localhost;" & _ 
      "Initial Catalog=Northwind;Trusted_Connection=yes") 
con.Open()

Weist die Verbindungszeichenfolge keinen Fehler auf, können Sie nun auf die Daten von Northwind zugreifen. Es gibt allerdings eine Reihe potenzieller Fehlerquellen, die zu einem Laufzeitfehler beim Verbindungsaufbau führen können:

• In der Verbindungszeichenfolge befindet sich ein Fehler.

• Der Anwender hat keine Zugriffsrechte auf die Datenbank.

• Der Datenbankserver ist nicht gestartet.

• Der Rechner, auf dem der Datenbankserver läuft, ist im Netzwerk nicht erreichbar.

Sie sollten daher das Öffnen einer Datenbankverbindung immer in einen Fehlerbehandlungsblock einschließen, der durch einen ergänzenden Finally-Zweig sicherstellt, dass im Fehlerfall offene Verbindungen geschlossen werden:

Try 
  Dim con As DbConnection = New SqlConnection(...); 
  con.Open(); 
  ' Anweisungen 
Catch ex As Exception 
  ' Anweisungen 
Finally 
  ' wenn offen, Verbindung schließen 
End Try

Um den Programmcode übersichtlich zu halten, wird in diesem Buch in den folgenden Codebeispielen auf die Fehlerbehandlung verzichtet.

Wenn Sie versuchen, ein bereits geöffnetes DbConnection-Objekt ein zweites Mal zu öffnen, wird die Ausnahme InvalidOperationException ausgelöst. In Zweifelsfällen sollten Sie vor dem Öffnen die Eigenschaft State abfragen:

If con.State = ConnectionState.Closed Then con.Open()

Obwohl die Enumeration ConnectionState insgesamt sechs verschiedene Zustände beschreibt, können aktuell nur zwei, nämlich Closed und Open, abgefragt werden. Alle anderen sind für zukünftige Versionen reserviert.

Verbindung schließen

Man könnte der Meinung sein, dass eine geöffnete Verbindung geschlossen wird, wenn das DbConnection-Objekt aufgegeben wird. Das wäre zum Beispiel der Fall, wenn die Referenz des DbConnection-Objekts auf Nothing gesetzt wird oder die Objektvariable ihren Gültigkeitsbereich verlässt. Das stimmt aber nur aus Sicht des zugreifenden Prozesses, denn tatsächlich werden auch auf dem Datenbankserver Ressourcen für die Verbindung reserviert, die nicht freigegeben werden, wenn das DbConnection-Objekt nur aufgegeben, aber noch nicht vom Garbage Collector bereinigt wird. Anhand des folgenden Codes, der das Click-Ereignis einer Schaltfläche behandelt, soll die Problematik erörtert werden:

Private Sub button1_Click(sender As Object, e As EventArgs) 
  Dim con As DbConnection = New SqlConnection(...) 
  con.Open() 
End Sub

Das DbConnection-Objekt wird innerhalb des Ereignishandlers erzeugt. Anschließend wird die Verbindung geöffnet. Mit dem Öffnen werden auch Ressourcen auf dem Datenbankserver für die Verbindung reserviert. Obwohl das clientseitige Objekt nach dem Verlassen des Handlers Nothing ist, nimmt der Datenbankserver von dieser Tatsache keine Notiz. Er wird weiterhin die Verbindung als geöffnet betrachten. Sie können das sehr schön sehen, wenn Sie im SQL Server Management Studio das Tool SQL Server Profiler öffnen und eine Ablaufverfolgung starten. Klicken Sie mehrfach auf die Schaltfläche, wird der Datenbankserver auch mehrere Verbindungen als Login registrieren, ohne dass es zu einem Logout kommt. Erst nach dem Schließen der Anwendung werden auch alle Verbindungen seitens der Datenbank geschlossen (siehe Abbildung 23.2).

Abbildung 23.2 Ablaufverfolgung im SQL Server Profiler-Tool

Sie sollten daher immer so schnell wie möglich eine geöffnete Verbindung wieder schließen, indem Sie die Close-Methode des Connection-Objekts aufrufen:

... 
con.Open() 
' nur Anweisungen, die eine geöffnete Verbindung benötigen 
con.Close()

In unserem Beispiel mit der Schaltfläche wurde zu keinem Zeitpunkt Close aufgerufen. Dass dennoch spätestens beim Beenden der Anwendung die Datenbankressourcen für die Verbindungen freigegeben werden, liegt daran, dass der Garbage Collector mit dem Schließen der Windows-Anwendung implizit die Close-Methode aufruft. Der Aufruf von Close auf einer geschlossenen Verbindung löst übrigens keine Ausnahme aus.

Die Möglichkeiten zum Schließen einer Datenbankverbindung sind damit aber noch nicht ausgeschöpft. Sie können auch die Methode Dispose des DbConnection-Objekts aufrufen, die ihrerseits implizit Close aufruft. Sie sollten sich aber darüber im Klaren sein, dass das Verbindungsobjekt damit endgültig aus dem Speicher entfernt wird.

Kurzlebige Ressourcen können auch innerhalb eines Using-Blocks geöffnet werden, so auch das DbConnection-Objekt:

Using con As DbConnection = New SqlConnection() 
  ... 
  con.Open() 
  ... 
End Using

Using stellt sicher, dass die Dispose-Methode am Ende des Blocks aufgerufen wird, selbst wenn eine Ausnahme auftritt, die nicht behandelt wird (siehe Abschnitt 3.16.3, »Dispose und Using«).

Dauer des Verbindungsaufbaus

Standardmäßig wird 15 Sekunden lang versucht, die Verbindung erfolgreich aufzubauen. Ist nach dieser Zeit keine Verbindung zum Datenbankserver vorhanden, wird eine Ausnahme ausgelöst. Äußere Umstände wie die Netzwerk- oder Serverbelastung können dazu führen, dass diese Zeitspanne zu knapp bemessen ist. In der Verbindungszeichenfolge kann daher mithilfe des Attributs Connect Timeout (bzw. Connection Timeout) eine andere Zeitspanne festgelegt werden. Die Angabe erfolgt in Sekunden.

Dim con As DbConnection = new SqlConnection("Data Source=localhost;" & _ 
  "Initial Catalog=Northwind;Trusted_Connection=yes;Connect Timeout=30;")

Das DbConnection-Objekt verfügt auch über eine Eigenschaft ConnectionTimeout. Da sie schreibgeschützt ist, kann darüber keine neue Zeitspanne festgelegt werden. Somit ist die Verbindungszeichenfolge die einzige Stelle, wo Sie diese Zeitspanne festlegen können.

Wie lange sollte eine Verbindung geöffnet bleiben?

Grundsätzlich sollte eine Verbindung so schnell wie möglich wieder geschlossen werden, um die dafür beanspruchten Ressourcen eines Datenbankservers schnell für andere Anwendungen frei zu machen. Dies ist besonders wichtig im Zusammenhang mit mehrschichtigen Anwendungen (ASP.NET, Webservices), bei denen zu einem gegebenen Zeitpunkt sehr viele User gleichzeitig Dateninformationen bearbeiten wollen.

Etwas anders kann die Argumentation ausfallen, wenn es sich bei dem Client um ein Windows-Programm handelt, aus dem heraus die Datenbank direkt ohne Zwischenschaltung einer weiteren Schicht auf die Datenressourcen zugreift. Nehmen wir an, dass zur Laufzeit des Programms immer wieder Daten abgerufen und geändert werden und nicht sehr viele Anwender gleichzeitig dieses Programm einsetzen. Sie haben dann die Wahl, sich zwischen zwei Strategien zu entscheiden:

• Sie lassen die Verbindung offen. Damit beansprucht das Programm während der gesamten Laufzeit den Datenbankserver, ist aber hinsichtlich der Performance optimal.

• Sie öffnen die Verbindung nur, wenn Sie Befehle gegen die Datenbank absetzen, und schließen die Verbindung anschließend umgehend. Die Datenbank ist dann nicht so belastet wie bei einer permanent geöffneten Verbindung, Sie bezahlen diesen Vorteil aber mit einem Performance-Verlust.

Bitte beachten Sie, dass einige ADO.NET-Objekte Ihnen nur eine eingeschränkte Entscheidungsfreiheit zugestehen. Hier sei die Fill-Methode des DbDataAdapter-Objekts exemplarisch angeführt, die Sie später noch kennenlernen werden.

Es kann keinen auf alle denkbaren Einsatzfälle zutreffenden Tipp geben, um Ihnen die Entscheidung abzunehmen. Zu viele Kriterien können dafür entscheidend sein. Wenn Sie keine Entscheidungstendenz erkennen können, sollten Sie das Verhalten von Anwendung und Datenbankserver zumindest in einer simulierten Realumgebung einfach testen.

23.2.7 Testrahmen

In der Praxis ist eine geordnete Fehlerbehandlung unerlässlich. Das folgende Codefragment können Sie als Ausgangsbasis für Ihre Programme nehmen:

'...\ADO\Verbindung\Rahmen.vb

Option Strict On 
Imports System.Data.Common 
Imports System.Data.SqlClient 
Namespace ADO 
  Module Rahmen 
    Sub Test() 
      Using con As DbConnection = New SqlConnection() 
        Try 
          con.ConnectionString = "Data Source=(local);" & _ 
            "Initial Catalog=Northwind;Integrated Security=sspi" 
          ' Arbeiten mit der Datenbank 
          con.Open() 
        Catch ex As Exception 
          Console.WriteLine("Datenbankfehler: {0}", ex.Message) 
        Finally 
          If (con.State And ConnectionState.Open) > 0 Then con.Close() 
        End Try 
      End Using 
      Console.ReadLine() 
    End Sub 
  End Module 
End Namespace

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.