|
Make Code Robust With Flexible Queries
Platform-independent query abstractions let you infer relations, simplifying your .NET code and making it more robust.
by Kathleen Dollard
August 13, 2004
Technology Toolbox: VB.NET, SQL Server 2000, XML
In the past, if you needed extensive flexibility in data selection, you'd probably concatenate the query as a text string in your application. However, this concatenated Transact-SQL (T-SQL) is vulnerable to SQL injection attacks and platform-dependent, which suggests you should use stored procedures as a best practice. But this presents a new problem: What if you can't use stored procedures because you need to offer dozens or hundreds of filtering options? Stored procedures just don't provide the flexibility old-style concatenated SQL does.
I'll show you how to resolve this issue with platform-independent query abstractions you can express in the dialect of your back end. These abstractions also let you infer relations, rather than express them directly, to simplify your .NET code and make it more robust. I'll extend these ideas to include child queries in a single database call, and I'll show you how to parameterize the queries for protection against SQL injection. I've included a WinForms application for testing this system that you can download. It also illustrates how the query is broken down for the abstraction (see Figure 1).
Stored procedures execute under the privileges of the person who created them, so you can reduce the permissions granted to users of your application. This benefit is not available when you pass SQL as text in the solution I'll describe, so I suggest you use this article's solution only for SELECT queries and when you require a good deal of flexibility. Restricting non-stored procedure usage to SELECT queries lets you limit user permissions to SELECT access.
In this scenario, performance is secondary. The T-SQL won't be as fast as possible, but the queries will provide solid performance, assuming you're using the appropriate indexes. Parameterizing your SQL queries protects you against SQL injection attacks because SQL Server doesn't execute values passed as parameters unless you use sp_executesql, which I don't recommend doing.
Use the phrase query to refer to the entire set of SQL statements—the entire T-SQL batch—sent as a group to the server. This batch might consist of different types of SELECT statements. Support child retrieval and isolate the joins required for the filter (WHERE clause) from those required for the display columns by selecting the primary keys for the main table into a SQL Server temp table. SQL Server automatically manages temp tables, which are prefixed by a pound sign (#). The query always includes selections from the main table, and might optionally include statements to retrieve child data (see Listing 1 for a sample of these T-SQL statements). The filter criteria include support for a parenthetical segment, allowing you to control execution by overriding operator precedence when necessary.
Shift the Burden
Core to this approach is an abstraction for the query. An abstraction is a set of classes that work together to provide the behavior you're modeling. In addition to the parts of the SQL query itself, you'll want to shift the burden for detecting relations, and ultimately for creating JOIN clauses, from the programmer to the system. Inferring relations depends on the definition of tables and relations in your database. The abstraction also must support parameterization of the query. All these tasks force the resulting abstraction to consist of many classes (see the BaseQuery project in the downloadable code). The core Query class contains the main table (as a string), an array of strings for each child table, a strongly typed collection of DisplayColumn objects, and a Filter object (see Figure 2 for an illustration of the relations between these classes). The DisplayColumn objects simply contain table and column names.
Back to top
|