/// <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();
}
}
}
