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 }); } } }