I have looked at several examples of passing an array as a sql parameter but I also need to pass some additional parameters. I am using the array to filter my insert and also passing in the values for the update. The code below compiles but when I run it, I get an odd error about a linked server. That makes no sense since I am only updating a single table on the current server.
public List<InvoiceForEmail> RejectInvoices(
List<int> invoiceIds,
string reason,
string ADUsername)
{
if (invoiceIds == null || invoiceIds.Count <= 0) throw new ArgumentOutOfRangeException(nameof(invoiceIds));
if (reason == null) throw new ArgumentNullException(nameof(reason));
int rowsUpdated;
using (var context = new ShopAPDbContext())
{
var cmd = new SqlCommand(@"
UPDATE ShopAP.dbo.ShopPO
SET ModifiedDate = GETDATE(),
RejectedDate = GETDATE(),
RejectedReason = @Reason,
RejectedBy = @RejectedBy,
FailedReason = @FailedReason
WHERE ID IN ({Id})
AND ApprovalDate IS NULL
AND RejectedDate IS NULL");
cmd.AddArrayParameters("Id", invoiceIds);
cmd.Parameters.Add(new SqlParameter("@Reason", reason));
cmd.Parameters.Add(new SqlParameter("@RejectedBy", ADUsername));
cmd.Parameters.Add(new SqlParameter("@FailedReason", DBNull.Value));
rowsUpdated = context.Database.ExecuteSqlCommand(cmd.ToString());
cmd.Dispose();
}
if (rowsUpdated == 0)
return null;
return GetInvoicesForEmail(invoiceIds);
}
I get this error when I try and execute:
System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Source=.Net SqlClient Data Provider
StackTrace:
<Cannot evaluate the exception stack trace>
Here is the extension method for the array.
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace ShopAP.API.Data.Extensions
{
public static class SqlCommandExt
{
/// <summary>
/// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
/// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN ({paramNameRoot}))
/// </summary>
/// <param name="cmd">The SqlCommand object to add parameters to.</param>
/// <param name="paramNameRoot">What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced.</param>
/// <param name="values">The array of strings that need to be added as parameters.</param>
/// <param name="dbType">One of the System.Data.SqlDbType values. If null, determines type based on T.</param>
/// <param name="size">The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value.</param>
public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
{
/* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually.
* Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
* IN statement in the CommandText.
*/
var parameters = new List<SqlParameter>();
var parameterNames = new List<string>();
var paramNbr = 1;
foreach (var value in values)
{
var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
parameterNames.Add(paramName);
SqlParameter p = new SqlParameter(paramName, value);
if (dbType.HasValue)
p.SqlDbType = dbType.Value;
if (size.HasValue)
p.Size = size.Value;
cmd.Parameters.Add(p);
parameters.Add(p);
}
cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(",", parameterNames));
return parameters.ToArray();
}
}
}
Database.ExecuteSqlCommand
takes a string and a list of parameters, not a SqlCommand object.
You can't pass a SqlCommand, so you're trying to .ToString()
the SqlCommand which returns the string:
System.Data.SqlClient.SqlCommand
So this
rowsUpdated = context.Database.ExecuteSqlCommand(cmd.ToString());
Is equivilent to
rowsUpdated = context.Database.ExecuteSqlCommand("System.Data.SqlClient.SqlCommand");
Which is causing the error, because this looks like a 4-part name using a linked server.
It should be something like:
var sql = cmd.CommandText;
var parameters = cmd.Parameters.Cast<SqlParameter>().ToArray();
db.Database.ExecuteSqlCommand(sql, parameters);
User contributions licensed under CC BY-SA 3.0