CODEWITHSUNDEEP
c#sqlstring
runcmd
runcmd

Reputation: 602

SQL String Builder Query in C# with Optional Parameters

I'm building a SQL update statement based on which check-boxes where selected for each field to update. I'm running into trouble syntax wise with my statement because certain parameters are optional. For example, if the user was to just select the Location check-box, the statement would execute perfectly because only one of the columns is updated: 

UPDATE [Group] SET Location = @p_Location WHERE GroupID = @p_GroupID

However, if the user selects more than one column to update then I have a problem syntax wise because I cannot control the commas after the parameters since they could be in any order

var sql = new StringBuilder();
sql.Append(@"UPDATE [Group] SET ");
if (updateModel.IsUseGroupNumberSelected)
{
    sql.Append("GroupNumber = @p_GroupNumber");
    cmd.Parameters.AddWithValue("@p_GroupNumber", updateModel.Group.GroupNumber);
}
if (updateModel.IsUseTerminationDateSelected)
{
    sql.Append("GroupNumber = @p_GroupNumber");
cmd.Parameters.AddWithValue("@p_TerminationDate", updateModel.Group.TerminationDate);
}
if (updateModel.IsUseLocationSelected)
{
    sql.Append("Location = @p_Location");
    cmd.Parameters.AddWithValue("@p_Location", updateModel.Group.Location);
}
sql.Append(" WHERE GroupID = @p_GroupID");
cmd.Parameters.AddWithValue("@p_GroupID", updateModel.Group.GroupID);

Does anyone have any ideas on how to combat this? I was thinking about looping through the SQL string and adding an extra comma where needed but it seems less secure and messy. Thought I should pick the minds of the brillent stackoverflow first. Thanks for your help!

Upvotes: 1

Views: 9597

Answers (4)

user957902
user957902

Reputation: 3060

A little bit of boolean logic is all you need to add to make it work like you want. The key is to only add the comma when you are not the first to add a column to update. 

       Bool first = true;
        var sql = new StringBuilder();
        sql.Append(@"UPDATE [Group] SET ");
           if (updateModel.IsUseGroupNumberSelected)
           {
               if (!first) sql.Append(", ");
               sql.Append("GroupNumber = @p_GroupNumber");
               cmd.Parameters.AddWithValue("@p_GroupNumber", updateModel.Group.GroupNumber);
               first = false;
            }
           if (updateModel.IsUseTerminationDateSelected)
           {
               if (!first) sql.Append(", ");
               sql.Append("TerminationDate = @p_TerminationDate");
               cmd.Parameters.AddWithValue("@p_TerminationDate", updateModel.Group.TerminationDate);
               first = false;
            }

           if (updateModel.IsUseLocationSelected)
           {
                if (!first) sql.Append(", ");
                sql.Append("Location = @p_Location");
                cmd.Parameters.AddWithValue("@p_Location", updateModel.Group.Location);
                first = false;
            }

          sql.Append(" WHERE GroupID = @p_GroupID");
          cmd.Parameters.AddWithValue("@p_GroupID", updateModel.Group.GroupID);

Upvotes: 2

rory.ap
rory.ap

Reputation: 35318

You could wait until the end to add the beginning of the query:

private void DoStuff()
{
    AppDomain.CurrentDomain.UnhandledException += HandleUnhandledException;


    var sql = new StringBuilder();
    if (updateModel.IsUseGroupNumberSelected)
    {
        AppendCommaIfNeeded(sql);
        sql.Append("GroupNumber = @p_GroupNumber");
        cmd.Parameters.AddWithValue("@p_GroupNumber", updateModel.Group.GroupNumber);
    }
    if (updateModel.IsUseTerminationDateSelected)
    {
        AppendCommaIfNeeded(sql);
        sql.Append("GroupNumber = @p_GroupNumber");
        cmd.Parameters.AddWithValue("@p_TerminationDate", updateModel.Group.TerminationDate);
    }
    if (updateModel.IsUseLocationSelected)
    {
        AppendCommaIfNeeded(sql);
        sql.Append("Location = @p_Location");
        cmd.Parameters.AddWithValue("@p_Location", updateModel.Group.Location);
    }
    sql.Insert(0, @"UPDATE [Group] SET ");
    sql.Append(" WHERE GroupID = @p_GroupID");
    cmd.Parameters.AddWithValue("@p_GroupID", updateModel.Group.GroupID);
}

private void AppendCommaIfNeeded(StringBuilder toBuilder)
{
    if (toBuilder.Length > 0) toBuilder.Append(",");
}

Upvotes: 1

Crowcoder
Crowcoder

Reputation: 11514

Create your update statement with all the parameters, then use COALESCE to ignore the parameters that were not set.

UPDATE [Group] 
   SET Location = COALESCE(@p_Location, [Location]),
       GroupNumber = COALESCE("@_GroupNumber", [GroupNumber]),
   ... etc

You can do that in the WHERE clause also if you need to dynamically filter.

Upvotes: 1

ps2goat
ps2goat

Reputation: 8485

Track the additional queries in a List<string>. You can join them with a comma after you have them all in a list:

https://dotnetfiddle.net/LtIhW8

   List<string> whereClauses = new List<string>();

    for(int i = 0; i < 10; i++){
        whereClauses.Add("where Clause " + i.ToString())        ;
    }


    sql.Append(string.Join(",", whereClauses));

Upvotes: 0

Related Questions