Can I pass a SqlParameterCollection object into a SQL Command

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();
}

Can you do this? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.In case anyone runs into the same issue I did, I am posting my solution to this problem. From what I have read, you can not create(instantiate) a new SqlParameterCollection class on a page. If anyone needs to see the solution to return a dataset, just reply to this post, and I will post the dataset results as well. The workaround that I used to solve this problem involves using an Arraylist and passing the arraylist to the connection class:

clsConn conn = new clsConn();
string sql = null;

conn.strConn = connectionstring;

sql = "sqlstring";

SqlParameterCollection newcollect = null;

//newcollect.Add("@.Switch",1); <-- Different
param =new SqlParameter("@.Parameter", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "Switch", DataRowVersion.Current, ParameterValue); <-- Different
conn.SQLParamCollection.Add(param); <-- Different

conn.OpenReader(sql);

while (conn.DR.Read())

{

read data onto page here...

}

conn.CloseReader();

Page 2 (connection class) :

private SqlConnection Conn; // provides connection for server
private SqlCommand Command; // provides sql command object
private SqlDataAdapter da;
private DataSet ds;
public SqlDataReader DR; // provides data reader for sql
public System.Collections.ArrayList SQLParamCollection = new System.Collections.ArrayList();
public string strConn = null;

public void OpenReader(string ProcedueName)//, System.Collections.ArrayList param)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(ProcedueName,Conn);
Command.CommandType = CommandType.StoredProcedure;

if(SQLParamCollection.Count > 0)
{
Different-->for(int i=0; i < SQLParamCollection.Count; i++)
{
Different-->Command.Parameters.Add(SQLParamCollection[i]);
}
}

Command.CommandTimeout = 300;

// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}|||You may have to use an ArrayList in this context.

Also, have a look at SqlCommandBuilder.DeriveParameters.

Can I make this code better using SQLParamater object?

I wrote a class to handle my sqlDataReaders:

Namespace CommonFunctions

PublicClass DataAccess

PublicSubNew()

EndSub

Private ConnStrAsString ="connectionString"

PublicFunction returnDR(ByVal strSQLAsString)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)

Dim myCmdAsNew SqlClient.SqlCommand(strSQL, myCn)

myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

EndClass' Public Class DataAccess

EndNamespace' Namespace CommonFunction

The way I invoke this is:

Imports CommonFunctions

Dim daAsNew DataAccess

Dim sqlDataReaderAs SqlDataReader

Dim strSQLAsNew StringBuilder

strSQL.Append("Some SQL Query")

sqlDataReader = da.returnDR(strSQL.ToString())

DoWhile sqlDataReader.Read()

strVar = sqlDataReader("columnName")

Loop' Do While sqlDataReader.Read()

sqlDataReader.Close()

What I would like to do, is use the SQL Paramater Object instead of passing my method a String. One so that I dont have to explicitly test for SQL Injection and two, cause I have never worked with the SQL Paramater Object before. :)

J

Sure, put this in your class:

PublicFunction returnDR(ByVal cmd AS SqlCommand)As SqlClient.SqlDataReader

Dim drAs SqlClient.SqlDataReader

Dim myCnAsNew SqlClient.SqlConnection(ConnStr)
mycmd.connection=myCn
myCn.Open()

dr = myCmd.ExecuteReader(CommandBehavior.CloseConnection)

Return dr

EndFunction' returnDR

Then you can pass returnDR either a SQLString, or a SqlCommand object.

like this:

dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE ID=@.ID")
cmd.parameters.add("@.ID",sqdbtype.int).value={Some ID}
sqlDataReader = da.returnDR(cmd)

and of course:
sqlDataReader=da.returnDR("SELECT * FROM MyTable WHERE ID=" & {Some ID})

will continue to work as well. That way you can move forward with using SqlCommand's for new queries, and covert the old queries when you have time.

You can then also change your old function to:

PublicFunction returnDR(ByVal sqlcmd AS String)As SqlClient.SqlDataReader
dim cmd as new sqlCommand(sqlcmd)
return returnDR(cmd)
End Function