DBHelper

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using System.Collections.Generic;
using System.Diagnostics;
///<summary>
/// DBHelper 的摘要说明
///</summary>
namespace DAL
{
    publicstaticclass DBHelper
    {

        privatestatic SqlConnection conn;  //定义一个连接对象
privatestatic SqlTransaction trans;//定义一个事务对象

        privatestaticstring connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["FredConnectionString"].ConnectionString;


        #region 打开连接
        ///<summary>
        /// 打开连接  单态模式
        ///</summary>
publicstaticvoid Open()
        {
            if (conn ==null)
            {
                conn =new SqlConnection(connectionString);
                conn.Open();
            }
            //判断连接是否关闭，如关闭再次打开
if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            //如果连接中断，重新打开
if (conn.State == System.Data.ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        #endregion

        #region 关闭连接
        ///<summary>
        /// 关闭数据库连接
        ///</summary>
publicstaticvoid Close()
        {
            if (conn !=null)
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }

            }

        }
        #endregion

        #region 执行 DML语句
        ///<summary>
        /// 执行sql语句返回影响的行数
        ///</summary>
        ///<param name="safeSql">字符串</param>
        ///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string safeSql)
        {
            Open();
            SqlCommand cmd =new SqlCommand(safeSql, conn);
            int result = cmd.ExecuteNonQuery();
            Close();
            return result;
        }
        ///<summary>
        /// 执行sql语句返回影响的行数
        ///</summary>
        ///<param name="sql">字符串</param>
        ///<param name="values">参数数组</param>
        ///<returns>影响的行数</returns>
publicstaticint ExecuteCommand(string sql, params SqlParameter[] values)
        {
            Open();
            SqlCommand cmd =new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(values);
            int result = cmd.ExecuteNonQuery();
            Close();
            return result;
        }
        ///<summary>
        /// 执行sql语句，返回一行一列的值
        ///</summary>
        ///<param name="safeSql">字符串</param>
        ///<returns>结果</returns>
publicstaticint GetScalar(string safeSql)
        {
            Open();
            SqlCommand cmd =new SqlCommand(safeSql, conn);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            Close();
            return result;
        }
        ///<summary>
        /// 执行sql语句，返回一行一列的值
        ///</summary>
        ///<param name="sql">字符串</param>
        ///<param name="values">参数数组</param>
        ///<returns>结果</returns>
publicstaticint GetScalar(string sql, params SqlParameter[] values)
        {
            Open();
            SqlCommand cmd =new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            Close();
            return result;
        }
        ///<summary>
        /// 执行sql语句，返回DataReader
        ///</summary>
        ///<param name="safeSql">字符串</param>
        ///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string safeSql)
        {
            Open();
            SqlCommand cmd =new SqlCommand(safeSql, conn);
            SqlDataReader reader = cmd.ExecuteReader();

            return reader;
        }
        ///<summary>
        /// 执行sql语句，返回DataReader
        ///</summary>
        ///<param name="sql">字符串</param>
        ///<param name="values">参数数组</param>
        ///<returns>DataReader</returns>
publicstatic SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            Open();
            SqlCommand cmd =new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        ///<summary>
        /// 执行sql语句，返回表
        ///</summary>
        ///<param name="safeSql">字符串</param>
        ///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string safeSql)
        {
            Open();
            DataSet ds =new DataSet();
            SqlCommand cmd =new SqlCommand(safeSql, conn);
            SqlDataAdapter da =new SqlDataAdapter(cmd);
            da.Fill(ds);
            Close();
            return ds.Tables[0];
        }
        ///<summary>
        /// 执行sql语句，返回表
        ///</summary>
        ///<param name="sql">字符串</param>
        ///<param name="values">参数数组</param>
        ///<returns>数据表</returns>
publicstatic DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            Open();
            DataSet ds =new DataSet();
            SqlCommand cmd =new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da =new SqlDataAdapter(cmd);
            da.Fill(ds);
            Close();
            return ds.Tables[0];
        }

        #endregion

        #region 事务处理
        //开始事务
publicstaticvoid BeginTrans()
        {
            Open();
            trans = conn.BeginTransaction();
        }
        //执行事务
publicstaticint ExecuteTrans(string sSQL)
        {
            try
            {

                SqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                cmd.CommandText = sSQL;
                cmd.CommandTimeout =600;
                int i = cmd.ExecuteNonQuery();

                return i;
            }
            catch (Exception e)
            {
                thrownew Exception(e.Message);
            }

        }
        //提交事务
publicstaticvoid CommitTrans()
        {
            trans.Commit();
            Close();
        }

        //回滚事务
publicstaticvoid RollBackTrans()
        {
            trans.Rollback();
            Close();
        }
        #endregion

        #region 创建SqlCommand，只在类内部调用
        privatestatic SqlCommand CreateCommand(string sProcName, params SqlParameter[] parms)
        {
            //打开连接
            Open();

            SqlCommand cmd =new SqlCommand(sProcName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout =600;

            //传参
if (parms !=null)
            {

                cmd.Parameters.AddRange(parms);
            }

            //返回参数
            cmd.Parameters.Add(new SqlParameter("Return Value", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return cmd;
        }
        #endregion

        #region  执行存储过程
        ///<summary>
        /// 无参数存储过程
        ///</summary>
        ///<param name="sProcName">存储过程名</param>
        ///<returns>返回Int</returns>
publicstaticint RunProc(string sProcName)
        {
            SqlCommand cmd = CreateCommand(sProcName, null);

            try
            {
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return-1;
            }
            finally
            {
                Close();
            }
            return (int)cmd.Parameters["Return Value"].Value;
        }
        #endregion


        #region 执行带参数的存储过程
        ///<summary>
        /// 执行带参数的存储过程
        ///</summary>
        ///<param name="sProcName">存储过程名称</param>
        ///<param name="p">参数数组</param>
        ///<returns>返回值</returns>
publicstaticint RunProc(string sProcName, params SqlParameter[] p)
        {
            SqlCommand cmd = CreateCommand(sProcName, p);
            try
            {
                cmd.ExecuteNonQuery();
                Close();
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                return-1;
            }
            finally
            {
                Close();
            }
            return (int)cmd.Parameters["Return Value"].Value;
        }
        #endregion

        #region 执行带参数的存储过程，返回output参数
        ///<summary>
        /// 执行带参数的存储过程，返回output参数
        ///</summary>
        ///<param name="sProcName">存储过程名称</param>
        ///<param name="p">参数数组</param>
        ///<returns>返回值</returns>
publicstaticobject RunProcWithOutPut(string sProcName, params SqlParameter[] p)
        {
            SqlCommand cmd = CreateCommand(sProcName, p);
            try
            {
                cmd.ExecuteNonQuery();
                Close();
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                returnnull;
            }
            finally
            {
                Close();
            }
            return cmd.Parameters["@output"].Value;
        }
        #endregion

        #region 执行无参存储过程，返回DataTable
        ///<summary>
        /// 执行无参存储过程，返回DataTable
        ///</summary>
        ///<param name="sProcName">存储过程名</param>
        ///<param name="table">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, out DataTable table)
        {
            DataSet ds =new DataSet();
            SqlCommand cmd = CreateCommand(sProcName, null);
            try
            {
                SqlDataAdapter da =new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "T");
                table = ds.Tables[0];
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                table =null;
            }
            finally
            {
                Close();
            }
        }
        #endregion

        #region 执行带参存储过程，返回DataTable
        ///<summary>
        /// 执行带参存储过程，返回DataTable
        ///</summary>
        ///<param name="sProcName">存储过程名</param>
        ///<param name="p">参数列表</param>
        ///<param name="dr">返回DataTable</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out DataTable table)
        {
            DataSet ds =new DataSet();
            SqlCommand cmd = CreateCommand(sProcName, p);
            try
            {
                SqlDataAdapter da =new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "T");
                table = ds.Tables[0];
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                table =null;
            }
            finally
            {
                Close();
            }
        }
        #endregion

        #region 执行无参存储过程，返回DataReader
        ///<summary>
        /// 执行无参存储过程，返回DataReader
        ///</summary>
        ///<param name="sProcName">存储过程名</param>
        ///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, out SqlDataReader dr)
        {
            SqlCommand cmd = CreateCommand(sProcName, null);
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                dr =null;
            }
        }
        #endregion

        #region 执行带参存储过程，返回DataReader
        ///<summary>
        /// 执行带参存储过程，返回DataReader
        ///</summary>
        ///<param name="sProcName">存储过程名</param>
        ///<param name="p">参数列表</param>
        ///<param name="dr">返回DataReader</param>
publicstaticvoid RunProc(string sProcName, SqlParameter[] p, out SqlDataReader dr)
        {
            SqlCommand cmd = CreateCommand(sProcName, p);
            try
            {
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
                dr =null;
            }
        }
        #endregion

        #region 创建SqlParameter
        ///<summary>
        /// 创建SqlParameter
        ///</summary>
        ///<param name="sParamName">参数名称</param>
        ///<param name="DbType">参数类型，SqlDbType枚举</param>
        ///<param name="size">参数大小</param>
        ///<param name="d">参数类型</param>
        ///<param name="v">参数值</param>
        ///<returns>返回创建好的SqlParameter</returns>
publicstatic SqlParameter MakeParam(string sParamName, SqlDbType DbType,
            Int32 size, ParameterDirection d, object v)
        {
            SqlParameter pa;
            if (size >0)
                pa =new SqlParameter(sParamName, DbType, size);
            else
                pa =new SqlParameter(sParamName, DbType);

            pa.Direction = d;

            if (!(d == ParameterDirection.Output && v ==null))
                pa.Value = v;

            return pa;
        }
        #endregion

        #region 创建输入参数
        ///<summary>
        /// 创建传入参数
        ///</summary>
        ///<param name="sParamName">参数名</param>
        ///<param name="d">参数类型，为SqlDbType枚举</param>
        ///<param name="size">大小</param>
        ///<param name="v">参数值</param>
        ///<returns>返回创建好的输入参数</returns>
publicstatic SqlParameter MakeInParam(string sParamName, SqlDbType d, int size, object v)
        {
            return MakeParam(sParamName, d, size, ParameterDirection.Input, v);
        }
        #endregion

        #region 创建输出参数
        ///<summary>
        /// 创建输出参数
        ///</summary>
        ///<param name="sParamName">参数名</param>
        ///<param name="d">参数类型，为SqlDbType枚举成员</param>
        ///<param name="size">参数大小</param>
        ///<returns>返回创建好的输出参数</returns>
publicstatic SqlParameter MakeOutParam(string sParamName, SqlDbType d, int size)
        {
            return MakeParam(sParamName, d, size, ParameterDirection.Output, null);
        }
        #endregion

    }

}