locked
Can I pass a SqlParameterCollection object into a SQL Command RRS feed

  • Question

  • User-964398211 posted
    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.
    Monday, December 6, 2004 12:37 PM

All replies

  • User-964398211 posted
    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(); }
    Monday, December 6, 2004 5:30 PM
  • User-2113013486 posted
    You may have to use an ArrayList in this context. Also, have a look at SqlCommandBuilder.DeriveParameters.
    Monday, December 6, 2004 7:50 PM
  • User864395880 posted

    Hi!
    Do in following way, its working fine for me.

    I take 3 parameters, one of it i have pass null value.

    SqlConnection _con = new SqlConnection(DAL_DBConnect.ConnectStringSystem());
                _con.Open();
                SqlCommand cmd = new SqlCommand("SYS_CountryGetDataAlphabatically", _con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameterCollection spc = cmd.Parameters;
                //SqlParameterCollection spc = new SqlCommand().Parameters;
                spc.AddWithValue("@pSearchText", this.CNTCountryNamme);
                spc.AddWithValue("@pIsActive", this.CNTIsActive);
                spc.AddWithValue("@pFromAutoSearch", this.FromAutoSearch);

                if (this.AllowActiveInActive == false)
                    spc[1].Value = DBNull.Value; //spc["@pIsActive"].Value = DBNull.Value;        
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

    You don't have to add spc object by loop to cmd command because its already parameter object of cmd.

    Enjoy...

    Wednesday, April 13, 2011 2:41 AM
  • User486408544 posted

    public static int Insert(string CommandText, SqlParameter[] sqlParameterArray)
    {

    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = CommandText;

    cmd.Parameters.AddRange(sqlParameterArray);

    Thursday, January 21, 2016 11:22 AM