controldatabase.cs

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

			//得到记录集的总记录数
			//sql:查询字符串,由它得到记录集
			//返回总记录数,如果失败,则返回-1
			int totalRec=0;
			try
			{
				SqlDataReader reader=(SqlDataReader)this.sqlDataReader(sql);
				if(reader.Read())
				{
					totalRec=reader.GetInt32(0);
				}
				return totalRec;
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return -1;
			}
			finally
			{
				this.connectClose();
			}
		}

		public string showAboutQuestionList(int questionid)
		{
			//显示某个问题的相关或相近问题列表
			//questionid:原始问题ID
			//返回相关问题的字符串列表
			//显示20条

			string keyword;//用于查询的关键字
			string image="<img src='images/file.gif'>";
			StringBuilder returnValue=new StringBuilder();//返回字符串

			try
			{
				DataTable dataTable=this.getDataTable("select * from [questions] where id="+questionid);
				if(dataTable.Rows.Count>0)
				{
					//-------------------------------------------
					//得到关键字符串
					keyword=dataTable.Rows[0]["keyword"].ToString().Trim();
					if(keyword=="")
					{
						keyword=dataTable.Rows[0]["subject"].ToString().Trim();
					}
					else
					{
						keyword+=","+dataTable.Rows[0]["subject"].ToString().Trim();
					}
					//如果启动字典
					if((bool)this.getSystemPara("wordbookAllow"))
					{
						keyword+=","+this.scanWordbook(keyword);
					}
					//-----------------------------------------------
					string viewPage="<a href=showQuestion.aspx?page=1&questionID=";
					
					string selectsql="select * from [questions] where (";
					//首先对传入的关键字进行分析,关键字字符串是以逗号分隔多个的
					//把中文逗号换成英文的
					keyword=keyword.Replace("，",",");
					string[] split=keyword.Split(new char[]{','});//关键字符串分解到数组
					string [] searchField=new string[]{"subject","keyword","content","answerContent"};//模糊匹配的字段
					string searchType=this.getSystemPara("searchType").ToString().Trim();//确定系统使用哪种方式搜索	
					if(searchType=="fullText")//全文检索
					{
						for(int j=0;j<split.Length;j++)
						{
							selectsql+=" contains(*,'\""+split[j]+"*\"') or";
						}
					}
					else if(searchType=="matching")//模糊匹配
					{
						for(int k=0;k<searchField.Length;k++)
						{
							for(int j=0;j<split.Length;j++)
							{
								selectsql+=" "+searchField[k]+" like '%"+split[j]+"%' or";//得到查询字符串
							}
						}
					}
				
					selectsql=selectsql.Substring(0,selectsql.Length-3);
					selectsql+=") and answered='1' order by accessCount";

					try
					{
						DataTable dt=this.getDataTable(selectsql);
						string target=(HttpContext.Current.Request.ServerVariables["SCRIPT_NAME"].ToString().IndexOf("showQuestion.aspx")>-1)?"":" target=\"_blank\"";
						int totalRecord=dt.Rows.Count;
						if(totalRecord>0)
						{
							if(totalRecord>20)
							{
								totalRecord=20;
							}
							for(int i=0;i<totalRecord;i++)
							{
								if(dt.Rows[i]["subject"].ToString().Trim()!=dataTable.Rows[0]["subject"].ToString().Trim())//不出现正显示的记录
								{
									returnValue.Append("&nbsp;&nbsp;"+image+viewPage+dt.Rows[i]["id"].ToString()+target+">"+dt.Rows[i]["subject"].ToString()+"</a><br>");
								}
							}
						}
						return returnValue.ToString();
					}
					catch(Exception ee)
					{
						errorString=ee.ToString();
						return "";
					}
				}
				return "";
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "";
			}
		}

		public string showAboutQuestionList(string keyword,string showQuestionListPage,int curPage,int pageCount)
		{
			//执行搜索功能,按系统设定可进行全文检索和模糊匹配搜索
			//显示某个问题的相关或相近问题列表
			//keyword:查询关键字
			//pageCount:显示条数
			//showQuestionListPage:显示返回结果的页面
			//返回相关问题的字符串列表

			//默认显示20条
			if(pageCount<=0)
				pageCount=20;

			int totalRecord=0;//总记录数
			int totalPage=0;//总页数

			string viewPage="<a href=showQuestion.aspx?page=1&keyword="+keyword+"&questionID=";//显示问题的页面

			showQuestionListPage="<a href="+showQuestionListPage+"?keyword="+keyword+"&page=";

			string image="<img src='images/file.gif'>";

			StringBuilder returnValue=new StringBuilder();
			string selectsql="select * from [questions] where (";

			//首先对传入的关键字进行分析,关键字字符串是以逗号分隔多个的
			string[] split=keyword.Split(new char[]{','});
			
			string [] searchField=new string[]{"subject","keyword","content","answerContent"};//模糊匹配的字段
			string searchType=this.getSystemPara("searchType").ToString().Trim();//确定系统使用哪种方式搜索	
			if(searchType=="fullText")//全文检索
			{
				for(int j=0;j<split.Length;j++)
				{
					selectsql+=" contains(*,'\""+split[j]+"*\"') or";
				}
			}
			else if(searchType=="matching")//模糊匹配
			{
				for(int k=0;k<searchField.Length;k++)
				{
					for(int j=0;j<split.Length;j++)
					{
						selectsql+=" "+searchField[k]+" like '%"+split[j]+"%' or";//得到查询字符串
					}
				}
			}
				
			selectsql=selectsql.Substring(0,selectsql.Length-3);
			selectsql+=") and answered='1' order by accessCount";

			try
			{
				DataTable dataTable=this.getDataTable(selectsql);

				totalRecord=dataTable.Rows.Count;
				totalPage=(totalRecord+pageCount-1)/pageCount;
				if(curPage<1)
					curPage=1;
				if(curPage>totalPage)
					curPage=totalPage;

				if(totalPage>0)
				{
					returnValue.Append("共找到"+totalRecord+"条相关记录,分"+totalPage+"页显示,每页"+pageCount+"条<br>");
					int startLine=(curPage-1)*pageCount;
					for(int i=startLine;i<(pageCount+startLine);i++)
					{
						if(i<totalRecord)
						{
							returnValue.Append(image+viewPage+dataTable.Rows[i]["id"].ToString()+" target=\"_blank\">"+dataTable.Rows[i]["subject"].ToString()+"["+dataTable.Rows[i]["accessCount"].ToString()+"]</a><br>");
						}
						else
						{
							break;
						}
					}
					if(totalPage>1)
					{
						//显示分页导航条
						returnValue.Append("<br>&nbsp;");
						for(int i=1;i<=totalPage;i++)
						{
							if(i!=curPage)
							{
								returnValue.Append(showQuestionListPage+i.ToString()+">"+i.ToString()+"</a> ");
							}
							else
							{
								returnValue.Append(showQuestionListPage+i.ToString()+"><b>"+i.ToString()+"</b></a> ");
							}
						}
					}
					return returnValue.ToString();
				}
				return "";
			}
			catch(Exception ee)
			{
				errorString=ee.Message;
				return "";
			}
		}

		public string scanWordbook(string inputString)
		{
			//扫描整个字典表,对传入的文本进行词法分析
			//inputString:将进行词法分析的文本
			//返回:用逗号分隔的关键词字符串
			//20050524

			StringBuilder returnValue=new StringBuilder();
			bool isKey;//是否在inputString中找到
			string keyValue;//字典值
			string sql="select * from [wordbook]";

			DataTable dataTable=this.getDataTable(sql);

			foreach(DataRow dataRow in dataTable.Rows)
			{
				keyValue=dataRow.ItemArray[1].ToString().Trim();
				isKey=(inputString.IndexOf(keyValue)>-1)?true:false;
				if(isKey && (keyValue!=inputString))
				{
					returnValue.Append(","+keyValue);
				}
			}
			if(returnValue.Length>0)
			{
				returnValue.Remove(0,1);
			}
			//得到结束时间
			long endTime=System.DateTime.Now.Ticks;

			return returnValue.ToString();
		}

		public object getSystemPara(string paraName)
		{
			//获得系统参数
			//paraName:参数名
			//返回参数的值
			//20040524

			string paraValue="";
			string sql="select paraValue from [systemPara] where [paraName]='"+paraName.Trim()+"'";
			DataTable dataTable=this.getDataTable(sql);
			try
			{
				if(dataTable.Rows.Count>0)
				{
					paraValue=dataTable.Rows[0]["paraValue"].ToString().Trim();
					if(paraValue=="true")
					{
						return true;
					}
					else if(paraValue=="false")
					{
						return false;
					}
					else
					{
						return paraValue;
					}
				}
				else
				{
					return "NULL";
				}
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}

		public bool isAnswered(int questionid)
		{
			//判断问题是否已经回答
			//questionid:问题ID
			//如果已经回答返回true
			//20040525

			try
			{
				string sql="select [answered] from [questions] where [id]="+questionid;
				this.connectToSqlServer();
				SqlCommand command=new SqlCommand(sql,connect);
				this.connectOpen();
				string c=command.ExecuteScalar().ToString().Trim();
				if(c=="1")
					return true;
				else
					return false;
			}
			catch(Exception ee)
			{
				errorString=ee.Message;
				return false;
			}
			finally
			{
				this.connectClose();
			}

		}

		public string getLastError()
		{
			//得到最后的一个错误
			return this.ErrorString;
		}

		#endregion

		#region 搜索
		public string getKeyWord(string keyword)
		{
			//得到查询关键字
			//可以根据空格或逗号分隔
			if(keyword.Length>0)
			{
				string returnValue="";
				string[] key=keyword.Split(new char[]{' ',','});
				if((bool)this.getSystemPara("wordbookAllow"))//允许字典搜索
				{
					keyword+=","+this.scanWordbook(keyword);
					key=keyword.Split(new char[]{' ',','});
				}
				for(int i=0;i<key.Length;i++)
				{
					if(key[i]!="")
					{
						returnValue+=this.getAndKeyWord(key[i])+" or ";
					}
				}
				returnValue=returnValue.Substring(0,returnValue.Length-4);
				return returnValue;
			}
			else
				return "";
		}

		private string getAndKeyWord(string keyword)
		{
			//得到and（且）查询关键字
			if(keyword.Length>0)
			{
				StringBuilder returnValue=new StringBuilder();
				string[] key=keyword.Split(new char[]{'&'});
				string [] searchField=new string[]{"subject","keyword","content","answerContent"};//模糊匹配的字段
				if(this.getSystemPara("searchType").ToString().Trim()=="fullText")//全文检索
				{
					returnValue.Append("(");
					for(int i=0;i<key.Length;i++)
					{
						if(key[i]!="")
						{
							returnValue.Append("contains(*,'\""+key[i]+"*\"') and ");
						}
					}
					if(returnValue.ToString().EndsWith("and "))
						returnValue.Remove(returnValue.Length-5,5);
					returnValue.Append(")");
				}
				else if(this.getSystemPara("searchType").ToString().Trim()=="matching")//模糊匹配
				{
					returnValue.Append("(");
					for(int i=0;i<key.Length;i++)
					{
						returnValue.Append("(");
						if(key[i]!="")
						{
							for(int j=0;j<searchField.Length;j++)
							{
								if(j!=searchField.Length-1)
								{
									returnValue.Append(searchField[j]+" like '%"+key[i]+"%' or ");
								}
								else
								{
									returnValue.Append(searchField[j]+" like '%"+key[i]+"%'");
								}
							}
						}
						if(i!=key.Length-1)
						{
							returnValue.Append(") and ");
						}
						else
						{
							returnValue.Append(")");
						}
					}
					returnValue.Append(")");
				}
				return returnValue.ToString();
			}
			else
				return "";
		}

		public string searchQuestion(string keyword,string type,string showResultPage,int curPage,int pageCount)
		{
			//查找
			//type:查找范围
			try
			{
				string sql="select * from [questions] where ("+this.getKeyWord(keyword)+") and rootID="+type+" and answered=1 order by accessCount desc";
				if(type=="0")
				{
					sql="select * from [questions] where ("+this.getKeyWord(keyword)+") and answered=1 order by accessCount desc";
				}

				if(pageCount<=0)
					pageCount=30;

				string viewPage="<a href=showQuestion.aspx?page=1&keyword="+HttpContext.Current.Server.UrlEncode(keyword)+"&questionID=";

				StringBuilder returnValue=new StringBuilder();
			
				DataTable dt=this.getDataTable(sql);

				int totalRecord=0;
				int totalPage=0;
				totalRecord=dt.Rows.Count;
				totalPage=(totalRecord+pageCount-1)/pageCount;

				if(curPage<=0)
					curPage=1;
				if(curPage>totalPage)
					curPage=totalPage;
				int curLine=(curPage-1)*pageCount;
			
				if(totalPage>0)
				{
					//写入到文件
					string filePath=HttpContext.Current.Server.MapPath("searchResult");
					string fileName=keyword;
					bool fileExist=File.Exists(filePath+"\\"+fileName+".txt");