Social Networks

Adding Parameters to SQL Commands– When working with data, you’ll often want to filter results based on some criteria. Typically, this is done by accepting input from a user and using that input to form a SQL query.
parameter-sql-command
The following code demonstrates a working program that uses SqlParameter objects.


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

class ParamDemo
{
static void Main()
{
// conn and reader declared outside try
// block for visibility in finally block
SqlConnection conn = null;
SqlDataReader reader = null;

string inputCity = "London";

try
{
// instantiate and open connection
conn = new
SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();

// don't ever do this!
// SqlCommand cmd = new SqlCommand(
// "select * from Customers where city = '" + inputCity + "'";

// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);

// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;

// 3. add new parameter to command object
cmd.Parameters.Add(param);

// get data stream
reader = cmd.ExecuteReader();

// write each record
while(reader.Read())
{
Console.WriteLine("{0}, {1}",
reader["CompanyName"],
reader["ContactName"]);
}
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}

// close connection
if (conn != null)
{
conn.Close();
}
}
}
}

Leave a Reply

Your email address will not be published. Required fields are marked *

Allowed Tags:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>