WcsFramework/ServiceCenter/SqlSugars/SqlSugarHelper.cs

using Microsoft.Data.SqlClient;
using Newtonsoft.Json.Linq;
using SqlSugar;
using System.Data;

namespace ServiceCenter.SqlSugars
{
    /// <summary>
    ///  ORM
    /// </summary>
    public class SqlSugarHelper
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private static SqlSugarScope? _Db { get; set; } = null;

        /// <summary>
        /// 默认数据库连接Key
        /// </summary>
        private static string _Default { get; set; } = "";

        /// <summary>
        /// PLC据库连接Key
        /// </summary>
        private static string _PLCEX { get; set; } = "";

        /// <summary>
        /// PLC据库连接Key
        /// </summary>
        public static string _PLC { get; set; } = "";

        /// <summary>
        /// Dlog数据库连接Key
        /// </summary>
        private static string _Dlog { get; set; } = "";

        /// <summary>
        ///  设置数据库连接Key
        /// </summary>
        /// <param name="configId">默认多租户ID</param>
        public static void SetDefault(string configId)
        {
            _Default = configId;
        }

        /// <summary>
        ///  设置 Dlog数据库连接Key
        /// </summary>
        /// <param name="configId">多租户Dlog ID</param>
        public static void SetDlog(string configId)
        {
            _Dlog = configId;
        }

        /// <summary>
        ///  设置 plc数据库连接Key
        /// </summary>
        /// <param name="configId">多租户Dlog ID</param>
        public static void SetPLC(string configId)
        {
            _PLC = configId;
        }

        /// <summary>
        ///  设置 plc数据库连接Key
        /// </summary>
        /// <param name="configId">多租户Dlog ID</param>
        public static void SetPLCEX(string configId)
        {
            _PLCEX = configId;
        }

        /// <summary>
        /// 默认数据库连接Key
        /// </summary>
        public SqlSugarScopeProvider Default
        {
            get
            {
                if (_Default == "") throw new Exception("请调用[SqlSugarHelper.SetDefault]方法设置默认数据库连接");
                if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");
                return _Db.GetConnectionScope(_Default);
            }
        }

        /// <summary>
        /// Dlog数据库连接Key
        /// </summary>
        public SqlSugarScopeProvider Dlog
        {
            get
            {
                if (_Dlog == "") throw new Exception("请调用[SqlSugarHelper.SetDlog]方法设置默认数据库连接");
                if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");
                return _Db.GetConnectionScope(_Dlog);
            }
        }

        /// <summary>
        /// plc数据库连接Key
        /// </summary>
        public SqlSugarScopeProvider PLC
        {
            get
            {
                if (_PLC == "") throw new Exception("请调用[SqlSugarHelper.SetPLC]方法设置默认数据库连接");
                if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");
                return _Db.GetConnectionScope(_PLC);
            }
        }

        /// <summary>
        /// plc数据库连接Key
        /// </summary>
        public SqlSugarScopeProvider PLCEX
        {
            get
            {
                if (_PLCEX == "") throw new Exception("请调用[SqlSugarHelper.SetPLC]方法设置默认数据库连接");
                if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");
                return _Db.GetConnectionScope(_PLCEX);
            }
        }

        /// <summary>
        ///  设置数据库连接
        /// </summary>
        /// <param name="sqlSugarScope"></param>
        public static void SetDb(SqlSugarScope sqlSugarScope)
        {
            _Db = sqlSugarScope;
        }

        /// <summary>
        /// 数据库连接
        /// 注意需要
        /// </summary>
        public SqlSugarScope Connect
        {
            get
            {
                return _Db ?? throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");
            }
        }

        /// <summary>
        ///  执行事务
        /// </summary>
        /// <param name="act"></param>
        /// <exception cref="Exception"></exception>
        public static void Do(Action<SqlSugarHelper> act)
        {
            if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");

            // 创建独立的SqlSugarHelper实例，避免共享事务状态
            var db = new SqlSugarHelper();

            try
            {
                // 使用 SqlSugar 的 Ado.UseTran 方法进行事务管理（推荐方式）
                var result = db.Connect.Ado.UseTran(() =>
                {
                    act(db);
                });

                if (!result.IsSuccess)
                {
                    if (result.ErrorMessage.Contains("SqlTransaction"))
                    {
                        throw new Exception($"事务处理失败 - {result.ErrorMessage}。这通常是由于事务状态冲突导致的，请检查并发访问情况。");
                    }
                    throw new Exception($"事务执行失败: {result.ErrorMessage}");
                }
            }
            catch (Exception ex)
            {
                // 如果 UseTran 方法不可用，回退到手动事务管理
                if (ex.Message.Contains("UseTran") || ex is System.MissingMethodException)
                {
                    DoWithManualTransaction(act, db);
                }
                else
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// 手动事务管理的回退方法
        /// </summary>
        private static void DoWithManualTransaction(Action<SqlSugarHelper> act, SqlSugarHelper db)
        {
            var transactionStarted = false;
            try
            {
                // 检查是否已经在事务中
                if (db.Connect.Ado.Transaction == null)
                {
                    db.Connect.BeginTran();
                    transactionStarted = true;
                }

                act(db);

                if (transactionStarted)
                {
                    db.Connect.CommitTran();
                }
            }
            catch (Exception ex)
            {
                if (transactionStarted)
                {
                    try
                    {
                        db.Connect.RollbackTran();
                    }
                    catch (Exception rollbackEx)
                    {
                        throw new Exception($"原始错误: {ex.Message}; 回滚失败: {rollbackEx.Message}");
                    }
                }

                if (ex.Message.Contains("SqlTransaction"))
                {
                    throw new Exception($"事务处理失败 - {ex.Message}。这通常是由于事务已被提交或回滚后再次使用导致的。建议检查事务的使用方式和并发访问。");
                }
                throw new Exception($"事务执行失败: {ex.Message}");
            }
        }

        /// <summary>
        ///  直接返回查询结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="act"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static T Do<T>(Func<SqlSugarHelper, T> act)
        {
            if (_Db == null) throw new Exception("请调用[SqlSugarHelper.SetDb]方法设置设置数据库连接");

            // 创建独立的SqlSugarHelper实例，避免共享事务状态
            var db = new SqlSugarHelper();

            try
            {
                // 使用 SqlSugar 的 Ado.UseTran 方法进行事务管理（推荐方式）
                var result = db.Connect.Ado.UseTran(() =>
                {
                    db.Connect.Ado.CommandTimeOut = 10;
                    return act(db);
                });

                if (!result.IsSuccess)
                {
                    if (result.ErrorMessage.Contains("SqlTransaction"))
                    {
                        throw new Exception($"事务处理失败 - {result.ErrorMessage}。这通常是由于事务状态冲突导致的，请检查并发访问情况。");
                    }
                    throw new Exception($"事务执行失败: {result.ErrorMessage}");
                }

                return result.Data;
            }
            catch (Exception ex)
            {
                // 如果 UseTran 方法不可用，回退到手动事务管理
                if (ex.Message.Contains("UseTran") || ex is System.MissingMethodException)
                {
                    return DoWithManualTransaction(act, db);
                }
                else
                {
                    throw;
                }
            }
        }

        /// <summary>
        /// 手动事务管理的回退方法（带返回值）
        /// </summary>
        private static T DoWithManualTransaction<T>(Func<SqlSugarHelper, T> act, SqlSugarHelper db)
        {
            var transactionStarted = false;
            try
            {
                // 检查是否已经在事务中
                if (db.Connect.Ado.Transaction == null)
                {
                    db.Connect.BeginTran();
                    transactionStarted = true;
                }

                db.Connect.Ado.CommandTimeOut = 10;
                var result = act(db);

                if (transactionStarted)
                {
                    db.Connect.CommitTran();
                }

                return result;
            }
            catch (Exception ex)
            {
                if (transactionStarted)
                {
                    try
                    {
                        db.Connect.RollbackTran();
                    }
                    catch (Exception rollbackEx)
                    {
                        throw new Exception($"原始错误: {ex.Message}; 回滚失败: {rollbackEx.Message}");
                    }
                }

                if (ex.Message.Contains("SqlTransaction"))
                {
                    throw new Exception($"事务处理失败 - {ex.Message}。这通常是由于事务已被提交或回滚后再次使用导致的。建议检查事务的使用方式和并发访问。");
                }
                throw new Exception($"事务执行失败: {ex.Message}");
            }
        }
    }
}