连接数据库类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace WebApplication1
{
    public class SqlOperation
    {
        #region 属性
        /// <summary>
        /// 保存在Web.config中的连接字符串
        /// </summary>
        private static readonly string connectionstring = "Data Source=计算机名;Initial Catalog=数据库名;Persist Security Info=是否安全（true|falsw）;User ID=用户名;Password=密码;Connect Timeout=连接超时时间";
        /// <summary>
        /// SqlConnection对象
        /// </summary>
        protected static SqlConnection conn = new SqlConnection();
        /// <summary>
        /// SqlCommand对象
        /// </summary>
        protected static SqlCommand comm = new SqlCommand();
        #endregion

        #region 内部函数
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private static void ConnectionOpen()
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Close();
                conn.ConnectionString = connectionstring;
                comm.Connection = conn;
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private static void ConnectionClose()
        {
            conn.Close();
            conn.Dispose();
            comm.Dispose();
        }

        #endregion

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SqlString">要执行的SQL语句</param>
        public static void ExecuteSQL(string SqlString)
        {
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <param name="coll">存储过程需要的参数集合</param>
        public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                comm.Parameters.Clear();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        /// <summary>
        /// 执行Sql查询并返回第一行的第一条记录，返回object，使用时需要拆箱 -> unbox
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>返回object类型的第一行第一条记录</returns>
        public static object ExecuteScalar(string SqlString)
        {
            object obj = new object();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                obj = comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return obj;
        }

        /// <summary>
        /// 执行SQL语句,同时进行事务处理
        /// </summary>
        /// <param name="sqlstr">要执行的SQL语句</param>
        public static void ExecuteTransactionSQL(string SqlString)
        {
            SqlTransaction trans;
            trans = conn.BeginTransaction();
            comm.Transaction = trans;
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                comm.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
        }

        /// <summary>
        /// 执行指定SQL查询，返回DataSet
        /// </summary>
        /// <param name="sqlstr">要执行的SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetBySQL(string SqlString)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }

        /// <summary>
        /// 通过存储过程返回DataSet
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <param name="coll">SqlParameter集合</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.Clear();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandText = ProcedureName;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }

        /// <summary>
        /// 通过存储过程返回DataSet
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSetByProcedure(string ProcedureName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                comm.Parameters.Clear();
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return ds;
        }

        /// <summary>
        /// 返回指定sql语句的DataTable
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableBySQL(string SqlString)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.CommandType = CommandType.Text;
                comm.CommandText = SqlString;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }

        /// <summary>
        /// 根据存储过程返回DataTable
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="coll">SqlParameter集合</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }

        /// <summary>
        /// 根据存储过程返回DataTable
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableByProcedure(string ProcedureName)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                ConnectionOpen();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = ProcedureName;
                da.SelectCommand = comm;
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                ConnectionClose();
            }
            return dt;
        }
    }
}