One of the biggest concerns I face as a developer when writing SQL queries in my application is how to make them safe so that they are not prone to SQL injection attacks and at the same time they execute efficiently. Most developers go the route of putting all their queries as stored procedures in the database to handle the security and performance concern. Although this is a viable solution, it splits your application code in multiple places making it difficult to read, maintain and debug.

Also I want to avoid writing the tedious ADO.NET code to create SQL Commands, add the parameters, set the right data types, check for nulls set nulls, etc.

We can achieve all this with a high level of security, flexibility and maintainability by writing parameterized SQL queries and letting the Orasis Mapping Studio handle the mapping of parameters and generating data access code that executes the parameterized query and maps the results to our objects.

Parameterized Query

A parameterized query has SQL parameters embedded in the query itself, and the values of the parameters in the queries are set using the ADO.NET API to set parameters on the Command object. The parameterized query has a fixed execution plan therefore the database is able to cache the execution plan and it runs faster the next time the same query is executed. They are the safer, better, faster alternative to dynamic queries, that developers build by concatenating strings to the query to give it the dynamic effect.

Example query:

select * from users where UserName = @userName 

Optional parameters

Parameterized queries can also be written in such a way that allows certain parameters to be optional so that the database can ignore them if there is a null value provided for them. Here is an example of a parameterized query that has an optional parameter which will get ignored if there is a null value assigned to it.

select * from users where username = @username and (usertype = @usertype or @usertype is null)

The (usertype = @usertype or @usertype is null) clause will resolve to ‘true’ if the @usertype parameter is set to null and therefore the query will in effect ignore the parameter.

This query when mapped through the Orasis Mapping Studio will automatically have the parameters set to a null value if the user passes in null to the .NET method that gets generated.

Database Differences

Different database use different delimiters for handling parameters such as ‘@’ for Microsoft SQL Server and Access, ‘:’ for Oracle,  and ‘@’ for MySql. The Orasis Mapping Studio is able to identify the right parameter for your database provider and parse the queries accordingly so that the code is generated accordingly and the values are set correctly.

Example

Let’s look at a sample query with parameters and map it to an object and see how the Orasis Mapping Studio generates code for the query. We will then run the query with different values and see the results we get.

Once the method and assembly are generated we can call them in our application using the following test method and see the results in our application.

public void TestParameterizedQueryMethod()
{

	//Instantiate the generated DAL Class        
	SqlServerProject.DataAccess.AdventureDBAccess dataAccess = new SqlServerProject.DataAccess.AdventureDBAccess();
	        
	//Execute the method with parameter value 1        
	IList<Employee> resultsList = dataAccess.EmployeeQueryMapping(1);
	System.Console.WriteLine("Results with value {0}: {1}",1, resultsList.Count);

	//Execute the same method with a null value for the parameter        
	resultsList = dataAccess.EmployeeQueryMapping(null);
	System.Console.WriteLine("Results with value {0}: {1}","null", resultsList.Count);

}

This concludes showing the how easy it is to use Parameterized queries and map them with the Orasis Mapping Studio. The same can be done with different databases by using the correct parameter delimiter.