小型SqlHelper执行类

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

namespace SMS.DBUtility
{
    /// <summary>
    /// 数据操作类
    /// </summary>
    public class SqlHelper
    {
        private readonly static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        /// <summary>
        /// 执行一条sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回值</returns>
        public static int ExecuteSql(string strSql)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(strSql, con))
                {
                    try
                    {
                        con.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException e)
                    {
                        con.Close();
                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        con.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 执行带参sql语句,返回影响条数
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteSql(string strSql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try 
                {
                    PrepareCommand(cmd, con, null, strSql, parameters);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行带参sql语句,返回数据SqlDataReader
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="parameters"></param>
        /// <returns>返回值</returns>
        public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                try
                {
                    PrepareCommand(cmd, con, null, strSql, parameters);
                    SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return sdr;
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="ProcName">过程名</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public static SqlDataReader RunProcedure(string ProcName, params IDataParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection())
            {
                try
                {
                    SqlDataReader sdr = new SqlDataReader();
                    SqlCommand cmd = BuildCommand(con, ProcName, parameters);
                    sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    cmd.Parameters.Clear();
                    return sdr;
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
        }
        
        /// <summary>
        /// 分解存储过程参数
        /// </summary>
        /// <param name="con"></param>
        /// <param name="ProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static SqlCommand BuildCommand(SqlConnection con, string ProcName, params IDataParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand(ProcName, con);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                //检查未分配值的输出参数,将其分配以DBNull.Value.
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                command.Parameters.Add(parameter);
            }
            return cmd;
        }

        /// <summary>
        /// 分解参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="con"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        public static void PrepareCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (con.State != ConnectionState.Open)
                con.Open();
            cmd.Connection = con;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}