.NET 中数据访问用的 DBHelper（Sql Server）类

最新推荐文章于 2026-05-05 09:45:58 发布

原创最新推荐文章于 2026-05-05 09:45:58 发布 · 992 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

C# 专栏收录该内容

29 篇文章

订阅专栏

本文介绍了一个使用C#编写的SQL数据库操作助手类，包括数据库连接、执行SQL语句、存储过程及获取数据集等功能，适用于需要频繁进行数据库操作的开发场景。

    public class DBHelper
    {
        private static string DBConnectString = "Data Source=.;Initial Catalog=Roseonly;Integrated Security=True";
        private static SqlConnection conn;
        public static SqlDataAdapter dap;
        private static SqlCommand cmd;
        private static SqlCommandBuilder builder;
        private static DBHelper dBHelper;
        public static DataSet ds = new DataSet();
        public DBHelper()
        {
            conn = new SqlConnection(DBConnectString);
        }
        /// <summary>
        /// 实例化DBHelper对象
        /// </summary>
        /// <returns></returns>
        public static DBHelper Instance()
        {
            if (dBHelper == null)
            {
                dBHelper = new DBHelper();
            }
            return dBHelper;
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        void DBOpen()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        void DBClose()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行SQL语句获取数据集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>DataTable数据集</returns>
        public DataTable GetDataTableBySql(string sql)
        {
            DBOpen();
            DataTable dt = new DataTable();
            dap = new SqlDataAdapter(sql, conn);
            try
            {
                dap.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                DBClose();
            }
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>是否执行成功</returns>
        public bool ExcuteSql(string sql)
        {
            DBOpen();
            cmd = new SqlCommand(sql, conn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                DBClose();
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="proName">存储过程名称</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>是否执行成功</returns>
        public bool ExcuteProcedure(string proName, SqlParameter[] paras)
        {
            DBOpen();
            cmd = new SqlCommand(proName, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < paras.Length; i++)
            {
                cmd.Parameters.Add(paras[i]);
            }
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                DBClose();
            }

        }
        /// <summary>
        /// 执行存储过程获得数据集
        /// </summary>
        /// <param name="proName">存储过程名</param>
        /// <param name="paras">存储过程参数</param>
        /// <returns>DataTable数据集</returns>
        public DataTable GetDataTableByProcedure(string proName, SqlParameter[] paras)
        {
            DBOpen();
            cmd = new SqlCommand(proName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            dap = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            for (int i = 0; i < paras.Length; i++)
            {
                cmd.Parameters.Add(paras[i]);
            }
            try
            {
                dap.Fill(dt);
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                DBClose();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySql(string sql)
        {
            DBOpen();
            dap = new SqlDataAdapter(sql, conn);
            try
            {
                builder = new SqlCommandBuilder(dap);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                DBClose();
            }
        }
    }