///-------------------------------------------------------------------------------- /// <summary>This method sets up a stored procedure command, loaded with values from /// the input data access object where appropriate.</summary> /// /// <param name="sprocName">Name of the stored procedure.</param> /// <param name="sprocParams">The input stored procedure parameter values.</param> /// /// <returns>A DbCommand for the stored procedure.</returns> ///-------------------------------------------------------------------------------- public DbCommand SetupSprocCommand(string sprocName, NameObjectCollection sprocParams) { GenericMethod sprocMethod = null; DbCommand command = null; try { // get sproc method from cache or db sprocMethod = DbProcs.MethodList.Find("MethodName", sprocName); if (sprocMethod == null) { // get sproc from db an add to cache sprocMethod = GetSprocFromDB(sprocName, true); } // set up the base command information command = _database.GetStoredProcCommand(sprocName); command.Connection = Connection; command.CommandTimeout = DBOptions.CommandTimeout; // load the sproc parameters with input data if found foreach (GenericParameter loopParameter in sprocMethod.MethodParameterList) { bool foundValue = false; if (sprocParams[loopParameter.ParameterName] != null && sprocParams[loopParameter.ParameterName].GetString() != String.Empty) { foundValue = true; } if (foundValue == false && loopParameter.IsNullable == false && loopParameter.ParameterTypeCode == 1) { // required parameter not found, throw exception throw new System.Exception("MoPlus.Data.BuildSprocParamsWithInputValues - required input value parameter " + loopParameter.ParameterName + " for stored procedure " + sprocName + " not found."); } _database.AddParameter(command, loopParameter.ParameterName, SqlHelper.GetDbTypeFromSqlDataType(loopParameter.DataTypeCode), loopParameter.Size, SqlHelper.GetParameterDirectionFromColumnType(loopParameter.ParameterTypeCode), loopParameter.IsNullable, (byte)loopParameter.Precision, (byte)4, loopParameter.ParameterName, DataRowVersion.Default, sprocParams[loopParameter.ParameterName]); } } catch (Exception ex) { bool reThrow = ExceptionHandler.HandleException(ex); if (reThrow) { throw; } } return(command); }
///-------------------------------------------------------------------------------- /// <summary>This method gets a sproc from the database and loads it into a generic /// method. The results can be added to the stored procedure cache, if specified.</summary> /// /// <param name="sprocName">Name of the stored procedure to get.</param> /// <param name="addSprocToCache">Flag indicating whether to add sproc method to cache.</param> /// /// <returns>A stored procedure from the db as a GenericMethod.</returns> ///-------------------------------------------------------------------------------- public GenericMethod GetSprocFromDB(string sprocName, bool addSprocToCache) { GenericMethod sprocMethod = null; try { // go to the database to retrieve the sproc DbCommand command = _database.GetStoredProcCommand("sp_sproc_columns"); string sprocShortName = sprocName.Replace("[", "").Replace("]", ""); if (sprocShortName.IndexOf('.') > 0) { sprocShortName = sprocShortName.Substring(sprocShortName.IndexOf('.') + 1); } _database.AddInParameter(command, "@procedure_name", DbType.String, sprocShortName); command.Connection = Connection; command.CommandTimeout = DBOptions.CommandTimeout; IDataReader dbSproc = _database.ExecuteReader(command); bool foundRows = false; // add sproc parameters to method while (dbSproc.Read()) { if (foundRows == false) { // create the method with sproc name sprocMethod = new GenericMethod(); sprocMethod.MethodName = sprocName; sprocMethod.MethodParameterList = new SortableDataObjectList <GenericParameter>(); foundRows = true; } GenericParameter sprocParameter = new GenericParameter(); sprocParameter.ParameterName = dbSproc["COLUMN_NAME"].GetString(); if (sprocParameter.ParameterName.StartsWith("@") == true) { sprocParameter.ParameterName = sprocParameter.ParameterName.Substring(1); } sprocParameter.ParameterTypeCode = dbSproc["COLUMN_TYPE"].GetInt(); sprocParameter.DataTypeCode = dbSproc["DATA_TYPE"].GetInt(); sprocParameter.DataTypeName = dbSproc["TYPE_NAME"].GetString(); sprocParameter.Precision = dbSproc["PRECISION"].GetInt(); sprocParameter.Size = dbSproc["LENGTH"].GetInt(); sprocParameter.IsNullable = dbSproc["NULLABLE"].GetBool(); sprocParameter.Order = dbSproc["ORDINAL_POSITION"].GetInt(); sprocMethod.MethodParameterList.Add(sprocParameter); } dbSproc.Close(); if (foundRows == true) { if (addSprocToCache == true) { // add sproc to the cache DbProcs.MethodList.Add(sprocMethod); } } else { // sproc not found, throw exception throw new System.Exception("MoPlus.Data.BuildSprocParamsWithInputValues - stored procedure" + sprocName + " not found in database."); } } catch (Exception ex) { bool reThrow = ExceptionHandler.HandleException(ex); if (reThrow) { throw; } } return(sprocMethod); }