controldatabase.cs

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

using System;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Collections;
using System.Text;
using System.IO;

namespace dayi
{
	/// <summary>
	/// controlDatabase 的摘要说明。
	/// 操作数据库的类
	/// </summary>
	public class controlDatabase
	{
		private string serverName;//SQL数据库服务器名
		private string databaseName;//数据库名
		private string userid;//登录数据库的用户名
		private string password;//登录密码
		private SqlConnection connect;//连接
		private string errorString;

		private string[] sqlServerMsg;

		public controlDatabase()
		{
			sqlServerMsg=this.sqlServerMessage().Split(new char[]{','});
			//serverName="KEJIANET";
			serverName=sqlServerMsg[0];
			databaseName="dayiSys";
			//userid="sa";
			//password="shanzhu";
			userid=sqlServerMsg[1];
			password=sqlServerMsg[2];
		}
		public controlDatabase(string host,string database,string userid,string userpwd)
		{
			this.serverName=host;
			this.databaseName=database;
			this.userid=userid;
			this.password=userpwd;
		}

		#region 基本信息设置
		//get or set the database's host name
		public string sqlServerName
		{
			get
			{
				return serverName;
			}
			set
			{
				sqlServerName=value;
			}
		}
		//get or set the database's name
		public string sqlDatabaseName
		{
			get
			{
				return databaseName;
			}
			set
			{
				databaseName=value;
			}
		}
		//get or set the user id
		public string sqlUserid
		{
			get 
			{
				return userid;
			}
			set
			{
				userid=value;
			}
		}
		//get or set the password
		public string sqlPassword
		{
			get
			{
				return password;
			}
			set 
			{
				password=value;
			}
		}
		//get current connection
		public SqlConnection sqlConnection
		{
			get
			{
				return connect;
			}
		}
		//get error
		public string ErrorString
		{
			get
			{
				return errorString;
			}
		}
		#endregion

		#region 数据连接基本操作
		private void connectToSqlServer()
		{
			//与sqlserver数据库建立连接
			string dataSource="Data Source="+serverName+";";
			string security="user id="+userid+";password="+password+";";
			string database="initial catalog="+databaseName+";";
			string connectString=dataSource+security+database;
			connect=new SqlConnection(connectString);
		}
		public void connectOpen()
		{
			//打开连接
			try
			{
				connect.Open();
			}
			catch(Exception ee)
			{
				throw new Exception(ee.Message);
			}
		}
		public void connectClose()
		{
			//close connection
			try
			{
				if(connect.State==ConnectionState.Open)
				{
					connect.Close();
				}
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
			}
		}

		public string sqlServerMessage()
		{
			try
			{
				string sqlServerString="";
				string filePath=HttpContext.Current.Server.MapPath("server/sqlServer.txt");
				StreamReader reader=File.OpenText(filePath);
				sqlServerString=reader.ReadLine();
				reader.Close();
				return sqlServerString;
			}
			catch(Exception ee)
			{
				throw new Exception(ee.Message);
			}
		}
		#endregion

		#region 数据操作
		public bool catchRecord(string sql)
		{
			//指定查询语句，检查数据库中是否有相应的记录
			//如果有，则返回true，否则返回false
			//sql:查询语句
			bool returnValue=false;
			this.connectToSqlServer();
			SqlCommand command=new SqlCommand(sql,connect);
			this.connectOpen();
			try
			{
				SqlDataReader datareader=command.ExecuteReader();
				if(datareader.Read())
				{
					returnValue=true;
				}
				return returnValue;
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return returnValue;
			}
			finally
			{
				this.connectClose();
			}
		}


		public string showHotRecord(int rootID,string imageName,int count,string viewPage)
		{
			//功能：在指定表中显示访问量最大的count条记录，数据表中第一个字段为ID，第二个字段为问题标题
			//rootID:表questions中的rootID字段，对应哪一个科目
			//imageName:小图标名
			//count：显示多少条记录
			//viewPage:查看该记录的网页地址，传过该记录的ID
			string subject;
			string returnValue="";//返回值
			string sql="select * from questions where rootID="+rootID+" order by accessCount desc";
			//初始化参数
			if(imageName=="")
			{
				imageName="<img src='images/file.gif'>";
			}
			else
			{
				imageName="<img src='images/"+imageName+"'>";
			}
			if(viewPage=="")
			{
				viewPage="<a href=showQuestion.aspx?page=1&questionID=";
			}
			else
			{
				viewPage="<a href="+viewPage+"?page=1&questionID=";
			}

			DataTable dataTable=this.getDataTable(sql);
			if(dataTable.Rows.Count<count)
				count=dataTable.Rows.Count;
			DataRow dataRow;
			try
			{
				for(int j=0;j<count;j++)
				{
					dataRow=dataTable.Rows[j];
					subject=dataRow["subject"].ToString().Trim();
					if(subject.Length>30)
					{
						subject=subject.Substring(0,30)+"...";
					}
					returnValue+="&nbsp;&nbsp;"+imageName+viewPage+dataRow["id"].ToString().Trim()+">"+subject+"</a>["+dataRow["accessCount"].ToString()+"]<br>";
				}
				return returnValue;
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return returnValue;
			}
		}

		public string showHotList(string boardImage,string questionImage,string boardViewPage,string questionViewPage,int count)
		{
			//显示每个主栏目的最热文章
			//image:图标文件名
			//viewPage:显示该栏目的网页

			StringBuilder returnValue=new StringBuilder();
			this.connectToSqlServer();
			string sql="select * from [board] where rootid=0";

			DataTable dataTable=this.getDataTable(sql);
			try
			{
				foreach(DataRow dataRow in dataTable.Rows)
				{
					returnValue.Append("<img src='images/"+boardImage+"'><a href='"+boardViewPage+"?boardID="+dataRow["boardID"].ToString()+"'>"+dataRow["boardName"].ToString()+"</a><br>");
					returnValue.Append(this.showHotRecord(Convert.ToInt32(dataRow["boardID"]),questionImage,count,questionViewPage));
				}
				return returnValue.ToString();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
				return "ERROR";
			}
		}

		public int updateDatabase(string sql)
		{
			//对数据库进行插入、修改、删除等操作,返回影响的行数
			//sql:insert,delete or update statement
			this.connectToSqlServer();
			SqlCommand command=new SqlCommand(sql,connect);
			this.connectOpen();
			try
			{
				return command.ExecuteNonQuery();
				
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();//"操作失败！可能你输入的字段过长。";
				throw new Exception();
				//return -1;
			}
			finally
			{
				this.connectClose();
			}

		}

		public void modifyQuestion(int questionid,string subject,string keyword,string path,string content,string type)
		{
			//修改问题表中的一条记录
			//应用于修改问题或回答
			//questionid:问题ID
			//subject:问题标题
			//keyword:问题关键字
			//path:问题路径
			//content:修改后的内容
			//type:更新类型,为answer则修改回答,为question则修改问题
			//20040525

			string sql="select * from [questions] where [id]="+questionid;
			try
			{
				this.connectToSqlServer();
				SqlDataAdapter adapter=new SqlDataAdapter();
				
				SqlCommand command=new SqlCommand(sql,connect);
				adapter.SelectCommand=command;

				SqlCommandBuilder cb=new SqlCommandBuilder(adapter);
				adapter.UpdateCommand=cb.GetUpdateCommand();

				DataSet dataSet=new DataSet();
				this.connectOpen();
				adapter.Fill(dataSet);

				DataTable dataTable=dataSet.Tables[0];
				if(dataTable.Rows.Count>0)
				{
					DataRow dataRow=dataTable.Rows[0];
					if(type=="answer")
					{
						dataRow["answerContent"]=content;
						dataRow["answered"]="1";
						dataRow["answerUser"]=HttpContext.Current.Session["userName"].ToString();
						dataRow["answerTime"]=System.DateTime.Now.ToString();
					}
					else if(type=="question")
					{
						string[] split=path.Split(new char[]{'#'});
						string parentID=split[0];
						string rootID=split[1];
						string boardPath=split[2];

						if(rootID=="0")
						{
							rootID=parentID;
						}

						dataRow["subject"]=subject;
						dataRow["keyword"]=keyword;
						dataRow["parentID"]=parentID;
						dataRow["rootID"]=rootID;
						dataRow["boardPath"]=boardPath;
						dataRow["content"]=content;
					}
					//更新数据库
					adapter.Update(dataTable);
					dataTable.AcceptChanges();
				}
				else
					return;
			}
			catch(Exception ee)
			{
				errorString=ee.Message;
				return;
			}
			finally
			{
				this.connectClose();
			}
		}

		public void bindToGrid(string selectSql,DataGrid datagrid)
		{
			//从数据库中检索，然后绑定到dataGrid
			//selectSql：select 语句
			//datagrid：dataGrid组件
			this.connectToSqlServer();
			SqlCommand command=new SqlCommand(selectSql,connect);

			//create dataAdapter
			SqlDataAdapter adapter=new SqlDataAdapter();
			adapter.SelectCommand=command;

			//create dataSet
			DataSet dataSet=new DataSet();

			//open connection
			this.connectOpen();
			try
			{
				adapter.Fill(dataSet);
				//绑定到datagrid
				datagrid.DataSource=dataSet.Tables[0];
				datagrid.DataBind();
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
			}
			finally
			{
				this.connectClose();
			}
		}


		public void bindToDropDownList(int parentid,DropDownList list)
		{
			//从类别表中检索类名和类ID，插入到列表中
			//由于利用了递归,可能会降低效率
			//列表的text为类名，列表对应的value为类ID（boardID)+“#”+根ID（rootID）+“#“+类别路径（boardPath)。
			//如2#1#0,3,2，2表示boardID,1表示rootID,"0,3,2"表示boardPath（已经加上自身的ID）
			//list：DropDownList服务器控件
			//board表字段:boardID,boardName,rootID,parentID,boardMaster,boardPath
			string selectSql="select * from board where parentID="+parentid;
			DataTable dataTable=this.getDataTable(selectSql);
			try
			{
				foreach(DataRow dataRow in dataTable.Rows)
				{
					list.Items.Add(new ListItem(this.addChar(dataRow["boardPath"].ToString().Trim(),"├")+dataRow["boardName"].ToString().Trim(),dataRow["boardID"].ToString().Trim()+"#"+dataRow["rootID"].ToString().Trim()+"#"+dataRow["boardPath"].ToString().Trim()+","+dataRow["boardID"].ToString().Trim()));
					this.bindToDropDownList(Convert.ToInt32(dataRow["boardID"]),list);
				}
				list.SelectedIndex=0;
			}
			catch(Exception ee)
			{
				errorString=ee.ToString();
			}
		}

		private string addChar(string boardPath,string c)
		{
			//根据给定的字符串,分析有多少个逗号,然后返回相应的重复字符串
			//boardPath:包括逗号的字符串
			//c:重复返回的字符串,一般为一个字符
			string returnValue="";
			string[] path=boardPath.Split(new char[]{','});
			for(int i=0;i<(path.Length-1);i++)
			{
				returnValue+=c;
			}
			return returnValue;
		}
		
		public void execProcedure(string procedureName)
		{
			//执行没有参数的存储过程
			//procedureName:存储过程名
			this.connectToSqlServer();
			SqlCommand command=new SqlCommand(procedureName,connect);
			command.CommandType=CommandType.StoredProcedure;
			try
			{
				this.connectOpen();