数据访层

DbConnection.cs

using System;
using System.Data.SqlClient;

namespace DbControl
{
    /**//// <summary>
    /// 数据库链接
    /// </summary>
    public class DbConnection
    {
        //SQL数据库链接字符串
        private string _strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ "SqlConnectionString" ];
        //XML文件链接字符串
        private string _strXmlConnection = string.Empty;

        public string ConnectionString
        {
            get
            {
                return _strSqlConnection;
            }
            set
            {
                this._strSqlConnection = System.Configuration.ConfigurationSettings.AppSettings[ value ];
            }
        }

        public SqlConnection SqlConnectionString
        {
            get
            {
                return new SqlConnection( ConnectionString );
            }
        }

        public string XmlConnectionString
        {
            get
            {
                return this._strXmlConnection;
            }
            set
            {
                this._strXmlConnection = value;
            }
        }

        public DbConnection()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
    }
}

AppControl.cs

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

namespace DbControl
{
    /**//// <summary>
    /// AppControl 的摘要说明。
    /// </summary>
    public class SqlControl : DbConnection
    {
        private SqlConnection _Conn;
        private SqlCommand _Cmd;

        public SqlControl()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /**//// <summary>
        /// 打开数据库链接
        /// </summary>
        private void Open()
        {
            this._Conn = this.SqlConnectionString;
            try
            {
                _Conn.Open();
            }
            catch ( System.Exception ex )
            {
                throw ex;
            }
        }

        /**//// <summary>
        /// 关闭数据库链接
        /// </summary>
        private void Close()
        {
            try
            {
                this._Conn.Close();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
        }

        /**//// <summary>
        /// 运行SQL，返回DataTable数据
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <returns>返回DataTable</returns>
        public DataTable RunSqlToDataTable( string query )
        {
            this.Open();
            DataTable objDataTable = new DataTable();
            SqlDataAdapter objAdapter = new SqlDataAdapter( query , _Conn );
            try
            {
                objAdapter.Fill( objDataTable );
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataTable;
        }

        /**//// <summary>
        /// 执行SQL语句,返回单个值
        /// </summary>
        /// <param name="query">标准T-SQL语句</param>
        /// <returns>返回string</returns>
        public string RunSqlToResult( string query )
        {
            this.Open();
            this._Cmd = new SqlCommand( query,this._Conn );
            System.Text.StringBuilder strResult = new System.Text.StringBuilder();
            try
            {
                strResult.Append( this._Cmd.ExecuteScalar() );
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return strResult.ToString();
        }

        /**//// <summary>
        /// 运行单个SQL的相关操作
        /// </summary>
        /// <param name="query">SQL语句</param>
        /// <returns>返回AppControl</returns>
        public SqlControl ExecuteNonQuery( string query )
        {
            this.Open();
            _Cmd = new SqlCommand( query , _Conn );
            try
            {
                _Cmd.ExecuteNonQuery();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return this;
        }

        /**//// <summary>
        /// 按事务执行SQL语句数组
        /// </summary>
        /// <param name="query">标准T-SQL语句数组</param>
        /// <returns>返回AppControl</returns>
        public SqlControl ExecuteNonQuery( string[] query )
        {
            this.Open();
            this._Cmd = new SqlCommand();
            SqlTransaction objTran = this._Conn.BeginTransaction();
            this._Cmd.Connection = this._Conn;
            this._Cmd.Transaction = objTran;
            try
            {
                for ( int i=0 ; i<query.Length ; i++ )
                {
                    this._Cmd.CommandText = query[i];
                    this._Cmd.ExecuteNonQuery();
                }
                objTran.Commit();
            }
            catch( System.Exception ex )
            {
                objTran.Rollback();
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return this;
        }


        /**//// <summary>
        /// 执行SQL语句,返回SqlDataReader.
        /// </summary>
        /// <param name="query">标准SQL语句</param>
        /// <returns>返回DataReader</returns>
        public SqlDataReader RunSqlToDataReader( string query )
        {
            this.Open();
            this._Cmd = new SqlCommand( query , this._Conn );
            SqlDataReader objDataReader;
            try
            {
                objDataReader = this._Cmd.ExecuteReader();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataReader;
        }

        /**//// <summary>
        /// 运行存储过程（有参数，无输出）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="parameters">存储过程参数数组</param>
        /// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName,SqlParameter[] parameters )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,parameters );
            try
            {
                this._Cmd.ExecuteNonQuery();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return this;
        }

        /**//// <summary>
        /// 运行存储过程（无参数，无输出）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,null );
            try
            {
                this._Cmd.ExecuteNonQuery();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return this;
        }

        /**//// <summary>
        /// 运行存储过程（无参数，有输出）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="parametersOutput">存储过程输出参数数组</param>
        /// <returns>返回AppControl</returns>
        public SqlControl RunProcedure( string procedureName,ref SqlParameter[] parametersOutput )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,parametersOutput );
            try
            {
                this._Cmd.ExecuteNonQuery();
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return this;
        }

        /**//// <summary>
        /// 运行存储过程（有参数，无输出）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="parameters">存储过程参数数组</param>
        /// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName,SqlParameter[] parameters )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,parameters );
            DataTable objDataTable = new DataTable();
            try
            {
                this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataTable;
        }

        /**//// <summary>
        /// 运行存储过程（有参数，有输出）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="parametersInput">存储过程输入参数数组</param>
        /// <param name="parametersOutput">存储过程输出参数数组</param>
        /// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName,SqlParameter[] parametersInput,ref SqlParameter[] parametersOutput )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,parametersInput );
            if ( parametersOutput != null )
            {
                foreach( SqlParameter parameter in parametersOutput )
                {
                    this._Cmd.Parameters.Add( parameter );
                }
            }
            DataTable objDataTable = new DataTable();
            try
            {
                this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataTable;
        }

        /**//// <summary>
        /// 运行存储过程（无参数）
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <returns>返回DataTable</returns>
        public DataTable RunProcedureToDataTable( string procedureName )
        {
            this.Open();
            this._Cmd = this.MakeProcedure( procedureName,null );
            DataTable objDataTable = new DataTable();
            try
            {
                this._Cmd.ExecuteNonQuery();
                SqlDataAdapter objAdapter = new SqlDataAdapter( this._Cmd );
                objAdapter.Fill( objDataTable );
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataTable;
        }

        /**//// <summary>
        /// 建立存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="parameters">存储过程参数数组</param>
        /// <returns>返回AppControl</returns>
        private SqlCommand MakeProcedure( string procedureName,SqlParameter[] parameters )
        {
            SqlCommand objCmd = new SqlCommand( procedureName,this._Conn );
            objCmd.CommandType = CommandType.StoredProcedure;
            if ( parameters != null )
            {
                foreach( SqlParameter sqlParamet in parameters )
                {
                    objCmd.Parameters.Add( sqlParamet );
                }
            }
            return objCmd;
        }

        /**//// <summary>
        /// 多表查询
        /// </summary>
        /// <param name="query">标准SQL语句集</param>
        /// <returns>返回DataSet</returns>
        public DataSet RunSqlToDataSet( string[] query )
        {
            this.Open();
            DataSet objDataSet = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter();
            this._Cmd = new SqlCommand();
            this._Cmd.Connection = this._Conn;
            objAdapter.SelectCommand = this._Cmd;
            try
            {
                for ( int i=0 ; i<query.Length ; i++ )
                {
                    objAdapter.SelectCommand.CommandText = query[i];
                    objAdapter.Fill( objDataSet.Tables.Add() );
                }
            }
            catch( System.Exception ex )
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return objDataSet;
        }
    }
}