private String GetOffersQueryString(GetMVGOffersRequest reqst) { return(@" declare @acctNo nvarchar(10) = '" + reqst.AcctNo + @"' declare @startDate DateTime = '" + reqst.Start.ToString("yyyy-MM-dd") + @"' declare @endDate DateTime = '" + reqst.End.ToString("yyyy-MM-dd") + @"' select PlayerAccountNumber, MktgEventName, EventStartDtm, EventEndDtm, OfferDescName, OfferDetailedDesc, OfferCategoryName, RedeemedDate, OfferCost, IsRedeemed, IsExpired, CasinoId, OfferValidEffectiveDate, OfferValidThruDate from fBICM_CMMktgOffer where PlayerAccountNumber=@acctNo and EventStartDtm >= @startDate and EventStartDtm < @endDate order by OfferValidThruDate asc"); }
public GetMVGOffersResponse GetOffers(GetMVGOffersRequest reqst) { DateTime kickoff = DateTime.Now; XmlConfigurator.Configure(new System.IO.FileInfo(config.Log4NetConfigPath)); GetMVGOffersResponse reply = new GetMVGOffersResponse(); reply.Offers = new OfferList(); try { log.Info("GetOffers. AcctNo: " + reqst.AcctNo + ", Start: " + reqst.Start.ToString("yyyy-MM-dd") + ", End: " + reqst.End.ToString("yyyy-MM-dd") + "."); reply.Success = true; GetOffersFromDB(ref reply, reqst); log.Info("Request fulfilled. (" + executionTime(new TimeSpan(DateTime.Now.Ticks - kickoff.Ticks)) + "ms)"); reply.Success = true; return(reply); } catch (Exception excp) { log.Error("\nMessage:\n" + excp.Message + "\nStack Trace:\n" + excp.StackTrace); reply.Success = false; ProcessError err = new ProcessError(); err.ErrorMessage = excp.Message; reply.Errors.Add(err); return(reply); } }
private void GetOffersFromDB(ref GetMVGOffersResponse reply, GetMVGOffersRequest reqst) { reply.Success = false; using (SqlConnection sqlConnection = new SqlConnection("user id=" + config.DW_User + ";" + "password="******";" + "server=" + config.DW_Server + ";" + "Trusted_Connection=false;" + "database=" + config.DW_Database + "; " + "connection timeout=120")) { sqlConnection.Open(); using (SqlCommand sqlCommand = new SqlCommand(GetOffersQueryString(reqst), sqlConnection)) { sqlCommand.CommandTimeout = config.SqlTimeOut; using (SqlDataReader dataReader = sqlCommand.ExecuteReader()) { reply.Offers = new OfferList(); while (dataReader.Read()) { reply.Offers.Add( new MVGOffer { PlayerAccountNumber = DBHelper.dbHelper.getDbStringVal(dataReader, "PlayerAccountNumber"), MktgEventName = DBHelper.dbHelper.getDbStringVal(dataReader, "MktgEventName"), EventStartDtm = DBHelper.dbHelper.getDbDateVal(dataReader, "EventStartDtm"), EventEndDtm = DBHelper.dbHelper.getDbDateVal(dataReader, "EventEndDtm"), OfferDescName = DBHelper.dbHelper.getDbStringVal(dataReader, "OfferDescName"), OfferDetailedDesc = DBHelper.dbHelper.getDbStringVal(dataReader, "OfferDetailedDesc"), OfferCategoryName = DBHelper.dbHelper.getDbStringVal(dataReader, "OfferCategoryName"), RedeemedDate = DBHelper.dbHelper.getDbDateVal(dataReader, "RedeemedDate"), OfferCost = (double)DBHelper.dbHelper.getDbDecimalNullVal(dataReader, "OfferCost"), IsRedeemed = DBHelper.dbHelper.getDbBoolVal(dataReader, "IsRedeemed"), IsExpired = DBHelper.dbHelper.getDbBoolVal(dataReader, "IsExpired"), CasinoId = DBHelper.dbHelper.getDbStringVal(dataReader, "CasinoId"), OfferValidEffectiveDate = DBHelper.dbHelper.getDbDateVal(dataReader, "OfferValidEffectiveDate"), OfferValidThruDate = DBHelper.dbHelper.getDbDateVal(dataReader, "OfferValidThruDate") } ); } reply.Success = true; } } } }