public Dictionary <string, string> GetWorkCollectionSchedule(VRTNWorkCollectionObject vrtnworkcollectionobject, TRTNWorkCollectionScheduleObject trtnworkcollectionscheduleobject) { Dictionary <string, string> returnedValues = new Dictionary <string, string>(); if (trtnworkcollectionscheduleobject.ri_id < 1 && String.IsNullOrEmpty(vrtnworkcollectionobject.frequency)) { throw new ArgumentException("Return institution ID and frequency is required"); } else { var comamndText = @"SELECT schedule_id, ri_id, work_collection_id, work_collection_date FROM t_rtn_work_collection_schedule WHERE work_collection_id = (SELECT DISTINCT a.work_collection_id/*, e.ri_type_id,e.frequency*/ FROM t_rtn_returns_work_collection_mapping a JOIN (SELECT DISTINCT return_code FROM t_rpt_computation_rulemakeup UNION SELECT DISTINCT c.return_code FROM t_rpt_computation_rulemakeup a JOIN t_rpt_computation_rulemakeup_formula c ON a.ruleBase_ID = c.ruleBase_ID) d ON a.return_code = d.return_code JOIN v_rtn_work_collection e ON e.work_collection_id = a.work_collection_id WHERE ri_type_id = @ritypeid AND frequency = @frequency) AND is_valid = @isvalid AND ri_id = @riid AND work_collection_date = @wcdate"; using (SqlCommand command = new SqlCommand(comamndText, DatabaseOps.OpenSqlConnection())) { command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@ritypeid", vrtnworkcollectionobject.ri_type_id); command.Parameters.AddWithValue("@frequency", vrtnworkcollectionobject.frequency); command.Parameters.AddWithValue("@isvalid", trtnworkcollectionscheduleobject.is_valid); command.Parameters.AddWithValue("@riid", trtnworkcollectionscheduleobject.ri_id); command.Parameters.Add("@wcdate", SqlDbType.DateTime).SqlValue = trtnworkcollectionscheduleobject.work_collection_date; _resultTable = _databaseOperations.GetDataTable(command); if (_resultTable.Rows.Count > 0) { for (int j = 0; j < _resultTable.Rows.Count; j++) { returnedValues.Add("schedule_id", _resultTable.Rows[j]["schedule_id"].ToString()); returnedValues.Add("ri_id", _resultTable.Rows[j]["ri_id"].ToString()); returnedValues.Add("work_collection_id", _resultTable.Rows[j]["work_collection_id"].ToString()); returnedValues.Add("work_collection_date", _resultTable.Rows[j]["work_collection_date"].ToString()); } } else { returnedValues = null; } return(returnedValues); } } }
//SqlDataReader _reader; public TRTNWorkCollectionScheduleHandler() { _databaseOperations = new DatabaseOps(); _resultTable = new DataTable(); _workCollectionSheduler = new TRTNWorkCollectionScheduleObject(); }