ADO .NET: Creating and Executing Parameterized Queries

In this post we’ll discuss how we can execute parameterized queries in .Net. At times when you can’t use the most recommended way to retrieve/update data through Stored Procedures, you can rely on parameterized queries. It is much safer and recommended than building a sql string dynamically, which is a bit more error prone as well as hard to maintain. Parameterized queries are queries that have one or more embedded parameters in sql statement which are also type safe. You build them separately and attach them into the sql statement.

Following example shows how to use parameterized queries with ADO.Net.

static void GetCustomersWithCity(string city)
{
   DataSet ds;
   SqlConnection con = new SqlConnection("server=localDBServer;database=AdventureWorks;Trusted_Connection=yes");

   SqlCommand cmd = new SqlCommand("select c.FirstName, c.LastName  from Person.Contact c " +
                                       " inner join Person.Address a " +
                                       " on c.ContactID = a.AddressID " +
                                       " where a.City = @CityParam;", con);

   SqlParameter cityParam = cmd.Parameters.Add("@CityParam", SqlDbType.VarChar);
   cityParam.Value = city;

   ds = new DataSet();
   SqlDataAdapter adapter = new SqlDataAdapter();
   adapter.SelectCommand = cmd;
   adapter.Fill(ds);
}

Read more: Beyond Rational

Posted via email from jasper22's posterous