locked
Need help using ExecuteReader(storedProcName As String, ParamArray paramValues() as Object) RRS feed

  • Question

  • User-176773395 posted

    This is from the Microsoft enterprise library.  You can see the definition here: http://msdn.microsoft.com/en-us/library/bb748706(BTS.10).aspx

    I'm trying to do something like this to no avail:

    Dim myParams() As Object = {"Val1", "Val2"}
            Dim myReader As SqlDataReader = DatabaseFactory.CreateDatabase().ExecuteReader("usp_Test", myParams)



    It gives me a runtime error saying the number of parameters does not match.  My stored proc takes two varchar(10) parameters.  

    Anyone know what I'm doing wrong?

    Thursday, September 17, 2009 12:56 PM

Answers

  • User-627724879 posted

     I have my DAAB calls abstracted away. Here is an example of how I use them. Note: I utilize the SafeDataReader from the CSLA framework to give me more IDataReader goodness :) :

                SqlParameter[] arParams = new SqlParameter[4];
    
                arParams[0] = new SqlParameter("@lon", vLongitude);
                arParams[1] = new SqlParameter("@lat", vLatitude);
                arParams[2] = new SqlParameter("@Miles", vMiles);
                arParams[3] = new SqlParameter("@AppType", vAppType);
    
                List<LatLong> lLatLongs = new List<LatLong>();
    
                using (SafeDataReader dr = new SafeDataReader(ExecuteReader("spGetThreats", CommandType.StoredProcedure, arParams)))
                {
    
                    while (dr.Read())
                    {
                        lLatLongs.Add(new LatLong(dr.GetDecimal("Longtitude"), dr.GetDecimal("Latitude"),
                            dr.GetString("Name"));
                    }
    
                }
                
                return lLatLongs;
    


     

     

     Here is how I abstracted ExecuteDataReader in my framework:

     

            #region " DataReader "
    
            public SqlDataReader ExecuteReader(string sqlCommand)
            {
                return this.ExecuteReader(sqlCommand, CommandType.StoredProcedure, null);
            }
    
            public SqlDataReader ExecuteReader(string sqlCommand, CommandType ct)
            {
                return this.ExecuteReader(sqlCommand, ct, null);
            }
    
            public SqlDataReader ExecuteReader(string sqlCommand, CommandType ct, string Param, object ParamValue)
            {
                SqlParameter[] arParam = new SqlParameter[] { new SqlParameter(Param, ParamValue) };
                return this.ExecuteReader(sqlCommand, ct, arParam);
            }
    
            public SqlDataReader ExecuteReader(string sProc, CommandType ct, SqlParameter[] arParam)
            {
                Database database = DatabaseFactory.CreateDatabase(ConnectionString);
                DbCommand storedProcCommand = database.GetStoredProcCommand(sProc);
                if (null != arParam)
                {
                    foreach (SqlParameter parameter in arParam)
                    {
                        database.AddInParameter(storedProcCommand, parameter.ParameterName,
                            parameter.DbType, parameter.Value);
                    }
                }
                return (SqlDataReader)database.ExecuteReader(storedProcCommand);
            }
    
            #endregion
    


     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 17, 2009 1:56 PM