[ASP.net]数据访层

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

DbConnection.cs

using System;
using System.Data.SqlClient;

namespace DbControl
{

    /// 数据库链接
    /// </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
{

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

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

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

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

        /// 运行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;
        }

        /// 执行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();
        }

        /// 运行单个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;
        }

        /// 按事务执行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;
        }

        /// 执行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>
        /// <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>
        /// <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>
        /// <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>
        /// <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>
        /// <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>
        /// <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>
        /// <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>
        /// <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 )
{