Using Parameters in Queries
Parameters are often used when querying relational databases. Using parameters in queries allows the database client to run an SQL statement multiple times with potentially different sets of values. This allows queries with the same structure and with only differing variables to be run without having to recompile the query multiple times. This improved efficiency can be used to minimize query execution time, especially for large queries or queries that are run many times.
In Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE), input parameters in a query are specified with parameter markers. A parameter marker is a question mark (?) placed in the location of an input expression in an SQL statement. Values for the parameters are passed programmatically when the command is executed.
Named parameters are not supported in SQL Server CE. Parameters can be used to replace only column values. They cannot be used in queries to replace other objects, such as table or column names. The number and order in which you programmatically specify parameters must correspond to the parameter markers in the query.
The .NET Compact Framework Data Provider for SQL Server CE supports use of parameters in queries. For more information about the System.Data.SqlServerCe.SqlCeParameter and System.Data.SqlServerCe.SqlCeParameterCollection classes, see the .NET Compact Framework SDK in Microsoft Visual Studio® .NET.
The OLE DB Provider for SQL Server CE also supports using parameters in queries. For more information, see Parameters.
A. Using parameters in an SQL query
In the following simple statement using the Northwind sample database, the requested value for CustomerID is passed programmatically when the SELECT statement is executed.
SELECT * FROM Orders WHERE CustomerID =?
B. Using parameters to an insert a new row
In the following example, an SQL INSERT statement uses parameters to insert a new row into the Employees table of the Northwind sample database.
INSERT INTO Employees(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo, PhotoPath, rowguid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)