public UserMotorcycleViewModel GetMotorcycleForMainForm(int motorcycleId)
{
UserMotorcycleViewModel model = new UserMotorcycleViewModel();
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
StringBuilder getMotorcycleInfoQuery = new StringBuilder();
getMotorcycleInfoQuery.Append(" select md.id, md.make, md.model ");
getMotorcycleInfoQuery.Append(" from motorcycledata md ");
getMotorcycleInfoQuery.Append($" where md.id = {motorcycleId} ");
command.CommandText = getMotorcycleInfoQuery.ToString();
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
model.MotorcycleId = int.Parse(reader["id"].ToString());
model.Make = reader["make"].ToString();
model.Model = reader["model"].ToString();
}
}
}
catch (Exception ex)
{
logger.LogExceptionText(ex.ToString(), $"Motorcycle ID e:{motorcycleId}");
}
finally
{
connection.Close();
}
return(model);
}
public static void ExecuteNonQuery(string query)
{
try
{
command.CommandText = query;
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
command.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogExceptionText(ex.ToString(), $"ExecuteNonQuery from commant executer problem query:{query}");
}
finally
{
connection.Close();
}
}
public List <string> GetCurrentBikeInsuranceAgensies(int motorcycleId)
{
try
{
StringBuilder selectAsuranceCompaniesQuerySb = new StringBuilder();
selectAsuranceCompaniesQuerySb.Append($"select distinct r.insuranceagency from review r where r.motorcycledata_id = {motorcycleId}");
com.CommandText = selectAsuranceCompaniesQuerySb.ToString();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
List <string> insuranceCompanies = new List <string>();
using (var reader = com.ExecuteReader())
{
while (reader.Read())
{
insuranceCompanies.Add(reader["insuranceagency"].ToString());
}
}
return(insuranceCompanies);
}
catch (Exception ex)
{
logger.LogExceptionText(ex.ToString());
throw ex;
}
finally
{
con.Close();
}
}
public void FillDataGridInformation(string maintenanceType, ref DataGridView dataGrid)
{
try
{
StringBuilder selectInfoQuerySb = new StringBuilder();
selectInfoQuerySb.Append($"select * from {maintenanceType} t ");
if (maintenanceType == GlobalConstants.MantainanceType.OilFilter)
{
selectInfoQuerySb.Append($" where t.motorcycle_id = {GlobalVariables.CurrentBikeId}");
}
selectInfoQuerySb.Append($" where t.motorcycledata_id = 5");
com.CommandText = selectInfoQuerySb.ToString();
if (con.State == ConnectionState.Closed)
{
con.Open();
}
using (var reader = com.ExecuteReader())
{
int gridRow = 0;
while (reader.Read())
{
dataGrid.Rows.Add();
for (int i = 0; i < reader.FieldCount; i++)
{
dataGrid.Rows[gridRow].Cells[i].Value = reader[i].ToString();
}
}
}
}
catch (Exception ex)
{
logger.LogExceptionText(ex.ToString());
}
finally
{
con.Close();
}
}
public LastTaxPayedViewModel GetLastTax(int motorcycleId)
{
LastTaxPayedViewModel lastTaxIdAndContCount = null;
try
{
string query = $"select first 1 t.id ,t.contributioncount, t.payday, t.price from tax t where t.motorcycledata_id = {motorcycleId} order by t.payday desc";
com.CommandText = query;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
using (var reader = com.ExecuteReader())
{
while (reader.Read())
{
int taxId = int.Parse(reader["id"].ToString());
short contCount = short.Parse(reader["contributioncount"].ToString());
DateTime payDate = DateTime.Parse(reader["payday"].ToString());
double price = double.Parse(reader["price"].ToString());
lastTaxIdAndContCount = new LastTaxPayedViewModel
{
ContCount = contCount,
PayDate = payDate,
Price = price,
TaxId = taxId,
};
}
}
}
catch (Exception ex)
{
logger.LogExceptionText(ex.ToString());
}
finally
{
con.Close();
}
return(lastTaxIdAndContCount);
}