/// <summary>
/// Updates a region record within the dimension table
/// </summary>
/// <param name="region"></param>
public bool UpdateRegionDimension(RegionDTO region)
{
bool Success = true;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"UPDATE DimRegion SET Region = @Region WHERE ID = @ID";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("ID", region.ID));
dbComm.Parameters.Add(new SqlParameter("Region", region.Region));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Deletes a location Record by ID from the dimension table
/// </summary>
/// <param name="ID"></param>
public bool DeleteRegionDimension(int ID)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"DELETE FROM DimRegion WHERE ID = @ID";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("ID", ID));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Updates a location record within the dimension table
/// </summary>
/// <param name="location"></param>
public bool UpdateLocationDimension(LocationDTO location)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"UPDATE DimLocation SET LocationID = @LocationID, LocationNumber = @LocationNumber WHERE ID = @ID";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("ID", location.ID));
dbComm.Parameters.Add(new SqlParameter("LocationID", location.LocationID));
dbComm.Parameters.Add(new SqlParameter("LocationNumber", location.LocationNumber));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Creates a region within the dimension table
/// </summary>
/// <param name="region"></param>
public bool CreateRegionDimension(RegionDTO region)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"IF NOT EXISTS (SELECT Region from DimRegion WHERE Region = @Region)
INSERT INTO DimRegion (Region) VALUES (@Region)";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("Region", region.Region));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Creates a location record within the dimension table
/// </summary>
/// <param name="location"></param>
public bool CreateLocationDimension(LocationDTO location)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"IF NOT EXISTS (SELECT LocationID from DimLocation WHERE LocationID = @LocationID)
INSERT INTO DimLocation (LocationID, LocationNumber) VALUES (@LocationID, @LocationNumber)";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("LocationID", location.LocationID));
dbComm.Parameters.Add(new SqlParameter("LocationNumber", location.LocationNumber));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Updates a country record within the dimension table
/// </summary>
/// <param name="country"></param>
public bool UpdateCountryDimension(CountryDTO country)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"UPDATE DimCountry SET CountryCode = @CountryCode, CountryName = @CountryName WHERE ID = @ID";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("ID", country.ID));
dbComm.Parameters.Add(new SqlParameter("CountryCode", country.CountryCode));
dbComm.Parameters.Add(new SqlParameter("CountryName", country.CountryName));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Creates a country record within the dimension table
/// </summary>
/// <param name="country"></param>
public bool CreateCountryDimension(CountryDTO country)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"IF NOT EXISTS (SELECT CountryCode from DimCountry WHERE CountryCode = @CountryCode)
INSERT INTO DimCountry (CountryCode, CountryName) VALUES (@CountryCode, @CountryName)";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("CountryCode", country.CountryCode));
dbComm.Parameters.Add(new SqlParameter("CountryName", country.CountryName));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Updates an account record in the dimension table
/// </summary>
/// <param name="account"></param>
public bool UpdateAccountDimension(AccountDTO account)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"UPDATE DimAccount SET AccountID = @AccountID, AccountName = @AccountName WHERE ID = @ID";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("ID", account.ID));
dbComm.Parameters.Add(new SqlParameter("AccountID", account.AccountID));
dbComm.Parameters.Add(new SqlParameter("AccountName", account.AccountName));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}
/// <summary>
/// Creates an account record within the dimension table
/// </summary>
/// <param name="account"></param>
public bool CreateAccountDimension(AccountDTO account)
{
bool Success = false;
SQLAzure.DbConnection dbConn = new SQLAzure.DbConnection(_connectionString);
try
{
string query = @"IF NOT EXISTS (SELECT AccountID from DimAccount WHERE AccountID = @AccountID)
INSERT INTO DimAccount (AccountID, AccountName) VALUES (@AccountID, @AccountName)";
dbConn.Open();
SQLAzure.RetryLogic.DbCommand dbComm = new SQLAzure.RetryLogic.DbCommand(dbConn);
dbComm.CommandText = query;
dbComm.Parameters.Add(new SqlParameter("AccountID", account.AccountID));
dbComm.Parameters.Add(new SqlParameter("AccountName", account.AccountName));
dbComm.ExecuteNonQuery(System.Data.CommandType.Text);
Success = true;
}
catch (SqlException)
{
throw;
}
finally
{
dbConn.Close();
}
return(Success);
}