public Article ConvertArticle(IDataReader dr) { Article a = new Article(); a.IDx = int.Parse(dr["idx"].ToString()); a.Title = dr["title"].ToString(); a.Content = dr["content"].ToString(); a.Icon = dr["icon"] == DBNull.Value ? string.Empty : dr["icon"].ToString(); a.Url = dr["Url"] == DBNull.Value ? string.Empty : dr["Url"].ToString(); if (dr["click"] != null && dr["click"] != DBNull.Value) { a.Click = int.Parse(dr["click"].ToString()); } else a.Click = 0; if (dr["istop"] != null && dr["istop"] != DBNull.Value) { a.IsTop = int.Parse(dr["istop"].ToString()); } if (dr["indate"] != null && dr["indate"] != DBNull.Value) { a.InDate = DateTime.Parse(dr["indate"].ToString()); } else a.InDate = new DateTime(2000, 1, 1); if (dr["authorid"] != null && dr["authorid"] != DBNull.Value) { a.AuthorID = int.Parse(dr["authorid"].ToString()); } else a.AuthorID = 0; if (dr["cid"] != null && dr["cid"] != DBNull.Value) { a.CID = int.Parse(dr["cid"].ToString()); } else a.CID = 0; if (dr["cp1"] != null && dr["cp1"] != DBNull.Value) { a.CP1 = int.Parse(dr["cp1"].ToString()); } else a.CP1 = 0; if (dr["cp2"] != null && dr["cp2"] != DBNull.Value) { a.CP2 = int.Parse(dr["cp2"].ToString()); } else a.CP2 = 0; a.Kwd = dr["kwd"].ToString(); a.Desc = dr["desc"].ToString(); return a; }
public Helper.PageModel GetPageModelList(int page, int pageSize, Article art) { List<Article> l = GetList(page, pageSize, art); Helper.PageModel pm = new Helper.PageModel(); pm.List = l; string recordcountSql = "select count(1) from `article` " + BuildWhere(art); int recordcount = db.ExecScalarInt(recordcountSql); int pagecount = (int)Math.Ceiling(recordcount * 1.0 / pageSize); pm.Page = page; pm.Pagesize = pageSize; pm.TotalRecord = recordcount; return pm; }
public List<Article> GetList(int page, int pageSize, Article art) { if (page < 1) page = 1; string where = BuildWhere(art); List<Article> l = new List<Article>(); int beginIndex = 0; if (page > 1) { beginIndex = (page - 1) * pageSize; } string sql = string.Format(@"select * from `article` {0} order by idx desc limit {1},{2}", where, beginIndex, pageSize); IDataReader dr = db.GetReader(sql); using (dr) { while (dr.Read()) { l.Add(DaoUtil.Instance.ConvertArticle(dr)); } } return l; }
public int Insert(Article art) { DbConnection conn = db.GetConn(); string insert = @"insert into `article`(title,content,icon,url,click,authorid,cid,cp1,cp2,indate,kwd,`desc`,`istop`)values(?title,?content,?icon,?url,?click, ?authorid,?cid,?cp1,?cp2,?indate,?kwd,?desc,?istop)"; string max = @"select idx from `article` order by `idx` desc limit 0,1 "; int result = 0; using (conn) { Dictionary<string, object> dic = new Dictionary<string, object>(); dic.Add("?title", MySqlDbType.VarChar); dic.Add("?content", MySqlDbType.VarChar); dic.Add("?icon", MySqlDbType.VarChar); dic.Add("?url", MySqlDbType.VarChar); dic.Add("?click", MySqlDbType.Int32); dic.Add("?authorid", MySqlDbType.Int32); dic.Add("?cid", MySqlDbType.Int32); dic.Add("?cp1", MySqlDbType.Int32); dic.Add("?cp2", MySqlDbType.Int32); dic.Add("?indate", MySqlDbType.Datetime); dic.Add("?kwd", MySqlDbType.VarChar); dic.Add("?desc", MySqlDbType.VarChar); dic.Add("?istop", MySqlDbType.Int32); List<object> vals = new List<object>(); vals.Add(art.Title); vals.Add(art.Content); vals.Add(art.Icon); vals.Add(art.Url); vals.Add(art.Click); vals.Add(art.AuthorID); vals.Add(art.CID); vals.Add(art.CP1); vals.Add(art.CP2); vals.Add(art.InDate); vals.Add(art.Kwd); vals.Add(art.Desc); vals.Add(art.IsTop); IDbDataParameter[] arr = db.GetParams(dic, vals); db.ExecNonQuery(conn, insert, arr); result = db.ExecScalarInt(conn, max); conn.Close(); } return result; }
private string BuildWhere(Article art) { string where = ""; if (art != null) { string wh = " "; if (art.IDx != 0) wh += " and idx=" + art.IDx; if (!string.IsNullOrEmpty(art.Kwd)) wh += " and( title like '%" + art.Kwd + "%' or kwd like '%" + art.Kwd + "%' or `desc` like '%" + art.Kwd + "%' )"; if (art.CID != 0) wh += " and cid= " + art.CID; if (art.CP1 != 0) wh += " and cp1=" + art.CP1; if (art.CP2 != 0) wh += " and CP2=" + art.CP2; if (art.AuthorID != 0) wh += " and AuthorID=" + art.AuthorID; if (!string.IsNullOrEmpty(wh)) { where = " where 1=1 " + wh; } } return where; }
public Article Update(Article art) { if (art.IDx <= 0) throw new Exception("no such article!"); string sql = string.Format(@"update `article` set title=?title,content=?content,icon=?icon, url=?url,click=?click,authorid=?authorid,cid=?cid,cp1=?cp1,cp2=?cp2,indate=?indate,kwd=?kwd,`desc`=?desc,istop=?istop where idx={0}", art.IDx.ToString()); //db.ExecNonQuery( List<string> names = new List<string>() { "?title","?content","?icon","?url","?click","?authorid","?cid","?cp1","?cp2","?indate","?kwd","?desc","?istop" }; List<object> vals = new List<object>() { art.Title,art.Content,art.Icon,art.Url,art.Click,art.AuthorID,art.CID,art.CP1,art.CP2,art.InDate,art.Kwd,art.Desc, art.IsTop }; List<object> ts = new List<object>() { MySqlDbType.VarChar,MySqlDbType.VarChar,MySqlDbType.VarChar,MySqlDbType.VarChar,MySqlDbType.Int32, MySqlDbType.Int32,MySqlDbType.Int32,MySqlDbType.Int32,MySqlDbType.Int32, MySqlDbType.Datetime,MySqlDbType.VarChar,MySqlDbType.VarChar,MySqlDbType.Int32 }; IDbDataParameter[] idp = db.GetParams(names, vals, ts); db.ExecNonQuery(sql, idp); return art; }
public void TestartList() { MySqlArticle msa = new MySqlArticle(str); List<Article> list = new List<Article>(); Article ap = new Article(); ap.CID = 1; PageModel pm = msa.GetPageModelList(1, 2, ap); list = pm.List as List<Article>; foreach (Article art in list) { Console.WriteLine("idx:" + art.IDx + ", title=" + art.Title); } Console.WriteLine("TotalRecord:" + pm.TotalRecord); Console.WriteLine("TotalPage:" + pm.TotalPage); Console.WriteLine("CurrentPage:" + pm.Page); }