Try:
if (Ordre.Attatchments.Any(x => x.SaveState == false))
{
string connectionString = GetConnectionString(connectionStringName);
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
const string sql = "UPDATE dbo.SaleAttatchments SET[File] = @FileData WHERE RefUid = @Id And FileName = @FileName;";
var parameters = new List<DynamicParameters>();
foreach (AttatchmentModel doc in Ordre.Attatchments.Where(x => x.SaveState == false))
{
var dParams = new DynamicParameters();
dParams.Add("@Id", parameters.RefUID, DbType.String);
dParams.Add("@FileData", parameters.File, DbType.Binary);
dParams.Add("@FileName", parameters.FileName, DbType.String);
parameters.Add(dParams);
}
connection.Execute(sql, parameters);
}
}
Execute a Command multiple times - Dapper[
^]
Alternatively, use a transaction:
if (Ordre.Attatchments.Any(x => x.SaveState == false))
{
string connectionString = GetConnectionString(connectionStringName);
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
const string sql = "UPDATE dbo.SaleAttatchments SET[File] = @FileData WHERE RefUid = @Id And FileName = @FileName;";
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (AttatchmentModel doc in Ordre.Attatchments.Where(x => x.SaveState == false))
{
var dParams = new DynamicParameters();
dParams.Add("@Id", parameters.RefUID, DbType.String);
dParams.Add("@FileData", parameters.File, DbType.Binary);
dParams.Add("@FileName", parameters.FileName, DbType.String);
connection.Execute(sql, parameters, transaction);
}
transaction.Commit();
}
}
}
Either way,
NEVER try to embed your parameter values directly into the SQL query.