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