31.3 Die Verbindung zu einer Datenbank herstellen

31.3.1 Das Connection-Objekt

Die erste Entscheidung, die Sie treffen müssen, ist die des Datenproviders. Oben wurden die entscheidenden Kriterien erörtert, die die Wahl beeinflussen. Für die einzelnen Klassen jedes .NET-Datenproviders ist ein separater Namespace in der .NET-Klassenbibliothek vorgesehen. Da wir als Datenquelle den SQL Server 2008 benutzen, bietet sich der Einsatz des SqlClient-Datenproviders an. Daher sollten Sie mit

using System.Data.SqlClient;

den entsprechenden Namespace zuerst bekannt geben.

Die Verbindung zu einer Datenbank wird durch ein Connection-Objekt beschrieben. Um präzise zu sein, gibt es die Klasse Connection unter ADO.NET nicht. Stattdessen wird, abhängig vom verwendeten .NET-Datenprovider, ein Präfix vorangestellt. Benutzen Sie den SqlClient-Datenprovider, heißt die Klasse SqlConnection, beim OleDb-Datenprovider 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, denn wie Sie in den folgenden Abschnitten noch sehen werden, unterscheiden sich die providerspezifischen Connection-Objekte nur geringfügig.

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

• der Name des Rechners, auf dem die SQL Server-Instanz läuft
• der Name der Datenbank, deren Dateninformationen ausgewertet oder manipuliert werden sollen
• die Anmeldeinformationen, mit denen sich der Anwender authentifiziert

Diese Verbindungsinformationen werden nach einem bestimmten Muster in einer Zeichenfolge zusammengefasst, die als Verbindungszeichenfolge bezeichnet wird. 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 SqlConnectionStringBuilder.

31.3.2 Die Verbindungszeichenfolge

Sehen wir uns zuerst den parameterlosen Konstruktor an.

SqlConnection con = new SqlConnection();

Damit erzeugen wir bereits das Verbindungsobjekt, das aber noch sehr »dumm« ist, da ihm sämtliche Informationen fehlen, die zum Aufbau einer Verbindung zu einer Datenquelle notwendig sind. Diese müssen der Eigenschaft ConnectionString des Connection-Objekts zugewiesen werden:

SqlConnection con = new SqlConnection();
con.ConnectionString = "<Verbindungszeichenfolge>";

Der parametrisierte Konstruktor gestattet es, die Verbindungszeichenfolge direkt als Argument zu übergeben:

SqlConnection con = new SqlConnection("<Verbindungszeichenfolge>");

Die Werte der Verbindungszeichenfolge

Alle Informationen, die zum Aufbau einer Verbindung zu einer Datenquelle erforderlich sind, werden in der Verbindungszeichenfolge beschrieben. Eine Verbindungszeichenfolge 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:

string strCon = "Attribut1=Wert1;Attribut2=Wert2;Attribut3=Wert3;...";

Die Bezeichner der einzelnen Attribute sind festgelegt und hängen vom verwendeten .NET-Datenprovider ab. In der folgenden Tabelle sind die Bezeichner des SqlClient-Datenproviders aufgeführt. Groß-/Kleinschreibung spielt dabei ebenso wenig eine Rolle wie die Reihenfolge der Attribute. Beachten Sie, dass es meistens mehrere Attributbezeichner gibt, die gleichwertig eingesetzt werden können.

31.3.3 Die Verbindung mit einer SQL Server-Instanz aufbauen

Befindet sich der SQL Server auf dem lokalen Rechner und beabsichtigen Sie, die Beispieldatenbank Northwind zu öffnen, könnte die Verbindungszeichenfolge wie folgt lauten:

SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=(local);" +
                       "Initial Catalog=Northwind;" +
                       "Integrated Security=sspi";

Listing 31.1 Beispiel einer Verbindungszeichenfolge

Data Source beschreibt den Rechner, auf dem sich die laufende SQL Server-Instanz befindet. Hier können Sie den Rechnernamen und eine TCP/IP-Adresse eintragen. Handelt es sich dabei um den lokalen Rechner, dürfen Sie anstatt des Rechnernamens auch (local), localhost oder einfach nur einen Punkt angeben – die beiden Letztgenannten allerdings 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. Möchten Sie auf eine andere, benannte Instanz zugreifen, geben Sie zuerst den Rechnernamen und darauf folgend einen Backslash (\) an. Dahinter folgt die Angabe der SQL Server-Instanz. Möchten Sie sich beispielsweise mit der Instanz SQLExpress auf der lokalen Maschine verbinden, sieht das Data Source-Attribut wie folgt aus:

Data Source=.\\SQLExpress

Hier sind zwei Backslashes notwendig, da C# einen einfachen Backslash als Escape-Sequenz interpretiert. Alternativ können Sie auch vor der Zeichenfolge das Zeichen @ setzen. Hinter Initial Catalog ist die Datenbank angegeben, zum Schluss folgen noch Informationen zur Authentifizierung.

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

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;" +
                                       "Initial Catalog=Northwind;" +
                                       "Integrated Security=sspi");

Listing 31.2 Nutzen des überladenen Konstruktors von »SqlConnection«

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

Die Authentifizierung

Soll die Verbindung zu einer Datenbank aufgebaut werden, muss sich der Anwender bei der Datenbank authentifizieren. 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:

• Die integrierte Windows-Authentifizierung
  Zur Authentifizierung benutzt SQL Server das Authentifizierungssystem von Windows (NT/2000/XP/2003/Vista/Windows 7 ...). Mit Ausnahme der Benutzer mit administrativen Rechten muss der Datenbankadministrator für jeden anderen Benutzer eine entsprechende Datenbankanmeldung definieren.
• Die SQL Server-Authentifizierung
  Diese basiert auf der internen Benutzerliste, die von SQL Server verwaltet wird. Die Liste beinhaltet keine Windows-Benutzer. Stattdessen werden Benutzer mit Hilfe 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. Per Vorgabe ist die SQL Server-Authentifizierung deaktiviert. Sie können aber auch einen gemischten Modus aus beiden Authentifizierungen wählen. Eine nachträgliche Änderung der Server-Authentifizierung 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 müssen weder Benutzername noch Passwort explizit gesendet werden. Mit der Angabe von Integrated Security=sspi verwendet das System automatisch das Windows-Benutzerkonto des aktuellen Users bestehend aus Benutzername und Passwort und reicht es an 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 folgendermaßen:

SqlConnection con = 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 Benutzerkonto mit diesem Namen eingerichtet ist und ob das angegebene Passwort stimmt. Falls die übermittelten Anmeldeinformationen falsch sind, misslingt die Authentifizierung, und der Benutzer erhält eine Fehlermeldung.

Es ist grundsätzlich nicht empfehlenswert, die Daten zur Benutzer-Authentifizierung 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.

Änderung des Passwortes 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 2008 kann der Benutzer sein Passwort ändern, ohne dass der Datenbankadministrator eingreifen muss. Hier hilft die statische Methode ChangePassword der Klasse SqlConnection weiter. Vorausgesetzt, es wurde zuvor mit bekannten Verbindungsinformationen die Verbindung zu der Datenbank geöffnet, kann unter vorheriger Angabe der alten Authentifizierungsinformationen (Benutzername und Passwort) im zweiten Argument das neue Passwort übermittelt werden.

SqlConnection con = 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 auch an, wenn das alte Passwort abgelaufen ist.

Verbindungszeichenfolgen mit dem »SqlConnectionStringBuilder«-Objekt

Fordern Sie den Anwender dazu auf, seine Authentifizierungsinformationen bestehend aus Benutzername und Passwort in einem Dialog einzutragen, besteht die Gefahr, dass »böse Buben« im Log-in- oder Passwortfeld zusätzliche Parameter eintragen. Im Extremfall kann dies zu Sicherheitsproblemen führen, die als SQL-Injection bekannt sind. Abgesehen von böswilligen Eingriffen könnte der Anwender aber auch Zeichen gewählt haben, die in der Verbindungszeichenfolge eine besondere Bedeutung haben, beispielsweise »;« oder »=«. Die Eingabe dieser Zeichen würde zu einer Fehlermeldung führen.

Um diesen Problemen aus dem Wege zu gehen, benutzen Sie die Klasse SqlConnectionStringBuilder. Diese Klasse stellt für alle Attribute der Verbindungszeichenfolge Eigenschaften zur Verfügung, denen Sie nur noch die passenden Werte zuweisen müssen. Das Ergebnis wird der Eigenschaft ConnectionString des SqlConnectionStringBuilder-Objekts zugeführt. Sie müssen diese Eigenschaft am Ende nur noch dem Konstruktoraufruf von SqlConnection übergeben.

SqlConnectionStringBuilder conBuilder =
           new SqlConnectionStringBuilder();
conBuilder.DataSource = ".\\sqlexpress";
conBuilder.InitialCatalog = "Northwind";
conBuilder.IntegratedSecurity = true;
SqlConnection con = new SqlConnection(conBuilder.ConnectionString);

Listing 31.3 Verbindungszeichenfolge mit »SqlConnectionStringBuilder« erzeugen

Lassen Sie sich die erzeugte Verbindungszeichenfolge im Befehlsfenster ausgeben, wird Folgendes angezeigt:

Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True

31.3.4 Öffnen und Schließen einer Verbindung

Die Verbindung öffnen

Das Instanziieren der Klasse SqlConnection und das Bekanntgeben 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 auf das Connection-Objekt aufgerufen werden:

SqlConnection con = new SqlConnection(@"Data Source=.\sqlexpress;" +
                                       "Initial Catalog=Northwind;" +
                                       "Integrated Security=True");
con.Open();

Listing 31.4 Öffnen einer Datenbankverbindung

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:

• Ein Fehler befindet sich in der Verbindungszeichenfolge.
• Der Anwender hat keine Zugriffsrechte auf die Datenbank.
• Der SQL Server ist nicht gestartet.
• Der Rechner, auf dem die SQL Server-Instanz läuft, ist im Netzwerk nicht erreichbar.

Sie sollten daher das Öffnen einer Datenbankverbindung immer in einen Fehlerbehandlungsblock einschließen.

try {
  SqlConnection con = new SqlConnection(...);
  con.Open();
}
catch(Exception e) {
  [...]
}

Listing 31.5 Einbetten in eine Fehlerbehandlung

Versuchen Sie, ein bereits geöffnetes SqlConnection-Objekt ein zweites Mal zu öffnen, wird die Ausnahme InvalidOperationException ausgelöst. Sollten Sie sich über den Zustand der Verbindung nicht im Klaren sein, können Sie sie mit der Eigenschaft State abfragen:

if(con.State == ConnectionState.Closed) con.Open();

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

Die Verbindung schließen

Man könnte der Meinung sein, dass eine geöffnete Verbindung geschlossen wird, wenn das Connection-Objekt aufgegeben wird. Das wäre zum Beispiel der Fall, wenn die Referenz des Connection-Objekts auf null 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 Connection-Objekt nur aufgegeben, aber noch nicht vom Garbage Collector bereinigt wird. Stellen Sie sich dazu vor, Sie hätten den folgenden Code in einer Methode programmiert:

SqlConnection con = new SqlConnection(...);
con.Open();
[...]
con = null;

Das SqlConnection-Objekt wird erzeugt und nach dem Öffnen der Verbindung durch Setzen von null aufgegeben. Mit dem Öffnen werden auch Ressourcen auf dem SQL Server für die Verbindung reserviert. Obwohl das clientseitige Objekt nach dem Verlassen des Handlers null 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. Erst nach dem Schließen der Anwendung wird die Verbindung seitens der Datenbank geschlossen (siehe Abbildung 31.1).

Abbildung 31.1 Die Ablaufverfolgung im Tool SQL Server Profiler

Sie sollten daher immer so schnell wie möglich eine geöffnete Verbindung durch Aufruf der Close-Methode auf das Connection-Objekt wieder schließen.

[...]
con.Open();
[...]
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 eine geschlossene 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 SqlConnection-Objekts aufrufen, die ihrerseits implizit Close aufruft. Sie sollten sich aber darüber bewusst 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 SqlConnection-Objekt:

using(SqlConnection con = new SqlConnection())
{
  [...]
  con.Open();
  [...]
}

using stellt sicher, dass die Dispose-Methode am Ende des Blocks aufgerufen wird, selbst wenn eine Ausnahme auftritt, die nicht behandelt wird.

Die Dauer des Verbindungsaufbaus

Standardmäßig wird 15 Sekunden lang versucht, die Verbindung aufzubauen. Verstreicht diese Zeit, ohne dass der Datenbankserver erreicht wird, wird eine Ausnahme ausgelöst. Äußere Umstände wie die Netzwerk- oder Serverbelastung können dazu führen, dass diese Zeitspanne unter Berücksichtigung aller Umstände zu knapp bemessen ist. In der Verbindungszeichenfolge kann daher mit Hilfe des Attributs Connect Timeout (bzw. Connection Timeout) eine andere Zeitspanne eingestellt werden. Die Angabe erfolgt in Sekunden:

SqlConnection con = new SqlConnection("Data Source=localhost;" +
     "Initial Catalog=Northwind;" +
     "Connect Timeout=30;" +
     "Integrated Security=true");

Listing 31.6 Dauer für den Versuch des Verbindungsaufbaus festlegen

Das SqlConnection-Objekt verfügt auch über eine Eigenschaft ConnectionTimeout, die allerdings schreibgeschützt ist. Ihr kann daher auch keine vom Standard abweichende Zeitspanne zugewiesen werden. Somit verbleibt Ihnen nur, eine etwaige Änderung der Standardvorgabe über die Verbindungszeichenfolge vorzunehmen.

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 möglichst gering zu halten. Im Zusammenhang mit mehrschichtigen Anwendungen (ASP.NET, Web Services), bei denen man davon ausgehen kann, dass zu einem gegebenen Zeitpunkt sehr viele User gleichzeitig Dateninformationen bearbeiten wollen, ist diese Grundregel immer zu beherzigen.

Etwas anders könnte 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 jedoch hinsichtlich der Performance optimal ausgerüstet.
• 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 Performanceverlust.

An dieser Stelle sei bereits darauf hingewiesen, dass einige ADO.NET-Objekte Ihnen nur eine eingeschränkte Entscheidungsfreiheit zugestehen. Hier sei die Fill-Methode des SqlDataAdapter-Objekts exemplarisch angeführt, die Sie später noch kennenlernen.

Es kann keinen auf alle denkbaren Einsatzfälle projizierbaren 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.

31.3.5 Das Verbindungspooling

Stellen Sie sich eine Datenbank im Internet vor. Es könnte sich dabei beispielsweise um eine Datenbank handeln, in der die Angebote eines Touristikunternehmens enthalten sind. Man kann davon ausgehen, dass sich innerhalb einer kurzen Zeitspanne mehrere Anwender über die Angebote des Touristikunternehmens informieren wollen. Das ständige Auf- und Abbauen der Verbindungen ist jedoch nachteilig, denn mit jedem Aufbau und Abbau einer physischen Verbindung werden die Ressourcen belastet, was zu einer schlechteren Antwortzeit des Datenbankservers führt.

Um die Leistung von Datenbankanwendungen zu verbessern, unterstützt ADO.NET das Konzept der Verbindungspools. Eben wurde noch gesagt, dass mit dem Aufruf der Methode Close die Verbindung zu der Datenbank geschlossen wird. Wollen wir präzise sein, stimmt diese Aussage nicht (wenn man von den Standardeinstellungen ausgeht). Close bewirkt lediglich, dass die Verbindung in einen Pool geschoben wird. Die physische Verbindung bleibt auch dann bestehen, wenn das SqlConnection-Objekt aufgegeben wird.

Ein Verbindungspool beherbergt nur Verbindungen, die exakt dieselbe Verbindungszeichenfolge aufweisen. Unterscheidet sich diese, wird ein neuer, zusätzlicher Pool eröffnet. Versucht ein Client, die Verbindung mit einer Datenbank herzustellen, werden zunächst alle vorhandenen Pools daraufhin untersucht, ob es nicht bereits einen Pool mit einer passenden Verbindung gibt. Wenn ja, wird sie dem anrufenden Client zugeordnet, wenn nicht, wird die angeforderte Verbindung neu erstellt. Der Client bearbeitet auf dieser Verbindung die Daten und kann sie am Ende mit Close wieder aufgeben. In jedem Fall wird die Verbindung danach einem Pool zugeführt.

Ein Verbindungspool beherbergt nur Datenbankverbindungen, deren Verbindungszeichenfolge identisch ist. Für jeden Client, der nicht aus einem vorhandenen Verbindungspool versorgt werden kann, wird eine neue Verbindung erstellt. Bei stark frequentierten Datenbanken würde das auf die Dauer zu einem inakzeptablen Anwachsen des Pools führen. Daher wird eine Verbindung aus dem Pool gelöscht, wenn sie eine bestimmte Zeit lang nicht mehr aktiviert worden ist. Standardmäßig ist das nach ca. fünf Minuten der Fall.

ADO.NET gestattet es Ihnen, das Poolen der Verbindungen zu steuern. Sie können sowohl die maximale als auch die minimale Poolgröße festlegen, gepoolte Verbindungen manuell freigeben und das Verbindungspooling sogar deaktivieren.

Beispiel für ein Verbindungspooling

Wir wollen uns das Poolen jetzt an einem Beispiel verdeutlichen. Dazu wird im folgenden Code eine Verbindung zehnmal angefordert.

// Beispiel: ..\Kapitel 31\VerbindungsPooling
class Program {
  static void Main(string[] args) {
    SqlConnection con = new SqlConnection( ... );
    // Verbindung 10-mal öffnen und schließen
    for (int i = 0; i < 10; i++) {
      con.Open();
      con.Close();
      Thread.Sleep(100);
    }
    Console.ReadLine();
  }
}

Listing 31.7 Testen des Verbindungspoolings

Weiter oben in diesem Kapitel haben wir bereits das Tool SQL Server Profiler aus dem SQL Server Management Studio eingesetzt, um uns von den Auswirkungen der Methode Close zu überzeugen. Natürlich spielte auch bei diesen Beispielen das Verbindungspooling eine Rolle, musste aber zum grundlegenden Verständnis der Close-Methode noch nicht berücksichtigt werden.

Nun verwenden wir den Profiler, um das Poolen von Verbindungen zu erleben. In Abbildung 31.2 sehen Sie die Aufzeichnung nach dem Ausführen des Beispielprogramms Verbindungspooling. Beachten Sie, dass im Code zwar zehnmal eine Verbindung aufgebaut wird, aber dennoch nur ein Log-in- und ein abschließendes Log-out-Ereignis auftritt. Dies geschieht, weil jede Verbindung nach dem Öffnen und dem darauf folgenden Schließen mit Close zwar aus Sicht des Clients geschlossen wird, tatsächlich jedoch in einen Pool wandert, aus dem sie bei jedem weiteren Schleifendurchlauf mit Open wieder in Anspruch genommen wird.

Abbildung 31.2 Das Ablaufverfolgungsprotokoll des SQL Server Profilers beim Poolen

Deaktivieren des Verbindungspoolings

Standardmäßig ist das Pooling aktiviert. Um es zu deaktivieren, ergänzen Sie die Verbindungszeichenfolge wie folgt:

SqlConnection con = new SqlConnection(" ...;Pooling=False");

Erzeugen Sie die Verbindungszeichenfolge mit einem SqlConnectionStringBuilder-Objekt, so legen Sie dessen Eigenschaft Pooling auf False fest.

In SQL Server Profiler kann der Effekt, den das Abschalten des Poolings nach sich zieht, wieder anschaulich beobachtet werden. Für jedes Verbindungsgesuch wird ein Log-in- und ein Log-out-Ereignis protokolliert. Abbildung 31.3 zeigt das Protokoll des Beispiels Verbindungspooling, nun jedoch mit ausgeschaltetem Pooling.

Beeinflussen der Verbindungspoolgröße

Sowohl die Maximalgröße als auch die Minimalgröße eines Verbindungspools lassen sich steuern. Per Vorgabe ist die Minimalgröße auf 0 festgelegt, die Maximalgröße auf 100 gepoolte Verbindungen.

Abbildung 31.3 Das Ablaufverfolgungsprotokoll des SQL Server Profilers, wenn das Poolen abgeschaltet ist

Betrachten wir zuerst die Minimalgröße etwas genauer. Fordert ein Client eine Verbindung an, die sich in keinem Pool befindet, und ist die Minimalgröße auf zehn Verbindungen festgelegt, werden über die angeforderte Verbindung hinaus neun weitere geöffnet und im Pool abgelegt. Es gibt dann also mindestens zehn Verbindungen im Pool. Diese bedienen eventuell anfordernde Clients. Sind mehr Verbindungen notwendig, wird der Pool vergrößert, aber die Mindestanzahl wird nicht mehr unterschritten, auch wenn zeitweise keine Verbindung mehr benötigt wird. Die Lebensdauer von ca. fünf Minuten, die ansonsten für gepoolte Verbindungen gilt, betrifft nicht die zehn Verbindungen, die zur Sicherung der Mindestpoolgröße erforderlich sind.

Die Festlegung der Maximalpoolgröße gewährleistet, dass ein Datenbankserver zu Spitzenzeiten nicht überstrapaziert wird. Zu einem gegebenen Zeitpunkt könnte der Pool ausgeschöpft sein, weil alle darin enthaltenen Verbindungen aktiv von Clients beansprucht werden. Kommt es dann zu einem weiteren Verbindungsgesuch, wird versucht, für die Zeitspanne, die in Connect Timeout festgelegt ist, dem anfordernden Client eine Verbindung bereitzustellen. Gelingt das nicht innerhalb der Zeitspanne, wird eine Exception (InvalidOperationException) ausgelöst.

Zur Festlegung der minimalen und maximalen Verbindungspoolgröße dienen uns wieder zwei Attribute in der Verbindungszeichenfolge: Min Pool Size und Max Pool Size. Passend dazu werden von einem SqlConnectionStringBuilder-Objekt die beiden Eigenschaften MinPoolSize und MaxPoolSize angeboten.

SqlConnection con = new SqlConnection("...;Min Pool Size=5; Max Pool Size=200");

Listing 31.8 Das Verbindungspooling beeinflussen

Freigabe gepoolter Verbindungen

Gepoolte Verbindungen können mit den beiden statischen Methoden ClearPool und ClearAllPools freigegeben werden.

Die Methode ClearPool erwartet als Argument ein SqlConnection-Objekt.

SqlConnection.ClearPool(con);

Das Connection-Objekt ist notwendig, weil die Methode daraus die Verbindungszeichenfolge bezieht, um zu wissen, in welchem Verbindungspool die Verbindungen aufgegeben werden sollen. Dabei handelt es sich nur um die freien Verbindungen und nicht um die, die in diesem Moment aktiv sind, also von anderen Clients beansprucht werden.

Die Methode ClearAllPools definiert keinen Parameter. Sie löscht alle freien Verbindungen in den Verbindungspools.

31.3.6 Die Ereignisse eines »Connection«-Objekts

Mit InfoMessage und StateChange besitzt das SqlConnection-Objekt nur zwei Ereignisse.

Das Ereignis »InfoMessage«

Bei auftretenden Problemen gibt SQL Server eine Informationsmeldung an den Aufrufer zurück, die das Problem beschreibt. Ein Problem kann mehr oder weniger schwerwiegend sein. Um das genauer zu beschreiben, unterscheidet der SQL Server Fehler in ihrem Schweregrad und definiert dazu 25 Stufen. Die Schweregrade 0 bis 10 stehen ausschließlich für Informationsmeldungen zur Verfügung. Fehler der Schweregrade 11 bis 16 kann ein Anwender selbst beheben, ab Schweregrad 17 muss der Datenbankadministrator aktiv werden.

Das InfoMessage-Ereignis wird ausgelöst, wenn vom SQL Server eine Meldung mit einem Schweregrad von 10 oder weniger zurückgegeben wird. Im folgenden Beispiel wird die Anweisung PRINT an den SQL Server geschickt. Die hinter PRINT angeführte Zeichenfolge wird von der Datenbank als Informationsquelle an den Client gesendet, was zur Auslösung des InfoMessage-Ereignisses führt. Die Servermeldung wird der Eigenschaft Message des Args-Objekts entnommen. Wenn Sie sich den Programmcode dieses Beispiels ansehen, sollten Sie die Anweisungen nach dem Öffnen der Verbindung ignorieren, da Sie die dazu notwendigen Informationen erst im nächsten Kapitel erhalten.

// Beispiel: ..\Kapitel 31\InfoMessageEvent
class Program {
  static void Main(string[] args) {
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "...";
    con.InfoMessage += new SqlInfoMessageEventHandler(con_InfoMessage);
    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandText = "PRINT 'Informationsmeldung'";
    cmd.ExecuteNonQuery();
    con.Close();
    Console.ReadLine();
  }
  static void con_InfoMessage(object obj,SqlInfoMessageEventArgs e) {
    Console.WriteLine("Meldung vom Server: {0}", e.Message);
  }
}

Listing 31.9 Das Ereignis »InfoMessage« behandeln

Das InfoMessage-Ereignis wird normalerweise nur bei Informations- und Warnmeldungen des Servers ausgelöst. Bei einem tatsächlichen Fehler wird eine Ausnahme ausgelöst. Das könnte im Zusammenhang mit den Methoden ExecuteNonQuery oder ExecuteReader, die wir in Kapitel 32 behandeln, der Fall sein.

Wollen Sie die Verarbeitung der restlichen Anweisungen unabhängig von den vom Server erzeugten Fehlern dennoch fortsetzen, legen Sie die FireInfoMessageEventOnUserErrors-Eigenschaft des SqlConnection-Objekts auf true fest. Bei dieser Vorgehensweise wird beim Auftreten von Fehlern von der Verbindung das InfoMessage-Ereignis ausgelöst, anstatt eine Ausnahme auszulösen und die Verarbeitung zu unterbrechen.

Das Ereignis »StateChange«

Das Ereignis StateChange tritt auf, wenn sich die State-Eigenschaft ändert. Im Ereignishandler können Sie die Eigenschaften OriginalState und CurrentState des Args-Objekts auswerten, um den alten und neuen Zustand der Verbindung zu überprüfen.

// Beispiel: ..\Kapitel 31\StateChangeEvent
class Program {
  static void Main(string[] args) {
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "...";
    con.StateChange += new StateChangeEventHandler(con_StateChange);
    con.Open();
    con.Close();
    Console.ReadLine();
  }
  static void con_StateChange(object obj,StateChangeEventArgs e) {
    Console.Write("Zustand: von {0}", e.OriginalState.ToString());
    Console.WriteLine(" nach {0}", e.CurrentState.ToString());
  }
}

Listing 31.10 Das Ereignis »StateChange« behandeln

31.3.7 Verbindungszeichenfolgen aus einer Konfigurationsdatei abrufen

Bisher haben wir die Verbindungszeichenfolgen immer im Code geschrieben (und werden es in diesem Buch auch weiter tun). Das spiegelt die Anforderungen in der täglichen Praxis nicht wider, denn Sie werden nur selten eine Datenbankanwendung entwickeln, die unter Einbeziehung der Produktionsserverdatenbank getestet wird. Stattdessen werden Sie bestenfalls mit einer Kopie der Datenbank arbeiten, die sich auf einem anderen Rechner befindet und somit eine andere Verbindungszeichenfolge erfordert als die Produktionsdatenbank. Nach dem bisherigen Kenntnisstand bedeutet dies, dass Sie nach dem erfolgreichen Testen und vor Auslieferung und Installation der Anwendung die Verbindungsinformationen abschließend ändern und noch einmal kompilieren müssen.

Auch ein anderes typisches Szenario ist denkbar: Die Produktionsdatenbank wird »verschoben«, beispielsweise auf einem anderen Rechner installiert, oder der Rechner, auf dem die Datenbank installiert ist, erhält eine andere TCP/IP-Adresse. Auch hier muss die Anwendung neu kompiliert werden, um mit der neuen Verbindungszeichenfolge den Zugriff auf die Dateninformationen zu gewährleisten.

Eine gute Lösung ist es, die Verbindungszeichenfolge isoliert zu betrachten. .NET bietet mit den Konfigurationsdateien dazu die passende Lösung an. Konfigurationsdateien gibt es auf mehreren Ebenen: beispielsweise die Maschinenkonfigurationsdatei für eine lokale Maschine oder die Anwendungskonfigurationsdatei für ein bestimmtes Programm. Konfigurationsdateien werden, soweit vorhanden, vor dem Starten einer .NET-Anwendung ausgewertet. Verbindungszeichenfolgen lassen sich in Konfigurationsdateien hinterlegen. Der Vorteil dabei ist, dass eine Verbindungszeichenfolge ohne Neukompilierung der Anwendung geändert werden kann, sogar mit jedem einfachen Texteditor, denn Konfigurationsdateien sind XML-Dateien.

An einem Beispiel möchte ich Ihnen zeigen, wie Sie nicht nur eine Anwendungskonfigurationsdatei hinsichtlich der Verbindungszeichenfolge auswerten können, sondern auch, wie Sie mittels Programmcode in die Konfigurationsdatei schreiben.

// Beispiel: ..\Kapitel 31\ConnectionStringAppConfig
...
using System.Configuration;
namespace ConnectionstringInKonfdatei {
  class Program {
    static void Main(string[] args) {
      ConnectionStringSettings setting =
          ConfigurationManager.ConnectionStrings["SQL2008"];
      // Prüfen, ob es in der Konfigurationsdatei einen
      // Eintrag ‘SQL2008’ gibt
      if (setting == null) {
        setting = new ConnectionStringSettings();
        setting.Name = "SQL2008";
        setting.ConnectionString =
            @"Data Source=.\sqlexpress;Initial Catalog=Northwind;" +
            "Integrated Security=true";
        Configuration config = ConfigurationManager.OpenExeConfiguration
                                     (ConfigurationUserLevel.None);
        config.ConnectionStrings.ConnectionStrings.Add(setting);
        config.Save();
      }
      SqlConnection con = new SqlConnection(setting.ConnectionString);
      con.Open();
      Console.WriteLine("Verbindung geöffnet");
      con.Close();
      Console.ReadLine();
    }
  }
}

Listing 31.11 Schreiben und Auswerten der Anwendungskonfigurationsdatei

Beachten Sie bitte, dass Sie die Bibliothek System.Configuration.dll unter Verweise einbinden müssen. Im Code wird zuerst überprüft, ob es in der Anwendungskonfigurationsdatei einen Eintrag namens SQL2008 gibt. Wenn nicht, wird er angelegt und eine Verbindungszeichenfolge definiert. Sollte es noch keine Anwendungskonfigurationsdatei geben, wird diese im Code erzeugt. Danach wird der entsprechende Eintrag aus der Konfigurationsdatei als Argument dem SqlConnection-Konstruktoraufruf übergeben.

Nun sollten wir uns auch noch die Anwendungskonfigurationsdatei ansehen.

<?xml version="1.0" encoding="utf-8"?>
  <configuration>
    <connectionStrings>
      <add name="SQL2008"
             connectionString="Data Source=wsak\\SQL2008;
                               Initial Catalog=Northwind;
                               Integrated Security=sspi" />
    </connectionStrings>
</configuration>

Listing 31.12 Verbindungszeichenfolge in einer Anwendungskonfigurationsdatei

Anwendungskonfigurationsdateien werden standardmäßig im Verzeichnis der ausführbaren Programmdatei (exe-Datei) gespeichert. Der Dateibezeichner lautet genauso wie der Dateibezeichner der ausführbaren Datei, ergänzt um .config. Innerhalb des Stammelements <configuration> können eine Vielzahl auswertbarer untergeordneter Elemente definiert werden, zu denen auch <connectionStrings> zählt. Jeder Eintrag einer Verbindungszeichenfolge wird mit dem Element <add> eingeleitet, das sich <connectionStrings> unterordnet. <add> definiert drei Attribute, von denen zwei zwingend angegeben werden müssen: connectionString und name. Das dritte, providerName, ist optional und hat die Standardeinstellung System.Data.SqlClient. Es gestattet, den Datenprovider für die beschriebene Verbindungszeichenfolge festzulegen.

Ändert sich im laufenden Betrieb die Verbindungszeichenfolge, beispielsweise wegen einer Änderung der TCP/IP-Adresse des Datenbankservers, passen Sie die Verbindungszeichenfolge in der Konfigurationsdatei entsprechend an. Eine Neukompilierung der Anwendung mit nachfolgender Neuverteilung ist nicht notwendig.

31.3.8 Verbindungen mit dem OleDb-Datenprovider

Im Gegensatz zum SqlClient-Datenprovider, der nur den Zugriff auf SQL Server ab Version 7.0 ermöglicht, ist der OleDb-Datenprovider sehr flexibel einsetzbar. Sie können ihn zur Kommunikation mit dem SQL Server benutzen. Er unterstützt aber gleichzeitig auch alle OLE DB-Datenbanken, zu denen beispielsweise auch Oracle und Access zählen.

Prinzipiell ändert sich nur wenig, wenn Sie anstelle des SqlClient-Datenproviders den OleDb-Provider einsetzen. Sie sollten aber daran denken, vorher den richtigen Namespace bekannt zu geben:

using System.Data.OleDb;

Zum Aufbau einer Verbindung benötigt auch der OleDb-Provider ein Connection-Objekt. Der exakte Name der Klasse lautet, angelehnt an den ausgewählten Provider, OleDbConnection. Die Verbindungszeichenfolge wird ebenfalls entweder über den parametrisierten Konstruktor oder über die Eigenschaft ConnectionString bereitgestellt. Die Attribute der Verbindungszeichenfolge gleichen denen des SqlClient-Datenproviders, werden jedoch noch um das Attribut Provider ergänzt, mit dem die Datenquelle genauer zu spezifizieren ist. In Tabelle 31.1 sind die wichtigsten Attribute aufgeführt.

In der Tabelle ist ein Wert für den Zugriff auf ODBC-Datenquellen nicht angegeben, denn für diese sollten die Klassen des Namespaces System.Data.Odbc benutzt werden.

Verbindungsaufbau zu einer SQL Server-Datenbank

Das folgende Codefragment zeigt, wie eine Verbindung zur Beispieldatenbank Northwind einer SQL Server-Instanz hergestellt wird, die sich auf dem lokalen Rechner befindet. Als OleDb-Provider dient der Providername SQLNCLI10. Der Authentifizierungsmodus ist bei diesem Codefragment die SQL Server-Authentifizierung.

string strCon = @"Provider=SQLNCLI10;Data Source=.\sqlexpress;" +
                "Initial Catalog=Northwind;" +
                "User ID=testuser;" +
                "Password=2zz6sl3";
OleDbConnection con = new OleDbConnection(strCon);
con.Open();
[...]
con.Close();

Listing 31.13 Verbindungszeichenfolge mit dem OleDb-Provider für SQL Server 2008

Verbindungsaufbau zu einer Access-Datenbank

Um die Verbindung zu einer Access-Datenbank herzustellen, wird der spezifische Datenprovider Microsoft.ACE.OLEDB.12.0 benutzt. Handelt es sich um eine andere Version der Datenbank, müssen Sie nur die die Version beschreibenden Ziffern austauschen. Die Verbindungszeichenfolge sieht etwas anders aus als die, mit der die Verbindung zum SQL Server hergestellt wird. Hinter dem Attribut Data Source wird nun nicht mehr der Rechnername angegeben, sondern der Pfad zur mdb-Datei, da es sich um eine dateibasierte Datenbank handelt.

OleDbConnection con = new OleDbConnection();
con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                        "Data Source=C:\NWIND.mdb";
con.Open();

Listing 31.14 Verbindungszeichenfolge mit dem OleDb-Provider für Access 2007/2010

Um die Verbindung genauer zu beschreiben, steht eine Reihe weiterer Schlüsselwörter zur Verfügung. Sie können diese der Microsoft Data Access SDK entnehmen.

Authentifizierung mit dem OleDb-Provider

Der OleDb-Datenprovider bietet für SQL Server eine weitere interessante Möglichkeit zur Authentifizierung des Anwenders. Dazu muss weder der Benutzername noch das Passwort in der Verbindungszeichenfolge angegeben werden. Ergänzen Sie diese vielmehr um prompt=prompt, also beispielsweise:

con.ConnectionString = "Provider= SQLNCLI10;Data Source=(local);" +
   "Initial Catalog=Northwind;prompt=prompt";

Beim Verbindungsaufbau mit der Methode Open wird daraufhin ein Dialog geöffnet, wie in Abbildung 31.4 gezeigt.

Abbildung 31.4 Anmeldedialog des OleDb-Datenproviders

Beabsichtigt der Anwender, sich über sein aktuelles Windows-Benutzerkonto zu authentifizieren, wird die Auswahlbox Vertrauenswürdige Verbindung verwenden gewählt. Soll die Verbindung über die spezifische SQL Server-Authentifizierung hergestellt werden, muss der Anwender den entsprechenden Benutzernamen und das dazu passende Passwort eingeben.

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.