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