using System;
using System.Collections;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using Solog.Model;
using Solog.IDbTask;
namespace Solog.AcessTask
...{
/**//// <summary>
/// 数据操作层
/// </summary>
public class SqlProfile:IDataTask
...{
提取Blog文章#region 提取Blog文章
/**//// <summary>
/// 提取Blog文章数据
/// </summary>
/// <returns>ArticleInfo 实体隐藏数组</returns>
public ArticleInfo[] ListArticle(string sqlfilter)
...{
GetData getdata = new GetData();
ArrayList arrArt = new ArrayList();
if (sqlfilter != string.Empty && sqlfilter != null)
...{
int cateId = Convert.ToInt32(sqlfilter);
string cmdsql = "";
cmdsql += "select L.*,C.cate_Name from blog_Content AS L,blog_Category AS C where L.log_CateID=C.cate_ID and L.log_CateID="+cateId;
cmdsql += " order by log_IsTop ASC,log_ID desc";
OleDbDataReader dr = getdata.GetReader(cmdsql);
while (dr.Read())
...{
ArticleInfo inf = SqlHelper.ConvertToArticleInfo(dr, new ArticleInfo());
arrArt.Add(inf);
}
dr.Close();
}
else
...{
string cmdsql = "";
cmdsql += "select L.*,C.cate_Name from blog_Content AS L,blog_Category AS C where L.log_CateID=C.cate_ID";
cmdsql += " order by log_IsTop ASC,log_ID desc";
OleDbDataReader dr = getdata.GetReader(cmdsql);
while (dr.Read())
...{
ArticleInfo inf = SqlHelper.ConvertToArticleInfo(dr, new ArticleInfo());
arrArt.Add(inf);
}
dr.Close();
}
return (ArticleInfo[])arrArt.ToArray(typeof(ArticleInfo));
}
#endregion
//获取top10文章列表
public ArticleInfo[] TopArticles()
...{
GetData getdata = new GetData();
ArrayList arrArt = new ArrayList();
string cmdsql = "";
cmdsql += "select top 10 L.*,C.cate_Name from blog_Content AS L,blog_Category AS C where L.log_CateID=C.cate_ID";
cmdsql += " order by log_ID desc";
OleDbDataReader dr = getdata.GetReader(cmdsql);
while (dr.Read())
...{
ArticleInfo inf = SqlHelper.ConvertToArticleInfo(dr, new ArticleInfo());
arrArt.Add(inf);
}
dr.Close();
return (ArticleInfo[])arrArt.ToArray(typeof(ArticleInfo));
}
public ArticleInfo[] SearchResult(string searchKey)
...{
GetData getdata = new GetData();
ArrayList arrArt = new ArrayList();
string sqlfilter="";
sqlfilter += "where L.log_Title like '%" + searchKey + "%'";
string cmdsql = "";
cmdsql += "select L.*,C.cate_Name from blog_Content AS L,blog_Category AS C " + sqlfilter;
cmdsql += "and L.log_CateID=C.cate_ID order by log_ID desc";
OleDbDataReader dr = getdata.GetReader(cmdsql);
while (dr.Read())
...{
ArticleInfo inf = SqlHelper.ConvertToArticleInfo(dr, new ArticleInfo());
arrArt.Add(inf);
}
dr.Close();
return (ArticleInfo[])arrArt.ToArray(typeof(ArticleInfo));
}
/**//// <summary>
/// 获取评论数据
/// </summary>
/// <returns></returns>
public DataSet ListComment()
...{
string cmdsql = "select top 5 blog_ID,left(comm_Content,10)as comment from blog_Comment order by comm_ID desc";
GetData getdata = new GetData();
DataSet ds = getdata.GetDataset(cmdsql);
return ds;
}
public DataSet ListCate()
...{
GetData getdata = new GetData();
string querystring = "select cate_ID,cate_Name,cate_order from blog_Category";
DataSet ds = getdata.GetDataset(querystring);
return ds;
}
//public DataTable listCate()
//{
// OleDbDataReader dr = ListCate();
// DataTable dt = new DataTable("category");
// dt.Columns.Add("cateID", typeof(Int32));
// dt.Columns.Add("cateName",typeof(string));
// while (dr.Read())
// {
// DataRow myrow = dt.NewRow();
// myrow["cateID"]=dr["cate_ID"];
// myrow["cateName"] = dr["cate_Name"];
// dt.Rows.Add(myrow);
// }
// return dt;
//}
//获取views.aspx查看文章
public OleDbDataReader GetArticleReader(int id)
...{
GetData getdata = new GetData();
int contentId = id;
string querystring = "select L.*,C.cate_Name from blog_Content as L,blog_Category as C where L.log_ID=" + contentId + " and L.log_CateID=C.cate_ID ";
OleDbDataReader dr = getdata.GetReader(querystring);
return dr;
}
public DataSet GetArticle(int id)
...{
GetData getdata = new GetData();
int contentId = id;
string querystring = "select L.*,C.cate_Name from blog_Content as L,blog_Category as C where L.log_ID=" + contentId + " and L.log_CateID=C.cate_ID ";
DataSet ds = getdata.GetDataset(querystring);
return ds;
}
//获取要编辑的文章内容
public ArticleInfo EditArtInfo(int id)
...{
ArticleInfo inf=new ArticleInfo();
OleDbDataReader dr = GetArticleReader(id);
while (dr.Read())
...{
inf =SqlHelper.ConvertToArticleInfo(dr, new ArticleInfo());
}
return inf;
}
//获取ID文章的评论
public DataSet GetComment(int id)
...{
GetData getdata = new GetData();
int contentId = id;
string querystring= "select * from blog_Comment where blog_ID=" + contentId;
DataSet ds = getdata.GetDataset(querystring);
return ds;
}
//添加评论
public void PostComment(CommentInfo inf)
...{
GetData getdata = new GetData();
string querystring=string.Empty;
querystring += "insert into blog_Comment (blog_ID,comm_Content,comm_Author,comm_PostTime,author_email,author_web,comm_PostIp) Values" ;
querystring += "('"+inf.logId+"','"+inf.commContent+"','"+inf.author+"','"+inf.postTime+"','"+inf.email+"','"+inf.web+"','"+inf.postIp+"')";
getdata.excNonQuery(querystring);
string querystring2 = "update blog_Content set log_CommNums=log_CommNums+1 where log_ID=" + inf.logId;
getdata.excNonQuery(querystring2);
}
//更新查看数据
public void UpdateViewNums(int id)
...{
GetData getdata = new GetData();
string query = "update blog_Content set log_ViewNums=log_ViewNums+1 where log_ID="+id;
getdata.excNonQuery(query);
}
//添加文章
public void AddArticle(ArticleInfo inf)
...{
GetData getdata = new GetData();
string querystring = string.Empty;
querystring += "INSERT INTO blog_Content(log_CateID,log_Title,log_Intro,log_Content,log_From,log_DisUBB,log_IsTop,log_DisComment,log_IsShow) VALUES";
querystring +="('"+inf.cateId+"','"+inf.title+"','"+inf.intro+"','"+inf.content+"','"+inf.from+"',"+inf.DisUbb+","+inf.isTop+","+inf.DisComment+","+inf.isShow+")";
getdata.excNonQuery(querystring);
}
//更新文章
public void UpdateArticle(ArticleInfo inf)
...{
GetData getdata = new GetData();
string querystring = string.Empty;
querystring += "update blog_Content set log_Title='"+inf.title+"',log_CateID='"+inf.cateId+"',log_Intro='"+inf.intro+"',log_Content='"+inf.content+"',";
querystring += "log_From='" + inf.from + "',log_DisUBB=" + inf.DisUbb + ",log_IsTop=" + inf.isTop + ",log_DisComment=" + inf.DisComment + ",log_IsShow=" + inf.isShow + "";
querystring+=" where log_ID="+inf.ID;
getdata.excNonQuery(querystring);
}
删除ID的文章#region 删除ID的文章
public void DelArticle(int id)
...{
GetData getdata = new GetData();
string delstring = "delete * from blog_Content where log_ID=" +id;
string delstring2 = "delete from blog_Comment where blog_ID="+id;
getdata.excNonQuery(delstring);
getdata.excNonQuery(delstring2);
}
#endregion // 删除ID的文章
//删除评论
public void DelComment(int id)
...{
GetData getdata = new GetData();
string query1 = string.Empty;
string query2 = "delete from blog_Comment where comm_ID="+id;
query1 += "update blog_Content set log_CommNums=log_CommNums-1 where log_ID=";
query1 += "(select blog_ID from blog_Comment where comm_ID=" + id + ")";
getdata.excNonQuery(query1);
getdata.excNonQuery(query2);
}
//删除分类
public void DelCate(int cateid)
...{
//删除cate表数据
GetData getdata = new GetData();
string query1 = "delete from blog_Category where cate_ID="+cateid;
string query2 = "delete from blog_Content where log_CateID="+cateid;
string query3 = "delete from blog_Comment where blog_ID in(select log_ID from blog_Content where log_CateID=" + cateid + ")";
getdata.excNonQuery(query1);
//先删除评论再删除文章,注意顺序
getdata.excNonQuery(query3);
getdata.excNonQuery(query2);
}
//添加分类
public void AddCate(string cateName)
...{
GetData getdata = new GetData();
string query = "insert into blog_Category(cate_Name)values('"+cateName+"') ";
getdata.excNonQuery(query);
}
//仅执行SQL命令
public void ExcuteSQL(string sql)
...{
GetData getdata = new GetData();
getdata.excNonQuery(sql);
}
//修改分类
public void EditCateName(string name, int cateId)
...{
GetData getdata = new GetData();
string query = "update blog_Category set cate_Name='"+name+"' where cate_ID="+cateId;
getdata.excNonQuery(query);
}
//转移分类
public void Transfer(int fromCate, int toCate)
...{
//先转移log_Content再删除cate表cate数据
GetData getdata = new GetData();
string query1 = "update blog_Content set log_CateID=" + toCate + " where log_CateID=" + fromCate;
string query2 = "delete from blog_Category where cate_ID=" + fromCate;
getdata.excNonQuery(query1);
getdata.excNonQuery(query2);
}
//获取文章数
public int GetArticleNums()
...{
GetData getdata = new GetData();
string query = "select count(log_Title) from blog_Content";
return getdata.excScalar(query);
}
//获取评论数
public int GetCommentNums()
...{
GetData getdata = new GetData();
string query= "select count(comm_Content) from blog_Comment";
return getdata.excScalar(query);
}
//获取评论列表
public CommentInfo[] GetComments()
...{
CommentInfo comInf = new CommentInfo();
ArrayList arrCom = new ArrayList();
GetData getdata = new GetData();
string query = "select * from blog_Comment order by comm_ID asc";
OleDbDataReader dr= getdata.GetReader(query);
while (dr.Read())
...{
comInf = SqlHelper.ConvertToCommentInfo(dr,new CommentInfo());
arrCom.Add(comInf);
}
dr.Close();
getdata.closeConn();
return (CommentInfo[])arrCom.ToArray(typeof(CommentInfo));
}
//统计用户访问记录
public void InsertVistorInfo(VistorInfo inf)
...{
GetData getdata = new GetData();
string query=string.Empty;
query += "insert into blog_Counter(coun_IP,coun_Agent,coun_Refer,coun_Time)";
query += "values('"+inf.IP+"','"+inf.Agent+"','"+inf.ReferUrl+"','"+inf.VistorTime+"')";
getdata.excNonQuery(query);
}
//获取访问记录
public VistorInfo[] GetVistorInfo()
...{
VistorInfo visInf = new VistorInfo();
ArrayList arrVis = new ArrayList();
GetData getdata = new GetData();
string query = "select * from blog_Counter";
OleDbDataReader dr = getdata.GetReader(query);
while (dr.Read())
...{
visInf = SqlHelper.ConvertToVistorInfo(dr,new VistorInfo());
arrVis.Add(visInf);
}
dr.Close();
return(VistorInfo[])arrVis.ToArray(typeof(VistorInfo));
}
}
}
本文转载:
CSDN博客