ADO.NET 3.5 Cookbook, 2nd Edition

3.17. Executing a Parameterized Query

Problem

You want to create and execute a SQL statement with parameters that are set dynamically.

Solution

Add parameters to the Command object's Parameters collection.

The solution begins by creating a parameterized SQL statement that retrieves records from the Sales.SalesOrderHeader table in AdventureWorks where the TotalDue field is greater than a parameterized amount. A Command is built from the statement and the single parameter, @TotalDue, is created and set to 200,000. A DataAdapter uses the Command to fill a DataTable. Several columns for all rows in the result set are written to the console. The solution shows how to create and execute a parameterized query using both the SQL Server data provider and the OLE DB data provider. Results are output to the console.

The C# code in Program.cs in the project ExecutingParameterizedQuery is shown in Example 3-22.

Example 3-22. File: Program.cs for ExecutingParameterizedQuery solution

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace ExecutingParameterizedQuery
{
    class Program { static void Main(string[] args) { // SQL Server parameterized query Console.WriteLine("---Data Provider for SQL Server"); string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader " + "WHERE TotalDue > @TotalDue"; SqlConnection sqlConnection = new SqlConnection(sqlConnectString); ...