public string GetDatabaseVersion()
{
var mySQLString = "Select mdbVersion From tblCaptions";
if (GetConnection(out _myAccessConn)) return null;
try
{
using (var myAccessCommand = new OleDbCommand(mySQLString, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
_mdbVersion = dr.GetString(dr.GetOrdinal("mdbVersion"));
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
_myAccessConn.Close();
}
return _mdbVersion;
}
private void LoadStorageUnits()
{
try
{
using (var myAccessCommand = new OleDbCommand(MMSQLGetStorageUnits, _myAccessConn))
{
// _myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
var myStorageUnitDTO = new StorageUnitDTO
{
AccountID = dr.GetInt32(dr.GetOrdinal(("CustomerID"))),
FacilityName = dr.GetString(dr.GetOrdinal("FacilityName")),
BuildingNumber = dr.GetString(dr.GetOrdinal("BldgNumber")),
UnitNumber = dr.GetString(dr.GetOrdinal("UnitNumber")),
UnitRate = dr.GetDecimal(dr.GetOrdinal("UnitRate")),
UnitStatus = dr.GetString(dr.GetOrdinal("UnitStatus")),
UnitSize = dr.GetString(dr.GetOrdinal("UnitSize"))
};
mystorageUnitDTOs.Add(myStorageUnitDTO);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
}
}
public override List<AccountDTO> GetAccounts()
{
if (myData.GetConnection(out _myAccessConn)) return null;
try
{
using (var myAccessCommand = new OleDbCommand(MMSQLGetAccounts, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
var myaccountDTO = new AccountDTO
{
AccountID = dr.GetInt32(dr.GetOrdinal("CustomerID")),
Line1 = dr.GetString(dr.GetOrdinal("Line1")),
Line2 = dr.GetString(dr.GetOrdinal("Line2")),
Line3 = dr.GetString(dr.GetOrdinal("Line3")),
Line4 = dr.GetString(dr.GetOrdinal("Line4")),
Line5 = dr.GetString(dr.GetOrdinal("Line5")),
Balance = dr.GetDecimal(dr.GetOrdinal("Balance")),
};
myaccountDTOs.Add(myaccountDTO);
}
}
LoadStorageUnits();
MergeStorageUnits();
LoadContactItems();
MergeContactItems();
return myaccountDTOs;
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
_myAccessConn.Close();
}
}
private void LoadContactItems()
{
try
{
using (var myAccessCommand = new OleDbCommand(MMSQLGetContactItems, _myAccessConn))
{
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
var myContactItemDTO = new ContactItemDTO()
{
AccountID = dr.GetInt32(dr.GetOrdinal(("CustomerID"))),
ContactItemID = dr.GetInt32(dr.GetOrdinal("PhoneID")),
ContactItem = dr.GetString(dr.GetOrdinal("PhoneNumber")),
Caption = dr.GetString(dr.GetOrdinal("Caption"))
};
myContactItemDTOs.Add(myContactItemDTO);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
}
finally
{
_myAccessConn.Close();
}
}
public override List<TransactionDTO> GetTransactions(int accountID)
{
_accountIDParm = accountID;
var myParm = _accountIDParm.ToString(CultureInfo.InvariantCulture);
var getAccountTransactions = MMSQLGetTransactionsByID + myParm;
if (myData.GetConnection(out _myAccessConn)) return null;
try
{
using (var myAccessCommand = new OleDbCommand(getAccountTransactions, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
var myTransactionDTO = new TransactionDTO
{
TransactionID = dr.GetInt32(dr.GetOrdinal("TransactionID")),
AccountID = dr.GetInt32(dr.GetOrdinal("CustomerID")),
TransactionDate = dr.GetSmartDate(dr.GetOrdinal("TransDate")),
Amount = dr.GetDecimal(dr.GetOrdinal("Amount")),
Description = dr.GetString(dr.GetOrdinal("Description")),
TransactionCode = dr.GetString(dr.GetOrdinal("TranCode")),
UnitID = dr.GetInt32(dr.GetOrdinal("UnitID")),
RentalDate = dr.GetSmartDate(dr.GetOrdinal("RentalDate"))
};
mytransactionDTOs.Add(myTransactionDTO);
}
}
return mytransactionDTOs;
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
_myAccessConn.Close();
}
}
public override RentalBusinessDTO GetRentalBusinessInfo()
{
if (myData.GetConnection(out _myAccessConn)) return null;
var businessInfo = new RentalBusinessDTO();
try
{
using (var myAccessCommand = new OleDbCommand(MMSQLRentalBusinessInfo, _myAccessConn))
{
_myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
{
businessInfo.BusinessName = dr.GetString(dr.GetOrdinal("FacilityName"));
businessInfo.Address = dr.GetString(dr.GetOrdinal("StreetAddress"));
businessInfo.City = dr.GetString(dr.GetOrdinal("City"));
businessInfo.StateProvince = dr.GetString(dr.GetOrdinal("State"));
businessInfo.PostalCode = dr.GetString(dr.GetOrdinal("Zip"));
businessInfo.PhoneNumber = dr.GetString(dr.GetOrdinal("Phone"));
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
}
finally
{
_myAccessConn.Close();
}
return businessInfo;
}
/// <summary>
/// Adds the navigation item.
/// </summary>
/// <param name="reader">The reader.</param>
/// <param name="currentGroup">The current group.</param>
private static void AddNavigationItem(SafeDataReader reader, NavigationGroupDTO currentGroup)
{
var navItemDto = new NavigationItemDTO
{
Id = reader.GetInt32(5),
Name = reader.GetString(6),
SystemName = reader.GetString(7),
IconId = reader.GetNullableInt(8),
IconUrl = reader.GetString(9),
AssemblyName = reader.GetString(10),
ProcessId = reader.GetNullableInt(11),
ProcessSystemName = reader.GetString(13),
Description = reader.GetString(17)
};
navItemDto.Description = navItemDto.ProcessId == null ? reader.GetString("HelpText") : reader.GetString(reader.GetOrdinal("Description"));
navItemDto.BackgroundColor = reader.GetInt64(reader.GetOrdinal("BackgroundColor"));
navItemDto.ProcessViewGuid = reader.GetNullableGuid(reader.GetOrdinal("ProcessViewGuid"));
navItemDto.IsSystem = reader.GetNullableBool(reader.GetOrdinal("IsSystem"));
navItemDto.IsBeta = reader.GetBoolean(reader.GetOrdinal("IsBeta"));
var processName = reader.GetString(12);
if (!string.IsNullOrEmpty(processName))
navItemDto.Name = processName;
currentGroup.NavigationItems.Add(navItemDto);
}
public List<AccountDTO> GetData()
{
var strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
var installPath = (string)Registry.GetValue(@"HKEY_CURRENT_USER\Software\VB and VBA Program Settings\MiniManager\Settings", "TARGETDIR", null);
if (installPath != null)
{
// Do stuff
strAccessConn = strAccessConn + installPath;
}
const string strAccessSelect = "SELECT * FROM tblCustomers";
OleDbConnection myAccessConn = null;
try
{
myAccessConn = new OleDbConnection(strAccessConn);
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
return null;
}
try
{
using (var myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn))
{
myAccessConn.Open();
using (var dr = new SafeDataReader(myAccessCommand.ExecuteReader()))
using (dr)
while (dr.Read())
{
var myDTO = new AccountDTO
{
AccountID = dr.GetInt32(dr.GetOrdinal("CustomerID")),
Line1 = dr.GetString(dr.GetOrdinal("Line1")),
Line2 = dr.GetString(dr.GetOrdinal("Line2")),
Line3 = dr.GetString(dr.GetOrdinal("Line3")),
Line4 = dr.GetString(dr.GetOrdinal("Line4")),
Line5 = dr.GetString(dr.GetOrdinal("Line5"))
};
this.myDTOs.Add(myDTO);
}
}
return myDTOs;
}
catch (Exception ex)
{
Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
return null;
}
finally
{
myAccessConn.Close();
}
}