- Choosing a Provider
- Connection
- Command
- DataReader
- SQL Provider Parameterization
- OLEDB Provider Parameterization
- Generic Connected Data Access
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
SQL Provider Parameterization
Sometimes you want the parameter capability of the stored procedure but don't want the hassle of creating one on the database server. The SQL .NET data provider provides a way to pass parameters to a SQL statement that saves the programmer from having to develop that hard-to-code SQL INSERT or UPDATE statement, especially when you're passing strings.
The SQL statement looks like a TSQL stored procedure in having @ characters preceding the variables in the SQL statements. These named parameters can be substituted programmatically with the Parameters collection of the Command object in the same manner as we used in the previous demo of stored procedure execution. Listing 9 shows an example of this technology with the SQL provider.
Listing 9 SQL Provider Named Parameter Query Demo
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConnectedOpsDemo
{
class SqlCmdParamDemo
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
try
{
// open connection to the data source
conn = new
SqlConnection("server=(local);database=Northwind;" +
"integrated security=true;");
conn.Open();
// create a parameter for the name variable to the
// parameterized query that returns DataReader via
//ExecuteReader
SqlCommand cmd = new SqlCommand(
"SELECT * FROM Customers WHERE CustomerID LIKE @Name",
conn);
SqlParameter name = new
SqlParameter("@Name",SqlDbType.VarChar,7);
name.Value = "%D%";
cmd.Parameters.Add(name);
// execute command to return a datareader that closes our
// parent connection when it is closed
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine("\nQUERY: {0} \nReturned the following " +
"rows from ExecuteReader",
cmd.CommandText);
// read through all rows
while (rdr.Read())
{
// display CustomerID (position 0),
// ContactName (by name) columns
// from the DataReader
Console.WriteLine("CustomerID: {0} ContactName: {1}",
rdr.GetString(0), rdr["ContactName"]);
}
}
finally
{
// close the reader in the face of exceptions
if (rdr != null)
{
if (!rdr.IsClosed)
rdr.Close();
}
}
}
}
}
This example gives us a way to parameterize the rows selected from the Customers table without having to worry about quotes and building an ugly SQL string inside our program. The query has an @Name parameter to hold the value passed in.
A SQLParameter object represents the @Name parameter and is used in an identical way as in the previous demo. Listing 10 shows the output result.
Listing 10 SQL Provider Named Parameter Query Demo Results
QUERY: SELECT * FROM Customers WHERE CustomerID LIKE @Name Returned the following rows from ExecuteReader CustomerID: BOLID ContactName: Martín Sommer CustomerID: DALEM ContactName: Dale Michalk CustomerID: DRACD ContactName: Sven Ottlieb CustomerID: DUMON ContactName: Janine Labrune CustomerID: GALED ContactName: Eduardo Saavedra CustomerID: GODOS ContactName: José Pedro Freyre CustomerID: LINOD ContactName: Felipe Izquierdo CustomerID: MAISD ContactName: Catherine Dewey CustomerID: OLDWO ContactName: Rene Phillips CustomerID: QUEDE ContactName: Bernardo Batista CustomerID: SPECD ContactName: Dominique Perrier CustomerID: SUPRD ContactName: Pascale Cartrain CustomerID: TRADH ContactName: Anabela Domingues CustomerID: WANDK ContactName: Rita Müller