none
都来看看这段代码有什么问题 RRS feed

  • 问题

  • 大家好,我们老师让我们用简单工厂模式实现数据访问层,下面是我写的SqlHelper类(继承于IDbHelper),大家帮忙看看是否规范,有什么问题,如何改进?请不吝赐教,谢谢!!!

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;

    namespace Tiers.DataAccessor
    {
        class SqlHelper : IDbHelper
        {
            /// <summary>
            /// Constructor
            /// </summary>
            /// <param name="connStr">Connection string of database</param>
            public SqlHelper(string connStr)
            {
                this.connectionString = connStr;
            }

            private string connectionString;

            private SqlConnection sqlConn;
            private SqlCommand sqlCmd;


            #region IDbHelper members

            private DbConnection OpenConnetion()
            {
                try
                {
                    sqlConn = new SqlConnection(this.connectionString);
                    sqlConn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return sqlConn;
            }

            private void CloseConnetion()
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }

            /// <summary>
            /// Execute non query command
            /// </summary>
            /// <param name="queryString">query statement</param>
            public void ExecuteQueryString(string queryString)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = queryString;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                sqlCmd.ExecuteNonQuery();
                CloseConnetion();
            }

            /// <summary>
            /// Execute non query by procdure
            /// </summary>
            /// <param name="procName">Procdure name</param>
            public void ExecuteProc(string procName)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                sqlCmd.ExecuteNonQuery();
                CloseConnetion();
            }

            /// <summary>
            /// Execute non query by procdure has parameters
            /// </summary>
            /// <param name="procName">Procdure name</param>
            /// <param name="parameters">Procdure parameters</param>
            public void ExecuteProcHasPara(string procName, params string[] parameters)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                for (int i = 0; i < sqlCmd.Parameters.Count; i++)
                {
                    sqlCmd.Parameters[i].Value = parameters[i];
                }
                sqlCmd.ExecuteNonQuery();
                CloseConnetion();
            }

            public DataSet QueryStringGetDs(string queryString)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = queryString;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                CloseConnetion();
                return ds;
            }

            public DataSet ProcGetDs(string procName)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                CloseConnetion();
                return ds;
            }

            public DataSet ProcHasParaGetDs(string procName, params string[] parameters)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                for (int i = 0; i < sqlCmd.Parameters.Count; i++)
                {
                    sqlCmd.Parameters[i].Value = parameters[i];
                }
                SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                CloseConnetion();
                return ds;
            }

            public DbDataReader QueryStringGetDr(string queryString)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = queryString;
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlDataReader dr;
                dr = sqlCmd.ExecuteReader();
                CloseConnetion();
                return dr;
            }

            public DbDataReader ProcGetDr(string procName)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlDataReader dr;
                dr = sqlCmd.ExecuteReader();
                CloseConnetion();
                return dr;
            }

            public DbDataReader ProcHasParaGetDr(string procName, params string[] parameters)
            {
                sqlCmd = new SqlCommand();
                sqlCmd.CommandText = procName;
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Connection = OpenConnetion() as SqlConnection;
                SqlCommandBuilder.DeriveParameters(sqlCmd);
                for (int i = 0; i < sqlCmd.Parameters.Count; i++)
                {
                    sqlCmd.Parameters[i].Value = parameters[i];
                }
                SqlDataReader dr;
                dr = sqlCmd.ExecuteReader();
                CloseConnetion();
                return dr;
            }

            #endregion
        }
    }


    Never bend, never break, never back down!
    2010年7月10日 8:11

答案

  • 过于简单了,我可以给你一个,你参考一下。工厂模式,要先定义一个接口IDataOperate,公开所有的数据操作,比如执行SQL的,执行存储过程的,还有有不同的返回值类型,比如DataTable,DataSet,bool等等,这个你有了。

    之后,各种数据库都要继承这个接口,比如Oracle类,用来访问Oracle数据库;Access类,用来访问Access数据库;SQLServer类,用来访问SQL Server;Table类,用来访问Excel,等等。

    最后定义Factory类,通过配置文件,或是参数,知道我想访问什么数据库,这样从工厂出来后,就构造好了。同时,Factory类必须用接口编程。

    为了方便起见,我们会创建一个类BaseDataCommon,提供默认的数据库访问,或是说,经常使用的数据库类型,让业务层的类继承这个类。


    1+1=The World http://blog.csdn.net/liuning800203
    • 已标记为答案 Sven K 2010年7月14日 0:43
    2010年7月13日 15:27

全部回复

  • 你这是wrapper,非 Factory.

    Factory在于抽象出对多种不同数据库的操作接口。

    2010年7月11日 3:17
  • 过于简单了,我可以给你一个,你参考一下。工厂模式,要先定义一个接口IDataOperate,公开所有的数据操作,比如执行SQL的,执行存储过程的,还有有不同的返回值类型,比如DataTable,DataSet,bool等等,这个你有了。

    之后,各种数据库都要继承这个接口,比如Oracle类,用来访问Oracle数据库;Access类,用来访问Access数据库;SQLServer类,用来访问SQL Server;Table类,用来访问Excel,等等。

    最后定义Factory类,通过配置文件,或是参数,知道我想访问什么数据库,这样从工厂出来后,就构造好了。同时,Factory类必须用接口编程。

    为了方便起见,我们会创建一个类BaseDataCommon,提供默认的数据库访问,或是说,经常使用的数据库类型,让业务层的类继承这个类。


    1+1=The World http://blog.csdn.net/liuning800203
    • 已标记为答案 Sven K 2010年7月14日 0:43
    2010年7月13日 15:27