用于操作数据库的通用模块

在开发过程中有大量涉及到与数据库相关的操作，为了便于开发和维护，把这些操作封装成类，实现代码复用原则。

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

namespace CommonClass
{
    public class DBExecute
    {
        private static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

        /// <summary>
        /// 无返回值的数据库操作，如插入、删除等；
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static int ExecuteSql(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, conn);

            try
            {
                conn.Open();        //打开数据库
                cmd.ExecuteNonQuery();  //执行无返回值的数据库操作
                return 0;
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message); //抛出异常
            }
            finally
            {
                cmd.Dispose();  //释放该组件占用的资源
                conn.Close();   //每次操作完毕都要关闭连接
            }
        }

        /// <summary>
        /// 返回值为DataReader类型的操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static int ExecuteSqlReader(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, conn);

            try
            {
                conn.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    return 0;   //执行成功，返回 0
                }
                else
                {
                    throw new Exception("Value Unavailable!");
                }
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 返回值为DataSet类型的操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static DataSet ExecuteSqlDS(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);

            try
            {
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
                DataSet ds = new DataSet("ds");
                sda.Fill(ds);   //调用SqlDataAdapter的Fill方法，为DataSet填充数据
                return ds;  //返回得到的DataSet对象，保存了从数据库查询到的数据
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 返回值为int类型的操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static int ExecuteSqlValue(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, conn);

            try
            {
                conn.Open();
                object o = cmd.ExecuteScalar();
                if (Object.Equals(o, null)) //判断对象是否为空
                {
                    throw new Exception("Value Unavailable!");
                }
                else
                {
                    return (int)o;
                }
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 直接返回object
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        protected static object ExecuteSqlObj(string strSQL)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            try
            {
                conn.Open();
                object o = cmd.ExecuteScalar();
                if (Object.Equals(o, null))
                {
                    throw new Exception("Object Unavailable!");
                }
                else
                {
                    return o;
                }
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 执行多条无返回值的数据库操作
        /// </summary>
        /// <param name="strSQLs"></param>
        /// <returns></returns>
        public static int ExecuteSqls(string[] strSQLArr)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand();
            int i = strSQLArr.Length; //获取字符串数组中字符串的个数

            try
            {
                conn.Open();
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            SqlTransaction tran = conn.BeginTransaction();

            try
            {
                cmd.Connection = conn;
                cmd.Transaction = tran;
                foreach (string str in strSQLArr)
                {
                    cmd.CommandText = str;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();  //提交事务
                return 0;
            }
            catch (SqlException e)
            {
                tran.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 执行存储过程的，返回值为object类型
        /// </summary>
        /// <param name="ProcName">存储过程名称</param>
        /// <param name="parm">参数</param>
        /// <returns>object</returns>
        public static object ExecuteProcGetObject(string ProcName, SqlParameter[] parm)
        {
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            if (parm != null)
            {
                foreach (SqlParameter p in parm)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
            }
            cmd.CommandText = ProcName;
            cmd.Connection = conn;
            cmd.CommandTimeout = 0;
            object obj = null;

            try
            {
                conn.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }

            return obj;

        }

        /// <summary>
        /// 执行存储过程，返回值为DataSet类型
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static DataSet ExecuteProcGetDataSet(string ProcName, SqlParameter[] parm)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            if (parm != null)
            {
                foreach (SqlParameter p in parm)
                {
                    if (p != null)
                    {
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        cmd.Parameters.Add(p);
                    }
                }
            }
            cmd.CommandText = ProcName;
            cmd.Connection = conn;
            cmd.CommandTimeout = 0;
            SqlDataAdapter sda = new SqlDataAdapter(cmd);

            try
            {
                conn.Open();
                sda.Fill(ds);
            }
            catch (SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }

            return ds;

        }

        /// <summary>
        /// 执行存储过程，返回值为DataTable类型
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcGetDataTable(string ProcName, SqlParameter[] parm)
        {
            DataSet ds = ExecuteProcGetDataSet(ProcName, parm);
            if (ds == null) return null;
            if (ds.Tables.Count >= 1)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

    }
}