9

How do you copy DbCommand parameters to another DbCommand, I want a new DbCommand with the same parameters as my last DbCommand. But now with a different sql string.

Irshad
  • 3,071
  • 5
  • 30
  • 51
Aivan Monceller
  • 4,636
  • 10
  • 42
  • 69

6 Answers6

4
// Copy parameters from cmd1 to cmd2
// Creates an array with new parameters
var nsp = cmd1.Parameters.Cast<ICloneable>().Select(x => x.Clone() as SqlParameter).Where(x => x != null).ToArray();
// Copy parameters into another command
cmd2.Parameters.AddRange(nsp);
Alexey
  • 1,826
  • 3
  • 17
  • 20
3

You could put the code you need to re-use in a separate method:

public DbCommand RecycledParameters(string sql, IList<DbParameter> parameters)
{
    var result = db.GetSqlStringCommand(sql);
    foreach(DbParameter p in parameters)
    {  
        db.AddInParameter(result, p.ParameterName, p.DbType, p.Value);
    }
    return result;
}
Kevin Stricker
  • 17,178
  • 5
  • 45
  • 71
  • is there any other way. If I would do this I would have to create a function for every recycled parameters I have. – Aivan Monceller Jan 24 '11 at 05:07
  • how are able to create a new instance of DBCommand? The method prototype should have been public DBCommand AddParameters(DBCommand dbCommand, DBParameterCollection parameters); now the caller could pass any type that derives from DBCommand for the first param and for the second param type that derives from DBParameterCollection. ex: AddParams(sqlCommand, sqlParameterCollection) – Vijay Sirigiri Jan 24 '11 at 05:32
  • True, that would be more generic. Thanks. – Kevin Stricker Jan 24 '11 at 05:40
2

could you do something like this?

  System.Data.Common.DbCommand command = new System.Data.SqlClient.SqlCommand();
  System.Data.Common.DbCommand command1 = new System.Data.SqlClient.SqlCommand();

  command1.Parameters.AddRange(command.Parameters.Cast<System.Data.Common.DbParameter>().ToArray());
Divi
  • 7,621
  • 13
  • 47
  • 63
  • 3
    This won't work because you can't add parameters from one command to another, `Add` will recognize it and throw an `ArgumentException`. https://msdn.microsoft.com/en-us/library/ht4eset1(v=vs.110).aspx The `Cast`+`ToArray` will change the collection not the parameters, they are still the same instances. – Tim Schmelter May 23 '17 at 11:15
  • 1
    This will produce exception: The SqlParameter is already contained by another SqlParameterCollection. – llessurt Jul 01 '19 at 17:56
1

If all you are after is the parms collection, you could try a helper method that creates a deep copy of the .parameters collection on your command. See if this will spit out what your looking for.

I can't take credit for the ObjectCopier method, it's just a useful base class method I got from a past project. 

    private DbParameterCollection cloneParms(DbCommand commandWithParms)
    {
        return ObjectCopier.Clone<DbParameterCollection>(commandWithParms.Parameters);
    }

    public static class ObjectCopier
    {
        /// <summary>
        /// Perform a deep Copy of the object.
        /// </summary>
        /// <typeparam name="T">The type of object being copied.</typeparam>
        /// <param name="source">The object instance to copy.</param>
        /// <returns>The copied object.</returns>
        public static T Clone<T>(T source)
        {
            if (!typeof(T).IsSerializable)
            {
                throw new ArgumentException("The type must be serializable.", "source");
            }

            // Don't serialize a null object, simply return the default for that object
            if (Object.ReferenceEquals(source, null))
            {
                return default(T);
            }

            IFormatter formatter = new BinaryFormatter();
            Stream stream = new MemoryStream();
            using (stream)
            {
                formatter.Serialize(stream, source);
                stream.Seek(0, SeekOrigin.Begin);
                return (T)formatter.Deserialize(stream);
            }
        }
    }
Tj Kellie
  • 6,336
  • 2
  • 31
  • 40
0

A simple way in vb.net

p is an incoming sqlparameter:

Dim p1 As SqlClient.SqlParameter = CType(CType(p, ICloneable).Clone, SqlClient.SqlParameter)
user2728841
  • 1,333
  • 17
  • 32
0
private List<Tuple<string, SqlDbType, string>> where_param;
public IEnumerable<SqlParameter> RecycledParameters(){
  foreach(Tuple<string, SqlDbType, string> tuple in where_param) {
    SqlParameter local_arg = new SqlParameter(tuple.Item1, tuple.Item2);
    local_arg.Value = tuple.Item3;
    yield return local_arg;
  }
}
nedrobme
  • 11
  • 2