controldatabase.cs

网上自动答疑系统(C#)

				command.ExecuteNonQuery();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
			}
			finally
			{
				this.connectClose();
			}
		}

		public string addBoard(string boardname,string boardmaster,string idpath)
		{
			//添加栏目
			//boardname:栏目名;rootid:根ID;parentid:父ID;boardmaster:管理者名;boardpath:栏目路径
			//如果操作成功,返回影响的行数,如果失败,返回ERROR
			string[] split=idpath.Split(new char[]{'#'});
			string rootid=split[1];
			string parentid=split[0];
			string boardpath=split[2];
			if(int.Parse(rootid)==0 && int.Parse(parentid)>0)
			{
				rootid=parentid;
			}
			string sql="insert into [board](boardName,rootID,parentID,boardMaster,boardPath) values('";
			sql+=boardname+"',"+rootid+","+parentid+",'"+boardmaster+"','"+boardpath+"')";
			try
			{
				int line=this.updateDatabase(sql);
				return line.ToString();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}

		public string deleteBoard(int id)
		{
			//删除栏目,包括子栏目.
			//id:要删除的栏目ID
			//返回值:如果成功,返回DELETED;如果失败,返回ERROR
			string sql="delete from [board] where boardID="+id;
			string delQuestion="delete from [questions] where parentID="+id;
			//删除栏目
			this.updateDatabase(sql);
			//删除问题
			this.updateDatabase(delQuestion);
			//删除子栏目
			try
			{
				string selSql="select * from [board] where parentID="+id;
				this.connectToSqlServer();
				SqlCommand command=new SqlCommand(selSql,connect);
				this.connectOpen();
				SqlDataReader reader=command.ExecuteReader();
				while(reader.Read())
				{
					this.deleteBoard(Convert.ToInt32(reader.GetValue(0).ToString().Trim()));
				}
				return "DELETED";
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
			finally
			{
				this.connectClose();
			}
		}

		public string showBoard(int boardid,string image,string viewPage,int curPage)
		{
			//显示某一个栏目下的所所有子栏目
			//boardid:栏目的ID号
			//image:显示的图标
			//viewPage:栏目显示的网页,为showBoard.aspx
			//返回显示结果
			//应用:应用于showBoard.aspx等下显示某一栏目下的子栏目
			StringBuilder returnValue=new StringBuilder();
			if(image=="")
				image="<img src='images/folder.gif'>";
			else
				image="<img src='images/"+image+"'>";
			
			if(viewPage=="")
				viewPage="<a href='showBoard.aspx?boardID=";
			
			string sql="select * from [board] where parentID="+boardid;
			try
			{
				//SqlDataReader reader=(SqlDataReader)this.sqlDataReader(sql);
				DataTable dt=this.getDataTable(sql);
				//while(reader.Read())
				foreach(DataRow row in dt.Rows)
				{
					returnValue.Append(image+viewPage+row["boardID"].ToString()+"'>"+row["boardName"].ToString()+"</a><br>");
				}
				//显示该栏目下的所有问题
				returnValue.Append(this.showQuestionsList(boardid,"","","",0,curPage));
				return returnValue.ToString();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "";
			}
		}

		public string getMainBoardList(int parentID)
		{
			//得到主栏目列表
			//string image="<img src='images/folder.gif'>";
			string viewPage="<a href='showBoard.aspx?boardID=";
			StringBuilder returnValue=new StringBuilder();
			try
			{
				string sql="select * from [board] where parentID="+parentID;
				DataTable dt=this.getDataTable(sql);
				foreach(DataRow row in dt.Rows)
				{
					returnValue.Append(this.addChar(row["boardPath"].ToString().Trim(),"&nbsp;&nbsp;&nbsp;&nbsp;")+viewPage+row["boardID"].ToString()+"'>"+row["boardName"].ToString()+"</a><br>");
					returnValue.Append(this.getMainBoardList(Convert.ToInt32(row["boardID"])));
				}
				return returnValue.ToString();
			}
			catch(Exception ee)
			{
				errorString=ee.Message;
				return "";
			}

		}
		public DataTable getDataTable(string selectsql)
		{
			//得到一个数据表
			//selectsql:查询字符串
			//20040525

			try
			{
				this.connectToSqlServer();
				SqlDataAdapter adapter=new SqlDataAdapter(selectsql,connect);
				DataSet dataSet=new DataSet();
				this.connectOpen();
				adapter.Fill(dataSet);
				return dataSet.Tables[0];
			}
			catch(Exception ee)
			{
				errorString=ee.Message;
				return new DataTable();
			}
			finally
			{
				this.connectClose();
			}
		}
		public object sqlDataReader(string selectsql)
		{
			//得到一个SqlDataReader对象
			//selectsql:查询字符串
			//返回得到的SqlDataReader对象
			//如果出错,将返回ERROR
			//注意,不要忘记关闭连接
			try
			{
				if(selectsql!="")
				{
					this.connectToSqlServer();
					SqlCommand command=new SqlCommand(selectsql,connect);
					this.connectOpen();
					SqlDataReader reader=command.ExecuteReader();
					return reader;
				}
				else
				{
					errorString="传入的查询字符串为空";
					return "ERROR";
				}
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}

		public string showQuestionsList(int boardid,string image,string viewQuestionPage,string viewBoardPage,int pageCount,int curPage)
		{
			//显示某一栏目下的所有问题记录
			//boardid:栏目ID号
			//image:图标名
			//viewQuestionPage:显示问题内容的网页(showQuestion.aspx)
			//viewBoardPage:显示栏目的网页(showBoard.aspx?boardID=)
			//pageCount:每页多少条记录
			//curPage:当前页
			//返回问题记录字符串
			//-------------------------
			//初始化参数
			if(image=="")
				image="<img src='images/file.gif'>";
			else
				image="<img src='images/"+image+"'>";

			if(viewBoardPage=="")
				viewBoardPage="<a href=showBoard.aspx?boardID="+boardid;
			else
				viewBoardPage="<a href="+viewBoardPage+"?boardID="+boardid;

			if(viewQuestionPage=="")
				viewQuestionPage="<a href=showQuestion.aspx?questionID=";
			else
				viewQuestionPage="<a href="+viewQuestionPage+"?questionID=";

			if(pageCount<=0)
				pageCount=15;
			//--------------------------
			StringBuilder returnValue=new StringBuilder();
			string sql="select * from [questions] where parentID="+boardid+" order by accessCount desc";
			DataTable dt=this.getDataTable(sql);
			int totalRecord=dt.Rows.Count;//总记录数
			int totalPage=0;//总页数
			try
			{
				if(totalRecord>0)
				{
					//得到总页数
					totalPage=(totalRecord+pageCount-1)/pageCount;

					if(curPage<=0)
						curPage=1;
					else if(curPage>totalPage)
						curPage=totalPage;

					//实现分页
					int startLine=(curPage-1)*pageCount;
					for(int i=startLine;i<startLine+pageCount;i++)
					{
						if(i<totalRecord)
						{
							returnValue.Append("&nbsp;&nbsp;"+image+viewQuestionPage+dt.Rows[i]["id"].ToString()+">"+dt.Rows[i]["subject"].ToString()+"</a>["+dt.Rows[i]["accessCount"]+"]<br>");
						}
						else
							break;
					}
					if(totalPage>1)
					{
						returnValue.Append(this.showPageString(viewBoardPage,curPage,totalPage,pageCount,totalRecord));
					}
					return returnValue.ToString();
				}
				return "";
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				throw new Exception();
			}
		}
		
		public string showPageString(string viewPage,int curPage,int totalPage,int pageCount,int totalRec)
		{
			//显示分页导航栏
			//viewPage:链接地址
			//curPage:当前页数
			//totalPage:总页数
			//pageCount:每页多少条记录
			//totalRec:总记录数
			string returnValue="";
			if(viewPage.IndexOf("?")>-1)
			{
				viewPage+="&page=";
			}
			else
			{
				viewPage+="?page=";
			}
			if(curPage<1)
				curPage=1;
			if(curPage>totalPage)
				curPage=totalPage;

			if(totalPage==1)
			{
				returnValue="第一页&nbsp;上一页&nbsp;下一页&nbsp;最末页&nbsp;";
			}
			else if(totalPage>1)
			{
				if(curPage==1)
				{
					returnValue="第一页&nbsp;上一页&nbsp;";
				}
				else
				{
					returnValue=viewPage+"1>第一页</a>&nbsp;";
					returnValue+=viewPage+Convert.ToString((curPage-1))+">上一页</a>&nbsp;";
				}
				if(curPage==totalPage)
				{
					returnValue+="下一页&nbsp;最末页&nbsp;";
				}
				else
				{
					returnValue+=viewPage+Convert.ToString((curPage+1))+">下一页</a>&nbsp;";
					returnValue+=viewPage+totalPage.ToString()+">最末页</a>&nbsp;";
				}
			}
			returnValue+=curPage.ToString()+"/"+totalPage.ToString()+"&nbsp;";
			returnValue+=pageCount.ToString()+"条记录/页&nbsp;共"+totalRec+"条记录<br>";
			return returnValue;
		}

		public string showQuestionAndAnswer(int questionid,int curPage,string keyword)
		{
			//显示问题与回答,还有该问题的相关讨论
			//questionid:问题ID号
			//curPage:当前页数
			//keyword:传入搜索时的关键字,进行加亮显示
			//如果成功,返回该记录内容;反之,返回ERROR
			StringBuilder returnValue=new StringBuilder();
			try
			{
				string selectsql="select * from [questions] where id="+questionid;

				string subject="";
				string content="";
				string answerContent="";

				DataTable dt=this.getDataTable(selectsql);
				if(dt.Rows.Count>0)
				{
					//阅读次数加一
					this.updateDatabase("update [questions] set accessCount=accessCount+1 where id="+questionid);
					//问题相关信息
					subject=dt.Rows[0]["subject"].ToString().Trim();
					content=dt.Rows[0]["content"].ToString();
					answerContent=dt.Rows[0]["answerContent"].ToString();

					keyword=keyword.Trim();
					if(keyword!="")
					{
						subject=lightShow(subject,keyword);
						content=lightShow(content,keyword);
						answerContent=lightShow(answerContent,keyword);
					}
					returnValue.Append("<center><b><img src='images/filewrite.gif'><font size=4>"+subject+"</font></b><br>"+dt.Rows[0]["asktime"].ToString());
					returnValue.Append("&nbsp;作者:"+dt.Rows[0]["askuser"].ToString()+"</center>");
					returnValue.Append(content+"<br>");
					returnValue.Append("<div align='right'>已经被阅"+dt.Rows[0]["accessCount"].ToString()+"次</div>");
					//显示解答
					returnValue.Append("<hr width=300 size=1 noshade align=left>");
					returnValue.Append("<h4><img src='images/filewrite.gif'>问题解答:</h4>");
					if(dt.Rows[0]["answered"].ToString()=="1")//已答
					{
						returnValue.Append(answerContent);
						returnValue.Append("<div align='right'>解答时间:"+dt.Rows[0]["answertime"].ToString()+"&nbsp;解答人:"+dt.Rows[0]["answerUser"].ToString()+"</div>");
					}
					else
					{
						returnValue.Append("尚没有解答.");
					}
					return returnValue.ToString();
				}
				return "";
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}


		public string showQuestionAbout(int questionid,int pageCount,int curPage)
		{
			//显示某个问题的相关讨论
			//questionid:问题ID
			//分页显示返回
			//pageCount:每页多少条记录
			//curPage:当前页
			StringBuilder returnValue=new StringBuilder();
			int totalRecord=0;//总记录数
			int totalPage=0;//总页数
			string ttsql="select count(*) as cnt from [questionAbout] where questionID="+questionid;
			string sql="select * from [questionAbout] where questionID="+questionid+" order by id desc";

			//得到问题的根ID,以检验当前用户是否为合法管理员
			DataTable dt=this.getDataTable("select * from [questions] where [id]="+questionid);
			string rootID="";
			if(dt.Rows.Count>0)
			{
				rootID=dt.Rows[0]["rootID"].ToString().Trim();
			}

			//得到一个数据表
			DataTable dataTable=this.getDataTable(sql);
			try
			{
				//得到总记录数
				totalRecord=this.totalRecord(ttsql);
				//得到总页数
				totalPage=(totalRecord+pageCount-1)/pageCount;

				if(curPage<=0)
					curPage=1;
				else if(curPage>totalPage)
					curPage=totalPage;
					
				if(totalPage>0)
				{
					//实现分页
					int startLine=(curPage-1)*pageCount;
					int rowsCount=dataTable.Rows.Count;
					DataRow dataRow;
					for(int i=0;i<pageCount;i++)
					{
						if(startLine+i<rowsCount)
						{
							dataRow=dataTable.Rows[startLine+i];
							
							if(HttpContext.Current.Session["userName"].ToString()!="" && (HttpContext.Current.Session["userType"].ToString()=="admin" || HttpContext.Current.Session["userType"].ToString()=="master"+rootID))
							{
								returnValue.Append("<font color=blue>作者:"+dataRow["addUser"].ToString()+"&nbsp;"+dataRow["addTime"].ToString()+"</font>&nbsp;&nbsp;<a href=deleteQuestionAbout.aspx?questionID="+questionid+"&id="+dataRow["id"].ToString()+">删除本讨论</a><br>────────────────<br>"+this.htmlencode(dataRow["content"].ToString())+"<hr size=1 width=100% noshade align=left>");
							}
							else
							{
									returnValue.Append("<font color=blue>作者:"+dataRow["addUser"].ToString()+"&nbsp;"+dataRow["addTime"].ToString()+"</font><br>────────────────<br>"+this.htmlencode(dataRow["content"].ToString())+"<hr size=1 width=100% noshade align=left>");
							}
						}
					}
					returnValue.Remove(returnValue.Length-"<hr size=1 width=200 noshade align=left>".Length-1,"<hr size=1 width=200 noshade align=left>".Length+1);
					if(totalPage>1)
					{
							returnValue.Append("<br>"+this.showPageString("<a href=showQuestion.aspx?questionID="+questionid,curPage,totalPage,pageCount,totalRecord));
					}
				}
				//显示相关讨论
				
				if(returnValue.Length<1)
				{
					returnValue.Append("<h4><img src='images/filewrite.gif'>相关讨论:</h4>");
					returnValue.Append("尚无相关讨论");
				}
				else
				{
					returnValue.Insert(0,"<h4><img src='images/filewrite.gif'>相关讨论:</h4>");
				}

				return returnValue.ToString();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}

		private int totalRecord(string sql)
		{