• UA Library.CommandHandler直连第三方数据库

    UA Library.CommandHandler直连第三方数据库  

    数据库连接配置

    C:\Program Files\Siemens\SimaticIT\Unified\bin\Worker.exe.config 

    DBHelperMySQL.cs

    1. using Dapper;
    2. using Dapper.Contrib;
    3. using Dapper.Contrib.Extensions; 
    4. using Nancal.Model;
    5. using Newtonsoft.Json.Linq;
    6. using System;
    7. using System.Collections.Generic;
    8. using System.Configuration;
    9. using System.Data;
    10. using System.Data.SqlClient;
    11. using System.IO;
    12. using System.Linq;
    13. using System.Text;
    14. using System.Threading;
    15. using System.Threading.Tasks;
    16. using System.Xml.Linq;
    17.  
    18. namespace Nancal.DBUtility
    19. {
    20.  
    21.     public class DBHelperMySQL
    22.     {
    23.         #region Property
    24.  
    25.         //private static readonly string ConnectionString = ConfigurationManager.AppSettings["BaseDb"];
    26.         private static IDbConnection ReadingFunctionGetSQLDBConnection()
    27.         {
    28.             string key = "maindb";
    29.             var xmlStr = File.ReadAllText(@"C:\Program Files\Siemens\SimaticIT\Unified\bin\Worker.exe.config");
    30.             var str = XElement.Parse(xmlStr);
    31.             var connStrings = str.Elements("connectionStrings");
    32.             if (connStrings.Count() == 0)
    33.             {
    34.                 throw new Exception($"未配置数据库链接字符串");
    35.             }
    36.             var db = connStrings.Elements().Where(t => t.Attribute("name").Value == key).FirstOrDefault();
    37.             if (db == null)
    38.             {
    39.                 throw new Exception($"未找到数据库链接字符串");
    40.             }
    41.             var url = db.Attribute("connectionString");
    42.             if (url == null)
    43.             {
    44.                 throw new Exception($"未找到数据库链接字符串");
    45.             }
    46.             var urlStr = url.Value;
    47.             if (string.IsNullOrWhiteSpace(urlStr))
    48.             {
    49.                 throw new Exception($"未找到数据库链接字符串");
    50.             }
    51.             取接口地址
    52.             //var api = platform.Query().FirstOrDefault(t => t.Key == key);
    53.             //if (api == null || string.IsNullOrEmpty(api.ApiUrl))
    54.             //{
    55.             //    throw new Exception($"地址{key}不存在");
    56.             //}
    57.             var connection = new SqlConnection(urlStr);
    58.             if (connection.State == ConnectionState.Closed)
    59.             {
    60.                 connection.Open();
    61.             }
    62.             return connection;
    63.         }
    64.  
    65.         #endregion
    66.  
    67.         #region Method
    68.  
    69.         #region 按数据集方式获得数据
    70.  
    71.         /// 
    72.         /// 获得Table数据
    73.         ///  
    74.         /// 
    75.         /// 
    76.         /// 
    77.         public static DataTable GetData_Table(string sql, out string msg)
    78.         {
    79.             DateTime beforDT = DateTime.Now;
    80.             DataTable dt = new DataTable();
    81.             try
    82.             {
    83.                 #region 1.数据合法性校验 
    84.                 msg = "获取数据成功";
    85.                 if (string.IsNullOrEmpty(sql))
    86.                 {
    87.                     msg = "Sql语句为空！";
    88.                     return dt;
    89.                 } 
    90.                 #endregion
    91.  
    92.                 IDbConnection connection = ReadingFunctionGetSQLDBConnection();
    93.                 var reader = connection.ExecuteReader(sql);
    94.                 dt.Load(reader);
    95.                 //LogProxy.Info("查询开始==========================================================");
    96.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    97.                 //LogProxy.Info("GetData_Table()请求SQL:" + sql);
    98.                 //LogProxy.Info("查询结束==========================================================");
    99.                 return dt;
    100.             }
    101.             catch (Exception ex)
    102.             {
    103.                 msg = ex.Message;
    104.                 //LogProxy.Error("异常信息开始==========================================================");
    105.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    106.                 //LogProxy.Error("GetData_Table()请求SQL:" + sql);
    107.                 //LogProxy.Error("GetData_Table()异常信息:" + ex.Message);
    108.                 //LogProxy.Error("异常信息结束==========================================================");
    109.                 return dt;
    110.             }
    111.         }
    112.  
    113.         /// 
    114.         /// 获得数据集合
    115.         /// 
    116.         /// 
    117.         /// 
    118.         /// 
    119.         public static DataSet GetDataSet(string sql, out string msg)
    120.         {
    121.             DateTime beforDT = DateTime.Now;
    122.             try
    123.             {
    124.                 using (IDbConnection connection = ReadingFunctionGetSQLDBConnection())
    125.                 {
    126.                     msg = "数据获取成功!";
    127.                     DataSet dataSet = new XDataSet();
    128.                     IDataReader reader = connection.ExecuteReader(sql);
    129.                     dataSet.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });
    130.                     //LogProxy.Info("查询开始==========================================================");
    131.                     //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    132.                     //LogProxy.Info("GetDataSet()请求SQL:" + sql);
    133.                     //LogProxy.Info("查询结束==========================================================");
    134.                     return dataSet;
    135.                 }
    136.             }
    137.             catch (Exception ex)
    138.             {
    139.                 msg = ex.Message;
    140.                 //LogProxy.Error("异常信息开始==========================================================");
    141.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    142.                 //LogProxy.Error("GetDataSet()请求SQL:" + sql);
    143.                 //LogProxy.Error("GetDataSet()异常信息:" + ex.Message);
    144.                 //LogProxy.Error("异常信息结束==========================================================");
    145.                 return null;
    146.             }
    147.         }
    148.  
    149.         #endregion
    150.  
    151.         #region 按实体类方式获得数据
    152.  
    153.         /// 
    154.         /// 通过主键获得实体
    155.         /// 
    156.         /// 
    157.         /// 
    158.         /// 
    159.         /// 
    160.         public static T GetDataById<T>(string id, out string msg) where T : class
    161.         {
    162.             DateTime beforDT = DateTime.Now;
    163.             try
    164.             {
    165.                 #region 1.数据合法性校验
    166.  
    167.                 msg = "获取数据成功";
    168.                 if (string.IsNullOrEmpty(id))
    169.                 {
    170.                     msg = "主键Id为空！";
    171.                     return default(T);
    172.                 } 
    173.                 #endregion
    174.  
    175.                 #region 2.获得数据
    176.  
    177.                 IDbConnection connection =  ReadingFunctionGetSQLDBConnection();
    178.                 T model = connection.Get(id);
    179.                 //LogProxy.Info("查询开始==========================================================");
    180.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    181.                 //LogProxy.Info("GetData()请求条件:" + id);
    182.                 //LogProxy.Info("查询结束==========================================================");
    183.                 return model;
    184.  
    185.                 #endregion
    186.             }
    187.             catch (Exception ex)
    188.             {
    189.                 msg = ex.Message;
    190.                 //LogProxy.Error("异常信息开始==========================================================");
    191.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    192.                 //LogProxy.Error("GetData()请求条件:" + id);
    193.                 //LogProxy.Error("GetData()异常信息:" + ex.Message);
    194.                 //LogProxy.Error("异常信息结束==========================================================");
    195.                 return default(T);
    196.             }
    197.         }
    198.  
    199.         /// 
    200.         /// 获得单条数据
    201.         /// 
    202.         /// 
    203.         /// 
    204.         /// 
    205.         /// 
    206.         public static T GetData<T>(string sql, out string msg)
    207.         {
    208.             DateTime beforDT = DateTime.Now;
    209.             try
    210.             {
    211.                 #region 1.数据合法性校验
    212.  
    213.                 msg = "获取数据成功";
    214.                 if (string.IsNullOrEmpty(sql))
    215.                 {
    216.                     msg = "Sql语句为空！";
    217.                     return default(T);
    218.                 } 
    219.                 #endregion
    220.  
    221.                 IDbConnection connection =  ReadingFunctionGetSQLDBConnection();
    222.                 List list = connection.Query(sql).AsList();
    223.                 //LogProxy.Info("查询开始==========================================================");
    224.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    225.                 //LogProxy.Info("GetData()请求SQL:" + sql);
    226.                 //LogProxy.Info("查询结束==========================================================");
    227.                 if (list.Count > 0)
    228.                 {
    229.                     return list[0];
    230.                 }
    231.                 else
    232.                 {
    233.                     return default(T);
    234.                 }
    235.             }
    236.             catch (Exception ex)
    237.             {
    238.                 msg = ex.Message;
    239.                 //LogProxy.Error("异常信息开始==========================================================");
    240.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    241.                 //LogProxy.Error("GetData()请求SQL:" + sql);
    242.                 //LogProxy.Error("GetData()异常信息:" + ex.Message);
    243.                 //LogProxy.Error("异常信息结束==========================================================");
    244.                 return default(T);
    245.             }
    246.         }
    247.  
    248.         /// 
    249.         /// 获得多条数据
    250.         /// 
    251.         /// 
    252.         /// 
    253.         /// 
    254.         /// 
    255.         public static List<T> GetDataList<T>(string sql, out string msg)
    256.         {
    257.             DateTime beforDT = DateTime.Now;
    258.             try
    259.             {
    260.                 #region 1.数据合法性校验
    261.  
    262.                 msg = "获取数据成功";
    263.                 if (string.IsNullOrEmpty(sql))
    264.                 {
    265.                     msg = "Sql语句为空！";
    266.                     return null;
    267.                 } 
    268.                 #endregion
    269.  
    270.                 IDbConnection connection =  ReadingFunctionGetSQLDBConnection();
    271.                 //LogProxy.Info("查询开始==========================================================");
    272.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    273.                 //LogProxy.Info("GetDataList()请求SQL:" + sql);
    274.                 //LogProxy.Info("查询结束==========================================================");
    275.                 return connection.Query(sql).AsList();
    276.             }
    277.             catch (Exception ex)
    278.             {
    279.                 msg = ex.Message;
    280.                 //LogProxy.Error("异常信息开始==========================================================");
    281.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    282.                 //LogProxy.Error("GetDataList()请求SQL:" + sql);
    283.                 //LogProxy.Error("GetDataList()异常信息:" + ex.Message);
    284.                 //LogProxy.Error("异常信息结束==========================================================");
    285.                 return null;
    286.             }
    287.         }
    288.  
    289.         /// 
    290.         /// 获得多条数据－带分页
    291.         /// 
    292.         /// 
    293.         /// 
    294.         /// 
    295.         /// 
    296.         public static Tuple<List<T>, int> GetDataList_Page<T>(string sql, out string msg)
    297.         {
    298.             DateTime beforDT = DateTime.Now;
    299.             try
    300.             {
    301.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    302.                 {
    303.                     try
    304.                     {
    305.                         msg = "数据获取成功!";
    306.                         List rows = null;
    307.                         int totalCount = 0;
    308.                         using (var queryMulti = connection.QueryMultiple(sql))
    309.                         {
    310.                             if (!queryMulti.IsConsumed)
    311.                             {
    312.                                 rows = queryMulti.Read() as List;
    313.                                 totalCount = queryMulti.Read<int>().Single();
    314.                             }
    315.                         }
    316.  
    317.                         //LogProxy.Info("查询开始==========================================================");
    318.                         //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    319.                         //LogProxy.Info("GetDataList_Page()请求SQL:" + sql);
    320.                         //LogProxy.Info("查询结束==========================================================");
    321.                         return new Tuple, int>(rows, totalCount);
    322.                     }
    323.                     catch (Exception ex)
    324.                     {
    325.                         msg = ex.Message;
    326.                         //LogProxy.Error(ex.Message);
    327.                         return null;
    328.                     }
    329.                 }
    330.             }
    331.             catch (Exception ex)
    332.             {
    333.                 msg = ex.Message;
    334.                 //LogProxy.Error("异常信息开始==========================================================");
    335.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    336.                 //LogProxy.Error("GetDataList_Page()请求SQL:" + sql);
    337.                 //LogProxy.Error("GetDataList_Page()异常信息:" + ex.Message);
    338.                 //LogProxy.Error("异常信息结束==========================================================");
    339.                 return null;
    340.             }
    341.         }
    342.  
    343.         /// 
    344.         /// 获得数据列表-带分页
    345.         /// 
    346.         /// 
    347.         /// 
    348.         /// 
    349.         /// 
    350.         /// 
    351.         public static Tuple<List<T>, int> GetDataList_Page<T>(string sql, string countSql, out string msg)
    352.         {
    353.             DateTime beforDT = DateTime.Now;
    354.             try
    355.             {
    356.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    357.                 {
    358.                     connection.Open();
    359.                     IDbTransaction transaction = connection.BeginTransaction();
    360.                     try
    361.                     {
    362.                         msg = "数据获取成功!";
    363.                         var rows = connection.Query(sql, transaction) as List;
    364.                         var totalCount = connection.Query<int>(countSql, transaction).Single();
    365.                         transaction.Commit();
    366.                         //LogProxy.Info("查询开始==========================================================");
    367.                         //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    368.                         //LogProxy.Info("GetDataList_Page()请求SQL:" + sql);
    369.                         //LogProxy.Info("查询结束==========================================================");
    370.                         return new Tuple, int>(rows, totalCount);
    371.                     }
    372.                     catch (Exception ex)
    373.                     {
    374.                         msg = ex.Message;
    375.                         //LogProxy.Error(ex.Message);
    376.                         transaction.Rollback();
    377.                         return null;
    378.                     }
    379.                 }
    380.             }
    381.             catch (Exception ex)
    382.             {
    383.                 msg = ex.Message;
    384.                 //LogProxy.Error("异常信息开始==========================================================");
    385.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    386.                 //LogProxy.Error("GetDataList_Page()请求SQL:" + sql);
    387.                 //LogProxy.Error("GetDataList_Page()异常信息:" + ex.Message);
    388.                 //LogProxy.Error("异常信息结束==========================================================");
    389.                 return null;
    390.             }
    391.         }
    392.  
    393.         #endregion
    394.  
    395.         /// 
    396.         /// 采用事务执行SQL语句
    397.         /// 
    398.         /// 
    399.         /// 
    400.         public static bool TransactionExcute(List<string> lsSqls, out string msg)
    401.         {
    402.             bool resultObj = false;
    403.             msg = string.Empty;
    404.  
    405.             try
    406.             {
    407.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    408.                 {
    409.                     connection.Open();
    410.                     IDbTransaction transaction = connection.BeginTransaction();
    411.                     try
    412.                     {
    413.                         foreach (string sql in lsSqls)
    414.                         {
    415.                             connection.Execute(sql, null, transaction);
    416.                         }
    417.                         transaction.Commit();
    418.                         resultObj = true;
    419.                     }
    420.                     catch (Exception ex)
    421.                     {
    422.                         msg = ex.Message;
    423.                         //LogProxy.Error(ex.Message);
    424.                         transaction.Rollback();
    425.                     }
    426.                 }
    427.             }
    428.             catch (Exception ex)
    429.             {
    430.                 msg = ex.Message;
    431.                 //LogProxy.Error(ex.Message);
    432.  
    433.             }
    434.             return resultObj;
    435.         }
    436.         #region 按SQL语句执行数据
    437.  
    438.         /// 
    439.         /// 带SQL语句执行数据（支持Insert、Update、Delete三种数据）
    440.         /// 
    441.         /// 
    442.         /// 
    443.         /// 
    444.         public static int Execute(string sql, out string msg)
    445.         {
    446.             DateTime beforDT = DateTime.Now;
    447.             int result = 0;
    448.             msg = "";
    449.             try
    450.             {
    451.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    452.                 {
    453.                     result = connection.Execute(sql);
    454.                 }
    455.                 //LogProxy.Info("查询开始==========================================================");
    456.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    457.                 //LogProxy.Info("Execute(string sql)请求SQL:" + sql);
    458.  
    459.                 //LogProxy.Info("查询结束==========================================================");
    460.             }
    461.             catch (Exception ex)
    462.             {
    463.                 msg = ex.Message;
    464.                 //LogProxy.Error("异常信息开始==========================================================");
    465.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    466.                 //LogProxy.Error("Execute(string sql)请求SQL:" + sql);
    467.                 //LogProxy.Error("Execute(string sql)异常信息:" + ex.Message);
    468.                 //LogProxy.Error("异常信息结束==========================================================");
    469.             }
    470.  
    471.             return result;
    472.         }
    473.  
    474.         /// 
    475.         /// 带参数SQL语句执行数据（支持insert、Update、Delete三种数据）
    476.         /// 
    477.         /// 
    478.         /// 
    479.         /// 
    480.         /// 
    481.         public static int Execute<T>(string sql, T model, out string msg)
    482.         {
    483.             DateTime beforDT = DateTime.Now;
    484.             int result = 0;
    485.             msg = "";
    486.             try
    487.             {
    488.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    489.                 {
    490.                     result = connection.Execute(sql, model);
    491.                 }
    492.                 //LogProxy.Info("查询开始==========================================================");
    493.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    494.                 //LogProxy.Info("Execute(string sql, T model)请求SQL:" + sql);
    495.                 //LogProxy.Info("Execute(string sql, T model)参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    496.                 //LogProxy.Info("查询结束==========================================================");
    497.             }
    498.             catch (Exception ex)
    499.             {
    500.                 msg = ex.Message;
    501.                 //LogProxy.Error("异常信息开始==========================================================");
    502.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    503.                 //LogProxy.Error("Execute(string sql, T model)请求SQL:" + sql);
    504.                 //LogProxy.Error("Execute(string sql, T model)参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    505.                 //LogProxy.Error("Execute(string sql, T model)异常信息:" + ex.Message);
    506.                 //LogProxy.Error("异常信息结束==========================================================");
    507.             }
    508.  
    509.             return result;
    510.         }
    511.  
    512.         #endregion
    513.  
    514.         #region 按实体类执行数据
    515.  
    516.         /// 
    517.         /// 直接通过实体类Insert
    518.         /// 
    519.         /// 
    520.         /// 
    521.         /// 
    522.         public static long Insert_Model<T>(T model, out string msg) where T : class
    523.         {
    524.  
    525.             msg = string.Empty;
    526.             DateTime beforDT = DateTime.Now;
    527.             long result = 0;
    528.             try
    529.             {
    530.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    531.                 {
    532.                     result = connection.Insert(model);
    533.                 }
    534.                 //LogProxy.Info("查询开始==========================================================");
    535.                 //LogProxy.Info("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    536.                 //LogProxy.Info("Insert_Model()参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    537.                 //LogProxy.Info("查询结束==========================================================");
    538.             }
    539.             catch (Exception ex)
    540.             {
    541.                 msg = ex.Message;
    542.                 //LogProxy.Error("异常信息开始==========================================================");
    543.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    544.                 //LogProxy.Error("Insert_Model()参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    545.                 //LogProxy.Error("Insert_Model()异常信息:" + ex.Message);
    546.                 //LogProxy.Error("异常信息结束==========================================================");
    547.             }
    548.  
    549.             return result;
    550.         }
    551.  
    552.         /// 
    553.         /// 通过实体类更新
    554.         /// 
    555.         /// 
    556.         /// 
    557.         /// 
    558.         /// 
    559.         public static bool Update_Model<T>(T model, out string msg) where T : class
    560.         {
    561.             msg = string.Empty;
    562.             DateTime beforDT = DateTime.Now;
    563.             bool isSuccess = false;
    564.             try
    565.             {
    566.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    567.                 {
    568.                     isSuccess = connection.Update(model);
    569.                 }
    570.                 //LogProxy.Info("更新开始==========================================================");
    571.                 //LogProxy.Info("更新时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    572.                 //LogProxy.Info("Update_Model()参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    573.                 //LogProxy.Info("更新结束==========================================================");
    574.             }
    575.             catch (Exception ex)
    576.             {
    577.                 msg = ex.Message;
    578.                 //LogProxy.Error("异常信息开始==========================================================");
    579.                 //LogProxy.Error("更新时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    580.                 //LogProxy.Error("Update_Model()参数model:" + Common.Helpers.JsonHelper.ObjectToJson(model));
    581.                 //LogProxy.Error("Update_Model()异常信息:" + ex.Message);
    582.                 //LogProxy.Error("异常信息结束==========================================================");
    583.             }
    584.  
    585.             return isSuccess;
    586.         }
    587.  
    588.         #endregion
    589.  
    590.         #region 单列值
    591.  
    592.         /// 
    593.         /// 获得单列值
    594.         /// 
    595.         /// 
    596.         /// 
    597.         public static JObject GetSingleColValue(string sql)
    598.         {
    599.             DateTime beforDT = DateTime.Now;
    600.             JObject resultObj = new JObject();
    601.             resultObj["flag"] = false;//接口是否正确
    602.             resultObj["msg"] = string.Empty;//错误信息
    603.  
    604.             try
    605.             {
    606.                 #region 1.数据合法性校验 
    607.  
    608.                 if (string.IsNullOrEmpty(sql))
    609.                 {
    610.                     resultObj["msg"] = "Sql语句为空！";
    611.                     return resultObj;
    612.                 } 
    613.                 #endregion
    614.  
    615.                 IDbConnection connection =  ReadingFunctionGetSQLDBConnection();
    616.                 var reader = connection.ExecuteScalar(sql);
    617.                 if (reader == null)
    618.                 {
    619.                     resultObj["flag"] = true;
    620.                     resultObj["singlecolvalue"] = string.Empty;
    621.                 }
    622.                 else
    623.                 {
    624.                     string singleColValue = reader.ToString();
    625.                     resultObj["flag"] = true;
    626.                     resultObj["singlecolvalue"] = singleColValue;
    627.                 }
    628.                 return resultObj;
    629.             }
    630.             catch (Exception ex)
    631.             {
    632.                 resultObj["flag"] = false;
    633.                 resultObj["msg"] = ex.Message;
    634.                 //LogProxy.Error("异常信息开始==========================================================");
    635.                 //LogProxy.Error("查询时间：" + DateTime.Now.Subtract(beforDT).TotalMilliseconds.ToString() + " 毫秒");
    636.                 //LogProxy.Error("GetComputeValue()请求SQL:" + sql);
    637.                 //LogProxy.Error("GetComputeValue()异常信息:" + ex.Message);
    638.                 //LogProxy.Error("异常信息结束==========================================================");
    639.                 return resultObj;
    640.             }
    641.         }
    642.  
    643.         #endregion
    644.  
    645.         #region 事务执行
    646.  
    647.         /// 
    648.         /// 采用事务执行SQL语句
    649.         /// 
    650.         /// 
    651.         /// 
    652.         public static JObject TransactionExcute(List<string> lsSqls)
    653.         {
    654.             JObject resultObj = new JObject();
    655.             resultObj["flag"] = false;
    656.             resultObj["msg"] = string.Empty;
    657.  
    658.             try
    659.             {
    660.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    661.                 {
    662.                     connection.Open();
    663.                     IDbTransaction transaction = connection.BeginTransaction();
    664.                     try
    665.                     {
    666.                         foreach (string sql in lsSqls)
    667.                         {
    668.                             connection.Execute(sql, null, transaction);
    669.                         }
    670.                         transaction.Commit();
    671.                         resultObj["flag"] = true;
    672.                     }
    673.                     catch (Exception ex)
    674.                     {
    675.                         resultObj["msg"] = ex.Message;
    676.                         //LogProxy.Error(ex.Message);
    677.                         transaction.Rollback();
    678.                     }
    679.                 }
    680.             }
    681.             catch (Exception ex)
    682.             {
    683.                 resultObj["msg"] = ex.Message;
    684.                 //LogProxy.Error(ex.Message);
    685.  
    686.             }
    687.             return resultObj;
    688.         }
    689.  
    690.         /// 
    691.         /// 带参数SQL语句执行数据（支持insert、Update、Delete三种数据）
    692.         /// 
    693.         /// 
    694.         /// 
    695.         /// 
    696.         public static JObject TransactionExcuteModel(List agencyModels)
    697.         {
    698.             JObject resultObj = new JObject();
    699.             resultObj["flag"] = false;
    700.             resultObj["msg"] = string.Empty;
    701.  
    702.             try
    703.             {
    704.                 using (IDbConnection connection =  ReadingFunctionGetSQLDBConnection())
    705.                 {
    706.                     connection.Open();
    707.                     IDbTransaction transaction = connection.BeginTransaction();
    708.                     try
    709.                     {
    710.                         foreach (AgencyModel agencyModel in agencyModels)
    711.                         {
    712.                             connection.Execute(agencyModel.sql, agencyModel.model, transaction);
    713.                         }
    714.                         transaction.Commit();
    715.                         resultObj["flag"] = true;
    716.                     }
    717.                     catch (Exception ex)
    718.                     {
    719.                         resultObj["msg"] = ex.Message;
    720.                         //LogProxy.Error(ex.Message);
    721.                         transaction.Rollback();
    722.                     }
    723.                 }
    724.             }
    725.             catch (Exception ex)
    726.             {
    727.                 resultObj["msg"] = ex.Message;
    728.                 //LogProxy.Error(ex.Message);
    729.  
    730.             }
    731.             return resultObj;
    732.         }
    733.  
    734.         #endregion
    735.  
    736.         #endregion
    737.     }
    738.  
    739. }

    XDataSet.cs

    1. using System;
    2. using System.Collections.Generic;
    3. using System.Data;
    4. using System.Linq;
    5. using System.Text;
    6. using System.Threading.Tasks;
    7.  
    8. namespace  Nancal.DBUtility
    9. {
    10.     public class XDataSet : DataSet
    11.     {
    12.         public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables)
    13.         {
    14.             XLoadAdapter adapter = new XLoadAdapter
    15.             {
    16.                 FillLoadOption = loadOption,
    17.                 MissingSchemaAction = MissingSchemaAction.AddWithKey
    18.             };
    19.             if (handler != null)
    20.             {
    21.                 adapter.FillError += handler;
    22.             }
    23.             adapter.FillFromReader(this, reader, 0, 0);
    24.             if (!reader.IsClosed && !reader.NextResult())
    25.             {
    26.                 reader.Close();
    27.             }
    28.         }
    29.     }
    30. }

    XLoadAdapter.cs

    1. using System;
    2. using System.Collections.Generic;
    3. using System.Data;
    4. using System.Data.Common;
    5. using System.Linq;
    6. using System.Text;
    7. using System.Threading.Tasks;
    8.  
    9. namespace  Nancal.DBUtility
    10. {
    11.     public class XLoadAdapter: DataAdapter
    12.     {
    13.         public XLoadAdapter()
    14.         {
    15.         }
    16.  
    17.         public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords)
    18.         {
    19.             return this.Fill(ds, "Table", dataReader, startRecord, maxRecords);
    20.         }
    21.     }
    22. }

  • 相关阅读:
    Lua脚本实现setnx命令
    离散数学 --- 二元关系 --- 关系的性质和关系的闭包
    ssh远程连接不了虚拟机ubuntu
    Cilium 系列-2-Cilium 快速安装
    软件设计师_计算机网络——IP地址和子网掩码
    vue修改子组件中的el-input的placeholder字体颜色
    Kafka内容相关
    初创公司即融资上亿，这个“人造超级大脑”赛道为什么不是噱头？
    Waitlist ，验证产品想法是否可行的一个方法
    C++核心编程(三十三)容器(list)
  • 原文地址：https://blog.csdn.net/lybwwp/article/details/134085940