internal async Task <IEnumerable <DriverDAL> > SelectByDateSpeed(DateTime dt, float speed)
{
List <DriverDAL> drivers = new List <DriverDAL>();
if (!CheckDBExists())
{
return(drivers);
}
using (var connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _directoryPath + @";Extended Properties=text"))
{
string commandStr = $"SELECT * FROM [{_fileName}] WHERE Speed > @speed AND Date BETWEEN #{dt.ToString("MM/dd/yyyy")} 00:00:00# AND #{dt.ToString("MM/dd/yyyy")} 23:59:59#";
await connection.OpenAsync();
OleDbCommand command = new OleDbCommand(commandStr, connection);
command.Parameters.AddWithValue("@speed", speed).OleDbType = OleDbType.Double;
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var model = new DriverDAL(reader[2].ToString(), DateTime.Parse(reader[0].ToString()), float.Parse(reader[1].ToString()));
drivers.Add(model);
}
}
return(drivers);
}
internal async Task <IEnumerable <DriverDAL> > SelectMinMaxByDate(DateTime dt)
{
List <DriverDAL> drivers = new List <DriverDAL>();
if (!CheckDBExists())
{
return(drivers);
}
using (var connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _directoryPath + @";Extended Properties=text"))
{
string commandStr = $"SELECT * FROM [{_fileName}] WHERE Speed = (Select MIN(Speed) from [{_fileName}] where Date BETWEEN #{dt.ToString("MM/dd/yyyy")} 00:00:00# AND #{dt.ToString("MM/dd/yyyy")} 23:59:59#) OR Speed = (Select MAX(Speed) from [{_fileName}] where Date BETWEEN #{dt.ToString("MM/dd/yyyy")} 00:00:00# AND #{dt.ToString("MM/dd/yyyy")} 23:59:59#)";
await connection.OpenAsync();
OleDbCommand command = new OleDbCommand(commandStr, connection);
var reader = command.ExecuteReader();
while (await reader.ReadAsync())
{
var model = new DriverDAL(reader[2].ToString(), DateTime.Parse(reader[0].ToString()), float.Parse(reader[1].ToString()));
drivers.Add(model);
}
}
return(drivers.OrderBy(x => x.Speed));
}
/// <summary>
/// Deletes Record from Database.
/// </summary>
/// <param name="objDriver">Object containing all data values.</param>
/// <returns>boolean value True if Record is deleted successfully
/// otherwise returns False.</returns>
public static bool Delete(Driver objDriver)
{
bool recDel;
recDel = DriverDAL.Delete(objDriver.DBID);
return(recDel);
}
internal async Task InsertAsync(DriverDAL model)
{
await CheckDBExistsAndCreateAsync();
using (var connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _directoryPath + @";Extended Properties=text"))
{
string commandStr = $"INSERT INTO [{_fileName}] ([Date],[Speed],[Number]) values (@date, @speed, @number)";
await connection.OpenAsync();
OleDbCommand command = new OleDbCommand(commandStr, connection);
command.Parameters.AddWithValue("@date", model.DateTime).OleDbType = OleDbType.Date;
command.Parameters.AddWithValue("@speed", model.Speed).OleDbType = OleDbType.Double;
command.Parameters.AddWithValue("@number", model.CarNumber).OleDbType = OleDbType.LongVarChar;
await command.ExecuteNonQueryAsync();
}
}
private async Task InsertAsync(DriverDAL model)
{
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _path + @"\;Extended Properties=text"))
{
string commandStr = $"INSERT INTO [{_fileName}] ([Date],[Speed],[Number]) values (@date, @speed, @number)";
OleDbCommand comand1 = new OleDbCommand(commandStr, connection);
comand1.Parameters.AddWithValue("@date", model.DateTime);
comand1.Parameters.AddWithValue("@speed", model.Speed);
comand1.Parameters.AddWithValue("@number", model.CarNumber);
await connection.OpenAsync();
await comand1.ExecuteNonQueryAsync();
connection.Close();
}
}
private async Task <IEnumerable <DriverDAL> > SelectMinMaxByDate(string path, string fileName)
{
List <DriverDAL> drivers = new List <DriverDAL>();
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=text"))
{
string commandStr = $"SELECT * FROM [{fileName}] WHERE Speed = (Select MIN(Speed) from [{fileName}] ) OR Speed = (Select MAX(Speed) from [{fileName}] )";
OleDbCommand command = new OleDbCommand(commandStr, connection);
await connection.OpenAsync();
var reader = command.ExecuteReader();
while (await reader.ReadAsync())
{
var model = new DriverDAL(reader[2].ToString(), DateTime.Parse(reader[0].ToString()), float.Parse(reader[1].ToString()));
drivers.Add(model);
}
connection.Close();
}
return(drivers);
}
/// <summary>
/// Saves Current Object Values into Database.
/// </summary>
/// <param name="objDriver">Object containing data values.</param>
/// <returns>Boolean value True if record is saved successfully
/// otherwise returns False indicating record is not saved.</returns>
public static bool Save(Driver objDriver, User objUser)
{
bool flgSave;
try
{
using (TransactionScope objTScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
if (objDriver.IsEdited || objDriver.IsNew)
{
DriverDAL.Save(objDriver, objUser);
}
flgSave = true;
objTScope.Complete();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(flgSave);
}
public static string Driver(string id)
{
return(new JavaScriptSerializer().Serialize(DriverDAL.Driver(id)));
}
public DriverBL()
{
dal = new DriverDAL();
}
public DriverBO()
{
driverDAL = new DriverDAL();
}
public async Task Add(DriverDAL model)
{
await _db.InsertAsync(model);
}
public async Task Add(DriverDAL model)
{
await CheckCurrentStorageExistsAsync();
await InsertAsync(model);
}
/// <summary>
/// Checks whether current Record already exists or not.
/// </summary>
/// <param name="objDriver">Object containing all Data Values.</param>
/// <returns>boolean value True if Record already Exists into Database
/// otherwise returns False.</returns>
public static bool IsDriverExist(Driver objDriver)
{
return(DriverDAL.IsDriverExist(objDriver));
}
/// <summary>
///
/// </summary>
/// <param name="dbid"></param>
/// <returns></returns>
public static Driver GetItem(int dbid)
{
return(DriverDAL.GetItem(dbid));
}
/// <summary>
///
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public static DriverList GetList(string strWhere)
{
return(DriverDAL.GetList(strWhere));
}
