public static int Update(SqlDbHelper dbHelper, TirePatternModel model) { return(dbHelper.ExecuteNonQuery(@"UPDATE Tuhu_productcatalog.dbo.PatternArticle SET Image = @Image, Title = @Title, Describe = @Describe, Author = @Author, Date = @Date, ArticleLink = @ArticleLink, IsShow = @IsShow, IsActive=@IsActive, Brand = @Brand, Pattern = @Pattern, UpdateTime = GETDATE() WHERE PKID = @PKID; ", CommandType.Text, new SqlParameter[] { new SqlParameter("@Image", model.Image), new SqlParameter("@Title", model.Title), new SqlParameter("@Describe", model.Describe), new SqlParameter("@Author", model.Author), new SqlParameter("@Date", model.Date.Value), new SqlParameter("@ArticleLink", model.ArticleLink), new SqlParameter("@IsShow", model.IsShow ?? false), new SqlParameter("@Brand", model.Brand), new SqlParameter("@Pattern", model.Pattern), new SqlParameter("@PKID", model.PKID), new SqlParameter("@IsActive", model.IsActive) })); }
public static int SaveAddMany(TirePatternModel model) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { dbHelper.BeginTransaction(); var result = -99; foreach (var brand in model.Patterns) { foreach (var pattern in brand.Patterns.Split(',')) { TirePatternModel tempModel = new TirePatternModel() { Image = model.Image, Title = model.Title, Describe = model.Describe, Author = model.Author, Date = model.Date, ArticleLink = model.ArticleLink, IsShow = false, IsActive = model.IsActive, Brand = brand.Brand, Pattern = pattern }; result = Insert(dbHelper, tempModel); if (result <= 0) { dbHelper.Rollback(); } } } dbHelper.Commit(); return(result); } }
public static int GetListCount(SqlDbHelper dbHelper, TirePatternModel model) { var OBJ = dbHelper.ExecuteScalar(@"SELECT COUNT(1) FROM ( SELECT DISTINCT VP.CP_Brand AS Brand, VP.CP_Tire_Pattern AS Pattern FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) WHERE VP.PID LIKE 'TR-%' AND ISNULL(VP.CP_Brand, '') <> '' AND ISNULL(VP.CP_Tire_Pattern, '') <> '' AND VP.OnSale=1 AND VP.stockout=0 ) AS T LEFT JOIN Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK ) ON PA.Brand = T.Brand AND PA.Pattern = T.Pattern WHERE ( T.Brand = @Brand OR @Brand IS NULL ) AND ( T.Pattern = @Pattern OR @Pattern IS NULL ) AND ( PA.Title = @Title OR @Title IS NULL ) AND ( PA.Author = @Author OR @Author IS NULL ) AND ( @Count = 0 OR @Count = 1 AND PA.PKID > 0 OR @Count = 2 AND PA.PKID IS NULL ) AND ( @ShowCount = 0 OR @ShowCount = 1 AND T.Pattern IN ( SELECT PAA.Pattern FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK ) WHERE PAA.IsShow = 1 ) OR @ShowCount = 2 AND T.Pattern NOT IN ( SELECT PAA.Pattern FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK ) WHERE PAA.IsShow = 1 ) AND PA.PKID > 0 )" , CommandType.Text, new SqlParameter[] { new SqlParameter("@Brand", String.IsNullOrWhiteSpace(model.Brand)?null:model.Brand), new SqlParameter("@Pattern", String.IsNullOrWhiteSpace(model.Pattern)?null:model.Pattern), new SqlParameter("@Title", String.IsNullOrWhiteSpace(model.Title)?null:model.Title), new SqlParameter("@Author", String.IsNullOrWhiteSpace(model.Author)?null:model.Author), new SqlParameter("@Count", model.Count), new SqlParameter("@ShowCount", model.ShowCount), }); return(Convert.ToInt32(OBJ)); }
public static bool CanShow(TirePatternModel model) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { var OBJ = dbHelper.ExecuteScalar(@"SELECT COUNT(1) FROM Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK ) WHERE PA.Pattern = @Pattern AND PA.Brand = @Brand AND PKID<>@PKID AND PA.IsShow = 1" , CommandType.Text, new SqlParameter[] { new SqlParameter("@Brand", model.Brand), new SqlParameter("@Pattern", model.Pattern), new SqlParameter("@PKID", model.PKID) }); return(Convert.ToInt32(OBJ) > 0); } }
public static int SaveUpdateOrAdd(TirePatternModel model) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { var result = -99; if (model.PKID > 0) { result = Update(dbHelper, model); } else { result = Insert(dbHelper, model); } return(result); } }
public static int Insert(SqlDbHelper dbHelper, TirePatternModel model) { return(dbHelper.ExecuteNonQuery(@"INSERT INTO Tuhu_productcatalog.dbo.PatternArticle ( Image, Title, Describe, Author, Date, ArticleLink, IsShow, Brand, Pattern, IsActive) VALUES ( @Image, @Title, @Describe, @Author, @Date, @ArticleLink, @IsShow, @Brand, @Pattern, @IsActive)", CommandType.Text, new SqlParameter[] { new SqlParameter("@Image", model.Image), new SqlParameter("@Title", model.Title), new SqlParameter("@Describe", model.Describe), new SqlParameter("@Author", model.Author), new SqlParameter("@Date", model.Date.Value), new SqlParameter("@ArticleLink", model.ArticleLink), new SqlParameter("@IsShow", model.IsShow ?? false), new SqlParameter("@Brand", model.Brand), new SqlParameter("@Pattern", model.Pattern), new SqlParameter("@IsActive", model.IsActive) })); }
public static bool CanShow(TirePatternModel model) => DalPattern.CanShow(model);
public static int SaveUpdateOrAdd(TirePatternModel model) => DalPattern.SaveUpdateOrAdd(model);
public static int SaveAddMany(TirePatternModel model) => DalPattern.SaveAddMany(model);
public static IEnumerable <TirePatternModel> SelectList(TirePatternModel model, PagerModel pager) => DalPattern.SelectList(model, pager);
public static IEnumerable <TirePatternModel> SelectList(TirePatternModel model, PagerModel pager) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { pager.TotalItem = GetListCount(dbHelper, model); return(dbHelper.ExecuteDataTable(@"SELECT T.Brand, T.Pattern, PA.Image, PA.Title, PA.Describe, PA.Author, PA.Date, PA.ArticleLink, PA.IsShow, PA.IsActive, PA.PKID FROM ( SELECT DISTINCT VP.CP_Brand AS Brand, VP.CP_Tire_Pattern AS Pattern FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) WHERE VP.PID LIKE 'TR-%' AND ISNULL(VP.CP_Brand, '') <> '' AND ISNULL(VP.CP_Tire_Pattern, '') <> '' AND VP.OnSale=1 AND VP.stockout=0 ) AS T LEFT JOIN Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK ) ON PA.Brand = T.Brand AND PA.Pattern = T.Pattern WHERE ( T.Brand = @Brand OR @Brand IS NULL ) AND ( T.Pattern = @Pattern OR @Pattern IS NULL ) AND ( PA.Title = @Title OR @Title IS NULL ) AND ( PA.Author = @Author OR @Author IS NULL ) AND ( @Count = 0 OR @Count = 1 AND PA.PKID > 0 OR @Count = 2 AND PA.PKID IS NULL ) AND ( @ShowCount = 0 OR @ShowCount = 1 AND T.Pattern IN ( SELECT PAA.Pattern FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK ) WHERE PAA.IsShow = 1 ) OR @ShowCount = 2 AND T.Pattern NOT IN ( SELECT PAA.Pattern FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK ) WHERE PAA.IsShow = 1 ) AND PA.PKID > 0 ) ORDER BY T.Pattern OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;", CommandType.Text, new SqlParameter[] { new SqlParameter("@Brand", String.IsNullOrWhiteSpace(model.Brand)?null:model.Brand), new SqlParameter("@Pattern", String.IsNullOrWhiteSpace(model.Pattern)?null:model.Pattern), new SqlParameter("@Title", String.IsNullOrWhiteSpace(model.Title)?null:model.Title), new SqlParameter("@Author", String.IsNullOrWhiteSpace(model.Author)?null:model.Author), new SqlParameter("@Count", model.Count), new SqlParameter("@ShowCount", model.ShowCount), new SqlParameter("@PageIndex", pager.CurrentPage), new SqlParameter("@PageSize", pager.PageSize), }).ConvertTo <TirePatternModel>()); } }