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

Question

大家好，我们老师让我们用简单工厂模式实现数据访问层，下面是我写的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!

Answer 1

你这是wrapper，非 Factory.

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

Answer 2

过于简单了，我可以给你一个，你参考一下。工厂模式，要先定义一个接口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