Simple SQL statement builder for .Net

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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;
}