19.9. Parameterized Queries

For most SQL queries you need to be able to specify some sort of criteria or the other, for this purpose we make use of parameterized queries which are actually parameters that will be passed to with our SQL query at run time. Parameterized queries are queries that have one or more embedded parameters in the SQL statement. This method of embedding parameters into a SQL statement is less prone to errors than the method of dynamically building up a SQL string. So let's take a look at an example making use of parameterized queries in a .NET application. Suppose we want to update Scott's salary by 500, then we would do something like the following:

The following example demonstrates using parameterized queries. The example also shows the use of an update statement.

   
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>

<script language="C#" runat="server" >

private void Page_Load(object sender, System.EventArgs e)
{
	string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
	EDBConnection conn = new EDBConnection(strConnectionString);
			
	string updateQuery  = "UPDATE emp SET sal = sal+500 where empno = :ID";
	
			
	try {
		conn.Open();

		EDBCommand cmdUpdate = new EDBCommand(updateQuery,conn);
			
		cmdUpdate.Parameters.Add(new EDBParameter(":ID", EDBTypes.EDBDbType.Integer));

		cmdUpdate.Parameters[0].Value = 7788;								

		cmdUpdate.ExecuteNonQuery();
				
		Response.Write("Record Updated");
				
	}
	catch(Exception exp) { 
		Response.Write(exp.ToString());
	}
	finally {
		conn.Close();
	}
}

</script>

   

Save the file as "updateSalary.aspx" and save it in your default web server's root then access it via typing http://localhost/updateSalary.aspx in your web browser. If no errors are generated then a "Record Updated" message should be shown.