public DataTableObject <IEnumerable <EmailAlertRecordEntity> > GetForDataTable(string id, DataTableParams param)
{
DataTableObject <IEnumerable <Data.Entities.EmailAlertRecordEntity> > dto;
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
OrderGridObject orderToUse = param.order.FirstOrDefault();
SqlParameter[] sparams = new SqlParameter[6];
sparams[0] = new SqlParameter("UserID", SqlDbType.VarChar);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = id;
sparams[1] = new SqlParameter("Start", param.start);
sparams[1].SqlDbType = SqlDbType.Int;
sparams[2] = new SqlParameter("Length", param.length);
sparams[2].SqlDbType = SqlDbType.Int;
sparams[3] = new SqlParameter("OrderByCol", (orderToUse == null) ? "" : param.cols.ElementAt(orderToUse.column).name);
sparams[3].SqlDbType = SqlDbType.VarChar;
sparams[4] = new SqlParameter("OrderDirection", (orderToUse == null) ? "" : orderToUse.dir);
sparams[4].SqlDbType = SqlDbType.VarChar;
sparams[5] = new SqlParameter("SearchText", (param.search == null) ? "" : (param.search.value ?? ""));
sparams[5].SqlDbType = SqlDbType.VarChar;
dto = context.WorkWithMultipleResultSetSproc <DataTableObject <IEnumerable <Data.Entities.EmailAlertRecordEntity> > >("usp_GetEmailAlertsForUser", sparams).FirstOrDefault();
dto.data = context.WorkWithMultipleResultSetSproc <EmailAlertRecordEntity>("usp_GetEmailAlertsForUser", sparams);
}
return(dto);
}
public IEnumerable <CurrencyEntity> GetActiveCurrenciesWithCurrentRates()
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
return(context.ExecuteSproc <CurrencyEntity>("usp_GetCurrenciesWithCurrentRates"));
}
}
internal override void RefreshData()
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
this.EntityData = context.ExecuteSproc <CurrencyEntity>("usp_GetCurrencies");
}
}
public void Save(EmailAlertEntity item)
{
string threshold;
if (item.Threshold == 0)
{
threshold = null;
}
else
{
threshold = item.Threshold.ToString();
}
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[6];
sparams[0] = new SqlParameter("Email", item.Email);
sparams[1] = new SqlParameter("CurrencyFrom", item.CurrencyFrom);
sparams[2] = new SqlParameter("CurrencyTo", item.CurrencyTo);
if (item.TimeToSend.Value.TotalMinutes == 0)
{
sparams[3] = new SqlParameter("Threshold", threshold);
}
else
{
sparams[3] = new SqlParameter("TimetoSend", item.TimeToSend);
}
sparams[4] = new SqlParameter("SessionID", item.SessionID);
sparams[5] = new SqlParameter("AlertID", item.AlertID);
context.ExecuteNonResultSproc("usp_SaveEmailAlert", sparams);
}
}
public static SecurityUser AuthenticateUser(string username, string password)
{
SecurityUser user = null;
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("UserName", username);
//have to do this because ie is an ass about getting this for some reason...
int counter = 0;
int limit = 10;
UserAuthEntity u = null;
do
{
u = context.ExecuteSproc <UserAuthEntity>("usp_GetUserCredentals", sparams).FirstOrDefault();
counter++;
} while (u == null || counter > limit);
if (u != null && Security.PasswordHash.ValidatePassword(password, u.Hash))
{
SqlParameter[] sparams2 = new SqlParameter[1];
sparams2[0] = new SqlParameter("UserName", username);
return(context.ExecuteSproc <SecurityUser>("usp_GetUser", sparams2).FirstOrDefault());
}
}
return(user);
}
internal CurrencySourceLists(bool activeOnly)
{
this.ActiveOnly = activeOnly;
//get full list from db
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
this.FullCurrencyList = context.ExecuteSproc <CurrencySource>("usp_GetCurrencySources").Where(c => c.Active = (this.ActiveOnly) ? true : c.Active);
}
}
public string GetUserIDByCookieID(string cookieID)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("SessionID", cookieID);
return(context.ExecuteSproc <string>("usp_GetUserIDByCookie", sparams).FirstOrDefault());
}
}
public void Delete(int id)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("AlertID", id);
context.ExecuteNonResultSproc("usp_DeleteEmailAlert", sparams);
}
}
internal IEnumerable <CurrencySource> GetListOfToCurrencies(string fromSource, string baseCurrency)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[2];
sparams[0] = new SqlParameter("SourceFrom", fromSource);
sparams[1] = new SqlParameter("BaseToUse", baseCurrency);
return(context.ExecuteSproc <CurrencySource>("usp_GetCurrencySourcesTo", sparams));
}
}
public UserEntity GetUser(string userName)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("UserName", userName);
return(context.ExecuteSproc <UserEntity>("usp_GetUser", sparams).FirstOrDefault());
}
}
public IEnumerable <CurrencyEntity> GetFor(string forParameter)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("CurrencySelected", forParameter);
return(context.ExecuteSproc <CurrencyEntity>("usp_GetAllowedCurrencies", sparams));
}
}
public bool CheckUserName(string username)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("UserName", username);
int result = context.ExecuteSproc <int>("usp_CheckUserName", sparams).FirstOrDefault();
return((result == 1) ? true : false);
}
}
public void SaveExisting(int entryID)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("EntryID", SqlDbType.Int);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = entryID;
context.ExecuteNonResultSproc("usp_SaveEntryAutoUpdateChange", sparams);
}
}
public IEnumerable <CurrencyRateEntity> GetRatesForCurrency(string currencyCd, bool fromToOnly)
{
//return this.EntityData.Where(e => e.FromCurrency == currencyCd || e.ToCurrency == currencyCd);
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[2];
sparams[0] = new SqlParameter("Currency", currencyCd);
sparams[1] = new SqlParameter("GetOnlyFromTo", fromToOnly);
return(context.ExecuteSproc <CurrencyRateEntity>("usp_GetCurrencyRatesUsingCurrency", sparams));
}
}
public EmailAlertEntity GetAlert(int alertID)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("AlertID", SqlDbType.Int);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = alertID;
return(context.ExecuteSproc <EmailAlertEntity>("usp_GetEmailAlert", sparams).FirstOrDefault());
}
}
public void DeleteAllForUser(string userName)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("UserName", SqlDbType.VarChar);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = userName;
context.ExecuteNonResultSproc("usp_DeleteAllAlertsByUser", sparams);
}
}
public void DeleteAll(string sessionID)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("SessionID", SqlDbType.VarChar);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = sessionID;
context.ExecuteNonResultSproc("usp_DeleteAllTrackerEntries", sparams);
}
}
public void Delete(int id)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("EntryID", SqlDbType.Int);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = id;
context.ExecuteNonResultSproc("usp_DeleteTrackerEntry", sparams);
}
}
public CurrencyRateEntity GetSingle(string fromCurrencyCd, string toCurrencyCd)
{
//return this.EntityData.SingleOrDefault(e => e.FromCurrency == fromCurrencyCd && e.ToCurrency == toCurrencyCd);
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[2];
sparams[0] = new SqlParameter("CurrencyFrom", fromCurrencyCd);
sparams[1] = new SqlParameter("CurrencyTo", toCurrencyCd);
return(context.ExecuteSproc <CurrencyRateEntity>("usp_GetCurrencyRatesForPair", sparams).FirstOrDefault());
}
}
public void UpdateProfile(UserEntity userPref)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[3];
sparams[0] = new SqlParameter("UserName", userPref.UserName);
sparams[1] = new SqlParameter("Email", userPref.Email);
sparams[2] = new SqlParameter("DisplayName", userPref.DisplayName);
context.ExecuteNonResultSproc("usp_SaveUserProfile", sparams);
}
}
public void UpdateUserCookie(string username, string cookieID, DateTime dateExpires)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[3];
sparams[0] = new SqlParameter("UserID", username);
sparams[1] = new SqlParameter("SessionID", cookieID);
sparams[2] = new SqlParameter("DateExpires", (dateExpires.Year == 1) ? DateTime.Now.AddDays(1) : dateExpires);
context.ExecuteNonResultSproc("usp_UpdateUserCookie", sparams);
}
}
public IEnumerable <RateHistoryEntity> GetHistoricalRatesFor(string currencyFrom, string currencyTo, DateTime dateFrom, DateTime dateTo)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[4];
sparams[0] = new SqlParameter("CurrencyFrom", currencyFrom);
sparams[1] = new SqlParameter("CurrencyTo", currencyTo);
sparams[2] = new SqlParameter("DateFrom", dateFrom);
sparams[3] = new SqlParameter("DateTo", dateTo);
return(context.ExecuteSproc <RateHistoryEntity>("usp_GetHistoricalCurrencyRates", sparams));
}
}
public void UpdateDefaults(UserEntity userPref)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[5];
sparams[0] = new SqlParameter("UserName", userPref.UserName);
sparams[1] = new SqlParameter("CurrencyFrom", (userPref.DefaultCurrencyFrom) ?? "");
sparams[2] = new SqlParameter("CurrencyTo", (userPref.DefaultCurrencyTo) ?? "");
sparams[3] = new SqlParameter("Country", (userPref.DefaultCountry) ?? "");
sparams[4] = new SqlParameter("AutoUpdateEntries", userPref.AutoUpdateEntries);
context.ExecuteNonResultSproc("usp_SaveUserDefaults", sparams);
}
}
public void CreateAccount(string username, string password, string email, string displayName, string sessionID, DateTime dateExpires)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[6];
sparams[0] = new SqlParameter("UserName", username);
sparams[1] = new SqlParameter("Password", password);
sparams[2] = new SqlParameter("Email", email);
sparams[3] = new SqlParameter("DisplayName", displayName);
sparams[4] = new SqlParameter("SessionID", sessionID);
sparams[5] = new SqlParameter("DateExpires", dateExpires);
context.ExecuteNonResultSproc("usp_SaveNewUser", sparams);
}
}
public bool ValidateUser(string username, string password)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("UserName", username);
UserAuthEntity u = context.ExecuteSproc <UserAuthEntity>("usp_GetUserCredentals", sparams).FirstOrDefault();
if (Security.PasswordHash.ValidatePassword(password, u.Hash))
{
return(true);
}
}
return(false);
}
private IEnumerable <CurrencySource> GetData(string sourceFrom)
{
//get full list from db
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
if (sourceFrom != "")
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("SourceFrom", sourceFrom);
sparams[0].Direction = System.Data.ParameterDirection.Input;
return(context.ExecuteSproc <CurrencySource>("usp_GetCurrencySources", sparams));
}
else
{
return(context.ExecuteSproc <CurrencySource>("usp_GetCurrencySources"));
}
}
}
public void Save(ConversionTrackerEntity item)
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[10];
sparams[0] = new SqlParameter("EntryName", item.EntryName);
sparams[1] = new SqlParameter("CurrencyFrom", item.CurrencyFrom);
sparams[2] = new SqlParameter("CurrencyTo", item.CurrencyTo);
sparams[3] = new SqlParameter("FromAmount", item.FromAmount);
sparams[4] = new SqlParameter("ToAmount", item.ToAmount);
sparams[5] = new SqlParameter("RateToUse", item.RateToUse);
sparams[6] = new SqlParameter("UpdateRate", item.UpdateRate);
sparams[7] = new SqlParameter("LastUpdatedDate", item.LastUpdatedDate.ToUniversalTime());
sparams[8] = new SqlParameter("SessionID", item.SessionID);
sparams[9] = new SqlParameter("DateCookieExpires", null); //get this from cookie object using session id when built. For now it'll work
context.ExecuteNonResultSproc("usp_SaveNewTrackerEntry", sparams);
}
}
private static void SaveRates(CurrencyRatesTableTypeCollection ttParam)
{
try
{
using (var context = new DataAccessFramework.DBConnection.DBConnectionContext(DatabaseName.CuMaster))
{
SqlParameter[] sparams = new SqlParameter[1];
sparams[0] = new SqlParameter("CurrencyRateList", SqlDbType.Structured);
sparams[0].Direction = System.Data.ParameterDirection.Input;
sparams[0].Value = ttParam;
//Debug.WriteLine("About to save rates");
context.Database.CommandTimeout = 900000000;
context.ExecuteNonResultSproc("usp_SaveCurrencyRates", sparams);
}
}
catch (Exception ex)
{
string errorLogPath = @"C:\UpdateRateBatch\ErrorLog.txt";
File.AppendAllText(errorLogPath, Environment.NewLine + ex.Message + Environment.NewLine + ex.StackTrace);
}
}
