EntLib extension to execute queries with less code

For internal security training of SDX developers I’m currently preparing a really small web-application with different vulnerabilities. The goal is to have an application that does have all of the OWASP Top 10, so that I can use it to make my colleagues search them and discuss mitigations.

This way I came across the issue of using parameters with SQL statements in DAAB. This is not really an “issue” as long as you accept to create a command object, then use the database object to add the parameters (one line of code per parameter) and finally execute the command to get the result. But I don’t like to repeat such steps and I wanted to use something more “elegant”, so I wrote a little extension method for the Database object of EntLib:

/// <summary>

/// The same as <see cref="Database.ExecuteScalar(System.Data.Common.DbCommand)"/>, but adds the properties of

As you can see it inspects a value of type “object” in order to use its properties to build up the parameters. This way you can use anonymous types to pass the parameters to the SQL statement:

privatestring CustomerIdByName(string customerName)

{

conststring Sql = "SELECT TOP 1 Id AS AspNetUserId " +

"FROM AspNetUsers " +

"WHERE UserName = @userName";

var database = new SqlDatabase(this.ConnectionString);

return database.ExecuteScalarWithParameters<string>(

Sql, new { userName = customerName });

}

I don’t think this is a really new invention, but it may be useful for you, so I want to share it.

Be aware that this code is far from being perfect: currently there’s no error handling, no input validation to guide the developer, it only handles strings, integers and GUIDs, etc., so you might not use it “as is” in production code. But since I did not find an example how to do this with Google, it might be the code snippet giving you an idea on how to keep your application code even smaller.

Advertisements

Share this:

Like this:

LikeLoading...

This entry was posted on Tuesday, January 21st, 2014 at 7:44 am and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Post navigation

One Response to EntLib extension to execute queries with less code

Just the other days I had to look into two (independent) projects to provide some review and coaching. One a commercial 3rd party application, the other provided by people actually providing guidance for other developers.
Both contained (among other code smells) code that was littered with details on how to achive certain tasks, many of those lines repeating itself. The net result is that these methods may do their job, but they make it very hard to determine what their job is in the first place.

While your particular helper method may not be rocket science, it shows what I call “the power of helper methods” quite clearly: It makes the calling method readable! Instead of hiding what is actually “semantically” done under a bunch of lines dealing with each detail at once, it clearly states what it is doing, without cluttering the method – I can read this method and understand it immediately.

Given that said applications cannot be dismissed with “what do you expect?”, this is a lesson far too many developers still have to learn.