/// <summary> /// Get List of all budgetMeters to invoice /// </summary> /// <param name="ErrorMessage">String: Errormessage when occurred</param> /// <returns>List of BudgetMeters</returns> public List <BudgetMeter> GetBudgetMeterData(out string ErrorMessage) { //Variables List <BudgetMeter> _BudgetMeterList = new List <BudgetMeter>(); SqlConnection _SQLConnection = null; SqlCommand _SqlCommand = null; SqlDataAdapter _SqlDataAdapter = null; DataSet _BudgetMeterDataSet = new DataSet(); string _Query = ""; ErrorMessage = ""; //Get data _Query = " SELECT ID,ContractNo,AccountNo,ProcessingStatus,ErrorMessage,EAN,AmountExcl,PrepaymentReference,StatementReference,AmountIncl,Booking,GridOwnerName,VATPercentage FROM [BudgetMeter].[vw_SourceData]"; try { _SQLConnection = GetConnection(); //Fix KVH: Central use of config parameter readings _SqlCommand = new SqlCommand(_Query, _SQLConnection); _SqlDataAdapter = new SqlDataAdapter(_SqlCommand); _SqlDataAdapter.Fill(_BudgetMeterDataSet); foreach (DataRow _row in _BudgetMeterDataSet.Tables[0].Rows) { BudgetMeter _Budget = new BudgetMeter(_row["ID"].ToString(), _row["ContractNo"].ToString(), _row["AccountNo"].ToString(), _row["ProcessingStatus"].ToString(), _row["ErrorMessage"].ToString(), _row["EAN"].ToString(), _row["PrepaymentReference"].ToString(), _row["StatementReference"].ToString(), Convert.ToDecimal(_row["AmountExcl"]), Convert.ToDecimal(_row["AmountIncl"]), Convert.ToBoolean(_row["Booking"]), _row["GridOwnerName"].ToString(), Convert.ToInt32(_row["VATPercentage"])); _BudgetMeterList.Add(_Budget); } } catch (SqlException ex) { ErrorMessage = "Error Getting BugetMeter Data \n"; //ErrorMessage += ex.ToString(); } catch (InvalidOperationException ex) { ErrorMessage = "Error Getting BugetMeter Data \n"; //ErrorMessage += ex.ToString(); } finally { if (_SqlDataAdapter != null) { _SqlDataAdapter.Dispose(); } if (_SqlCommand != null) { _SqlCommand.Dispose(); } if (_BudgetMeterDataSet != null) { _BudgetMeterDataSet.Dispose(); } if (_SQLConnection.State == ConnectionState.Open) { _SQLConnection.Close(); } } return(_BudgetMeterList); }
/// <summary> /// Update the specified budgetmeter /// </summary> /// <param name="budgetMeter">BudgetMeter: to update</param> /// <param name="errorMessage">String: Errormessage when occured</param> public void UpdateBudgetMeterData(BudgetMeter budgetMeter, out string errorMessage) { errorMessage = ""; int _errorNumber = 0, _actionType = 0; string _invoiceSalesNo = ""; SqlConnection _connection = null; SqlCommand _command = null; try { _connection = GetConnection(); //Fix KVH: Central use of config parameter readings //_SQLConnection.Open(); // Fix KVH: Open connection as late as possible! if (String.IsNullOrEmpty(budgetMeter.StatementReference) && budgetMeter.Booking) { _actionType = 3; } else if (String.IsNullOrEmpty(budgetMeter.StatementReference) && !budgetMeter.Booking) { _actionType = 1; _invoiceSalesNo = budgetMeter.PrepaymentReference; } else if (String.IsNullOrEmpty(budgetMeter.StatementReference)) { _actionType = 2; _invoiceSalesNo = budgetMeter.StatementReference; } //Fix Koen: code length reduction by using private method GetSQLParameter and the use of the Parameters.Add method _command = new SqlCommand("[BudgetMeter].[USP_UpdateSourceData]", _connection); _command.CommandTimeout = 120; _command.CommandType = System.Data.CommandType.StoredProcedure; _command.Parameters.Add(GetSQLParameter("@AccountNo", SqlDbType.VarChar, budgetMeter.AccountNo)); _command.Parameters.Add(GetSQLParameter("@ContractNo", SqlDbType.VarChar, budgetMeter.ContractNo)); _command.Parameters.Add(GetSQLParameter("@InvoiceNo", SqlDbType.VarChar, _invoiceSalesNo)); _command.Parameters.Add(GetSQLParameter("@Type", SqlDbType.Int, _actionType)); _command.Parameters.Add(GetSQLParameter("@Booking", SqlDbType.Bit, budgetMeter.Booking)); _command.Parameters.Add(GetSQLParameter("@ErrorMessage", SqlDbType.VarChar, 250, errorMessage, ParameterDirection.Output)); _command.Parameters.Add(GetSQLParameter("@ErrorNumber", SqlDbType.Int, null, System.Data.ParameterDirection.ReturnValue)); _connection.Open(); _command.ExecuteNonQuery(); _errorNumber = (int)_command.Parameters["@ErrorNumber"].Value; if (_errorNumber != 0) { errorMessage = _command.Parameters["@ErrorMessage"].Value.ToString(); } } catch (SqlException ex) { errorMessage = "Error Update BugetMeter Data \n"; errorMessage += ex.ToString(); } catch (InvalidOperationException ex) { errorMessage = "Error Update BudgetMeter Data\n"; errorMessage += ex.ToString(); } finally { if (_command != null) { _command.Dispose(); } if (_connection.State == ConnectionState.Open) { _connection.Close(); } } }