public void UpdatePath(int id, string path) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_staticpage SET PagePath=@path WHERE Page_Id =@id", new { id = id, path = path }); } }
public bool InsertToMapBatch(int articleId, string tagIds) { int[] tags = Array.ConvertAll <string, int>(tagIds.Split(','), s => Convert.ToInt32(s)); string sql = "INSERT INTO cms_tagmap(m_TagId,m_ArticleId)VALUES"; for (int i = 0; i < tags.Length; i++) { if (i == 0) { sql += "(" + tags[i] + "," + articleId + ")"; } else { sql += ",(" + tags[i] + "," + articleId + ")"; } } using (IDbConnection conn = SqlString.GetSQLiteConnection()) { int i = conn.Execute(sql); if (i > 0) { return(true); } else { return(false); } } }
public void Update(Model.Store.Order entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE store_order SET ItemId=@ItemId,Price=@Price,Num=@Num,Des=@Des,Address=@Address,Phone=@Phone,State=@State,AddTime=@AddTime,IsDeliver=@IsDeliver,TrackingNumber=@TrackingNumber,DeliverTime=@DeliverTime,IsReceipt=@IsReceipt WHERE Order_Id =@Order_Id", entity); } }
public void Update(Model.Store.Category entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE store_category SET GroupCode=@GroupCode,CategoryName=@CategoryName WHERE Category_Id =@Category_Id", entity); } }
public void Update(Model.Store.Member entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE store_member SET AvatarPath=@AvatarPath,Phone=@Phone,Password=@Password,AddTime=@AddTime,State=@State,LastTime=@LastTime,LoginName=@LoginName WHERE Member_Id =@Member_Id", entity); } }
public List <Model.Store.Order> ToPagedList(int pageIndex, int pageSize, string whereStr, string keySelector, out int count) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { string sql = "select a.*,b.Item_Id,b.Title,d.Member_Id,d.LoginName from store_order as a LEFT JOIN store_item as b on a.ItemId=b.Item_Id LEFT JOIN store_member as d on a.MemberId=d.Member_Id"; string sql01 = "select count(*) from store_order"; if (string.IsNullOrEmpty(whereStr) == false) { sql01 += " where " + whereStr; } count = conn.Query <int>(sql01).SingleOrDefault(); string query = "SELECT * from (" + sql + ")as c {0} ORDER BY " + keySelector + " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize; if (string.IsNullOrEmpty(whereStr) == false) { query = string.Format(query, " where " + whereStr); } else { query = string.Format(query, ""); } return(conn.Query <Model.Store.Order, Model.Store.Item, Model.Store.Member, Model.Store.Order>(query, (order, item, member) => { if (item != null) { order.Item = item; } if (member != null) { order.Member = member; } return order; }, null, null, "Item_Id,Member_Id", null, null).ToList()); } }
public void Update(Model.CMS.Article entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_article SET ColumnId=@ColumnId,Tags=@Tags,Tagids=@TagIds,ArticleContent=@ArticleContent,Title=@Title,CreateTime=@CreateTime,LastTime=@LastTime,Browse=@Browse,Author=@Author,ImgUrl=@ImgUrl,Digest=@Digest,SEO_Title=@SEO_Title,SEO_Keywords=@SEO_Keywords,SEO_DES=@SEO_DES,IsTop=@IsTop,IsShow=@IsShow,Source=@Source,SourceLink=@SourceLink,Praise=@Praise WHERE Article_Id =@Article_Id", entity); } }
public List <Model.Sys.User> ToPagedList(int pageIndex, int pageSize, string keySelector, out int count) { /* * firstIndex:起始索引 * pageSize:每页显示的数量 * orderColumn:排序的字段名 * sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句 */ using (IDbConnection conn = SqlString.GetSQLiteConnection()) { string sql = "select a.*,b.* from sys_user as a left join sys_role as b on a.RoleId=b.Role_Id"; string sql01 = "select count(*) from sys_user"; count = conn.Query <int>(sql01).SingleOrDefault(); //string query = "select top " + pageSize + " o.* from (select row_number() over(order by " + keySelector + ") as rownumber,* from(" + sql + ") as oo) as o where rownumber>" + (pageIndex-1) * pageSize; string query = "SELECT * from (" + sql + ")as c ORDER BY " + keySelector + " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize; return(conn.Query <Model.Sys.User, Model.Sys.Role, Model.Sys.User>(query, (user, role) => { if (role != null) { user.Role = role; } return user; }, null, null, "Role_Id", null, null).ToList()); } }
public void Update(Model.Store.Item entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE store_item SET Item_Img=@Item_Img,Title=@Title,CategoryId=@CategoryId,Content=@Content,Price=@Price,Stock=@Stock,AddTime=@AddTime,State=@State,Flag=@Flag WHERE Item_Id =@Item_Id", entity); } }
public void DeteteToMap(int articleId) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("DELETE FROM cms_tagmap WHERE m_ArticleId=@m_ArticleId", new { m_ArticleId = articleId }); } }
public void Delete(string ids) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute(string.Format("DELETE FROM cms_staticpage WHERE Page_Id in ({0})", ids)); } }
public void Update(Model.Sys.Role entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE sys_role SET RoleName=@RoleName,ActionIds=@ActionIds WHERE Role_Id =@Role_Id", entity); } }
public void Update(Model.CMS.TempPage entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_temppage SET TempName=@TempName,TempByname=@TempByname,TempParameter=@TempParameter,TempPath=@TempPath,TempContent=@TempContent,CreateTime=@CreateTime,LastTime=@LastTime WHERE TempPage_Id =@TempPage_Id", entity); } }
public List <Model.CMS.TempPage> GetAll() { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { return(conn.Query <Model.CMS.TempPage>("select * from cms_temppage").ToList()); } }
public List <Model.Store.StoreInfo> GetAll() { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { return(conn.Query <Model.Store.StoreInfo>("select * from store_info").ToList()); } }
public void Update(Model.CMS.Comment entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_comment SET ArticleId=@ArticleId,Content=@Content,CreateTime=@CreateTime,Nickname=@Nickname,Email=@Email,VoteFavour=@VoteFavour,VoteOppose=@VoteOppose WHERE Comment_Id =@Comment_Id", entity); } }
public void Delete(string ids) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute(string.Format("DELETE FROM store_info WHERE Store_Id in ({0})", ids)); } }
public System.Collections.Generic.List <Model.CMS.Column> GetAll() { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { return(conn.Query <Model.CMS.Column>("select * from cms_column").ToList()); } }
public System.Collections.Generic.List <Model.CMS.Article> ToSearchList(int pageIndex, int pageSize, string searchTitle, int sort, out int count) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { //sqlite使用||链接字符串 string sql01 = "select count(Article_Id) from cms_article where Title like '%'||@Title||'%'"; count = conn.Query <int>(sql01, new { Title = searchTitle }).SingleOrDefault(); Model.CMS.Article articleTemp = new Model.CMS.Article(); string sql = "select a.*,b.* from cms_article as a left join cms_column as b on a.ColumnId=b.Column_Id"; string sort_str = "DESC"; if (sort == 1) { sort_str = "DESC"; } else if (sort == 2) { sort_str = "ASC"; } string query = "SELECT * from (" + sql + ")as c where Title like '%'||@Title||'%' ORDER BY CreateTime " + sort_str + " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize; return(conn.Query <Model.CMS.Article, Model.CMS.Column, Model.CMS.Article>(query, (article, column) => { if (column != null) { article.Column = column; } return article; }, new { Title = searchTitle }, null, "Column_Id", null, null).ToList()); } }
public void Update(Model.CMS.Column entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_column SET Sort=@Sort,ColumnName=@ColumnName WHERE Column_Id =@Column_Id", entity); } }
public void Update(Model.CMS.Article entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_article SET ColumnId=@ColumnId,Tags=@Tags,Tagids=@TagIds,ArticleContent=@ArticleContent,Title=@Title,CreateTime=@CreateTime,LastTime=@LastTime,Browse=@Browse,Author=@Author,IsTop=@IsTop,IsShow=@IsShow WHERE Article_Id =@Article_Id", entity); } }
public List <Model.Sys.Role> GetAll() { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { return(conn.Query <Model.Sys.Role>("select a.* from sys_role as a").ToList()); } }
public void Delete(string ids) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute(string.Format("DELETE FROM cms_comment WHERE Comment_Id in ({0})", ids)); } }
public void Update(Model.CMS.StaticPage entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_staticpage SET PageName=@PageName,PageParameter=@PageParameter,CreateTime=@CreateTime,LastTime=@LastTime,TempPageId =@TempPageId WHERE Page_Id =@Page_Id", entity); } }
public void Update(Model.Store.StoreInfo entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE store_info SET StoreName=@StoreName,StoreLogoPath=@StoreLogoPath,StoreDes=@StoreDes,StoreSwitch=@StoreSwitch,CreateTime=@CreateTime,LastTime=@LastTime WHERE Store_Id =@Store_Id", entity); } }
public Model.CMS.TempPage Find(int id) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { string query = "select a.* from cms_temppage as a WHERE a.TempPage_Id = @id"; return(conn.Query <Model.CMS.TempPage>(query, new { id = id }).SingleOrDefault()); } }
public Model.Store.StoreInfo Find(int id) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { string query = "select a.* from store_info as a WHERE a.Store_Id = @id"; return(conn.Query <Model.Store.StoreInfo>(query, new { id = id }).SingleOrDefault()); } }
public Model.CMS.Column Find(int id) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { string query = "select a.* from cms_column as a WHERE a.Column_Id = @id"; return(conn.Query <Model.CMS.Column>(query, new { id = id }).SingleOrDefault()); } }
public void Update(Model.Sys.User entity) { using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE sys_user SET RoleId=@RoleId,Name=@Name,Password=@Password,NickName=@NickName,Avatar=@Avatar,Email=@Email," + "Phone=@Phone,Qq=@Qq,AddTime=@AddTime,LastLoginTime=@LastLoginTime,LastLoginIp=@LastLoginIp,IsSuperUser=@IsSuperUser WHERE User_Id =@User_Id", entity); } }
public void Update(Model.CMS.Tag entity) { entity.LastTime = DateTime.Now; using (IDbConnection conn = SqlString.GetSQLiteConnection()) { conn.Execute("UPDATE cms_tag SET TagName=@TagName,TagDes=@TagDes,LastTime=@LastTime WHERE Tag_Id =@Tag_Id", entity); } }