数据访问层及例子-1

/**//*
    --作者：二泉
    --日期：2004-6-3
    --版本：0.1
    --说明：1、类工厂 DbFactory 根据不同的 数据库类型 返回不同类型的 IDbConnection、IDbCommand 对象
    --函数介绍：

    
    欢迎免费使用，有问题至 erquan@126.com。

    版本历史：
        1、2004-6-3 下午 开始编写。
        2、2004-6-3 20:00 前完成 类工厂、数据访问层、逻辑层和表示层的代码
        3、2004-6-3 20:00 只完成了 数据访问层 中 SqlDR 的两个重载方法。
        4、2004-6-4 下午：完成 数据访问层中 SqlDS、SqlNonQuery 4 个重载方法。
        5、2004-6-4 下午：对类代码每句加上注释，函数的头说明，排版 等琐碎工作。
        6、2004-6-5 上午：根据重载参数最多的函数，去掉两个函数。
        7、2004-6-5 晚上：以标准的 XML 格式进行函数的注释。
*/    
    
    
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    //using System.Data.OracleClient;
    
    
    /**////-----------------------------------下面开始 数据层 类工厂 的定义----------------------------
        ///定义一个 枚举 连接类型
        public enum ConnectionType{
            Sql        =    1,        //SqlServer
            Oracle    =    2,        //Oracle
            OleDb    =    3        //其它。
        }

        /**////定义一个对连接对象的实例工厂。
        ///根据 CoonectionType 的不同，返回 类型 不同的 IDbConnection、IDbCommand 对象
        public class DbFactory{
        
            private ConnectionType _type;/**////私有的连接 枚举 类型
            
            private String    _strConn;    //连接字符串

            /**////定义 连接类型枚举 属性
            public ConnectionType type{
                set{
                    this._type    =    value;
                }
            }

            /**////定义 连接字符串 属性
            public String strConn{
                set{
                    this._strConn    =    value;
                }
            }

            /**////以 单态模式 实例化本类
            private static    DbFactory _df    =    null;

            /**////实例化入口
            public static DbFactory Instance(){
                if(_df==null){
                    _df    =    new DbFactory();
                }
                return _df;
            }/**////end  DbFactory Instance()

            private DbFactory(){}
    
//------------------------------------------------------------------------------------------------------

            public IDbConnection CreateConnection(){
                IDbConnection conn    =    null;
                switch(this._type){
                    
                    /**////如果是 MSSQL，则返回 SqlConnection
                    case ConnectionType.Sql:{
                        conn    =    new SqlConnection();
                        break;
                    }
                    /**////如果是 Oracle，则返回 OracleConnection
                    //case ConectionType.Oracle:{
                    //    conn    =    new OracleConnection();
                    //    break;
                    //}

                    /**////如果 是其他，则返回  OleDbConnction;
                    case ConnectionType.OleDb:{
                        conn    =    new OleDbConnection();
                        break;
                    }
                }

                /**////连接字符串。
                conn.ConnectionString    =    this._strConn;
                return conn;
            }/**////End  IDbConnection CreateConnection()

//------------------------------------------------------------------------------------------------------
            public IDbCommand CreateCommand(){

                /**////声明一个 IDbCommand 接口。
                IDbCommand cmd    =    null;

                switch(this._type){

                    /**////如果为 MSSQL
                    case ConnectionType.Sql:{
                        cmd    =    CreateConnection().CreateCommand();
                        break;
                    }

                    /**////如果为 Oracle
                    /**//*
                    case ConnectionType.Oracle:{
                        cmd    =    CreateConnection().CreateCommand();
                        break;
                    }
                    */

                    /**////如果为 其他。
                    case ConnectionType.OleDb:{
                        cmd    =    CreateConnection().CreateCommand();
                        break;
                    }
                }
                return cmd;
            }/**////End  IDbCommand CreateCommand()
        }
    //---------------------------------- 定义 数据层 类工厂 结束-----------------------------

    //************************************下面 再开始定义 数据层 类（MSSQL）************************

        /**////是基于 MSSQL 的 逻辑层 的父类
        public class SqlAccess{

            /**////定义全局的 SQL，在 逻辑层 中的类要用到。
            protected string Sql    =    null;
            
            /**////根据 类工厂，返回 类工厂 实例。
            private DbFactory df(){
                /**////实例一个类工厂
                DbFactory df    =    DbFactory.Instance();

                /**////选择 数据库 类型。
                df.type    =    ConnectionType.Sql;

                /**////修改 数据连接字符串
                df.strConn    =    "Server=(local);uid=sa;pwd=sa;database=Northwind";

                /**////返回 数据工厂 实例
                return df;
            }/**////end  DbFactory df()

            public SqlAccess(){}

            //SqlCommand conn    =    null;
            SqlCommand cmd    =    null;

//------------------------------------------------------------------------------------------------------

            /**//// 返回 SqlDataReader 对象的基础函数
            /// <summary>
            /// 执行SQL命令，返回 SqlDataReader 对象
            /// </summary>
            /// <param name="sql"> SQL 语句 </param>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> SqlDataReader 对象</returns>
            protected SqlDataReader SqlDR(String sql,CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////创建 SqlCommand 对象
                cmd    =    (SqlCommand)this.df().CreateCommand();

                try{
                    /**////填充 Command 对象。
                    this.PrepareCommand(cmd,sql,CmdType,cmdParams);

                    /**//// 返回 SqlDataReader，同时 关闭 Connection！切记！！
                    SqlDataReader dr    =    cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    /**////清空 Command.Parameters
                    cmd.Parameters.Clear();

                    /**////返回 SqlDataReader
                    return dr;
                }catch{

                    /**////异常时抛出且关闭连接。
                    cmd.Connection.Close();
                    throw;
                }
            }/**//// end SqlDR(String sql,CommandType CmdType,params SqlParameter[] cmdParams)

//------------------------------------------------------------------------------------------------------

            /**//// <summary>
            /// 执行SQL命令，只能是 T-SQL，不能是存储过程，返回 SqlDataReader 对象
            /// </summary>
            /// <returns> SqlDataReader 对象</returns>
            protected SqlDataReader SqlDR()
            {
                return SqlDR(this.Sql,CommandType.Text,null);
            }/**////end  SqlDR()

//------------------------------------------------------------------------------------------------------

            /**//// <summary>
            /// 执行SQL命令，返回 SqlDataReader 对象
            /// </summary>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> SqlDataReader 对象</returns>
            protected SqlDataReader SqlDR(CommandType CmdType,params SqlParameter[] cmdParams)
            {
                return SqlDR(this.Sql,CmdType,cmdParams);
            }/**//// end SqlDR(CommandType CmdType,params SqlParameter[] cmdParams)

//------------------------------------------------------------------------------------------------------

            /**//// <summary>
            /// 填充 SqlCommand 对象的  Parametes
            /// </summary>
            /// <param name="cmd"> SqlCommand 对象 </param>
            /// <param name="Sql"> SQL 语句 </param>
            /// <param name="CmdType"> SQL 的类型 </param>
            /// <param name="cmdParams"> 参数集合数组 </param>
            protected void PrepareCommand(SqlCommand cmd,string Sql,CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////如果已经打开，则不再进行 打开连接 操作。
                if(cmd.Connection.State!=ConnectionState.Open)
                    cmd.Connection.Open();

                /**////设置要 运行的文本，可能 为 SQL 或 SP
                cmd.CommandText    =    Sql;

                /**////设置运行的类型
                cmd.CommandType    =    CmdType;

                if(cmdParams!=null){
                    foreach(SqlParameter param in cmdParams){
                        /**////填充 cmd.Parameters 属性
                        cmd.Parameters.Add(param);
                    }
                }
            }/**////end PrepareCommand(SqlCommand cmd,string Sql,CommandType CmdType,params SqlParameter[] cmdParams)


//------------------------------------------------------------------------------------------------------
            /**//// <summary>
            /// 加上 事务 标志，填充 SqlCommand 对象的 Parametes
            /// </summary>
            /// <param name="cmd"> SqlCommand 对象 </param>
            /// <param name="Sql"> SQL 语句 </param>
            /// <param name="tran"> 事务 </param>
            /// <param name="CmdType"> SQL 的类型 </param>
            /// <param name="cmdParams"> 参数集合数组 </param>
            protected void PrepareCommand(SqlCommand cmd,string Sql,SqlTransaction tran,CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////如果已经打开，则不再进行 打开连接 操作。
                if(cmd.Connection.State!=ConnectionState.Open)
                    cmd.Connection.Open();

                /**////设置要 运行的文本，可能 为 SQL 或 SP
                cmd.CommandText    =    Sql;

                /**////设置运行的类型
                cmd.CommandType    =    CmdType;

                /**////设置事务
                //if(tran!=null)
                    cmd.Transaction    =    cmd.Connection.BeginTransaction();

                if(cmdParams!=null){
                    foreach(SqlParameter param in cmdParams){
                        /**////填充 cmd.Parameters 属性
                        cmd.Parameters.Add(param);
                    }
                }
            }/**////end PrepareCommand(SqlCommand cmd,string Sql,SqlTransaction tran,CommandType CmdType,params SqlParameter[] cmdParams)
//------------------------------------------------------------------------------------------------------
            /**////返回 SqlCommand 对象。
            ///以便 逻辑层 里可以通过 SqlCommand 对象返回 SqlParameter 里的值。
            /// <summary>
            /// 加上 事务 标志，填充 SqlCommand 对象的 Parametes
            /// </summary>
            /// <param name="cmd"> SqlCommand 对象 </param>
            /// <param name="Sql"> SQL 语句 </param>
            /// <param name="tran"> 事务 </param>
            /// <param name="CmdType"> SQL 的类型 </param>
            /// <param name="cmdParams"> 参数集合数组 </param>
            /// <returns> SqlCommand 对象 </returns>
            protected SqlCommand SqlCmd(CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////创建 SqlCommand 对象
                cmd    =    (SqlCommand)this.df().CreateCommand();

                /**////填充 SqlCommand.Parameters 集合
                this.PrepareCommand(cmd,Sql,CmdType,cmdParams);

                /**////返回 SqlCommand 对象
                return cmd;
            }/**////end SqlCmd(CommandType CmdType,params SqlParameter[] cmdParams)

//-----------------------------------------------------------------------------------------------------------

            /**////返回一个表的 DataSet 的基础函数
            /// <summary>
            /// 执行SQL命令，返回 DataSet 对象
            /// </summary>
            /// <param name="sql"> SQL 语句 </param>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> DataSet 对象</returns>
            protected DataSet SqlDS(string sql,CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////创建 SqlCommand 对象
                SqlCommand cmd    =    (SqlCommand)this.df().CreateCommand();


                /**////定义 DataSet
                DataSet ds    =    new DataSet();

                try
                {
                    /**////填充 SqlCommand.Parameters 集合
                    this.PrepareCommand(cmd,sql,CmdType,cmdParams);

                    /**////根据 SqlCommand 构造 SqlDataAdapter 对象
                    SqlDataAdapter sda    =    new SqlDataAdapter(cmd);

                    /**////由于在父类中已经打开连接，所以 逻辑层 不需要再打开
                    //cmd.Connection.Open();

                    /**////填充 DataSet
                    sda.Fill(ds);
                }
                catch
                {
                    /**////异常时抛出 且 关闭连接！！
                    cmd.Connection.Close();
                    throw;
                }
                finally
                {
                    /**////关闭连接
                    cmd.Connection.Close();
                }

                /**////返回 DataSet
                return ds;
            }/**////end SqlDS(string sql,CommandType CmdType,params SqlParameter[] cmdParams)

//--------------------------------------------------------------------------------------------------------
            /**//// <summary>
            /// 返回一个表的 DataSet 对象，只能根据 SQL，存储过程不行。
            /// </summary>
            /// <returns> DataSet 对象</returns>
            protected DataSet SqlDS()
            {    
                return this.SqlDS(this.Sql,CommandType.Text,null);

            }/**////end SqlDS()
//-----------------------------------------------------------------------------------------------------------
            /**//// <summary>
            /// 根据 SQL，返回一个表的 DataSet 对象
            /// </summary>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> DataSet 对象</returns>
            protected DataSet SqlDS(CommandType CmdType,params SqlParameter[] cmdParams)
            {

                return this.SqlDS(this.Sql,CmdType,cmdParams);

            }/**////end SqlDS(CommandType CmdType,params SqlParameter[] cmdParams)
///----------------------------------------------------------------------------------------------------------

            /// 返回 影响行数 的基础函数
            /// <summary>
            /// 执行SQL命令而不返回查询数据，通常执行关于对数据源进行添加，删除，更新的操作
            /// </summary>
            /// <param name="sql"> SQL 语句 </param>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> 返回受影响的行数 </returns>
            protected int SqlNonQuery(String sql,CommandType CmdType,params SqlParameter[] cmdParams)
            {
                /**////创建 SqlCommand 对象
                SqlCommand cmd =    (SqlCommand)this.df().CreateCommand();

                /**////定义 变量 i
                int i    =    0;

                try
                {
                    /**////填充 SqlCommand 对象的 SqlParameters 集合
                    this.PrepareCommand(cmd,sql,CmdType,cmdParams);

                    /**////执行 SQL，并返回 影响行数
                    i    =    cmd.ExecuteNonQuery();

                    /**////清空 Parameters 集合
                    cmd.Parameters.Clear();
                }
                catch
                {                    
                    /**////发生异常关闭连接且抛出。
                    cmd.Connection.Close();
                    throw;
                }
                finally
                {
                    /**////关闭连接。
                    cmd.Connection.Close();
                }

                /**////返回 影响行数
                return i;

            }/**////End SqlNonQuery(String sql,CommandType CmdType,params SqlParameter[] cmdParams)

///----------------------------------------------------------------------------------------------------------

            /// <summary>
            /// 执行SQL命令而不返回查询数据，通常执行关于对数据源进行添加，删除，更新的操作
            /// </summary>
            /// <returns> 返回受影响的行数 </returns>
            protected int SqlNonQuery()
            {

                return SqlNonQuery(this.Sql,CommandType.Text,null);

            }/**////end SqlNonQuery()
///----------------------------------------------------------------------------------------------------------
            /// <summary>
            /// 执行SQL命令而不返回查询数据，通常执行关于对数据源进行添加，删除，更新的操作
            /// </summary>
            /// <param name="CmdType"> SQL 类型</param>
            /// <param name="cmdParams">参数集合数组</param>
            /// <returns> 返回受影响的行数 </returns>
            protected int SqlNonQuery(CommandType CmdType,params SqlParameter[] cmdParams)
            {
    
                return SqlNonQuery(this.Sql,CmdType,cmdParams);

            }/**////End SqlNonQuery(CommandType CmdType,params SqlParameter[] cmdParams)



        }/**////end class SqlAccess


    //************************************** 定义 数据层 结束***************************************************

    //------------------------------- 下面 定义 逻辑层 ------------------------------

    /**////操作 Northwind 数据中  Orders 表。
    ///继承于 SqlAccess 类。
    public class Orders:SqlAccess{

        /**////构造函数私有化，必须能过 Instance 入口实例本类。
        private Orders(){}

        /**////Orders 类的局部变量
        private static Orders _orders    =    null;

        /**////实例化本类的入口，实现 单态模式
        public static Orders Instance(){
            if(_orders==null)
                _orders    =    new Orders();
            return _orders;
        }

//------------------------------------------------------------------------------------------------------
        /**////返回 Orders 表的 DataReader
        public SqlDataReader OrderDR(){
            /**////Sql 在 父类中已经定义。
            this.Sql    =    "SELECT * FROM orders WHERE EmployeeID=@EmployeeID AND DATEDIFF(d,OrderDate,@OrderDate)>0";

            SqlParameter[] prms    =    {
                                        //new SqlParameter("@EmployeeID",SqlDbType.VarChar,5),
                                        //new SqlParameter("@OrderDate",SqlDbType.DateTime)
                                        new SqlParameter("@EmployeeID",SqlDbType.VarChar,5,ParameterDirection.Input,true,0,0,"EmployeeID",DataRowVersion.Current,"5")
                                        ,new SqlParameter("@OrderDate",DateTime.Now.ToString())
                                    };
            //prms[0].Value    =    "5";
            //prms[1].Value    =    DateTime.Now.ToString();

            //返回 DataReader 数据流
            return SqlDR(CommandType.Text,prms);
        }

//------------------------------------------------------------------------------------------------------

        /**////返回 Orders 表的 DataSet
        public DataSet OrderDS(){
            /**////Sql 在 父类中已经定义。
            this.Sql    =    "SELECT * FROM orders WHERE EmployeeID=@EmployeeID AND DATEDIFF(d,OrderDate,@OrderDate)>0";

            SqlParameter[] prms    =    {
                                        new SqlParameter("@EmployeeID",SqlDbType.VarChar,5,ParameterDirection.Input,true,0,0,"EmployeeID",DataRowVersion.Current,"5"),
                                        new SqlParameter("@OrderDate",DateTime.Now.ToString())
                                    };
            return this.SqlDS(CommandType.Text,prms);
        }

//------------------------------------------------------------------------------------------------------
        /**////返回执行 存储过程 SalesByCategory 的数据流
        public SqlDataReader SalesByCategoryDR(){
            this.Sql    =    "SalesByCategory";
            SqlParameter[] prms    =    {
                                        new SqlParameter("@CategoryName","Beverages"),
                                        new SqlParameter("@OrdYear","1998")
                                    };
            return this.SqlDR(CommandType.StoredProcedure,prms);
        }

//------------------------------------------------------------------------------------------------------

        /**////返回 存储过程 sp_GetOutPutVal 的 OutPut 类型值 和 返回值。
        public string GetOutPutVal(){

            /**////设置 存储过程 。
            this.Sql    =    "sp_GetOutPutVal";
            SqlParameter[] prms    =    {
                                        new SqlParameter("ReturnValue",SqlDbType.Int),        /**////该值为 ReturnValue
                                        new SqlParameter("@OrderID","10248"),
                                        new SqlParameter("@CustomerID",SqlDbType.VarChar,8)    /**////该值为 Output 类型的值
                                    };

            /**////设置 ReturnValue
            prms[0].Direction    =    ParameterDirection.ReturnValue;

            /**//// 第 3 个参数为 Output 类型的值，所以需要指定 ParameterDirection
            prms[2].Direction    =    ParameterDirection.Output;
            
            SqlCommand cmd    =    this.SqlCmd(CommandType.StoredProcedure,prms);
            try{
                string CustomerID    =    "";
                cmd.ExecuteNonQuery();

                /**////Output 类型值
                if(cmd.Parameters.Contains("@CustomerID"))
                    CustomerID    =    "Output 类型的值："+cmd.Parameters["@CustomerID"].Value.ToString();
                else
                    CustomerID    =    "Output 类型的值："+"无值";

                /**////ReturnValue 值
                if(cmd.Parameters.Contains("ReturnValue"))
                    CustomerID    +=    "，"+"返回值为："+cmd.Parameters["ReturnValue"].Value.ToString();
                
                return CustomerID;
            }
            catch
            {
                cmd.Connection.Close();
                throw;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        /**////新增记录，还未实现。
        public int AddTest(){
            this.Sql    =    "sp_addtest";

            SqlParameter[] prms    =    {
                                        new SqlParameter("@a","")
                                    };
            int i    =    this.SqlNonQuery(CommandType.StoredProcedure,prms);
            return i;
        }
    }
    //------------------------------- 定义 逻辑层 结束 ------------------------------

    //******************************* 定义 表现层 ***********************************

    /**////显示 Orders 表数据。
    public class OrdersClient{

        /**////实例本 表现层 的逻辑类
        private Orders _orders    =    Orders.Instance();

        /**////在控制台中打印出数据。
        ///在 aspx 中实际就是 DataBind() 的实现。
        public void write(){
//------------------------------------------------------------------------------------
            SqlDataReader dr    =    _orders.OrderDR();
            int i    =    0;
            Console.WriteLine("");
            while(dr.Read()){
                i++;
                Console.WriteLine("{0,-20}{1,-30}",dr["CustomerID"].ToString(),dr["OrderDate"].ToString());
            }
            dr.Close();
            
            Console.WriteLine();
//------------------------------------------------------------------------------------
            /**////返回 执行存储过程  SalesByCategory 的返回结果
            dr    =    _orders.SalesByCategoryDR();
            i    =    0;
            Console.WriteLine("ProductName-----    ----TotalPurchase");
            while(dr.Read()){
                i++;
                Console.WriteLine("{0,-20}{1,-30}",dr["ProductName"].ToString(),dr["TotalPurchase"].ToString());
            }
            dr.Close();

            Console.WriteLine();
//-------------------------------------------------------------------------------------
            /**////返回 存储过程 GetOutPutVal 的 Output 类型值
            Console.WriteLine(_orders.GetOutPutVal());

            Console.WriteLine();
//-------------------------------------------------------------------------------------
            /**////根据 Orders 表的 DataSet 进行绑定
            DataRowCollection    rows    =    _orders.OrderDS().Tables[0].Rows;
            Console.WriteLine("客户ID------    ------订单日期");
            for(i=0;i<rows.Count;i++)
                Console.WriteLine("{0,-20}{1,-30}",rows[i]["CustomerID"].ToString(),rows[i]["OrderDate"].ToString());

            Console.WriteLine();
/**////------------------------------------------------------------------------------------
            Console.WriteLine(_orders.AddTest().ToString());
        }

        public static void Main(){
            OrdersClient oc    =    new OrdersClient();
            oc.write();
        }
    }