Can I pass a SqlParameterCollection object into a SQL Command

Question

I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:

Page 1: 

    string sql = null;
conn.strConn = connectionstring;

sql = "sqlstring";

SqlParameterCollection newcollect = null;
newcollect.Add("@Switch",1);

conn.OpenReader(sql, newcollect);
while (conn.DR.Read())
{
        read data onto page here...
}
conn.CloseReader();

Page 2 (connection class) :

public void OpenReader(string sql, SqlParameterCollection collect)
{
        Conn = new SqlConnection(strConn);
        Conn.Open();
        Command = new SqlCommand(sql,Conn);

        Command.Parameters.Add(collect);  <------This is the root of my question
        Command.CommandTimeout = 300;
        // executes sql and fills data reader with data
        DR = Command.ExecuteReader(); 
}

Also, before adding items to your collection, you need to initialize it! This doesn't work: SqlParameterCollection newcollect = null - you'll need a SqlParameterCollection newcollect = new SqlParameterCollection(); somewhere in your code, before adding any items... — marc_s
– marc_s, Commented Jun 12, 2011 at 7:35

marc_s · Accepted Answer · 2011-06-12 08:34:12Z

4

Basically, in ASP.NET, to persist something from page to page, you need to use session state and put your object there - so you could try something like this:

Page 1:

List<SqlParameter> newcollect = new List<SqlParameter>();
newcollect.Add(new SqlParameter("@Switch", 1));

Session["SqlParameters"] = newcollect;

Page 2 (connection class) :

public void OpenReader(string sql)
{
    Conn = new SqlConnection(strConn);
    Conn.Open();

    Command = new SqlCommand(sql,Conn);

    List<SqlParameter> coll = null;
    if(Session["SqlParameters"] != null)
    {
       coll = (List<SqlParameter>)Session["SqlParameters"];
    }

    Command.Parameters.AddRange(coll.ToArray());
    Command.CommandTimeout = 300;
    // executes sql and fills data reader with data
    DR = Command.ExecuteReader(); 
}

This will work - if you have Session state enabled on your ASP.NET site. This will not work if your site cannot use session state memory for some reason (like if you're on a webfarm and cannot use SQL Server for session state)

edited Jun 12, 2011 at 8:34

answered Jun 12, 2011 at 7:38

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

3 Comments

mmk_open Over a year ago

The statement "SqlParameterCollection newcollect = new SqlParameterCollection();" gives the following error: - Error 1 The type 'System.Data.SqlClient.SqlParameterCollection' has no constructors defined

marc_s Over a year ago

@Muhammed Rauf K: OK, you need to use List<SqlParameter> instead - too bad MS chose to make this collection class not instantiable....

mmk_open Over a year ago

Yes. I used like.. List<SqlParameter> spParamList = new List<SqlParameter>();

Zsolt Botykai · Accepted Answer · 2012-03-04 14:52:59Z

-2

You can instantiate a new SqlParameterCollection by like that:

var P = new SqlCommand().Parameters;

edited Mar 4, 2012 at 14:52

Zsolt Botykai

52k14 gold badges90 silver badges111 bronze badges

answered Mar 4, 2012 at 11:15

Marcin Pluta

1

Collectives™ on Stack Overflow

Can I pass a SqlParameterCollection object into a SQL Command

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related