常用的数据访问底层类

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

/*
 * 所属层次：数据访问类组件(数据操作)
 */
namespace DBUtility
{
    /// <summary>
    /// SqlHelper类用于操作数据库(可升级，扩展) 抽象类+静态方法。
    /// </summary>
    public abstract class SqlHelper
    {

        //数据库连接字符串
        //连接字符串在界面层的...的配置文件中。
        //public static readonly string ConnectionStringSqlServer = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
      

        /// <summary>
        ///  给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="commandText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>执行命令所影响的行数</returns>
        public static int ExecuteNonQuery( CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;

                if (cmd.Parameters != null)
                {
                    cmd.Parameters.Clear();
                }
                if (commandParameters != null)
                {
                    foreach (SqlParameter parm in commandParameters)
                        cmd.Parameters.Add(parm);
                }

                try
                {
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
                catch
                {
                    //关闭连接，抛出异常
                    conn.Close();
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行存储过程，返回一个输出参数
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>输出参数</returns>
        public static object ExecuteProcGetOutput( string cmdText, string outputParaName, SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandType = CommandType.StoredProcedure;

                if (commandParameters != null)
                {
                    foreach (SqlParameter parm in commandParameters)
                        cmd.Parameters.Add(parm);
                }

                try
                {
                    cmd.ExecuteNonQuery();
                    object val = cmd.Parameters[outputParaName].Value;
                    cmd.Parameters.Clear();
                    return val;
                }
                catch
                {
                    //关闭连接，抛出异常
                    conn.Close();
                    throw;
                }
            }
        }

        /// <summary>
        /// 用执行的数据库连接执行一个返回数据集的sql命令
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="commandType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="commandText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>包含结果的读取器</returns>
        public static SqlDataReader ExecuteReader( CommandType cmdType, string cmdText, SqlParameter[] commandParameters)
        {
            //创建一个SqlCommand对象
            SqlCommand cmd = new SqlCommand();
            //创建一个SqlConnection对象
            SqlConnection conn = new SqlConnection(connectionString);
            //不能使用using 因为要返回SqlDataReader,否则外面的SqlDataReader不能使用。

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
            try
            {
                //调用 SqlCommand  的 ExecuteReader 方法
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //清除参数
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }

        }
   
       
        /// <summary>
        /// 用执行的数据库连接执行一个返回一张表命令
        /// </summary>
        /// <param name="connectionString">一个有效的连接字符串</param>
        /// <param name="commandText">存储过程名称或者sql命令语句</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>包含结果的读取器</returns>
        public static DataTable ExectuteDataTable(string cmdText, SqlParameter[] commandParameters)
        {
           
            //创建一个SqlConnection对象
            SqlConnection conn = new SqlConnection(connectionString);
            //创建一个SqlDataAdapter对象
            SqlDataAdapter sda = new SqlDataAdapter(cmdText,conn);

            if (commandParameters != null)
            {
                foreach (SqlParameter parm in commandParameters)
                    sda.SelectCommand.Parameters.Add(parm);
            }
            try
            {
                //调用 SqlCommand  的 ExecuteReader 方法
                DataTable dt = new DataTable();
                sda.Fill(dt);
                //清除参数
                sda.SelectCommand.Parameters.Clear();
                return dt;
            }
            catch
            {
                conn.Close();
                throw;
            }

        }
   
    }
}