views:

437

answers:

3

I've jumped into an ongoing .Net 2.0 web app project for a larger company as a freelancer. Their DAL has lots of functions that manually construct and execute SQL statements -- many of them are long, messy and as a result difficult to understand and debug. I wrote a simple "sql helper" that lets me write things like this:

sqlh.addValue("name", name);
sqlh.addValue("address, address);
sqlh.addLiteral("created", "getDate()");

string sql = String.Format("INSERT INTO [Table1] ({0}) values ({1})", sqlh.getInsertFields(), sqlh.getInsertValues());

It handles nulls and also works for updates. That's about it.

Ideally I would be using Microsoft Data Application block or LINQ or something, but major architectural changes are not possible at this point. This method has saved me a lot of time, but seems like a problem best solved by using a "community-approved" solution.

Is there a popular, light-weight solution that achieves similar results, from Microsoft or otherwise?

EDIT

While the SqlParameter solutions described so far are an improvement over a purely manual statement building method, I think I still prefer my solution, where adding or removing a field from the query affects only a single line. Anything better? thanks

A: 

SqLCommand and SQLParameter objects might be of use to you:

  Dim sc As New SqlClient.SqlCommand

  Dim sp As New SqlClient.SqlParameter("@value", SqlDbType.VarChar)
  sp.Value = "test"

  sc.Parameters.Add(sp)
  sc.CommandText = "select column1, column2 from table where column3=@value"

  sc.ExecuteReader()

Its vb.net but you get the idea. This will help prevent sql injection, and looks neat and is readable too.

Dont forget to setup your connection string etc.

Pondidum
he knows about those. that's what he's trying to abstract.
AZ
not that you can tell from the snippet posted
Pondidum
+1  A: 

I created my own proc to create insert statements based on a given table schema, I thought I was pretty awesome. Then I saw this guys script. myAwesomeness--, thisGuysAwesomeness++

EDIT

I know that I found about that script as a result of a previous SO post, but I can't find it now, if someone else does or knows, please edit/or add to comment and I will edit. thx.

cmsjr
+1  A: 

You have several options from Microsoft: ADO.NET or Linq2Sql are the popular ones at this time. But link you mentioned, it will take a good amount of work to convert from generating SQL to an ORM.

To make the creation of SQL more straight-forward and safer, you should consider using SQL Parameters. Here is an example:

using (SqlCommand saveCommand = DbUtil.CreateSqlCommand(context.Transaction)) {
    saveCommand.CommandText =
        "INSERT INTO Hit (" +
            "Id, PersonId, TeamId, PlayerId" +
        ") VALUES (" +
            "@Id, @PersonId, @TeamId, @PlayerId" +
        ")";

    DbUtil.AddParameter(saveCommand, "@Id", SqlDbType.UniqueIdentifier, Guid.NewGuid());
    DbUtil.AddParameter(saveCommand, "@PersonId", SqlDbType.UniqueIdentifier, hit.PersonId);
    DbUtil.AddParameter(saveCommand, "@TeamId", SqlDbType.UniqueIdentifier, hit.TeamId);
    DbUtil.AddParameter(saveCommand, "@PlayerId", SqlDbType.UniqueIdentifier, hit.PlayerId);

    saveCommand.ExecuteNonQuery();
}

That will separate your parameter list from the SQL. Also the SQL we be much more legible. The DbUtil in the example is just a helper function I wrote to create my sql command from a connection or transaction. Also similar to your sqlh.addValue, I have a DbUtil.AddParameter that works by taking in the command, variable name, data type and value. Here's a sample including overloaded methods:

internal static SqlParameter CreateSqlParameter(
    string parameterName,
    SqlDbType dbType,
    ParameterDirection direction,
    object value
) {
    SqlParameter parameter = new SqlParameter(parameterName, dbType);

    if (value == null) {
        value = DBNull.Value;
    }

    parameter.Value = value;
    parameter.Direction = direction;
    return parameter;
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType
) {
    return AddParameter(sqlCommand, parameterName, dbType, null);
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType,
    object value
) {
    return AddParameter(sqlCommand, parameterName, dbType, ParameterDirection.Input, value);
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType,
    ParameterDirection direction,
    object value
) {
    SqlParameter parameter = CreateSqlParameter(parameterName, dbType, direction, value);
    sqlCommand.Parameters.Add(parameter);
    return parameter;
}

internal static SqlParameter AddParameter(
    SqlCommand sqlCommand,
    string parameterName,
    SqlDbType dbType,
    ParameterDirection direction
) {
    SqlParameter parameter = CreateSqlParameter(parameterName, dbType, direction, null);
    sqlCommand.Parameters.Add(parameter);
    return parameter;
}
DavGarcia