Pass Array Parameter in SqlCommand with additional parameters

Question

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();
        }
    }
}

Sounds like a possible ConnectionString issue. Have you verified your connection string is valid? — critical_error
– critical_error, Commented Sep 22, 2020 at 18:19
Same connection string I use for several other methods and they all work. cmd.ToString() gives me a valid looking query string. — Connie DeCinko
– Connie DeCinko, Commented Sep 22, 2020 at 18:28

David Browne - Microsoft · Accepted Answer · 2020-09-22 19:14:14Z

2

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);

answered Sep 22, 2020 at 19:14

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass Array Parameter in SqlCommand with additional parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related