刚学习C#时自己写的数据库连接类

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

namespace DBUtility
{
    /// <summary>
    /// SQLServer的数据库连接类
    /// </summary>
    public abstract class SQLDBHelper
    {
        private static SqlConnection conn = new SqlConnection(
            ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        private SQLDBHelper() { }

        ~SQLDBHelper()
        {
            Dispose();
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public static void Dispose()
        {
            // 确认连接是否已经关闭
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        private static void Open()
        {
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        private static void Close()
        {
            while (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行查询语句或存储过程并返回受影响的行数
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行查询语句或存储过程并返回受影响的行数
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            int returnValue;
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.ToLower().StartsWith("insert ") || sql.ToLower().StartsWith("update ")
                || sql.ToLower().StartsWith("delete "))
            {
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
            }
            try
            {
                Open();
                returnValue = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
            }
            return returnValue;
        }

        /// <summary>
        /// 执行查询或存储过程并返回是否有查询结果
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <returns>是否有查询结果</returns>
        public static bool ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }

        /// <summary>
        /// 执行查询或存储过程并返回是否有查询结果
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>是否有查询结果</returns>
        public static bool ExecuteReader(string sql, params SqlParameter[] parameters)
        {
            bool flag = false;
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.ToLower().StartsWith("select "))
            {
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
            }
            try
            {
                Open();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
            }
            return flag;
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <returns>结果集中第一行的第一列</returns>
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>结果集中第一行的第一列</returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            object returnValue;
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.ToLower().StartsWith("select "))
            {
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
            }
            try
            {
                Open();
                returnValue = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                Close();
            }
            return returnValue;
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <param name="tableName">要查询的表名</param>
        /// <returns>数据集</returns>
        public static DataSet GetDataSet(string sql, string tableName)
        {
            return GetDataSet(sql, tableName, null);
        }

        /// <summary>
        /// 获得数据集
        /// </summary>
        /// <param name="sql">查询语句或存储过程</param>
        /// <param name="tableName">要查询的表名</param>
        /// <param name="parameters">参数</param>
        /// <returns>数据集</returns>
        public static DataSet GetDataSet(string sql, string tableName, params SqlParameter[] parameters)
        {

            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.ToLower().StartsWith("select "))
            {
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, tableName);
            return ds;
        }

        /// <summary>
        /// 根据数据集修改数据库
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="sql">获得次数据集的查询语句或存储过程</param>
        /// <returns>受影响的行数</returns>
        public static int AcceptChanges(DataSet ds, string sql)
        {
            return AcceptChanges(ds, sql);
        }

        /// <summary>
        /// 根据数据集修改数据库
        /// </summary>
        /// <param name="ds">数据集</param>
        /// <param name="sql">获得次数据集的查询语句或存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int AcceptChanges(DataSet ds, string sql, params SqlParameter[] parameters)
        {
            int rows = 0;
            //取出表结构
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (sql.ToLower().StartsWith("select "))
            {
                cmd.CommandType = CommandType.Text;
            }
            else
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters[i]);
                }
            }
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            //生成实现 DataSet 的更改与关联的 SQL Server 实例之间的协调所需的 Transact-SQL 语句。
            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            try
            {
                rows = da.Update(ds, sql);
                ds.AcceptChanges();
            }
            catch
            {
            }
            return rows;
        }
    }
}

转载于:https://www.cnblogs.com/flying19880517/archive/2009/05/29/My-SQLDBHelper.html