Build efficient SQL statements with multiple parameters in C#-CodePudding

I have a list of items with different ids which represent a SQL table's PK values. Is there any way to build an efficient and safe statement?

Since now I've always prepared a string representing the statement and build it as I traversed the list via a foreach loop.

Here's an example of what I'm doing:

string update = "UPDATE table SET column = 0 WHERE";

foreach (Line l in list)
{
  update  = " id = "   l.Id   " OR";
}

// To remove last OR
update.Remove(update.Length - 3);

MySqlHelper.ExecuteNonQuery("myConnectionString", update);

Which feels very unsafe and looks very ugly.

Is there a better way for this?

CodePudding user response：

So yeah, in SQL you've got the 'IN' keyword which allows you to specify a set of values.

This should accomplish what you would like (syntax might be iffy, but the idea is there)

var ids = string.Join(',', list.Select(x => x.Id))

string update = $"UPDATE table SET column = 0 WHERE id IN ({ids})";

MySqlHelper.ExecuteNonQuery("myConnectionString", update);

However, the way you're performing your SQL can be considered dangerous (you should be fine as this just looks like ids from a DB, who knows, better to be safe than sorry). Here you're passing parameters straight into your query string, which is a potential risk to SQL injection which is very dangerous. There are ways around this, and using the inbuilt .NET 'SqlCommand' object

https://www.w3schools.com/sql/sql_injection.asp

https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand?view=dotnet-plat-ext-6.0

CodePudding user response：

It would be more efficient to use IN operator:

string update = "UPDATE table SET column = 0 WHERE id IN (";

foreach (Line l in list)
{
    update  = l.Id   ",";
}

// To remove last comma
update.Remove(update.Length - 1);

// To insert closing bracket
update  = ")";

CodePudding user response：

If using .NET Core Framework, see the following library which creates parameters for a WHERE IN. The library is a port from VB.NET which I wrote in Framework 4.7 years ago. Clone the repository, get SqlCoreUtilityLibrary project for creating statements.

Setup.

public void UpdateExample()
{
    var identifiers = new List<int>() { 1, 3,20, 2,  45 };
    var (actual, exposed) = DataOperations.UpdateExample(
        "UPDATE table SET column = 0 WHERE id IN", identifiers);

    Console.WriteLine(actual);
    Console.WriteLine(exposed);
}

Just enough code to create the parameterizing SQL statement. Note ActualCommandText method is included for development, not for production as it reveals actual values for parameters.

public static (string actual, string exposed) UpdateExample(string commandText, List<int> identifiers)
{

    using var cn = new SqlConnection() { ConnectionString = GetSqlConnection() };
    using var cmd = new SqlCommand() { Connection = cn };
    
    cmd.CommandText = SqlWhereInParamBuilder.BuildInClause(commandText   " ({0})", "p", identifiers);


    cmd.AddParamsToCommand("p", identifiers);

    return (cmd.CommandText, cmd.ActualCommandText());

}

For a real app all code would be done in the method above rather than returning the two strings.

Results

UPDATE table SET column = 0 WHERE id IN (@p0,@p1,@p2,@p3,@p4)
UPDATE table SET column = 0 WHERE id IN (1,3,20,2,45)