2.21. Using Parameterized SQL Statements
Problem
You want to create and execute a SQL statement having parameters that are set dynamically.
Solution
Add parameters to the Command
object’s Parameters
collection.
The sample code contains two event handlers and one method:
Form.Load
Sets up the sample by creating a
DataTable
containing all Customers data from Northwind. The default view of the table is bound to aCustomers
data grid on the form. The handler for theCurrentCellChanged
event of the data grid is called to initialize the grid containing Orders with the data for the row selected by default in theCustomers
data grid.DataGrid.CurrentCellChanged
Gets the
CustomerID
from the data grid when the rows selected in the data grid changes and calls theLoadOrderGrid( )
method to update the Orders displayed to match the selected Customer.LoadOrderGrid( )
This method defines a parameterized SQL statement. A
Command
is built from the statement and the single parameter,@CustomerID
is created and set to thecustomerId
argument passed into the method. TheCommand
is used by aDataAdapter
to fill aDataTable
with the Orders for the specified Customer. The default view of the table is bound to the Customers data grid on the form.
The C# code is shown in Example 2-30.
Example 2-30. File: UsingParameterizedQueriesForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // Table name constants private const String CUSTOMERS_TABLE ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.