/// <summary>
/// Returns a single account dimension
/// </summary>
/// <returns></returns>
public AccountDTO GetAccountDimension(int ID)
{
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
AccountDTO account = new AccountDTO();
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = "SELECT ID, AccountID, AccountName FROM DimAccount WHERE ID = @ID";
dbComm.Parameters.Add(new SqlParameter("ID", ID));
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
account.ID = Convert.ToInt32(rdr["ID"]);
account.AccountID = Guid.Parse(rdr["AccountID"].ToString());
account.AccountName = rdr["AccountName"].ToString();
}
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(account);
}
/// <summary>
/// Returns a single country dimension
/// </summary>
/// <returns></returns>
public RegionDTO GetRegionDimension(int ID)
{
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
RegionDTO region = new RegionDTO();
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = "SELECT ID, Region FROM DimRegion WHERE ID = @ID";
dbComm.Parameters.Add(new SqlParameter("ID", ID));
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
region.ID = Convert.ToInt32(rdr["ID"]);
region.Region = rdr["Region"].ToString();
}
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(region);
}
/// <summary>
/// Returns a single country dimension
/// </summary>
/// <returns></returns>
public CountryDTO GetCountryDimension(int ID)
{
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
CountryDTO country = new CountryDTO();
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = "SELECT ID, CountryCode, CountryName FROM DimCountry WHERE ID = @ID";
dbComm.Parameters.Add(new SqlParameter("ID", ID));
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
country.ID = Convert.ToInt32(rdr["ID"]);
country.CountryCode = rdr["CountryCode"].ToString();
country.CountryName = rdr["CountryName"].ToString();
}
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(country);
}
/// <summary>
/// Returns a list of regions from the database
/// </summary>
/// <returns></returns>
public RegionListDTO GetRegionDimensions(int Page, int PageSize, string Region = "")
{
RegionListDTO list = new RegionListDTO();
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
if (Region != string.Empty)
{
dbComm.CommandText = String.Format("SELECT ID, Region, TotalCount = COUNT(ID) OVER() FROM DimRegion WHERE Region LIKE '%' + @Region + '%' ORDER BY ID OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (Page * PageSize), PageSize);
dbComm.Parameters.Add(new SqlParameter("Region", Region));
}
else
{
dbComm.CommandText = String.Format("SELECT ID, Region, TotalCount = COUNT(ID) OVER() FROM DimRegion ORDER BY ID OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (Page * PageSize), PageSize);
}
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
RegionDTO region = new RegionDTO();
region.ID = Convert.ToInt32(rdr["ID"]);
region.Region = rdr["Region"].ToString();
list.Items.Add(region);
list.RecordCount = Convert.ToInt32(rdr["TotalCount"]);
}
list.PageNumber = Page;
list.PageSize = PageSize;
list.TotalPages = GetNumberOfPagesAvailable(PageSize, list.RecordCount);
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(list);
}
/// <summary>
/// Returns a list of accounts from the database
/// </summary>
/// <returns></returns>
public CountryListDTO GetCountryDimensions(int Page, int PageSize, string CountryName = "")
{
CountryListDTO list = new CountryListDTO();
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
if (CountryName != string.Empty)
{
dbComm.CommandText = String.Format("SELECT ID, CountryCode, CountryName, TotalCount = COUNT(ID) OVER() FROM DimCountry WHERE CountryName LIKE '%' + @CountryName + '%' ORDER BY ID OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (Page * PageSize), PageSize);
dbComm.Parameters.Add(new SqlParameter("CountryName", CountryName));
}
else
{
dbComm.CommandText = String.Format("SELECT ID, CountryCode, CountryName, TotalCount = COUNT(ID) OVER() FROM DimCountry ORDER BY ID OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (Page * PageSize), PageSize);
}
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
CountryDTO country = new CountryDTO();
country.ID = Convert.ToInt32(rdr["ID"]);
country.CountryCode = rdr["CountryCode"].ToString();
country.CountryName = rdr["CountryName"].ToString();
list.Items.Add(country);
list.RecordCount = Convert.ToInt32(rdr["TotalCount"]);
}
list.PageNumber = Page;
list.PageSize = PageSize;
list.TotalPages = GetNumberOfPagesAvailable(PageSize, list.RecordCount);
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(list);
}
/// <summary>
/// Returns a list of locations from the database
/// </summary>
/// <returns></returns>
public LocationListDTO GetLocationDimensions(int Page, int PageSize)
{
LocationListDTO list = new LocationListDTO();
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = String.Format("SELECT ID, LocationID, LocationNumber, TotalCount = COUNT(ID) OVER() FROM DimLocation ORDER BY ID OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (Page * PageSize), PageSize);
System.Data.IDataReader rdr = dbComm.ExecuteReader(System.Data.CommandType.Text);
while (rdr.Read())
{
LocationDTO location = new LocationDTO();
location.ID = Convert.ToInt32(rdr["ID"]);
location.LocationID = Guid.Parse(rdr["LocationID"].ToString());
location.LocationNumber = Convert.ToInt32(rdr["LocationNumber"].ToString());
list.Items.Add(location);
list.RecordCount = Convert.ToInt32(rdr["TotalCount"]);
}
list.PageNumber = Page;
list.PageSize = PageSize;
list.TotalPages = GetNumberOfPagesAvailable(PageSize, list.RecordCount);
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(list);
}