public Listing InsertListing(Listing listing)
{
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
var parameters = new DynamicParameters();
parameters.Add("@ListingId", listing.ListingId, DbType.Int32, direction: ParameterDirection.Output);
parameters.Add("@ModelId", listing.ModelId);
parameters.Add("@BodyStyleId", listing.BodyStyleId);
parameters.Add("@InteriorColorId", listing.InteriorColorId);
parameters.Add("@ExteriorColorId", listing.ExteriorColorId);
parameters.Add("@Condition", listing.Condition);
parameters.Add("@Transmission", listing.Transmission);
parameters.Add("@Mileage", listing.Mileage);
parameters.Add("@ModelYear", listing.ModelYear);
parameters.Add("@VIN", listing.VIN);
parameters.Add("@MSRP", listing.MSRP);
parameters.Add("@SalePrice", listing.SalePrice);
parameters.Add("@VehicleDescription", listing.VehicleDescription);
parameters.Add("@ImageFileUrl", listing.ImageFileUrl);
parameters.Add("@IsFeatured", listing.IsFeatured);
parameters.Add("@IsSold", listing.IsSold);
parameters.Add("@DateAdded", listing.DateAdded);
cn.Execute("ListingInsert", parameters, commandType: CommandType.StoredProcedure);
listing.ListingId = parameters.Get <int>("@ListingId");
}
return(listing);
}
private static bool VerifyConfiguration()
{
bool configOK = true;
var connectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.ServiceBusConnectionString);
return(configOK);
}
public List <Model> GetModelsByMakeId(int makeId)
{
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
List <Model> models = new List <Model>();
SqlCommand cmd = new SqlCommand("GetModelsByMakeId", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MakeId", makeId);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Model row = new Model();
row.Make = new Make();
row.ModelId = (int)dr["ModelId"];
row.ModelName = dr["ModelName"].ToString();
row.Make.MakeId = (int)dr["MakeId"];
row.Make.MakeName = dr["MakeName"].ToString();
row.DateAdded = (DateTime)dr["DateAdded"];
row.UserName = dr["UserName"].ToString();
models.Add(row);
}
}
if (models.Any())
{
return(models);
}
return(null);
}
}
public List <Special> GetSpecials()
{
List <Special> specials = new List <Special>();
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetSpecials", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Special row = new Special();
row.SpecialId = (int)dr["SpecialId"];
row.SpecialTitle = dr["SpecialTitle"].ToString();
row.SpecialMessage = dr["SpecialMessage"].ToString();
specials.Add(row);
}
}
}
return(specials);
}
public void SendMessage(string body, IDictionary <string, object> responseProperties = null)
{
var SBconnectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.ServiceBusConnectionString);
topicClient = TopicClient.CreateFromConnectionString(SBconnectionString, TopicName);
//topicClient = TopicClient.Create(TopicName);
BrokeredMessage message = CreateMessage(body, responseProperties);
try
{
topicClient.Send(message);
}
catch (MessagingException e)
{
if (!e.IsTransient)
{
Console.WriteLine(e.Message);
throw;
}
else
{
HandleTransientErrors(e);
}
}
Console.WriteLine(string.Format("Message sent: Id = {0}, Body = {1}", message.MessageId, message.GetBody <string>()));
topicClient.Close();
}
public AccessToken GenerateToken([FromBody] AccessVM access)
{
User user;
using (var c = new SqlConnection(con.GetConnectionString()))
{
user = c.QueryFirstOrDefault <User>(Sql.GetUserByIndentity, access, commandTimeout: 3000);
}
if (user == null)
{
Response.StatusCode = StatusCodes.Status401Unauthorized;
return(new AccessToken {
Success = false
});
}
var claims = new[]
{
new Claim("Id", user.Id.ToString()),
new Claim(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString())
};
var key = new SymmetricSecurityKey(Encoding.ASCII.GetBytes(configuration.SecretKey));
var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
var expiredOn = DateTime.Now.AddMinutes(configuration.TokenExpirationTime);
var token = new JwtSecurityToken(configuration.ValidIssuer,
configuration.ValidAudience,
claims,
expires: expiredOn,
signingCredentials: creds);
using (var c = new SqlConnection(con.GetConnectionString()))
{
c.Execute(Sql.AddJwt, new { UserId = user.Id, Token = new JwtSecurityTokenHandler().WriteToken(token), ExpirationDate = expiredOn }, commandTimeout: 3000);
}
return(new AccessToken
{
ExpireOnDate = token.ValidTo,
Success = true,
ExpiryIn = configuration.TokenExpirationTime,
Token = new JwtSecurityTokenHandler().WriteToken(token)
});
}
private void Initialize(string containerName)
{
lock (this)
{
_cloudStorageAccount = CloudStorageAccount.Parse(ConnectionStrings.GetConnectionString(ConnectionStrings.Key.MetadataBlobStorage));
_containerName = containerName.ToLower();
}
}
public DbContext CreateBaseDbContext()
{
var builder = new DbContextOptionsBuilder <IdentityContext>();
builder.UseSqlServer(ConnectionStrings.GetConnectionString("DefaultConnection"),
options => options.EnableRetryOnFailure());
//EF Core Doesn't support Ambient Transactions
builder.ConfigureWarnings(x => x.Ignore(RelationalEventId.AmbientTransactionWarning));
return(new IdentityContext(builder.Options));
}
public List <InventoryReport> InventoryReport(string report)
{
List <InventoryReport> reportItems = new List <InventoryReport>();
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
string query = "SELECT l.ModelYear, ma.MakeName, ModelName, COUNT(*) as [Count], SUM(l.SalePrice) AS 'StockValue' " +
"FROM Listings l " +
"INNER JOIN Models mo on mo.ModelId = l.ModelId " +
"INNER JOIN Makes ma on ma.MakeId = mo.MakeId ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
switch (report)
{
case "New":
query += "WHERE l.Condition = 1 AND IsSold = 0 ";
break;
case "Used":
query += "WHERE l.Condition = 2 AND IsSold = 0";
break;
default:
break;
}
query += "GROUP BY l.ModelYear, mo.ModelName, ma.MakeName, l.SalePrice";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
InventoryReport row = new InventoryReport();
row.ModelYear = (int)dr["ModelYear"];
row.MakeName = dr["MakeName"].ToString();
row.ModelName = dr["ModelName"].ToString();
row.Count = (int)dr["Count"];
row.StockValue = (decimal)dr["StockValue"];
reportItems.Add(row);
}
}
}
return(reportItems);
}
/// <summary>
/// Static constructor. Reads the connectionstring from web.config just once.
/// </summary>
static DataObjectFactory()
{
try
{
// Connection strings here
_connectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.EWEEntities);
_eweAdoConnectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.EWEADO);
}
catch (Exception ex)
{
throw (ex);
}
}
public Model Save(Model model)
{
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
var parameters = new DynamicParameters();
parameters.Add("@ModelId", model.ModelId, DbType.Int32, direction: ParameterDirection.Output);
parameters.Add("@MakeId", model.Make.MakeId);
parameters.Add("@ModelName", model.ModelName);
parameters.Add("@UserName", model.UserName);
parameters.Add("@DateAdded", model.DateAdded);
cn.Execute("SaveModel", parameters, commandType: CommandType.StoredProcedure);
return(model);
}
}
public void Init()
{
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
var cmd = new SqlCommand();
cmd.CommandText = "ResetDb";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = cn;
cn.Open();
cmd.ExecuteNonQuery();
}
}
public List <Road> GetRoadList()
{
var jwt = HttpContext.User;
//var prinicpal = (ClaimsPrincipal)Thread.CurrentPrincipal;
var userId = jwt.Claims.Where(c => c.Type == "Id").Select(c => c.Value).SingleOrDefault();
List <Road> roads = new List <Road>();
using (var c = new SqlConnection(con.GetConnectionString()))
{
//roads = c.Query<List<Road>>(Sql.GetUserByIndentity/*.GetParam()*/, commandTimeout: 3000);
}
return(roads);
}
private void ParseConnectionString()
{
var connectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.CollectedDataConnectionString);
string[] parts;
if (connectionString.StartsWith("AccountEndpoint="))
{
parts = connectionString.Split(';');
}
else
{
connectionString = string.Concat("AccountEndpoint=", connectionString);
parts = connectionString.Split(',');
}
for (var i = 0; i < parts.Length; ++i)
{
var nvp = parts[i];
var eqSignIndex = nvp.IndexOf('=');
var name = nvp.Substring(0, eqSignIndex).Trim().ToLowerInvariant();
var value = nvp.Substring(eqSignIndex + 1).Trim();
switch (name)
{
case "accountendpoint":
_serviceEndpoint = value.Trim();
break;
case "accountkey":
_authKey = value;
break;
case "authkey":
_authKey = value;
break;
case "dbname":
DatabaseName = value;
break;
}
}
if (string.IsNullOrWhiteSpace(_serviceEndpoint) || string.IsNullOrWhiteSpace(_authKey))
{
//throw new ConfigurationException("SurveyResponse ConnectionString is invalid. Service Endpoint and AuthKey must be specified.");
}
}
public EPIInfo7Entities(string connectionStringName)
: base(ConnectionStrings.GetConnectionString(ConnectionStrings.Key.EPIInfo7Entities))
{
}
public ActionResult Index(string surveyid)
{
EIWSTModel TestModel = new EIWSTModel();
try
{
string version = Assembly.GetExecutingAssembly().GetName().Version.ToString();
ViewBag.Version = version;
string connectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.EWEADO);
using (var conn = new System.Data.SqlClient.SqlConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
TestModel.DBTestStatus = TestResultEnum.Success.ToString();
cmd.CommandText = "SELECT * FROM lk_Status";
cmd.Parameters.AddWithValue("@StatusId", 1);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
var TestValue = reader.GetString(reader.GetOrdinal("Status"));
}
TestModel.DBTestStatus = TestResultEnum.Success.ToString();
}
}
catch (Exception ex)
{
TestModel.DBTestStatus = TestResultEnum.Error.ToString();
TestModel.STestStatus = "Incomplete";
TestModel.EFTestStatus = "Incomplete";
TempData[TempDataKeys.ExceptionMessage] = ex.Message.ToString();
TempData[TempDataKeys.ExceptionSource] = ex.Source.ToString();
TempData[TempDataKeys.ExceptionStackTrace] = ex.StackTrace.ToString();
return(View(ViewActions.Index, TestModel));
}
try
{
Epi.Web.EF.EntityOrganizationDao NewEntity = new Epi.Web.EF.EntityOrganizationDao();
List <OrganizationBO> OrganizationBO = new List <OrganizationBO>();
OrganizationBO = NewEntity.GetOrganizationNames();
if (OrganizationBO != null)
{
TestModel.EFTestStatus = TestResultEnum.Success.ToString();
}
}
catch (Exception ex)
{
TestModel.EFTestStatus = TestResultEnum.Error.ToString();
TestModel.STestStatus = "Incomplete";
TempData[TempDataKeys.ExceptionMessage] = ex.Message.ToString();
TempData[TempDataKeys.ExceptionSource] = ex.Source.ToString();
TempData[TempDataKeys.ExceptionStackTrace] = ex.StackTrace.ToString();
return(View(ViewActions.Index, TestModel));
}
return(View(ViewActions.Index, TestModel));
}
private static string CacheConnectionString()
{
return(ConnectionStrings.GetConnectionString(ConnectionStrings.Key.CacheConnectionString));
}
/// <summary>
/// Gets an connection instance based on current ConnectionString value
/// </summary>
/// <returns>Connection instance</returns>
public IDbConnection GetConnection()
{
var connectionString = ConnectionStrings.GetConnectionString(ConnectionStrings.Key.DBConnection);
return(GetNativeConnection(connectionString));
}
public IEnumerable <Listing> Search(ListingSearchParameters parameters)
{
List <Listing> listings = new List <Listing>();
using (var cn = new SqlConnection(ConnectionStrings.GetConnectionString()))
{
string query =
"SELECT TOP 20 ListingId, l.ModelId, mo.ModelName, l.ModelYear, " +
"ma.MakeId, ma.MakeName, l.BodyStyleId, bs.BodyStyleName, l.InteriorColorId, " +
"ic.InteriorColorName, l.ExteriorColorId, ec.ExteriorColorName, " +
"Condition, Transmission, Mileage, VIN, MSRP, SalePrice, VehicleDescription, " +
"ImageFileUrl, IsFeatured, IsSold, l.DateAdded " +
"FROM Listings l " +
"inner join Models mo on mo.ModelId = l.ModelId " +
"inner join Makes ma on ma.MakeId = mo.MakeId " +
"inner join InteriorColors ic on ic.InteriorColorId = l.InteriorColorId " +
"inner join ExteriorColors ec on ec.ExteriorColorId = l.ExteriorColorId " +
"inner join BodyStyles bs on bs.BodyStyleId = l.BodyStyleId " +
"WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
switch (parameters.View)
{
case "New":
query += "AND l.Condition = 1 ";
break;
case "Used":
query += "AND l.Condition = 2 ";
break;
case "Admin":
query += "AND (l.Condition = 1 or l.Condition = 2) ";
break;
case "Sales":
query += "AND l.IsSold = 0 ";
break;
default:
break;
}
if (parameters.MinPrice.HasValue)
{
query += "AND SalePrice >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND SalePrice <= @MaxPrice ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += "AND l.ModelYear >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += "AND l.ModelYear <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.QuickSearch))
{
query += "AND (ma.MakeName LIKE @QuickSearch OR mo.ModelName LIKE @QuickSearch OR l.ModelYear LIKE @QuickSearch) ";
cmd.Parameters.AddWithValue("@QuickSearch", parameters.QuickSearch + '%');
}
query += "ORDER BY DateAdded DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Listing row = new Listing();
row.ListingId = (int)dr["ListingId"];
row.ModelId = (int)dr["ModelId"];
row.ModelName = dr["ModelName"].ToString();
row.ModelYear = (int)dr["ModelYear"];
row.MakeId = (int)dr["MakeId"];
row.MakeName = dr["MakeName"].ToString();
row.BodyStyleId = (int)dr["BodyStyleId"];
row.BodyStyleName = dr["BodyStyleName"].ToString();
row.InteriorColorId = (int)dr["InteriorColorId"];
row.InteriorColorName = dr["InteriorColorName"].ToString();
row.ExteriorColorId = (int)dr["ExteriorColorId"];
row.ExteriorColorName = dr["ExteriorColorName"].ToString();
row.Condition = (Condition)dr["Condition"];
row.Transmission = (Transmission)dr["Transmission"];
row.Mileage = (int)dr["Mileage"];
row.VIN = dr["VIN"].ToString();
row.MSRP = (decimal)dr["MSRP"];
row.SalePrice = (decimal)dr["SalePrice"];
row.VehicleDescription = dr["VehicleDescription"].ToString();
row.IsFeatured = (bool)dr["IsFeatured"];
row.IsSold = (bool)dr["IsSold"];
if (dr["ImageFileUrl"] != DBNull.Value)
{
row.ImageFileUrl = dr["ImageFileUrl"].ToString();
}
listings.Add(row);
}
}
}
return(listings);
}
public void GetConnectionString_Not_Null_And_Not_Empty()
{
Assert.That(ConnectionStrings.GetConnectionString(ConnectionStringNames.TEST_CONNECTION_STRING_NAME), Is.Not.Null.And.Not.Empty);
}
public void GetConnectionString_Throws_ArgumentNullException_When_Parameter_Is_Null_Or_WhiteSpace()
{
Assert.That(() => ConnectionStrings.GetConnectionString(null), Throws.ArgumentNullException);
Assert.That(() => ConnectionStrings.GetConnectionString(" "), Throws.ArgumentNullException);
}