Verwenden von Parametern mit einem DataAdapter
Das DataAdapter-Objekt besitzt vier Eigenschaften, mit denen Daten aus der Datenquelle abgerufen und Daten in der Datenquelle aktualisiert werden. Mit der SelectCommand-Eigenschaft werden Daten aus der Datenquelle zurückgegeben. Mit den Eigenschaften InsertCommand, UpdateCommand und DeleteCommand werden Änderungen in der Datenquelle verwaltet. Für die SelectCommand-Eigenschaft muss ein Wert festgelegt werden, bevor die Fill-Methode des DataAdapter-Objekts aufgerufen wird. Für die Eigenschaften InsertCommand, UpdateCommand oder DeleteCommand muss ein Wert festgelegt werden, bevor die Update-Methode des DataAdapter-Objekts aufgerufen wird, je nachdem, welche Änderungen an den Daten im DataSet vorgenommen wurden. Wenn beispielsweise Zeilen hinzugefügt wurden, muss ein Wert für die InsertCommand-Eigenschaft festgelegt werden. Erst dann kann die Update-Methode aufgerufen werden. Wenn mit der Update-Methode eine eingefügte, aktualisierte oder gelöschte Zeile verarbeitet wird, verwendet das DataAdapter-Objekt die entsprechende Command-Eigenschaft zur Verarbeitung der Aktion. Aktuelle Informationen zur geänderten Zeile werden über die Parameters-Auflistung an das Command-Objekt übergeben.
Wenn Sie beispielsweise eine Zeile in der Datenquelle aktualisieren, rufen Sie die UPDATE-Anweisung auf, die einen eindeutigen Bezeichner verwendet, um die zu aktualisierende Zeile in der Tabelle anzugeben. Der eindeutige Bezeichner ist in der Regel der Wert eines Primärschlüsselfeldes. Die UPDATE-Anweisung verwendet Parameter, die sowohl den eindeutigen Bezeichner als auch die zu aktualisierenden Spalten und Werte enthalten, wie in der folgenden SQL-Anweisung gezeigt.
UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID
In diesem Beispiel wird das CompanyName-Feld mit dem Wert des @CompanyName-Parameters aktualisiert, und zwar in der Zeile, in der CustomerID dem Wert des @CustomerID-Parameters entspricht. Die Parameter rufen mit der SourceColumn-Eigenschaft des Parameter-Objekts Informationen aus der geänderten Zeile ab. Im Folgenden sehen Sie die Parameter für die vorhergehende UPDATE-Beispielanweisung.
custDA.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")
Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
Die Add-Methode der Parameters-Auflistung verwendet den Namen des Parameters, den DataAdapter-spezifischen Typ, die Größe (sofern für den Typ zutreffend) und den Namen der SourceColumn aus der DataTable. Beachten Sie, dass als SourceVersion für den @CustomerID-Parameter der Wert Original festgelegt wurde. Dadurch wird sichergestellt, dass die in der Datenquelle vorhandene Zeile aktualisiert wird, wenn der Wert der gekennzeichneten Spalte(n) in der bearbeiteten DataRow geändert wurde. In diesem Fall entspricht der Original-Zeilenwert dem aktuellen Wert in der Datenquelle und der Current-Zeilenwert enthält den aktualisierten Wert. Die SourceVersion des @CompanyName-Parameters ist nicht angegeben. Daher wird der Standardwert in der Current-Zeile verwendet.
Der folgende Code enthält SQL-Beispielanweisungen, die als CommandText für die Eigenschaften SelectCommand, InsertCommand, UpdateCommand und DeleteCommand des DataAdapter-Objekts verwendet werden. Für das OleDbDataAdapter-Objekt und das OdbcDataAdapter-Objekt müssen Sie Fragezeichenplatzhalter (?) zur Identifizierung der Parameter verwenden. Für das SqlDataAdapter-Objekt müssen Sie benannte Parameter verwenden.
SqlClient
Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City"
Dim insertSQL As String = "INSERT INTO Customers (CustomerID, CompanyName) " & _
"VALUES (@CustomerID, @CompanyName)"
Dim updateSQL As String = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " & _
"WHERE CustomerID = @OldCustomerID"
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = @CustomerID"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country AND City = @City";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (@CustomerID, @CompanyName)";
string updateSQL = "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
"WHERE CustomerID = @OldCustomerID";
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
OleDb oder Odbc
Dim selectSQL As String = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?"
Dim insertSQL AS String = "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"
Dim updateSQL AS String = "UPDATE Customers SET CustomerID = ?, CompanyName = ? WHERE CustomerID = ?"
Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
[C#]
string selectSQL = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";
string insertSQL = "INSERT INTO Customers (CustomerID, CompanyName) " +
"VALUES (?, ?)";
string updateSQL = "UPDATE Customers SET CustomerID = ?, CompanyName = ? " +
"WHERE CustomerID = ? ";
string deleteSQL = "DELETE FROM Customers WHERE CustomerID = ?";
Die parametrisierten Abfrageanweisungen definieren, welche Eingabe- und Ausgabeparameter erstellt werden müssen. Zur Erstellung eines Parameters verwenden Sie die Parameters.Add-Methode oder den Parameter-Konstruktor, um Spaltenname, Datentyp und Größe anzugeben. Für systeminterne Datentypen wie Integer müssen Sie die Größe nicht angeben. Sie können auch die Standardgröße angeben.
Das folgende Codebeispiel erstellt die Parameter für die SQL-Anweisung aus dem vorhergehenden Beispiel und füllt ein DataSet.
SqlClient
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As SqlDataAdapter = New SqlDataAdapter
Dim selectCMD AS SqlCommand = New SqlCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlDataAdapter custDA = new SqlDataAdapter();
SqlCommand selectCMD = new SqlCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
// Add parameters and set values.
selectCMD.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
OleDb
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter
Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=northwind;");
OleDbDataAdapter custDA = new OleDbDataAdapter();
OleDbCommand selectCMD = new OleDbCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
// Add parameters and set values.
selectCMD.Parameters.Add("@Country", OleDbType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OleDbType.VarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Odbc
Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
"Trusted_Connection=yes;Database=northwind")
Dim custDA As OdbcDataAdapter = New OdbcDataAdapter
Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, nwindConn)
custDA.SelectCommand = selectCMD
' Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"
Dim custDS As DataSet = New DataSet
custDA.Fill(custDS, "Customers")
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
"Trusted_Connection=yes;Database=northwind;");
OdbcDataAdapter custDA = new OdbcDataAdapter();
OdbcCommand selectCMD = new OdbcCommand(selectSQL, nwindConn);
custDA.SelectCommand = selectCMD;
//Add Parameters and set values.
selectCMD.Parameters.Add("@Country", OdbcType.VarChar, 15).Value = "UK";
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London";
DataSet custDS = new DataSet();
custDA.Fill(custDS, "Customers");
Hinweis Wenn für einen Parameter kein Parametername angegeben wird, erhält der Parameter standardmäßig den Namen ParameterN, beginnend mit "Parameter1", der jeweils um eins erhöht wird. Sie sollten die Benennungskonvention "ParameterN" bei der Angabe eines Parameternamens vermeiden, da der angegebene Name u. U. zu einem Konflikt mit einem vorhandenen Standardparameternamen in ParameterCollection führen kann. Wenn der angegebene Name bereits vorhanden ist, wird eine Ausnahme ausgelöst.
Parameter.DbType
Jeder .NET Framework-Datenprovider verwendet eigene Parametertypen. Wenn Sie den Typ angeben, wird der Parameter-Wert in den .NET Framework-Datenprovidertyp konvertiert, bevor er an die Datenquelle übergeben wird. Wird kein Typ angegeben, leitet ADO.NET den .NET Framework-Datenprovidertyp für Parameter vom .NET Framework-Typ ab, der für den Value des Parameter-Objekts angegeben ist.
Sie können den Parameter-Typ auch allgemein angeben, indem Sie die DbType-Eigenschaft des Parameter-Objekts auf einen bestimmten System.Data.DbType festlegen. Außerdem leitet ADO.NET den .NET Framework-Datenprovidertyp für einen Parameter vom DbType des Parameter-Objekts ab.
Der .NET Framework-Datenprovidertyp eines Parameter-Objekts wird vom .NET Framework-Typ für den Value des Parameter-Objekts oder vom DbType des Parameter-Objekts hergeleitet. Die folgende Tabelle zeigt den hergeleiteten Parameter-Typ auf der Grundlage des als Parameter-Wert weitergegebenen Objekts oder des angegebenen DbType.
| .NET Framework-Typ | System.Data.DbType | SqlDbType | OleDbType | OdbcType | OracleType |
|---|---|---|---|---|---|
| bool | Boolean | Bit | Boolean | Bit | Byte |
| byte | Byte | TinyInt | UnsignedTinyInt | TinyInt | Byte |
| byte[] | Binary | VarBinary. Diese implizite Konvertierung schlägt fehl, wenn das Bytearray größer als die maximale Größe von VarBinary (8000 Bytes) ist. Für Bytearrays, die größer als 8000 Bytes sind, müssen Sie SqlDbType explizit festlegen. | VarBinary | Binary | Raw |
| char | Die Herleitung von SqlDbType aus char wird nicht unterstützt. | Char | Char | Byte | |
| DateTime | DateTime | DateTime | DBTimeStamp | DateTime | DateTime |
| Decimal | Decimal | Decimal | Decimal | Numeric | Number |
| double | Double | Float | Double | Double | Double |
| float | Single | Real | Single | Real | Float |
| Guid | Guid | UniqueIdentifier | Guid | UniqueIdentifier | Raw |
| Int16 | Int16 | SmallInt | SmallInt | SmallInt | Int16 |
| Int32 | Int32 | Int | Int | Int | Int32 |
| Int64 | Int64 | BitInt | BigInt | BigInt | Number |
| object | Object | Variant | Variant | Die Herleitung von OdbcType aus Object wird nicht unterstützt. | Blob |
| string | String | NVarChar. Diese implizite Konvertierung schlägt fehl, wenn die Zeichenfolge größer als die maximale Größe von NVarChar (4000 Zeichen) ist. Für Zeichenfolgen, die mehr als 4000 Zeichen haben, müssen Sie SqlDbType explizit festlegen. | VarWChar | NVarChar | NVarChar |
| TimeSpan | Time | Die Herleitung von SqlDbType aus TimeSpan wird nicht unterstützt. | DBTime | Time | DateTime |
| UInt16 | UInt16 | Die Herleitung von SqlDbType aus UInt16 wird nicht unterstützt. | UnsignedSmallInt | Int | UInt16 |
| UInt32 | UInt32 | Die Herleitung von SqlDbType aus UInt32 wird nicht unterstützt. | UnsignedInt | BigInt | UInt32 |
| UInt64 | UInt64 | Die Herleitung von SqlDbType aus UInt64 wird nicht unterstützt. | UnsignedBigInt | Numeric | Number |
| AnsiString | VarChar | VarChar | VarChar | VarChar | |
| AnsiStringFixedLength | Char | Char | Char | Char | |
| Currency | Money | Currency | Die Herleitung von OdbcType aus Currency wird nicht unterstützt. | Number | |
| Date | Die Herleitung von SqlType aus Date wird nicht unterstützt. | DBDate | Date | DateTime | |
| SByte | Die Herleitung von SqlType aus SByte wird nicht unterstützt. | TinyInt | Die Herleitung von OdbcType aus SByte wird nicht unterstützt. | SByte | |
| StringFixedLength | NChar | WChar | NChar | NChar | |
| Time | Die Herleitung von SqlType aus Time wird nicht unterstützt. | DBTime | Time | DateTime | |
| VarNumeric | Die Herleitung von SqlDbType aus VarNumeric wird nicht unterstützt. | VarNumeric | Die Herleitung von OdbcType aus VarNumeric wird nicht unterstützt. | Number |
Hinweis Die .NET Framework-Datenprovider, die zum Lieferumfang von .NET Framework, Version 1.0, gehören, überprüfen nicht die Werte Precision und Scale von Decimal-Parameterwerten. Dies kann u. U. dazu führen, dass in der Datenquelle verkürzte Werte eingefügt werden. Wenn Sie .NET Framework, Version 1.0, verwenden, überprüfen Sie vor der Festlegung des Parameterwertes Precision und Scale der Decimal-Werte.
Für .NET Framework, Version 1.1 oder höher, wird eine Ausnahme ausgelöst, wenn ein Decimal-Parameterwert mit einem unzulässigen Precision-Wert festgelegt wird. Scale-Werte, die die Dezimalstellen des Decimal-Parameters übersteigen, werden weiterhin abgekürzt.
Parameter.Direction
Die folgende Tabelle zeigt die Werte, die Sie für die ParameterDirection-Enumeration verwenden können, um die Direction für den Parameter festzulegen.
| Membername | Beschreibung |
|---|---|
| Input | Der Parameter ist ein Eingabeparameter. Dies ist die Standardeinstellung. |
| InputOutput | Der Parameter kann sowohl für die Eingabe als auch für die Ausgabe verwendet werden. |
| Output | Der Parameter ist ein Ausgabeparameter. |
| ReturnValue | Der Parameter stellt einen Rückgabewert dar. |
Das folgende Codebeispiel zeigt, wie Sie die Direction für den Parameter festlegen.
myParm.Direction = ParameterDirection.Output
Parameter.SourceColumn, Parameter.SourceVersion
SourceColumn und SourceVersion können als Argumente an den Parameter-Konstruktor übergeben oder als Eigenschaften eines vorhandenen Parameters festgelegt werden. SourceColumn ist der Name der DataColumn der DataRow, aus der der Parameter-Wert abgerufen wird. SourceVersion gibt an, welche DataRow-Version das DataAdapter-Objekt zum Abrufen des Wertes verwendet.
Die folgende Tabelle zeigt die DataRowVersion-Enumerationswerte, die mit SourceVersion verwendet werden können.
| Membername | Beschreibung |
|---|---|
| Current | Der Parameter verwendet den aktuellen Wert der Spalte. Dies ist die Standardeinstellung. |
| Default | Der Parameter verwendet den Standardwert der Spalte. |
| Original | Der Parameter verwendet den Ausgangswert der Spalte. |
| Proposed | Der Parameter verwendet einen vorgeschlagenen Wert. |
Das folgende Codebeispiel definiert eine UPDATE-Anweisung, in der die CustomerID-Spalte als SourceColumn für zwei Parameter verwendet wird: @CustomerID (SET CustomerID = @CustomerID) und @OldCustomerID (WHERE CustomerID = @OldCustomerID). Mit dem @CustomerID-Parameter wird die CustomerID-Spalte auf den aktuellen Wert in der DataRow aktualisiert. Entsprechend wird die CustomerID SourceColumn mit einer SourceVersion von Current verwendet. Mit dem @OldCustomerID-Parameter wird die aktuelle Zeile in der Datenquelle identifiziert. Da der entsprechende Spaltenwert in der Original-Version der Zeile ermittelt wird, wird dieselbe SourceColumn (CustomerID) mit einer SourceVersion von Original verwendet.
SqlClient
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
SqlDbType.NChar, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
OleDb
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5,"CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName")
Dim myParm As OleDbParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", _
OleDbType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.Char, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 40, "CompanyName");
OleDbParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", OleDbType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
Odbc
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID")
custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName")
Dim myParm As OdbcParameter = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", _
OdbcType.Char, 5, "CustomerID")
myParm.SourceVersion = DataRowVersion.Original
[C#]
custDA.UpdateCommand.Parameters.Add("@CustomerID", OdbcType.Char, 5, "CustomerID");
custDA.UpdateCommand.Parameters.Add("@CompanyName", OdbcType.VarChar, 40, "CompanyName");
OdbcParameter myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", OdbcType.Char, 5, "CustomerID");
myParm.SourceVersion = DataRowVersion.Original;
UpdatedRowSource
Mit der UpdatedRowSource-Eigenschaft des Command-Objekts können Sie steuern, wie die Werte, die von der Datenquelle zurückgegeben werden, wieder dem DataSet zugeordnet werden. Wenn Sie für die UpdatedRowSource-Eigenschaft einen der UpdateRowSource-Enumerationswerte angeben, legen Sie fest, ob Parameter, die vom DataAdapter-Befehl zurückgegeben werden, ignoriert oder auf die geänderte Zeile im DataSet angewendet werden. Darüber hinaus können Sie angeben, ob die erste zurückgegebene Zeile (falls vorhanden) auf die geänderte Zeile im DataSet angewendet wird.
In der folgenden Tabelle werden die unterschiedlichen Werte der UpdateRowSource-Enumeration beschrieben und wie sie das Verhalten eines Befehls beeinflussen, der mit einem DataAdapter-Objekt verwendet wird.
| UpdateRowSource | Beschreibung |
|---|---|
| Both | Sowohl die Ausgabeparameter als auch die erste Zeile eines zurückgegebenen Resultsets können der geänderten Zeile im DataSet zugeordnet werden. |
| FirstReturnedRecord | Nur die Daten in der ersten Zeile eines zurückgegebenen Resultsets können der geänderten Zeile im DataSet zugeordnet werden. |
| None | Alle Ausgabeparameter oder Zeilen eines zurückgegebenen Resultsets werden ignoriert. |
| OutputParameters | Nur Ausgabeparameter können der geänderten Zeile im DataSet zugeordnet werden. |
Siehe auch
Datenzugriff mit .NET Framework-Datenprovidern | Verwenden von gespeicherten Prozeduren mit einem Befehl | DataRowVersion-Enumeration | OleDbDataAdapter-Klasse | OdbcDataAdapter-Klasse | ParameterDirection-Enumeration | SqlDataAdapter-Klasse