Instantiating SqlParameterCollection

Instantiating SqlParameterCollection

• KellyJohnson

  Chris, it's actually quite possible to do what you suggested - just create a List<SqlCommandParameter> and pass it. What you can't do (and I bet this is what you actually meant) is something like this:

  Code Snippet

  int ExecSProc(string name, SqlParameterCollection p)

  {

  SqlCommand cmd = new SqlCommand(MyConnection, name);

  cmd.Parameters = p;

  return cmd.ExecuteNonQuery();

  }

  I'm trying to think of what sorts of things would justify hiding the constructor. (Not just hiding the constructor; making the Parameters property readonly.) I wonder if some kind of badness occurs if the Parameters property of two SqlCommands points to the same object

  
  

• Sam Kong

  Here's a way to do it using Reflection:

  SqlParameterCollection collection = (SqlParameterCollection) typeof(SqlParameterCollection).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance, null, Type.EmptyTypes, null).Invoke(null);

  
  

• CSharpIsBFlat

  The constructor for SqlParameterCollection is not marked as private, it is marked as internal (Friend in VB.NET). The Parameters property initializes it using the internal constructor. The only other piece of code that calls the consturctor is the private implementation of the IDbCommand interface's Parameters property. So to answer your question, you cannot create an instance of a SqlParameterCollection because your code is not in the System.Data assembly.

  
  

• SCISYS DBA

  I'm curious about how SQLCommand would be instantiating SqlParameterCollection internally...

  There has to be some way to create an object of SqlParameterCollection (since SQLCommand can create object of it...)

  
  

• Santosh Ransubhe

  The Parameters property is readonly; therefore, there's no need to instantiate your own collection. Rather, you work with the Parameters propertry directly.

  sqlCmd.Parameters.AddWithValue("@Col1", "Value");

  
  
  

• Craig D

  Just an FYI, the property of SqlCommand.Parameters is implemented this way:

  get {

  if (internalParameters == null) {

  internalParameters = new SqlParameterCollection();

  }

  return internalParameters;

  }

  So we help you instantiate it first time you call it. .

  
  
  

• JConklin

  I agree. There should be a public constructor for SqlParameterCollection. It's easy to think of a use. If you could do the following, I would, but it is impossible:
  
  {
  SqlParameterCollection Parameters = new SqlParameterCollection();
  Parameters.Add(...);
  Parameters.Add(...);
  ...
  return ExecProc(..., Parameters);
  }
  
  bool ExecSproc(string SprocName, SqlParameterCollection Parameters)
  {
  ...
  SqlCommand cmd = new SqlCommand("EXEC " + SprocName);
  foreach( SqlParameter Parameter in Parameters )
  cmd.Parameters.Add(Parameter);
  ...
  }
  
  Sure, I could just pass an array of SqlParameter objects to ExecSproc instead, but to me, the approach of creating a collection object, adding parameters to it, and then passing the collection is logically more object-oriented.
  
  
  
  

• Casper Jensen

  dats a real smart way to do it
  
  
  

• mattpic

  There is a property parameters in SqlCommand class which is an instance of SqlParameterCollection class, When you create an instance of SqlCommand class, the parameters property will be instantiated. So you simply do the following to add the parameter

  //assume there is a SqlCommand object called comm

  comm.Parameters.Add("@param", SqlDbType.NVarChar, 16);

  i am not sure whether there is a way to instantiate SqlParameterCollection without using SqlCommand, however, i don't see the point of doing it ... since SqlParameterCollection will only be useful when you use it with SqlCommand.

  hope it helps,

  Ivan Wong

  
  
• Instantiating SqlParameterCollection