The ADO.NET command parameters

Building SQL commands based on parameters is a good way to block SQL injection vulnerabilities. This is a short article about how to use the ADO.NET parameters in different database management systems, like: Microsoft SQL Server, MySQL and Oracle. Each .NET Framework data provider included with the .NET Framework has a Command object. The .NET Framework Data Provider for OLE DB has the OleDbCommand object, the SQL Server data provider includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the Oracle data provider has an OracleCommand object. The first step in using parameters in SQL queries is to build a command string containing parameter placeholders. These placeholders are filled in with actual parameter values when the Command object executes.

ADO.NET parameters in SQL Server

The syntax of a SQL Server parameter uses a "@" symbol prefix on the parameter name as shown below:

C# .NET

// Define the SQL Server command to get the product having the ID = 100...

SqlCommand command = new SqlCommand();

command.CommandText = "SELECT * FROM Product WHERE Product.ID=@PROD_ID";

command.Parameters.Add(new SqlParameter("@PROD_ID", 100));

 

// Execute the SQL Server command...

SqlDataReader reader = command.ExecuteReader();

DataTable tblProducts = new DataTable();

tblProducts.Load(reader);

 

foreach (DataRow rowProduct in tblProducts.Rows)

{

    // Use the data...

}

ADO.NET parameters in MySQL

The syntax of a MySQL parameter uses a "?" symbol prefix on the parameter name as shown below:

C# .NET

// Define the MySQL command to get the product having the ID = 100...

MySqlCommand command = new MySqlCommand();

command.CommandText = "SELECT * FROM Product WHERE Product.ID=?PROD_ID";

command.Parameters.Add(new MySqlParameter("?PROD_ID", 100));

 

// Execute the MySql command...

MySqlDataReader reader = command.ExecuteReader();

DataTable tblProducts = new DataTable();

tblProducts.Load(reader);

 

foreach (DataRow rowProduct in tblProducts.Rows)

{

    // Use the data...

}

Here you can read more about how to use MySQL in C#: Connect to a MySQL 5.0 database with ADO.NET

ADO.NET parameters in Oracle

The syntax of an Oracle parameter uses a ":" symbol prefix on the parameter name as shown below:

C# .NET

// Define the Oracle command to get the product having the ID = 100...

OracleCommand command = new OracleCommand();

command.CommandText = "SELECT * FROM Product WHERE Product.ID=:PROD_ID";

command.Parameters.Add(new OracleParameter(":PROD_ID", 100));

 

// Execute the Oracle command...

OracleDataReader reader = command.ExecuteReader();

DataTable tblProducts = new DataTable();

tblProducts.Load(reader);

 

foreach (DataRow rowProduct in tblProducts.Rows)

{

    // Use the data...

}