C# Prepared Statements

In C# prepared statements (or parameterized statements) are built via the Command object. If your database management system is SQL Server, then you will be using the SqlCommand object (instead of your regular Command object that may be used with ODBC and Access).

The Command object allows you to add parameters to a parameter collection. In turn these parameters are matched by name and the underlying functionality handles escaping and any other necessary pre-processing of your SQL statement. Here is an example:

A simple query to authenticate a user against a database:

String sql = "SELECT count(username) FROM login where username=@USERNAME and pwd=@PWD";

The named parameters are @USERNAME and @PWD respectively.
The command object allows you to add SqlParameter objects to the Parameters collection. Here is one way of setting up your parameters:

SqlParameters example:

commandObj.Parameters.Add("@USERNAME", SqlDbType.VarChar, username.Length).Value = username; commandObj.Parameters.Add("@PWD", SqlDbType.VarChar, pwd.Length).Value = pwd;

The SqlCommand (or Command) object may be used to build parameterized queries and updates. The parameter usage is exactly the same with both types of operations. Because the Parameters member is a collection, when re-using the same SqlCommand object in a looping operation you must empty the collection to avoid having a crash. This removal of old parameters is done with the collection's Clear method:

Clearing Parameters:

commandObj.Parameters.Clear();

Parameterized queries provide a better and cleaner approach to SQL queries in your applications. The SQL statements don't have to be sanitized in order to avoid quotation and other inconsistencies.


[출처] http://prepared-statement.blogspot.com/2006/02/c-data-access-prepared-statements.html