DBHelpers.cs的两个版本

 

DBHelpers.cs1

 

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

namespace DAL
{
    public static class DBHelper
    {

        /// <summary>
        /// 从web.config中获得数据库连接字符串
        /// </summary>
        public static string Connstr
        {
            get { return ConfigurationManager.ConnectionStrings["conn"].ConnectionString; }
        }
        #region sql语句部分
                /// <summary>
        /// 执行sql语句返回DataTable
        /// </summary>
        /// <param name="sql">安全的sql语句</param>
        /// <returns>根据sql语句得到所有记录</returns>
        public static DataTable GetDataTable(string sql)
        {

            using (SqlConnection conn = new SqlConnection(Connstr))
            {

                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

        }
        /// <summary>
        /// 执行带参数的sql语句返回DataTable
        /// </summary>
        /// <param name="sql">带参数的sql语句</param>
        /// <param name="values">参数</param>
        /// <returns>根据sql语句得到所有记录</returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                cmd.Parameters.AddRange(values);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }

        }

        /// <summary>
        /// 执行无参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteCommand(string sql)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                int num = cmd.ExecuteNonQuery();
                conn.Close();
                return num;
            }
        }

        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">参数</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(values);
                conn.Open();
                int num = cmd.ExecuteNonQuery();
                conn.Close();
                return num;
            }
        }
        /// <summary>
        /// 执行无参数的sql语句，返回首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回首行首列</returns>
        public static object GetScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">参数</param>
        /// <returns>返回首行首列</returns>
        public static object GetScalar(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(values);
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
        /// <summary>
        /// 执行有参数的sql语句,返回首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">参数,是否返回首行首列</param>
        /// <returns>返回首行首列</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(values);
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
       
        /// <summary>
        /// 带事务的执行多条sql语句
        /// </summary>
        /// <param name="sqls"></param>
   
        /// <returns></returns>
        public static bool ExecuteCommandByTran(params string[] sqls)
        {

            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                conn.Open();
                //为数据库连接打开事务
                SqlTransaction tran = conn.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                //为命令对象指定要应用事务
                cmd.Transaction = tran;
                try
                {

                    for (int i = 0; i < sqls.Length; i++)
                    {
                        cmd.CommandText = sqls[i];
                        cmd.ExecuteNonQuery();
                    }

                    //没有异常的情况下，提交事务
                    tran.Commit();

                    return true;
                }
                catch
                {
                    //事务滚回去
                    tran.Rollback();

                    return false;

                }
                finally
                {

                    conn.Close();
                }

            }
        }

        #endregion

        #region 存储过程部分
        /// <summary>
        /// 执行sql语句返回DataTable
        /// </summary>
        /// <param name="sql">安全的sql语句</param>
        /// <returns>根据sql语句得到所有记录</returns>
        public static DataTable GetDataTableByProcedure(string sql)
        {

            using (SqlConnection conn = new SqlConnection(Connstr))
            {

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;

            }

        }
        /// <summary>
        /// 执行带参数的sql语句返回DataTable
        /// </summary>
        /// <param name="sql">带参数的sql语句</param>
        /// <param name="values">参数</param>
        /// <returns>根据sql语句得到所有记录</returns>
        public static DataTable GetDataTableByProcedure(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                //指定查询的方式使用存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                cmd.Parameters.AddRange(values);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                return dt;
            }

        }

        /// <summary>
        /// 执行无参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteCommandByProcedure(string sql)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                int num = cmd.ExecuteNonQuery();
                conn.Close();
                return num;
            }
        }

        /// <summary>
        /// 执行有参数的sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">参数</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteCommandByProcedure(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(values);
                conn.Open();
                int num = cmd.ExecuteNonQuery();
                conn.Close();
                return num;
            }
        }
        /// <summary>
        /// 执行无参数的sql语句，返回首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>返回首行首列</returns>
        public static object GetScalarByProcedure(string sql)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
        /// <summary>
        /// 执行有参数的sql语句,返回首行首列
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="values">参数,是否返回首行首列</param>
        /// <returns>返回首行首列</returns>
        public static object ExecuteScalarByProcedure(string sql, params SqlParameter[] values)
        {
            using (SqlConnection conn = new SqlConnection(Connstr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(values);
                conn.Open();
                object obj = cmd.ExecuteScalar();
                conn.Close();
                return obj;
            }
        }
        #endregion

    }
}

 

DBHelpers.cs2

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

namespace BIR.DAL
{
    public static class DBHelper
    {
        private static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["Birthday"].ConnectionString;
                connection = new SqlConnection(connectionString);
                if (connection == null)
                {                   
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        public static string ReturnStringScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            try
            {
                string result = cmd.ExecuteScalar().ToString();
                return result;
            }
            catch (Exception e)
            {
                return "0";
            }
            connection.Close();
        }

        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            try
            {
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
            catch (Exception e)
            {
                return 0;
            }
            connection.Close();
        }

  
        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
            connection.Close();
        }

        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
            reader.Close();
            reader.Dispose();
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
            reader.Close();
            reader.Dispose();
          
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            connection.Close();
            connection.Dispose();
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            connection.Close();
            connection.Dispose();
            return ds.Tables[0];   
        }

    }
}