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.

Example 19-5. Example - Parameterized Queries

   
<% @ 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.

19.9.1. Parameterized Queries - Explanation

You can update records by making use of the SQL Update command. For executing an update command with EDB Connector/.NET we are carrying out the following steps in the example above:

Each of the parameters specified with an EDBParameter object. For each parameter that you need to specify in an SQL query you create an EDBParameter object and then assign values to that object. Then you add the EDBParameter object to the parameters collection of the EDBCommand object.

Note: The syntax for specifying the parameter placeholder varies depending on the backend database. For an EnterpriseDB data source, parameter placeholders are a colon (:) instead of a question mark (?) or (@Parameter).