How to assign value in loop to parameters of stored procedure using SqlDataClient

Question

I attempted to passing value from object params array to each parameter of stored procedure using loop, but I get this error

'Procedure or function Proc_GetPaging_Product has too many arguments specified.'.

The problem is cmd.Parameters already has parameters when using SqlCommandBuilder.DeriveParameters(cmd) so no need to add value but assigning.

    public ServiceResult<T> GetPaging<T>(params object[] values)
    {
        var result = new ServiceResult<T>();

        using(_conn)
        {
            _conn.Open();                

            using (SqlCommand cmd = _conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "[SalesLT].[Proc_GetPaging_Product]";
                SqlCommandBuilder.DeriveParameters(cmd);

                int lengthParams = cmd.Parameters.Count;

                for (int i = 1; i < lengthParams; i++)
                {
                    cmd.Parameters.AddWithValue(cmd.Parameters[i].ParameterName, values[i - 1]);
                }                                             

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = Activator.CreateInstance<T>();
                        var properties = typeof(T).GetProperties();

                        foreach (var property in properties)
                        {                               
                            //Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
                            property.SetValue(item, reader[property.Name], null);
                        }

                        result.ListResult.Add(item);
                    }

                    reader.NextResult();
                }
            }
        }

        return result;
    }

Error I got in debugger

SqlCommandBuilder.DeriveParameters(cmd) adds parameters to command based on the definition of stored proc. And cmd.Parameters.AddWithValue adds new parameter. So basically you are trying to add the same parameters again to the command. — Chetan
– Chetan, Commented Oct 27, 2019 at 6:01

Zohar Peled · Accepted Answer · 2019-10-27 05:36:53Z

4

You're adding the parameters twice.

Once in this row:

SqlCommandBuilder.DeriveParameters(cmd);

and once for each parameter in the loop:

for (int i = 1; i < lengthParams; i++)
{
    cmd.Parameters.AddWithValue(cmd.Parameters[i].ParameterName, values[i - 1]);
}

Instead of using AddWithValue, simply do cmd.Parameter[i].Value = values[i].

Also, the parameters collection, like any other built-in collection in .Net, is zero based and not one based.

You loop should look like this:

for (int i = 0; i < lengthParams; i++)
{
    cmd.Parameters[i].Value = values[i];
}

Also, I you should add a condition before the loop to make sure the number of values you have match the number of parameters the stored procedure have.

And one more note: you should consider setting the parameters in the code instead of using DeriveParameters: Official documentation clearly states:

DeriveParameters requires an additional call to the database to obtain the information. If the parameter information is known in advance, it is more efficient to populate the parameters collection by setting the information explicitly.

answered Oct 27, 2019 at 5:36

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

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

2 Comments

Lê Bảo Cường Over a year ago

I modified my code follow your answer then it work, Thanks for your suggestion.

Zohar Peled Over a year ago

Glad to help :-)

Collectives™ on Stack Overflow

How to assign value in loop to parameters of stored procedure using SqlDataClient

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related