In the case that inspired this post, I needed to populate a simple ASP.NET 1.1 data repeater with data in a specific order, with minimal impact to the existing code. The existing code did not contain DataView or DataAdapter ADO.NET objects. Although I could have composed a SQL statement and switched the SqlCommand object to text query mode, I knew that is a bad idea, because:
- Composed text queries are begging for an injection attack, and introduce a massive security hole
- Stored procedures are much more efficient, because they are pre-compiled
SELECT * FROM Products ORDER BY @orderByColumn
The solution is to use a bit of both worlds. Pass the column name as a parameter to the stored procedure, and then compose it into a varchar parameter. Finally, execute the parameter containing the composite query.
DECLARE @orderByColumn varchar(50) -- (THE INPUT PARAMETER) @sql varchar(255) -- Be sure to include a space after ORDER BY SET @sql = 'SELECT * FROM Products ORDER BY ' + @orderByColumn EXEC(@sql)
Receiving data in the order you need saves you the trouble of adding a DataTable, DataView, DataAdapter, and possibly other objects, and allows you to bind the command results directly to a data repeater.