public void ResetDatabase()
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
connection.Execute("DbReset", commandType: CommandType.StoredProcedure);
}
}
public List <Category> SelectAll()
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
return(connection.Query <Category>("CategorySelectAll", commandType: CommandType.StoredProcedure).ToList());
}
}
public void EmployeeGetById()
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
//var result = connection.Get<Employee>(911);
//Assert.AreEqual(911, result.EmployeeId);
}
}
public void Update(CardSet cardSet)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CardSetID", cardSet.CardSetID);
p.Add("@CardSetName", cardSet.CardSetName);
connection.Execute("CardSetUpdate", p, commandType: CommandType.StoredProcedure);
}
}
public void Delete(int categoryID)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CategoryID", categoryID);
connection.Execute("CategoryDelete", p, commandType: CommandType.StoredProcedure);
}
}
public void Insert(Category category)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CategoryName", category.CategoryName);
connection.Execute("CategoryInsert", p, commandType: CommandType.StoredProcedure);
}
}
public void Update(ModifierType modifierType)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@ModifierTypeID", modifierType.ModifierTypeID);
p.Add("@ModifierTypeName", modifierType.ModifierTypeName);
connection.Execute("ModifierTypeUpdate", p, commandType: CommandType.StoredProcedure);
}
}
/// <summary>
/// 获取前一条新闻信息
/// </summary>
/// <param name="classify"></param>
/// <param name="device"></param>
/// <param name="publishDate"></param>
/// <returns></returns>
public async Task <News> GetPreNewsInfoAsync(string classify, string device, DateTime publishDate)
{
string sql = "SELECT TOP 1 * FROM dbo.News WHERE NewStatus=1 AND Classify=@Classify AND Device=@Device AND PublishDate<@PublishDate ORDER BY PublishDate DESC";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var model = await conn.QueryFirstOrDefaultAsync <News>(sql, new { Classify = classify, Device = device, PublishDate = publishDate });
return(model);
}
}
/// <summary>
/// 查询新闻详情
/// </summary>
/// <param name="classify"></param>
/// <param name="Id"></param>
/// <returns></returns>
public async Task <News> GetNewsInfoAsync(string classify, int Id)
{
string sql = "SELECT * FROM dbo.News WHERE Classify=@Classify AND ID=@ID";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var model = await conn.QueryFirstOrDefaultAsync <News>(sql, new { Classify = classify, ID = Id });
return(model);
}
}
public List <CardModifierView> SelectByCardID(int cardID)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
DynamicParameters p = new DynamicParameters();
p.Add("@CardID", cardID);
return(connection.Query <CardModifierView>("CardModifierSelectByCardID", p,
commandType: CommandType.StoredProcedure).ToList());
}
}
/// <summary>
/// 异步查询数据（广告信息）
/// </summary>
/// <param name="imgStatus"></param>
/// <param name="device"></param>
/// <returns></returns>
public async Task <IEnumerable <ImgAdv> > GetImgAdvListByStatusAsync(int?imgStatus, string device)
{
string sql = "SELECT * FROM dbo.ImgAdv WHERE ImgStatus=@ImgStatus AND Device=@Device";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var list = await conn.QueryAsync <ImgAdv>(sql, new { ImgStatus = imgStatus, Device = device });
return(list);
}
}
public Employee GetEmployeeNumber(string employeeNumber)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@EmployeeNumber", employeeNumber);
return
(connection.Query <Employee>("EmployeeSelectByEmployeeNumber", p,
commandType: CommandType.StoredProcedure).FirstOrDefault());
}
}
/// <summary>
/// 异步查询新闻信息
/// </summary>
/// <param name="device"></param>
/// <param name="classify"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task <IEnumerable <News> > GetNewsAsync(string device, string classify, int pageIndex, int pageSize)
{
string sql = $"SELECT* FROM(SELECT ROW_NUMBER() OVER(ORDER BY IsTop DESC, PublishDate DESC) rowId,* FROM dbo.News WHERE Classify = @Classify and Device = @Device and NewStatus = 1) AS T WHERE t.rowId BETWEEN {pageSize * pageIndex + 1} AND {pageSize + pageSize * pageIndex}";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var list = await conn.QueryAsync <News>(sql, new { Classify = classify, Device = device });
return(list);
}
}
public Category Select(int categoryID)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CategoryID", categoryID);
return
(connection.Query <Category>("CategorySelectByID", p, commandType: CommandType.StoredProcedure)
.FirstOrDefault());
}
}
public ModifierType Select(int modifierTypeID)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@ModifierTypeID", modifierTypeID);
return
(connection.Query <ModifierType>("ModifierTypeSelectByID", p, commandType: CommandType.StoredProcedure)
.FirstOrDefault());
}
}
public void Insert(CardModifier cardModifier)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
DynamicParameters p = new DynamicParameters();
p.Add("@CardID", cardModifier.CardID);
p.Add("@ModifierTypeID", cardModifier.ModifierTypeID);
p.Add("@ModifierValue", cardModifier.ModifierValue);
p.Add("@InstructionText", cardModifier.InstructionText);
connection.Execute("CardModifierInsert", p, commandType: CommandType.StoredProcedure);
}
}
public async Task <IEnumerable <Book> > GetAll()
{
try
{
var connection = new SqlConnection(ConfigurationSettings.GetConnectionString());
return(await connection.QueryAsync <Book>("BookAll", commandType : CommandType.StoredProcedure));
}
catch (Exception ex)
{
throw;
}
}
public void Update(Card card)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
DynamicParameters p = new DynamicParameters();
p.Add("@CardID", card.CardID);
p.Add("@CardSetID", card.CardSetID);
p.Add("@CardTitle", card.CardTitle);
p.Add("@ImagePath", card.ImagePath);
p.Add("@CardCost", card.CardCost);
connection.Execute("CardUpdate", p, commandType: CommandType.StoredProcedure);
}
}
public async Task Delete(int bookId)
{
try
{
var connection = new SqlConnection(ConfigurationSettings.GetConnectionString());
var p = new DynamicParameters();
p.Add("@BookId", bookId);
await connection.QueryAsync <Book>("BookDelet", p, commandType : CommandType.StoredProcedure);
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 添加用户购买信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public async Task <bool> AddInsuranceBuyer(Temp_InsuranceBuyerInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Temp_InsuranceBuyerInfo(");
strSql.Append("BuyerName,Gender,Relation,CreditNumber,Mobile,Email,CityName,SubmitDate,State,UpdateDate,UpdateUser,ProductCode,CarNo)");
strSql.Append(" values (");
strSql.Append("@BuyerName,@Gender,@Relation,@CreditNumber,@Mobile,@Email,@CityName,@SubmitDate,@State,@UpdateDate,@UpdateUser,@ProductCode,@CarNo)");
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var count = await conn.ExecuteAsync(strSql.ToString(), new { BuyerName = model.BuyerName, Gender = model.Gender, Relation = model.Relation, CreditNumber = model.CreditNumber, Mobile = model.Mobile, Email = model.Email, CityName = model.CityName, SubmitDate = model.SubmitDate, State = model.State, UpdateDate = model.UpdateDate, UpdateUser = model.UpdateUser, ProductCode = model.ProductCode, CarNo = model.CarNo });
return(count > 0);
}
}
public Book GetBookById(int bookId)
{
try
{
var connection = new SqlConnection(ConfigurationSettings.GetConnectionString());
var p = new DynamicParameters();
p.Add("@BookId", bookId);
return(connection.Query <Book>("BookById", p, commandType: CommandType.StoredProcedure).FirstOrDefault());
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 查询产品详情
/// </summary>
/// <param name="productCode"></param>
/// <returns></returns>
public async Task <ProductInfo> GetProductInfo(string productCode)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT ProductCode,ProductName,ProductDesc,ProductFeature,SuitableCrowd,SuitableAge,InsuranceTime ");
strSql.Append(" ,InsuranceMoney,InsuranceProfit,ProductType,ImgUrlDetail,ProductExplain,InsuranceInfo ");
strSql.Append(" ,InsuranceCase,PaymentService,MinPrice,IsPurchase,ImgUrlApp,ImgUrlCode FROM ProductInfo ");
strSql.Append(" where ProductCode=@ProductCode ");
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var model = await conn.QueryFirstOrDefaultAsync <ProductInfo>(strSql.ToString(), new { ProductCode = productCode });
return(model);
}
}
public CardView SelectView(int cardID)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CardID", cardID);
using (var multi = connection.QueryMultiple("CardSelectView", p, commandType: CommandType.StoredProcedure))
{
CardView view = multi.Read <CardView>().Single();
view.Categories = multi.Read <Category>().ToList();
view.Modifiers = multi.Read <CardModifierView>().ToList();
return(view);
}
}
}
public Card Insert(Card card)
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@CardSetID", card.CardSetID);
p.Add("@CardTitle", card.CardTitle);
p.Add("@CardCost", card.CardCost);
p.Add("@ImagePath", card.ImagePath);
p.Add("@CardID", null, dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("CardInsert", p, commandType: CommandType.StoredProcedure);
card.CardID = p.Get <int>("@CardID");
}
return(card);
}
public async Task <IEnumerable <BookView> > GetAuthorBook()
{
try
{
var conection = new SqlConnection(ConfigurationSettings.GetConnectionString());
return(await conection.QueryAsync <BookView, Author, BookView>("AuthorBook", (book, author) =>
{
book.Author = author;
return book;
}, splitOn : "Name",
commandType : CommandType.StoredProcedure));
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 查询新闻总行数
/// </summary>
/// <returns></returns>
public async Task <int> GetNewsCount(string device, string classify)
{
string sql = "SELECT COUNT(1) FROM dbo.News WHERE Classify = @Classify and Device = @Device and NewStatus = 1";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var count = await conn.QuerySingleAsync(typeof(Int32), sql, new { Classify = classify, Device = device });
if (count.Equals(null) || count.Equals(DBNull.Value))
{
return(0);
}
else
{
return((int)count);
}
}
}
/// <summary>
/// 分页查询产品
/// </summary>
/// <param name="productType"></param>
/// <param name="start"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task <IEnumerable <ProductInfo> > GetGetProductList(string productType, int pageIndex, int pageSize)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY OrderNum) rowId,ProductCode,ProductName,ProductDesc,ProductFeature,ImgUrlList ,ImgUrlApp,MinPrice FROM dbo.ProductInfo WHERE IsValid=1 ");
if (!string.IsNullOrEmpty(productType))
{
strSql.Append(" AND ProductType=@ProductType");
}
strSql.Append($" ) AS T WHERE T.rowId BETWEEN {pageSize*pageIndex+1} AND {pageSize+ pageSize * pageIndex}");
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var list = await conn.QueryAsync <ProductInfo>(strSql.ToString(), new { ProductType = productType });
return(list);
}
}
public void EmployeeGetAlltest()
{
using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
//var result = connection.GetList<Employee>();
//var enumerable = result as IList<Employee> ?? result.ToList();
//foreach (var model in enumerable)
//{
// Console.WriteLine("EmployeeId: {0}", model.EmployeeId);
// Console.WriteLine("FullName: {0} {1} {2}", model.FirstName, model.MiddleName, model.LastName);
// Console.WriteLine("Employee Number: {0}", model.EmployeeNumber);
// Console.WriteLine("ImageEmployee {0}", model.ImageEmployee);
// Console.WriteLine("===================================");
//}
//Assert.AreEqual(1002,enumerable.Count());
}
}
/// <summary>
/// 获得主页主推产品
/// </summary>
/// <param name="Top"></param>
/// <returns></returns>
public async Task <IEnumerable <ProductInfo> > GetRecommendProduct(int Top)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top);
}
strSql.Append(" ProductCode,ProductName,ProductFeature,MinPrice,ImgUrlList,ImgUrlApp,ProductDesc ");
strSql.Append(" FROM ProductInfo ");
strSql.Append(" where IsValid=1 and IsRecommend=1 ");
strSql.Append(" order by OrderNum ");
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var list = await conn.QueryAsync <ProductInfo>(strSql.ToString());
return(list);
}
}
/// <summary>
/// 添加浏览记录
/// </summary>
/// <param name="newsId"></param>
/// <param name="date"></param>
public void NewsVisitAsync(int newsId, string date)
{
string sql_exists = "SELECT COUNT(1) FROM NewsVisitor WHERE NewsID =@NewsID AND Dates=@Dates";
using (IDbConnection conn = new SqlConnection(ConfigurationSettings.GetConnectionString()))
{
var count = conn.QuerySingle(typeof(Int32), sql_exists, new { NewsID = newsId, Dates = date });
string sql = string.Empty;
if ((int)count > 0)
{
sql = "update NewsVisitor set clicks=clicks+1 where NewsID=@NewsID and Dates=@Dates";
//存在
conn.Execute(sql, new { NewsID = newsId, Dates = date });
}
else
{
sql = "insert into NewsVisitor(NewsID,Dates,Clicks)values(@NewsID,@Dates,1)";
//不存在
conn.Execute(sql, new { NewsID = newsId, Dates = date });
}
}
}
