SQL (using named parameters)

We recommend using the following Anonymous Types / Dictionary approaches for passing SQL parameters instead of formatted strings. This approach is safer and no longer requires you to provide quotes for escaping string values.

Bellow you can see how using an anonymous type object to provide the parameter values. Note how the parameter values are provided in the SQL statement:

Sql.SelectRow(
    "SELECT lager_nr, bestand FROM bestand WHERE lager_nr = :lagerNr AND art_nr = :artNr",
    new { lagerNr = 123, artNr = "abc" });

For further simplification you can use variables instead of explicit values for each variable needed.

var lagerNr = 123;
var artNr = "abc";

Sql.SelectRow(
    "SELECT lager_nr, bestand FROM bestand WHERE lager_nr = :lagerNr AND art_nr = :artNr",
    new { lagerNr, artNr });

For large SQL statements that need to be concatenated in a loop you can use a dictionary instead. The code bellow generates multiple inserts and executes them in a single Sql.Execute call.

var sqlParams = new Dictionary<string, object>();

// generate multiple update statements
for (int i = 0; i < 5; i++)
{
		sqlParams[$"lagerNr{i}"] = 123;
		sqlParams[$"artNr{i}"] = "abc";
		
		sqlStatement += 
				$"INSERT INTO bestand (lager_nr, artNr) VALUES ( :lagerNr{i}, :artNr{i} );" 
				+ Environment.NewLine;
}

Sql.Execute(sqlStatement, sqlParams);