C# 数据库访问类(MS SQL Server)

分类:C#     发布:2019-08-09     来源:本站     浏览:827 次
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace RC.Framework
{
    public class SqlHelper
    {
        #region 数据库访问

        /// <summary>
        ///     连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString; }
        }

        /// <summary>
        /// 查询DataTable数据集
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable Query(string conn, string sql, SqlParameter[] parms = null)
        {
            var table = new DataTable();
            using (var con = new SqlConnection(conn))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    using (var dr = cmd.ExecuteReader())
                    {
                        table.Load(dr);
                    }

                    cmd.Parameters.Clear();
                }

                con.Close();
            }

            return table;
        }
        /// <summary>
        /// 查询DataTable数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable Query(string sql, SqlParameter[] parms = null)
        {
            var table = new DataTable();
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    using (var dr = cmd.ExecuteReader())
                    {
                        table.Load(dr);
                    }

                    cmd.Parameters.Clear();
                }

                con.Close();
            }

            return table;
        }
        /// <summary>
        /// 执行命令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] parms = null)
        {
            int result;
            try
            {
                using (var con = new SqlConnection(ConnectionString))
                {
                    con.Open();
                    using (var cmd = new SqlCommand(sql, con))
                    {
                        if (parms != null)
                            foreach (var sqlParameter in parms)
                                cmd.Parameters.Add(sqlParameter);
                        result = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }

                    con.Close();
                }
            }
            catch (Exception ex)
            {
                LogManager.LogException(ex);
                return -1;
            }

            return result;
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, SqlParameter[] parms = null)
        {
            object result;
            using (var con = new SqlConnection(ConnectionString))
            {
                con.Open();
                using (var cmd = new SqlCommand(sql, con))
                {
                    if (parms != null)
                        foreach (var sqlParameter in parms)
                            cmd.Parameters.Add(sqlParameter);
                    result = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                con.Close();
            }
            return result;
        }

        #endregion
    }
}
        

如果觉得文章对您有帮助,您可以对我进行打赏 ¥1.4 元(金额随机^_^,每次刷新金额不同)。

请使用支付宝扫码支付

留言评论

*称  呼:
*联系方式: 方便与您取得联系,推荐使用邮箱。
*内  容:

已有评论

暂无数据

上一篇:C# 取得网站根目录(Web使用)

下一篇:Java 数据库访问类(Oracle数据库)