基于C# 开发的SOL SERVER 操作数据库类（SQLHelp）

说明:以下是我近两年年来开发中最常用的C#操作sql server数据库访问类,对初学者非常有用，容易扩展，支持多库操作,多研究研究,有什么问题欢迎留言

当前环境为 C# .NET CORE 3.0

首先在appsettings.json文件中配置日志保存路径，MSSQL 数据库连接串，可以配置多个，也可以在launchSettings.json文件中配置，编写相应的代码，可采用注入方式调用，也可写作基本类调用，当前演示基本类操作

示例：

代码：

  "AllowedHosts": "*",
  "ConnectionStrings": {
    "SqlHost": "IP",
    "SqlAdminName": "账号",
    "SqlAdminPswd": "密码",
    "SqlCatalogProFix": "数据库"
  },
  "ConnectionStrings2": {
    "SqlHost": "IP",
    "SqlAdminName": "账号",
    "SqlAdminPswd": "密码",
    "SqlCatalogProFix": "数据库"
  },
  "Executionlog": "", //系统执行日志路劲
  "UPFilepath": "", //图片上传路径
  "DWFilepath": {
    "Ptpip": "http://",
    "Ftpport": "端口"
  }

View Code

然后新建一个SQLHelp类，如果是分层架构，则在DLL接口后面添加SQLHelp层，由Service层调用，简单开发流程为：API接口→SQLHelp类，在API程序里面使用 SQLHelp ht = new SQLHelp();语法即可在下面的程序中调用，如果是分层架构：

分层的程序一般为三层或三层以上，如三层架构：UI→API→BLL→DLL→Model，像我自己常用的，则为：UI→API→BLL→IService→Service→DL基类，如SQLSuggger/EF/SQLHelp,这里演示为最简单的：

调用代码：

using CommAPP.HelpDoc;
using EvaluationSystem.Gettoken;
using EvaluationSystem.HelpTool;
using EvaluationSystem.HelpTool.SysHelp;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace EvaluationSystem.Controllers.WMSShipment
{
    /// <summary>
    ///曾经沧海难为水 除却巫山不是云 
    ///取次花丛懒回顾 半缘修道半缘君 
    ///                            --Hilton    基础信息接口
    /// 所有方法必须再次检测用户是否存在，若不存在则跳出action(方法)，不允许执行任何操作，防止网站被攻击，数据泄露或被破坏，在进程序前已由JWT拦截校验，相关技术自己去了解
    /// </summary>

    //[MyNoAuthentication] //不启用验证

    public class ****Controller : Controller
    {
        public IActionResult Index()
        {
            return View();
        }
        SQLHelp ht = new SQLHelp();
        SYSHelpTool st = new SYSHelpTool();

               [HttpPost]
        public dynamic ****data([FromBody] dynamic Rdata)//添加
        {
            var outData = new Dictionary<string, object>();
            try
            {
                string jsondata = Rdata.ToString();
                var dict1 = JsonConvert.DeserializeObject<Dictionary<string, object>>(jsondata);
                string data = dict1.ContainsKey("data") ? dict1["data"].ToString() : "";
                var dict = JsonConvert.DeserializeObject<Dictionary<string, object>>(data);
                string CreateUserID = HttpContext.Request.Headers["*****"].ToString() ?? "";
                CreateUserID = st.CommonAESDecrypt(CreateUserID);  //登录用户


                string PackageNo = dict.ContainsKey("PackageNo") ? dict["PackageNo"].ToString() : "";
                string PackageTypeID = dict.ContainsKey("PackageTypeID") ? dict["PackageTypeID"].ToString() : "";
                string PackageName = dict.ContainsKey("PackageName") ? dict["PackageName"].ToString() : "";
                string PackageSize = dict.ContainsKey("PackageSize") ? dict["PackageSize"].ToString() : "";
                string PackageQty = dict.ContainsKey("PackageQty") ? dict["PackageQty"].ToString() : "0";
                string Standardcost = dict.ContainsKey("Standardcost") ? dict["Standardcost"].ToString() : "0";
                string LotStatus = dict.ContainsKey("LotStatus") ? dict["LotStatus"].ToString() : "1";
                string StockID = dict.ContainsKey("StockID") ? dict["StockID"].ToString() : "";

                if (string.IsNullOrWhiteSpace(CreateUserID))
                {
                    outData = new Dictionary<string, object>
                      {
                        { "return_data","" },
                        { "return_codes", "-1" },
                        { "return_msg", "用户有误！" }
                       };
                    return outData;
                }
                if (string.IsNullOrWhiteSpace(PackageNo))
                {
                    outData = new Dictionary<string, object>
                      {
                        { "return_data","" },
                        { "return_codes", "-1" },
                        { "return_msg", "耗材编码为空！" }
                       };
                    return outData;
                }


                string upsql = "SQL语句*****WHERE PackageNo=@PackageNo";
                Dictionary<string, object> dic2 = new Dictionary<string, object>();

                dic2.Add("PackageNo", 表名);
                DataTable ds = ht.SelectT(dic2, upsql);
                var Ispectdatas1 = ht.QueryData(ds);

                if (Ispectdatas1.Count > 0)
                {
                    outData = new Dictionary<string, object>
                      {
                        { "return_data","" },
                        { "return_codes", "-1" },
                        { "return_msg", "已存在*****，请勿重复添加！" }
                       };
                    return outData;
                }

                Dictionary<string, object> dic = new Dictionary<string, object>();

                dic.Add("PackageNo", PackageNo);
                dic.Add("PackageTypeID", PackageTypeID);
                dic.Add("PackageName", PackageName);
                dic.Add("PackageSize", PackageSize);
                dic.Add("Standardcost", Standardcost);
                dic.Add("PackageQty", PackageQty);
                dic.Add("LotStatus", LotStatus);
                dic.Add("StockID", StockID);
                dic.Add("ModifyUserID", CreateUserID);
                dic.Add("ModifyDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                dic.Add("CreateUserID", CreateUserID);

                DataTable dt = new DataTable();
                dt = ht.AddDataTable(dic, "PackageInfo");
                var Ispectdatas2 = ht.QueryData(dt);
                if (Ispectdatas2.Count > 0)
                {
                    outData = new Dictionary<string, object>
                      {
                        { "return_data",""},
                        { "return_codes", "0" },
                        { "return_msg", "添加成功！" }
                      };
                }
                else
                {
                    outData = new Dictionary<string, object>
                      {
                        { "return_data",""},
                        { "return_codes", "0" },
                        { "return_msg", "添加失败！" }
                      };
                }

            }
            catch (Exception ex)
            {
                outData = new Dictionary<string, object>
                      {
                        { "return_data","" },
                        { "return_codes", "-2" },
                        { "return_msg", "添加失败，系统错误！"}
                      };
            }
            return outData;
        }

}