Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, December 16, 2005 10:56 AM
SqlParameterCollection's constructor is private, so it’s obvious that one cannot create it’s instance with using -
Dim objSqlParameterCollection as New SqlParameterCollection
I get it’s object with SqlCommand’s Parameters() method.
I’m curious about – how SqlCommand creates object of SqlParameterCollection?
Without using SqlCommand class, can I instantiate SqlParameterCollection?
Friday, December 16, 2005 2:58 PM ✅Answered
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.
Friday, December 16, 2005 12:00 PM
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
Friday, December 16, 2005 12:06 PM
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...)
Friday, December 16, 2005 4:10 PM
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");
Wednesday, April 18, 2007 6:56 PM | 1 vote
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.
Saturday, April 21, 2007 1:47 AM
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. .
Friday, December 28, 2007 9:01 PM | 1 vote
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);
Sunday, February 3, 2008 12:24 PM
dats a real smart way to do it
Monday, February 4, 2008 2:14 AM | 1 vote
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?
Wednesday, April 30, 2008 3:24 PM
The SqlParameterCollection should use the modifier known as "internal".
The constructor is public internally, just in the same library, but cannot be accessed by other library, that's how the SqlCommand instantiate the SqlParameterCollection.
And I see no reason to use this.
In OracleClient you can instantiate OracleParameterCollection, set the values, and then set the parameters to the command like this:
comm.Parameters = oracleParameters;
This approach used by Microsoft makes the MVC pattern harder to do.
[]'s
Thursday, August 7, 2008 4:40 PM
I see at least two scenarios where instantiating it would be very useful. 1) A single collection of parameters to use in multiple commands (i.e. different functionality between admin and public users), or 2) in the following example, I'm building a parameterized query on the fly. To create a Command, I first need the commandText, so I would have to check all the search criteria to build the commandText, then create the Connection/Command, then check all the criteria again to add the parameters. And if the user didn't actually enter any criteria, there will be no parameters, and no reason to return anything, so I might create the Command for no reason. Instead, I made my own object so I could build my sql and parameters at the same time, and only create the Connection/Command if I need it...
// SqlParameterData.cs
using System;
using System.Data;
namespace MyDataAccessProject
{
public class SqlParameterData
{
public string Name;
public SqlDbType Type;
public object Value;
public SqlParameterData( string name, SqlDbType type, object value )
{}
}
}
// SearchProvider.cs
using System.Collections.Generic;
using System.Text;
...
StringBuilder commandText = new StringBuilder();
List<SqlParameterData> parameters = new List<SqlParameterData>();
...
// check each search criteria item, building sql and adding params
if( categoryID > 0 )
{
commandText.Append( "CategoryID = @CategoryID" );
parameters.Add( new SqlParameterData( "@CategoryID", SqlDbType.Int, categoryID ) );
}
if( size > 0 )
{
if( commandText.Length > 0 ) commandText.Append( " AND " );
commandText.Append( "Size = @Size" );
parameters.Add( new SqlParameterData( "@Size", SqlDbType.VarChar, size ) );
}
if( parameters.Count > 0 )
{
// now I actually know I need the connection/command
using( SqlConnection connection ... )
{
using( SqlCommand command = new SqlCommand( commandText.ToString(), connection ) )
{
...
// add parameters from my generic list to the command's parameter collection
foreach( SqlParameterData spd in parameters )
{
command.Parameters.Add( spd.Name, spd.Type );
command.Parameters[command.Parameters.Count-1].Value = spd.Value;
}
...
}
}
}
Monday, August 11, 2008 12:56 PM
I din't reda the entire post . But i think instantiating
SqlParameterCollection can be done in this way.
SqlParameterCollection emailParamArray = new SqlCommand().Parameters;
emailParamArray.AddWithValue("Id", 1);
Just in case it helps somebody.
Friday, April 23, 2010 2:23 PM
Hi Dhims,
I tried that it didn't work for me.
I think it's because SqlCommand().Parameters is a read only property so you can't use it to instantiate the collection.
I just settled with using a Generic List <SqlParameter> as mentioned above.
Friday, June 11, 2010 7:00 AM
hi chris
i tried this code but it does not works it gives error
The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameterCollection objects.
please help me
its important for me.
Friday, June 11, 2010 7:01 AM
we can instantiate sqlparameter collection by using
SqlParameterCollection pr = new SqlCommand().Parameters;
Saturday, August 21, 2010 12:24 PM | 4 votes
The other approach you can use which I've used in a similar way involves creating a SqlParameter array offline and when you want to use it with your SqlCommand object just add it, think it can be done using the draft approach below:
public void DoProcess() {
int i = -1;
SqlParameter[] sqlParams = new SqlParameter[2];
//.. populate the sqlParams array
sqlParams[++i] = new SqlParameter("@Param1", "Test");
sqlParams[++i] = new SqlParameter("@Param2", "Test2");
ExecuteCommand("GET_DATA_STORED_PROC", sqlParams);
}
public void ExecuteCommand(string cmdText, SqlParameter[] sqlParams) {
SqlCommand sqlCmd = new SqlCommand(cmdText, _sqlConnectionInstance);
sqlCmd.Parameters.AddRange(sqlParams);
//execute sqlCmd as required... e.g.
sqlCmd.ExecuteNonQuery();
}
Wednesday, September 7, 2011 1:38 PM
This is what I did.
List<SqlParameter> parameters = new List<SqlParameter>();
Thursday, July 12, 2012 4:46 PM
I use a Generic List collection (System.Collection.List) too:
using System.Collections.Generic;...private void ExecuteWithParam(){ List<SqlParameter> param = new List<SqlParameter>(); param.Add(new SqlParameter("cliente", val(lblCodigo.Text))); ExecuteQuery(string.Format("Delete from CRM_ClientesHomologaciones where CH_id = @cliente", CH_id));}private void ExecuteWithoutParam(){ ExecuteQuery(string.Format("Delete from CRM_ClientesHomologaciones where CH_id = {0}", CH_id));}private void ExecuteQuery(string Query){ ExecuteQuery(Query, new List<SqlParameter>());}private void ExecuteQuery(string Query, List<SqlParameter> parameters){ SqlConnection Cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ateneaconnectionstring"].ConnectionString); Cnn.Open(); SqlCommand Cmd = new SqlCommand(Query, Cnn); foreach (SqlParameter param in parameters) Cmd.Parameters.Add(param); int result = Convert.ToInt32(Cmd.ExecuteNonQuery()); Cnn.Close();}
Tuesday, December 4, 2012 7:57 PM
Actually, there is a rather simple way to do this and it is elegant in it's simplicity. The SqlParameterCollection object lives in a SqlCommand object. Bearing that in mind, consider the following:
publicstaticSqlParameterCollectionMakeParameterCollection() {
SqlCommandcmd = newSqlCommand(
returncmd.Parameters; }
There are reasons to do this regardless of the limited imagination of those responding to the contrary. For instance, I have a list of parameters I'd like to pass into a method rather than passing in each one manually. Without condensing the parameters into 1 object, I'd have 20 some parameters to pass in of differing types. As you can imagine, this becomes rather unweildy. If I can pass in the collection object preformed to my liking, then my method signature is quite small, manageable, and readable. Now when I call into my method and pass my parameter collection, I just assign it to my command object and let the command do the rest. This also allows my stored procedure calling method to be generic and appropriate for all callers regardless of what the parameters are. Pass in a parameter collection and a string name representing the stored procedure to call. I think they call this....objec oriented or, specifically, polymorphic.
Monday, August 4, 2014 10:51 AM
Thank you...