database.cs

一个多用户在线题库管理系统,可以实现各类试题的添加和管理

using System;
using System.IO;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using TQMS.DataAccessHelper;

namespace TQMS.DataAccessLayer
{
	/// <summary>
	/// 类，用于数据访问的类。
	/// </summary>
	public class Database : IDisposable 
	{
		/// <summary>
		/// 保护变量，数据库连接。
		/// </summary>
		protected SqlConnection Connection;

		/// <summary>
		/// 保护变量，数据库连接串。
		/// </summary>
		protected String ConnectionString;
		
		/// <summary>
		// 构造函数。
		/// </summary>
		/// <param name="DatabaseConnectionString">数据库连接串</param>
		public Database()
		{
            ConnectionString = ConfigurationManager.ConnectionStrings["QuestionManagerConnectionString"].ToString();
		}

		/// <summary>
		/// 析构函数，释放非托管资源
		/// </summary>
		~Database()
		{
			try
			{
				if (Connection != null)
					Connection.Close();
			}
			catch{}
			try
			{
				Dispose();
			}
			catch{}
		}

		/// <summary>
		/// 保护方法，打开数据库连接。
		/// </summary>
		protected void Open() 
		{
			if (Connection == null)
			{
				Connection = new SqlConnection(ConnectionString);
			}
			if (Connection.State.Equals(ConnectionState.Closed))
			{
                try
                {
                    Connection.Open();
                }
                catch(SqlException e)
                {

                    //if (!File.Exists("error.txt"))
                    //{
                    //    File.Create("error.txt");
                    //}
                    //FileStream fs = new FileStream("error.txt", FileAccess.ReadWrite);
                    //StreamWriter w = new StreamWriter(fs);
                    //fs.Write(e.Message.
                  
                   
                    //string strMessage = e.Message;
                    ////以下把信息写入windows日志
                    ////要把aspnet用户添加到管理员组中，以便有写注册表权限
                    //if (!EventLog.SourceExists("TQMS"))
                    //    EventLog.CreateEventSource("TQMS", "Application");
                    //    //EventLog.CreateEventSource("mySource", "myLog");
                    //EventLog Event = new EventLog();
                    //Event.Source = "TQMS";
                    //Event.WriteEntry(strMessage, EventLogEntryType.Warning);
                    ////EventLog.Delete("myLog");
                   
                    //throw new Exception("我处理不了，请最高人民法院处理！");
                }
			}
		}

		/// <summary>
		/// 公有方法，关闭数据库连接。
		/// </summary>
		public void Close() 
		{
			if (Connection != null)
				Connection.Close();
		}

		/// <summary>
		/// 公有方法，释放资源。
		/// </summary>
		public void Dispose() 
		{
			// 确保连接被关闭
			if (Connection != null) 
			{
				Connection.Dispose();
				Connection = null;
			}				
		}

		/// <summary>
		/// 公有方法，获取数据，返回一个SqlDataReader （调用后注意调用SqlDataReader.Close()）。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader GetDataReader(String SqlString)
		{
			Open();
			SqlCommand cmd = new SqlCommand(SqlString,Connection);
			return cmd.ExecuteReader();
		}

		/// <summary>
		/// 公有方法，获取数据，返回一个SqlDataReader （调用后注意调用SqlDataReader.Close()、Database.Close()）。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>SqlDataReader</returns>
		public SqlDataReader GetSaDataReader(String SqlString)
		{
			Open();
			SqlCommand cmd = new SqlCommand(SqlString,Connection);
			return cmd.ExecuteReader();
		}

		/// <summary>
		/// 公有方法，获取数据，返回一个DataSet。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>DataSet</returns>
		public DataSet GetDataSet(String SqlString)
		{
			Open();
			SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection);
			DataSet dataset = new DataSet();
			adapter.Fill(dataset);
			Close();
			return dataset;
		}

		/// <summary>
		/// 公有方法，获取数据，返回一个DataTable。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>DataTable</returns>
		public DataTable GetDataTable(String SqlString)
		{
			DataSet dataset = GetDataSet(SqlString);
			dataset.CaseSensitive = false;
			return dataset.Tables[0];
		}

		/// <summary>
		/// 公有方法，获取数据，返回一个DataRow。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>DataRow</returns>
		public DataRow GetDataRow(String SqlString)
		{
			DataSet dataset = GetDataSet(SqlString);
            
			dataset.CaseSensitive = false;
			if (dataset.Tables[0].Rows.Count>0)
			{
				return dataset.Tables[0].Rows[0];
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 公有方法，执行Sql语句。
		/// </summary>
		/// <param name="SqlString">Sql语句</param>
		/// <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns>
		public int ExecuteSQL(String SqlString)
		{
			int count = -1;
			Open();
			try
			{
				SqlCommand cmd = new SqlCommand(SqlString,Connection);
				count = cmd.ExecuteNonQuery();
			}
			catch
			{
				count = -1;
			}
			finally
			{
				Close();
			}
			return count;
		}

        /// <summary>
        /// 公有方法，执行一组Sql语句。
        /// </summary>
        /// <param name="SqlStrings">Sql语句组</param>
        /// <returns>是否成功</returns>
        public bool ExecuteSQL(String[] SqlStrings)
        {
            bool success = true;
            Open();
            SqlCommand cmd = new SqlCommand();
            SqlTransaction trans = Connection.BeginTransaction();
            cmd.Connection = Connection;
            cmd.Transaction = trans;
            try
            {
                foreach (String str in SqlStrings)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch
            {
                success = false;
                trans.Rollback();
            }
            finally
            {
                Close();
            }
            return success;
        }

		/// <summary>
		/// 公有方法，执行一组Sql语句。
		/// </summary>
		/// <param name="SqlStrings">Sql语句组</param>
		/// <returns>是否成功</returns>
		public bool ExecuteSQL(ArrayList SqlStrings)
		{
			bool success = true;
			Open();
			SqlCommand cmd = new SqlCommand();
			SqlTransaction trans = Connection.BeginTransaction();
			cmd.Connection = Connection;
			cmd.Transaction = trans;
			try
			{
				foreach (String str in SqlStrings)
				{
					cmd.CommandText = str;
					cmd.ExecuteNonQuery();
				}
				trans.Commit();
			}
			catch
			{
				success = false;
				trans.Rollback();
			}
			finally
			{
				Close();
			}
			return success;
		}

		/// <summary>
		/// 公有方法，在一个数据表中插入一条记录。
		/// </summary>
		/// <param name="TableName">表名</param>
		/// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
		/// <returns>是否成功</returns>
		public bool Insert(String TableName,Hashtable Cols)
		{
			int Count = 0;

			if (Cols.Count<=0)			
			{
				return true;
			}

			String Fields = " (";
			String Values = " Values(";			
			foreach(DictionaryEntry item in Cols)
			{
				if (Count!=0)
				{
					Fields += ",";
					Values += ",";
				}
				Fields += "["+item.Key.ToString()+"]";
				Values += item.Value.ToString();
				Count ++;
			}
			Fields += ")";
			Values += ")";

			String SqlString = "Insert into "+TableName+Fields+Values;

			String[] Sqls = {SqlString};
			return ExecuteSQL(Sqls);
		}

	
		/// <summary>
		/// 公有方法，更新一个数据表。
		/// </summary>
		/// <param name="TableName">表名</param>
		/// <param name="Cols">哈西表，键值为字段名，值为字段值</param>
		/// <param name="Where">Where子句</param>
		/// <returns>是否成功</returns>
		public bool Update(String TableName,Hashtable Cols,String Where)
		{
			int Count = 0;
			if (Cols.Count<=0)			
			{
				return true;
			}
			String Fields = " ";
			foreach(DictionaryEntry item in Cols)
			{
				if (Count!=0)
				{
					Fields += ",";
				}
				Fields += "["+item.Key.ToString()+"]";
				Fields += "=";
				Fields += item.Value.ToString();
				Count ++;
			}
			Fields += " ";

			String SqlString = "Update "+TableName+" Set "+Fields+Where;

			String[] Sqls = {SqlString};
			return ExecuteSQL(Sqls);
		}		
	}
}