自己写了个DbHelper类

Question

/// <summary>
    /// 数据库操作类
    /// </summary>
    public sealed class DbHelper
    {
        //防止被类的使用者实例化(new DbHelper())
        private DbHelper() { }        #region 数据库操作方法        /// <summary>
        /// 执行数据库语句返回受影响的行数，失败或异常返回-1
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection=false;
            int result = 0;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteNonQuery();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一行第一列，失败或异常返回null
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            bool mustCloseConnection = false;
            object result = null;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteScalar();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一个内存表
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            DataTable dataTable = new DataTable();
            dataTable.Load(ExecuteReader(connection,commandText,commandType,parameter));
            return dataTable;
        }        /// <summary>
        /// 执行数据库语句返回一个自进结果集流
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection = false;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            IDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            bool canClear = true;
            foreach (IDataParameter commandParameter in command.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }
            if (canClear)
            {
                command.Parameters.Clear();
            }
            return dataReader;
        }        #endregion        #region private utility methods        /// <summary>
        /// 向命令添加参数
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="commandParameters">要添加的参数</param>
        private static void AttachParameters(IDbCommand command, IDataParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandParameters != null)
            {
                foreach (IDataParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }        /// <summary>
        /// 初始化命令
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandParameters">数据库参数</param>
        /// <param name="mustCloseConnection">返回一个bool值，如果是方法内部打开的连接则返回true，否则返回false</param>
        private static void PrepareCommand(IDbCommand command, IDbConnection connection, CommandType commandType, string commandText, IDataParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("SQL语句为空");
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }        #endregion
    }    
请大家给点建议这样写好不好

Answer 1

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;namespace LBC.Utility.DB
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public sealed class DbHelper
    {
        //防止被类的使用者实例化(new DbHelper())
        private DbHelper() { }        #region 数据库操作方法        /// <summary>
        /// 执行数据库语句返回受影响的行数，失败或异常返回-1
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection=false;
            int result = 0;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteNonQuery();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一行第一列，失败或异常返回null
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            bool mustCloseConnection = false;
            object result = null;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteScalar();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一个内存表
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            DataTable dataTable = new DataTable();
            dataTable.Load(ExecuteReader(connection,commandText,commandType,parameter));
            return dataTable;
        }        /// <summary>
        /// 执行数据库语句返回一个自进结果集流
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection = false;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            IDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            bool canClear = true;
            foreach (IDataParameter commandParameter in command.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }
            if (canClear)
            {
                command.Parameters.Clear();
            }
            return dataReader;
        }        #endregion        #region private utility methods        /// <summary>
        /// 向命令添加参数
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="commandParameters">要添加的参数</param>
        private static void AttachParameters(IDbCommand command, IDataParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandParameters != null)
            {
                foreach (IDataParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }        /// <summary>
        /// 初始化命令
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandParameters">数据库参数</param>
        /// <param name="mustCloseConnection">返回一个bool值，如果是方法内部打开的连接则返回true，否则返回false</param>
        private static void PrepareCommand(IDbCommand command, IDbConnection connection, CommandType commandType, string commandText, IDataParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("SQL语句为空");
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }        #endregion
    }    
}

Answer 2

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;namespace LBC.Utility.DB
{
    /// <summary>
    /// 数据库操作类
    /// </summary>
    public sealed class DbHelper
    {
        //防止被类的使用者实例化(new DbHelper())
        private DbHelper() { }        /// <summary>
        /// 执行数据库语句返回受影响的行数，失败或异常返回-1
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection=false;
            int result = 0;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteNonQuery();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一行第一列，失败或异常返回null
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            bool mustCloseConnection = false;
            object result = null;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            result = command.ExecuteScalar();
            command.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return result;
        }        /// <summary>
        /// 执行数据库语句返回第一个内存表
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            } 
            DataTable dataTable = new DataTable();
            dataTable.Load(ExecuteReader(connection,commandText,commandType,parameter));
            return dataTable;
        }        /// <summary>
        /// 执行数据库语句返回一个自进结果集流
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="parameter">数据库参数</param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(IDbConnection connection, string commandText, CommandType commandType, params IDataParameter[] parameter)
        {
            if (connection == null || string.IsNullOrEmpty(connection.ConnectionString))
            {
                throw new ArgumentException("connection参数为null或者提供的连接字符串为空！");
            }
            bool mustCloseConnection = false;
            IDbCommand command = connection.CreateCommand();
            PrepareCommand(command, connection, commandType, commandText, parameter, out mustCloseConnection);
            IDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            bool canClear = true;
            foreach (IDataParameter commandParameter in command.Parameters)
            {
                if (commandParameter.Direction != ParameterDirection.Input)
                    canClear = false;
            }
            if (canClear)
            {
                command.Parameters.Clear();
            }
            return dataReader;
        }        /// <summary>
        /// 向命令添加参数
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="commandParameters">要添加的参数</param>
        private static void AttachParameters(IDbCommand command, IDataParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandParameters != null)
            {
                foreach (IDataParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }        /// <summary>
        /// 初始化命令
        /// </summary>
        /// <param name="command">IDbCommand对象</param>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="transaction">数据库事务</param>
        /// <param name="commandType">解释命令的字符串</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandParameters">数据库参数</param>
        /// <param name="mustCloseConnection">返回一个bool值，如果是方法内部打开的连接则返回true，否则返回false</param>
        private static void PrepareCommand(IDbCommand command, IDbConnection connection, CommandType commandType, string commandText, IDataParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("创建数据库命令对象（IDbCommand对象）时失败！");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("SQL语句为空");
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }    }    
}

Answer 3

  有个小错误。
  public static IDataReader ExecuteReader(...
  ...
            if (canClear)
            {
                command.Parameters.Clear();
                break;                         //这里要加break。
            }
  ...

Answer 4

应该在以下的地方加break;
 if (commandParameter.Direction != ParameterDirection.Input)
                {
                    canClear = false;
                    break;
                }这样做能勉去得到结果后多余的循环