说明:以下是我近两年年来开发中最常用的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": "端口"
}

然后新建一个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
{
///

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

//\[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;  
    }

}

如果是多层架构，则如下：

本章介绍SQL Help类的使用，下一章介绍SQL Help基类的编写

今日分享结束

能帮朋友解决问题的记得给个关注支持一下，以后将多多分享SQL SERVER 相关知识