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.
No comments:
Post a Comment