public static string getAriNumb(string username)
{
string sql = "select COUNT(*) as line from dbo.articleinfo where wusername ='******'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> stalist = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (stalist.Count <= 0)
{
return("-1");// 错误
}
else
{
string lin = stalist[0].line.ToString();
return(lin);
}
}
catch (Exception)
{
return("-2");//严重错误
throw;
}
}
public static string statisticsReplyLine(string articleID)
{
string sql = "select COUNT(*) as line from dbo.commentinfo where dbo.commentinfo.particleID='" + articleID + "'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> listLine = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (listLine.Count <= 0)
{
return("N");//没有回复
}
else
{
int coun = Convert.ToInt32(listLine[0].line);
string sql2 = "UPDATE dbo.articleinfo SET wcommentsnum = '" + coun + "' WHERE ID = '" + articleID + "' ";
int yud = entit.ExecuteStoreCommand(sql2);
if (yud <= 0)
{
return("F");//更新失败
}
else
{
return("Y");//更新成功
}
}
}
catch (Exception e)
{
return("E" + e.ToString());//未知错误
throw;
}
}
public static List <userinfo> getUserinfoByName(string username)
{
//select * from dbo.userinfo where dbo.userinfo.username ='******' or UID='100001'
string sql = "";
Regex regChina = new Regex("^[^\x00-\xFF]");
Regex rg = new Regex("^[a-zA-Z]$");
if (regChina.IsMatch(username) || rg.IsMatch(username))
{
sql = "select * from dbo.userinfo where dbo.userinfo.username ='******'";
}
else
{
sql = "select * from dbo.userinfo where dbo.userinfo.username ='******' or UID='" + username + "'";
}
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <userinfo> listuseinfo = entit.ExecuteStoreQuery <userinfo>(sql).ToList();
return(listuseinfo);
}
catch (Exception e)
{
throw;
}
}
public static string getTitileCountByArtiTirle(string titleKey)
{
//select COUNT(*) as line from dbo.articleinfo WHERE dbo.articleinfo.wtitle Like '%文%'
string sql = "select COUNT(*) as line from dbo.articleinfo WHERE "
+ "dbo.articleinfo.wtitle Like '%" + titleKey + "%'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> statList = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (statList.Count <= 0)
{
return("0");
}
else
{
int coun = Convert.ToInt32(statList[0].line);
return(coun.ToString());
}
}
catch (Exception e)
{
return("-1");
throw;
}
}
public static bool userLogn(string userName, string pawd)
{
//select * from dbo.userinfo where dbo.userinfo.UID ='100001' and dbo.userinfo.password='******'
publicFunction publicfunc = new publicFunction();
string md5pawd = publicfunc.Md5encryption(pawd).ToString();
string sql = "select * from dbo.userinfo where dbo.userinfo.username ='******' and dbo.userinfo.password='******'";;
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <userinfo> uselist = entit.ExecuteStoreQuery <userinfo>(sql).ToList();
//List<userinfo> uselist2 = entit.ExecuteStoreQuery<userinfo>(sql2).ToList();
if (uselist.Count >= 1)
{
HttpContext.Current.Session["user"] = userName.ToString();
return(true);
}
else
{
HttpContext.Current.Session["user"] = null;
return(false);
}
}
catch (Exception e)
{
//return false;
throw;
}
}
public static List <articleinfo> getTitileCountByArtiTirleandPage(string titleKey, string page)
{
//select T.*from
//(
// SELECT *, row_number()
// over(order by wpostedtime desc) as row
// FROM dbo.articleinfo
// WHERE dbo.articleinfo.wtitle Like '%感%' or dbo.articleinfo.wcontent like '%感%'
//)T
//where row between 1 and 5
int pag = Convert.ToInt32(page);
int pages = ((pag - 1) * 5) + 1;
int pagee = (5 * pag);
string sql = "select T.*from ( "
+ "SELECT *, row_number() over(order by "
+ "wpostedtime desc) as row FROM dbo.articleinfo"
+ " WHERE dbo.articleinfo.wtitle Like '%" + titleKey
+ "%' )T where row between " + pages + " and " + pagee + "";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <articleinfo> artiList = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(artiList);
}
catch (Exception e)
{
throw;
}
}
public static string getClassCountByClassID(string classID)
{
//select COUNT(*) as line from dbo.articleinfo where dbo.articleinfo.classID ='9'
string sql = "select COUNT(*) as line from dbo.articleinfo"
+ " where dbo.articleinfo.classID ='" + classID + "' ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> counList = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (counList.Count <= 0)
{
return("0");
}
else
{
int coun = Convert.ToInt32(counList[0].line);
return(coun.ToString());
}
}
catch (Exception e)
{
return("-1");
throw;
}
}
public static string getUserAriCountBy(string Suid)
{
//select COUNT (*) line from dbo.articleinfo where dbo.articleinfo.wusername
//= (select userinfo.username from dbo.userinfo where dbo.userinfo.UID = '100009')
string sql = "select COUNT (*) line from dbo.articleinfo where dbo.articleinfo.wusername " +
"= (select userinfo.username from dbo.userinfo where dbo.userinfo.UID = '" + Suid + "')";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> staList = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
int countNu = staList[0].line;
if (countNu >= 0)
{
return(countNu.ToString());
}
else
{
return("-1");//数据出错
}
}
catch (Exception e)
{
return("-2");//查询出错
throw;
}
}
public static string isBelong(string User, string AriID)
{
//select COUNT(*) as line from articleinfo where wusername
//= (select username from userinfo where UID='100002') and ID ='1909'
string sql = "select COUNT(*) as line from articleinfo where wusername = '******' and ID ='" + AriID + "'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> staList = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (staList[0].line == 1)
{
return("1");//属于
}
else
{
return("-1");//NO
}
}
catch (Exception e)
{
return("-2");
throw;
}
}
public static string isMatching(string userUidorUserName)
{
var session = HttpContext.Current.Session["user"];
if (session == null)
{
return("-1");//没有登录
}
else
{
if (userUidorUserName.ToString() == session.ToString())
{
return("0");//正常
}
string sql = "select username from userinfo where UID='" + userUidorUserName + "'";
DbMybbsEntities entit = new DbMybbsEntities();
List <userinfo> userList = entit.ExecuteStoreQuery <userinfo>(sql).ToList();
var username = userList[0].username;
if (session.ToString() != username.ToString())
{
return("-2");//非法操作
}
}
return("0");//正常
}
public static List <articleinfo> getAriListBySuidandPage(string Suid, string page)
{
//select T.*from
//(
// SELECT ID,classID,wtitle,wpostedtime, row_number()
// over(order by wpostedtime desc) as row
// FROM dbo.articleinfo
// WHERE dbo.articleinfo.wusername =
// (select userinfo.username from dbo.userinfo where dbo.userinfo.UID = '100009')
//)T
//where row between 1 and 5
int pag = Convert.ToInt32(page);
int pages = ((pag - 1) * 15) + 1;
int pagee = (15 * pag);
string sql = "select T.* from ( SELECT ID,classID,wtitle,wpostedtime, row_number() " +
"over(order by wpostedtime desc) as row " +
"FROM dbo.articleinfo WHERE dbo.articleinfo.wusername ="******"(select userinfo.username from dbo.userinfo where dbo.userinfo.UID = '" + Suid + "')" +
")T where row between " + pages + " and " + pagee + "";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <articleinfo> artiList = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(artiList);
}
catch (Exception)
{
throw;
}
}
public static List <classinfo> getClassList()
{
string sql = "select * from dbo.classinfo ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <classinfo> clasList = entit.ExecuteStoreQuery <classinfo>(sql).ToList();
return(clasList);
}
catch (Exception e)
{
throw;
}
}
public static List <userinfo> slectUserinfoByusername(string username)
{
//select * from dbo.userinfo where userinfo.username='******'
string sql = "select * from dbo.userinfo where userinfo.username='******'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <userinfo> listUser = entit.ExecuteStoreQuery <userinfo>(sql).ToList();
return(listUser);
}
catch (Exception e)
{
throw;
}
}
public static List <commentinfo> lodeComment(string particleID)
{
//select * from dbo.commentinfo where particleID = '2'//--升序asc
string sql = "select * from dbo.commentinfo where particleID = '" + particleID + "' order by ptime asc ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <commentinfo> listComm = entit.ExecuteStoreQuery <commentinfo>(sql).ToList();
return(listComm);
}
catch (Exception e)
{
throw;
}
}
public static string setPublish(string ClasID, string Tite, string Content)
{
//insert into dbo.articleinfo(classID, wusername, wtitle, wcontent, wpostedtime, wpviews, wcommentsnum, wisFile, fID)
//output inserted.ID values('1', 'admin', '标题001', '内容001', '2019-12-12', '0', '0', '0', '0')
//insert into dbo.articleinfo VALUES ('1', 'admin', 'title', 'noerong', '2019-05-5', '50', '5', '', '')
// 类别id，用户名，标题，内容，时间，阅读量，评论数，我，
string sql = "";
if (useVerifier() != "-1")
{
string pusername = useVerifier();
string atime = DateTime.Now.ToLocalTime().ToString();
sql = "insert into dbo.articleinfo(classID, wusername, wtitle, "
+ "wcontent, wpostedtime, wpviews, wcommentsnum, wisFile, fID)"
+ "output inserted.ID as line values('" + ClasID + "', '" + pusername
+ "', '" + Tite + "', '" + Content + "', '" + atime + "', "
+ "'0', '0', '0', '0')";
}
else
{
return("0");//没有登录
}
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> stalis = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (stalis.Count == 1)
{
int arID = stalis[0].line;
return(arID.ToString());//刚刚发布的ID
}
else
{
return("-1");//失败
}
}
catch (Exception e)
{
return("Err" + e.ToString());
throw;
}
}
public static List <articleinfo> getArtiList(string linenumber, string clasID)
{
//select top 5 ID,wtitle from dbo.articleinfo where classID = '1' order by wpostedtime desc
//select top 100 PERCENT wtitle from dbo.articleinfo where classID = '1' order by wpostedtime desc
string sql = "select top " + Convert.ToInt32(linenumber) +
"wpostedtime,ID,wtitle from dbo.articleinfo where classID = '" +
clasID + "' order by wpostedtime desc ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <articleinfo> artlist = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(artlist);
}
catch (Exception e)
{
throw;
}
}
public static List <articleinfo> getartiContentByID(string artiID)
{
//select * from dbo.articleinfo where dbo.articleinfo.ID='1'
string sql = "select * from dbo.articleinfo where dbo.articleinfo.ID='" + artiID + "'";//查询文章内容
DbMybbsEntities entit = new DbMybbsEntities();
try
{
string Codp = statisticsReplyLine(artiID); //更新评论数
string Codv = upReading(artiID); //更新阅读量
List <articleinfo> artilist = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(artilist);
}
catch (Exception)
{
throw;
}
}
public static List <articleinfo> getArtiListandConteT(string clasID, string page)
{
//---假设每页显示5条--
//--有row / 5-- 页取整
// --一页为 1 - 5--1
//--二页为 6 - 10--2
//--三页为 11 - 15--3（ ((n - 1) * 5 + 1) ）
//--第n页为(n - 1) * 5 + 1) == 5 * n
//select T.*from
//(
// select *, row_number()
// over(order by wpostedtime desc) as row
// from dbo.articleinfo
// where dbo.articleinfo.classID = '2'
//) T
//where row between 1 and 5
int pag = Convert.ToInt32(page);
int pages = ((pag - 1) * 5) + 1;
int pagee = (5 * pag);
string sql = "select T.*from (select *, row_number() "
+ "over(order by wpostedtime desc) "
+ "as row from dbo.articleinfo "
+ "where dbo.articleinfo.classID = '" + clasID
+ "' ) T where row between " + pages + " and " + pagee + "";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <articleinfo> ariList = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(ariList);
}
catch (Exception e)
{
throw;
}
}
public static List <fileinfo> getFileinfo(string AriID, string type)
{
// select * from dbo.fileinfo where articleID = '195' and fType = 'img'
string sql = "";
DbMybbsEntities entit = new DbMybbsEntities();
if (type == "img")
{
sql = "select * from dbo.fileinfo where articleID = '" + AriID + "' and fType = 'img'";
}
else
{
sql = "select * from dbo.fileinfo where articleID = '" + AriID + "' and fType = 'other'";
}
try
{
List <fileinfo> filist = entit.ExecuteStoreQuery <fileinfo>(sql).ToList();
return(filist);
}
catch (Exception e)
{
throw;
}
}
public static string upReading(string articleID)
{
//查询阅量
string sql = "select dbo.articleinfo.wpviews as line from dbo.articleinfo where ID='" + articleID + "' ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <statisticsinfo> listSt = entit.ExecuteStoreQuery <statisticsinfo>(sql).ToList();
if (listSt.Count <= 0)
{
return("N");//不存在
}
else
{
int numb = listSt[0].line;//阅读量
int newNub = numb + 1;
string sql1 = "UPDATE dbo.articleinfo SET wpviews = '"
+ newNub.ToString() + "' WHERE ID = '" + articleID + "' "; //更新访问量
int yxH = entit.ExecuteStoreCommand(sql1); //受影响行数
if (yxH <= 0)
{
return("F");//失败
}
else
{
return("Y");//成功
}
}
}
catch (Exception e)
{
throw;
}
}
public static bool isRegister(string userName)
{
//select * from dbo.userinfo where username='******'
string sql = "select * from dbo.userinfo where username='******' ";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <userinfo> listUser = entit.ExecuteStoreQuery <userinfo>(sql).ToList();
if (listUser.Count >= 1)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception)
{
//return false;
throw;
}
}
public static List <articleinfo> searchArticleByTitle(string title)
{
//select T.*from
//(
// SELECT *, row_number()
// over(order by wpostedtime desc) as row
// FROM dbo.articleinfo
// WHERE dbo.articleinfo.wtitle Like '%文%'
//)T
//where row between 1 and 5
//SELECT * FROM dbo.articleinfo WHERE dbo.articleinfo.wtitle Like '%文%'
string sql = "SELECT * FROM dbo.articleinfo WHERE dbo.articleinfo.wtitle Like '%" + title + "%'";
try
{
DbMybbsEntities entit = new DbMybbsEntities();
List <articleinfo> artiList = entit.ExecuteStoreQuery <articleinfo>(sql).ToList();
return(artiList);
}
catch (Exception e)
{
throw;
}
}
public static string deleteAriByID(string ID)
{
string sqlisSeleingFil = "select * from dbo.fileinfo where articleID in ("
+ ID + ") and fType = 'img'";//查询文章是否有图片附件
string sqlisSeleothFil = "select * from dbo.fileinfo where articleID in ("
+ ID + ") and fType = 'other'";//查询文章是否有其他附件
string sqlSeleFill = "select * from dbo.fileinfo where articleID in ("
+ ID + ")";//查询是否有附件
string sqldelFile = "DELETE FROM dbo.fileinfo WHERE articleID in ("
+ ID + ")";//删除附件表数据
string sqlArit = "DELETE FROM dbo.articleinfo WHERE ID in ("
+ ID + ")";//删除文章表数据
try
{
int Cou = 0;
DbMybbsEntities entit = new DbMybbsEntities();
List <fileinfo> allFileList = entit.ExecuteStoreQuery <fileinfo>(sqlSeleFill).ToList();
if (allFileList.Count > 0)
{
List <fileinfo> imgfileList = entit.ExecuteStoreQuery <fileinfo>(sqlisSeleingFil).ToList();
List <fileinfo> othfileList = entit.ExecuteStoreQuery <fileinfo>(sqlisSeleothFil).ToList();
if (imgfileList.Count > 0)
{
//删除图片数据及其文件
for (int i = 0; i < imgfileList.Count; i++)
{
string imgurlTemp = imgfileList[i].url;
string imgurl = imgurlTemp.Replace(@"\", @"/");
var path = HttpContext.Current.Server.MapPath("~" + imgurl);
File.Delete(path);
}
}
if (othfileList.Count > 0)
{
//删除其他文件数据及其文件
for (int i = 0; i < othfileList.Count; i++)
{
string othurl = othfileList[i].url;
var path = HttpContext.Current.Server.MapPath("~" + othurl);
File.Delete(path);
}
}
int FilCou = entit.ExecuteStoreCommand(sqldelFile);
}
int ArCou = entit.ExecuteStoreCommand(sqlArit);
Cou = ArCou;
if (ArCou <= 0)
{
return("0");//操作数据库失败
}
return(Cou.ToString());
}
catch (Exception e)
{
return("-2");
throw;
}
}