dbaccess.cs

用AJAX做的一个在线求职系统

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace JobSiteStarterKit.DAL
{
    /// <summary>
    /// 数据访问层，主要用于简化ADO.NET 与数据库的操作细节。
    /// </summary>
	public class DBAccess:IDisposable
    {        
		private IDbCommand cmd=new SqlCommand();
		private string strConnectionString="";
		private bool handleErrors=false;
		private string strLastError="";

        /// <summary>
        /// 构造函数，从Web.Config中读取ConnectionString节的内容。
        /// </summary>
		public DBAccess()
		{
            ConnectionStringSettings objConnectionStringSettings = ConfigurationManager.ConnectionStrings["connectionstring"];
            strConnectionString = objConnectionStringSettings.ConnectionString;
            SqlConnection cnn=new SqlConnection();
			cnn.ConnectionString=strConnectionString;
			cmd.Connection=cnn;
            //默认情况下，将CommandType设为使用存储过程进行更新
            cmd.CommandType = CommandType.StoredProcedure;
        }
        /// <summary>
        /// 返回一个IDataReader接口，简化定义DBAccess的ExecuteReader
        /// </summary>
        /// <returns></returns>
		public IDataReader ExecuteReader()
		{
			IDataReader reader=null;
            try
            {
                //打开数据库连接
                this.Open();
                //执行完关闭毕连接
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }            
            catch (Exception ex)
            {
                if (handleErrors)
                    strLastError = ex.Message;
                else
                    throw;
            }
            catch
            {
                throw;
            }
			return reader;
		}
        /// <summary>
        /// 执行commandText字符串指定的Sql语句或存储过程，返回IDataReader
        /// </summary>
        /// <param name="commandtext">Sql语句或存储过程</param>
        /// <returns>实现了IDataReader接口的对象</returns>
		public IDataReader ExecuteReader(string commandtext)
		{
			IDataReader reader=null;
			try
			{
				cmd.CommandText=commandtext;
				reader=this.ExecuteReader();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return reader;
		}
        /// <summary>
        /// 返回结果集中的第一行第一列
        /// </summary>
        /// <returns>object类型，需要进行显示转换</returns>
		public object ExecuteScalar()
		{
			object obj=null;
			try
			{
				this.Open();
				obj= cmd.ExecuteScalar();
                this.Close();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return obj;
		}
        /// <summary>
        ///执行commandText字符串指定的Sql语句或存储过程，获取返回结果集中的第一行第一列
        /// </summary>
        /// <param name="commandtext">Sql语句或存储过程</param>
        /// <returns>object类型，需要进行显示转换</returns>
		public object ExecuteScalar(string commandtext)
		{
			object obj=null;
			try
			{
				cmd.CommandText=commandtext;
				obj= this.ExecuteScalar();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return obj;
		}
        /// <summary>
        /// 执行命令，并返回结果所影响的行数
        /// </summary>
        /// <returns></returns>
		public int ExecuteNonQuery()
		{
			int i=-1;
			try
			{
			    this.Open();
				i=cmd.ExecuteNonQuery();
                this.Close();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return i;
		}
        /// <summary>
        /// 执行commandText字符串指定的Sql语句或存储过程，并返回影响的行数
        /// </summary>
        /// <param name="commandtext">Sql语句或存储过程</param>
        /// <returns>执行Sql语句后所影响的行数</returns>
		public int ExecuteNonQuery(string commandtext)
		{
			int i=-1;
			try
			{
				cmd.CommandText=commandtext;
				i=this.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return i;
		}
        /// <summary>
        /// 执行命令，得到一个DataSet
        /// </summary>
        /// <returns>一个填充了数据的DataSet</returns>
		public DataSet ExecuteDataSet()
		{
			SqlDataAdapter da=null;
			DataSet ds=null;
			try
			{
				da=new SqlDataAdapter();
				da.SelectCommand=(SqlCommand)cmd;
				ds=new DataSet();
				da.Fill(ds);
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return ds;
		}
        /// <summary>
        /// 执行commandText字符串指定的Sql语句或存储过程，返回一个DataSet
        /// </summary>
        /// <param name="commandtext">Sql语句或存储过程</param>
        /// <returns>一个填充了数据的DataSet</returns>
		public DataSet ExecuteDataSet(string commandtext)
		{
			DataSet ds=null;
			try
			{
				cmd.CommandText=commandtext;
				ds=this.ExecuteDataSet();
			}
			catch(Exception ex)
			{
				if(handleErrors)
					strLastError=ex.Message;
				else
					throw;
			}
            catch
            {
                throw;
            }

			return ds;
		}
        /// <summary>
        ///获取或设置CommandText的值
        /// </summary>
		public string CommandText
		{
			get
			{
				return cmd.CommandText;
			}
			set
			{
				cmd.CommandText=value;
				cmd.Parameters.Clear();
			}
		}
        /// <summary>
        /// 返回一个IDataParameterCollection的只读属性
        /// </summary>
		public IDataParameterCollection Parameters
		{
			get
			{
				return cmd.Parameters;
			}
		}
        /// <summary>
        /// 添加参数到参数集合
        /// </summary>
        /// <param name="paramname">参数名称</param>
        /// <param name="paramvalue">参数值</param>
    	public void AddParameter(string paramname,object paramvalue)
		{
			SqlParameter param=new SqlParameter(paramname,paramvalue);
			cmd.Parameters.Add(param);
		}
        /// <summary>
        /// 直接天加一个IDataParameter类型的对象到参数集合。
        /// </summary>
        /// <param name="param"></param>
		public void AddParameter(IDataParameter param)
		{
			cmd.Parameters.Add(param);
		}
        /// <summary>
        /// 获取和设置连接字符串
        /// </summary>
		public string ConnectionString
		{
			get
			{
				return strConnectionString;
			}
			set
			{
				strConnectionString=value;
			}
		}
        /// <summary>
        /// 打开连接
        /// </summary>
        private void Open()
        {
            cmd.Connection.Open();
        }
        /// <summary>
        ///关闭连接 
        /// </summary>
        private void Close()
        {
            cmd.Connection.Close();
        }
        /// <summary>
        /// 获取或设置是否处理异常，如果不处理，则显示抛出异常
        /// </summary>
		public bool HandleExceptions
		{
			get
			{
				return handleErrors;
			}
			set
			{
				handleErrors=value;
			}
		}
        /// <summary>
        /// 如果抛出异常，获取异常信息。
        /// </summary>
		public string LastError
		{
			get
			{
				return strLastError;
			}
		}
        /// <summary>
        /// 显示释放cmd对象。
        /// </summary>
        public void Dispose()
        {
            cmd.Dispose();
        }
	}

}