异常单回滚

啊鑫

4 天以前 5707781039b9d902ce1d2d6727fba65bbab14574

 WebApi/Gs.Report/XlsInOutController.cs

@@ -2,7 +2,6 @@
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Text;
using Gs.Toolbox;
using Gs.Toolbox.ApiCore.Abstract.Mvc;
using Gs.Toolbox.ApiCore.Common.Mvc;
@@ -28,10 +27,9 @@
    }

    /// <summary>
    ///     通用存储过程执行方法
    /// 通用存储过程执行方法
    /// </summary>
    private DataSet RunProcedure(string procName,
        IDictionary<string, object> paramDict)
    private DataSet RunProcedure(string procName, IDictionary<string, object> paramDict)
    {
        var dset = new DataSet();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
@@ -41,17 +39,15 @@
                comm.CommandType = CommandType.StoredProcedure;
                // 动态添加参数
                foreach (var kvp in paramDict)
                    comm.Parameters.AddWithValue("@" + kvp.Key,
                        kvp.Value == null
                            ? DBNull.Value
                            : kvp.Value.ToString());
                {
                    comm.Parameters.AddWithValue("@" + kvp.Key, kvp.Value == null? DBNull.Value : kvp.Value.ToString());
                }
                using (var adapter = new SqlDataAdapter(comm))
                {
                    adapter.Fill(dset, "0");
                }
            }
        }

        return dset;
    }

@@ -62,13 +58,14 @@
        var paramDict = new Dictionary<string, object>();
        // 动态解析参数
        if (mode.outParams != null)
        {
            foreach (var prop in mode.outParams)
            {
                string name = prop.Name;
                object value = prop.Value;
                paramDict.Add(name, value);
            }

        }
        var _pdfFloder = AppSettingsHelper.getValueByKey("DownPath");
        var _pdfName = Guid.NewGuid() + ".xls";
        var _pdfSaveFolder = Path.Combine(AppContext.BaseDirectory, _pdfFloder);
@@ -80,24 +77,21 @@
        {
            var dset = RunProcedure(rptParameter, paramDict);
            if (dset == null || dset.Tables.Count <= 0)
                return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
                    "没有查询到任何数据");
                return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception, "没有查询到任何数据");
            var ary = new ArrayList();
            ExcelHelper.ExportAryHead(dset.Tables[0], ary, pdfSavePath);
            m.fileUrl = "down/" + _pdfName;
            return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
                "读取成功！");
            return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success, "读取成功！");
        }
        catch (Exception ex)
        {
            LogHelper.Debug(ToString(), ex.Message);
            return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
                "读取失败," + ex.Message);
            return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception, "读取失败," + ex.Message);
        }
    }

    /// <summary>
    ///     数据导出，需要存储过程支撑，目前没用到
    /// 数据导出，需要存储过程支撑，目前没用到
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
@@ -138,7 +132,6 @@
                        dt.Fill(dset, "0");
                    }
                }

                conn.Close();
            }

@@ -179,7 +172,7 @@
    #region 各种导出

    /// <summary>
    ///     导出首检
    /// 导出首检
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
@@ -196,18 +189,15 @@
        dynamic m = new ExpandoObject();
        //读数据
        var dset = new DataSet();
        SqlParameter[] parameters =
        {
        SqlParameter[] parameters ={
            new("@inMainGuid", guid),
            new("@inP1", ""),
            new("@inP2", ""),
            new("@inP3", ""),
            new("@inP4", "")
        };
            new("@inP4", "")};
        try
        {
            dset = DbHelperSQL.RunProcedure("[xlsOutShouJian]", parameters,
                "0");
            dset = DbHelperSQL.RunProcedure("[xlsOutShouJian]", parameters, "0");
            if (dset == null || dset.Tables.Count <= 0)
                return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
                    "没有查询到任何数据");
@@ -226,7 +216,7 @@
    }

    /// <summary>
    ///     导出iqc检
    /// 导出iqc检
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
@@ -243,14 +233,12 @@
        dynamic m = new ExpandoObject();
        //读数据
        var dset = new DataSet();
        SqlParameter[] parameters =
        {
        SqlParameter[] parameters ={
            new("@inMainGuid", guid),
            new("@inP1", ""),
            new("@inP2", ""),
            new("@inP3", ""),
            new("@inP4", "")
        };
            new("@inP4", "")};
        try
        {
            dset = DbHelperSQL.RunProcedure("[xlsOutIqc]", parameters, "0");
@@ -273,7 +261,7 @@


    /// <summary>
    ///     导出巡检
    /// 导出巡检
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
@@ -290,14 +278,12 @@
        dynamic m = new ExpandoObject();
        //读数据
        var dset = new DataSet();
        SqlParameter[] parameters =
        {
        SqlParameter[] parameters ={
            new("@inMainGuid", guid),
            new("@inP1", ""),
            new("@inP2", ""),
            new("@inP3", ""),
            new("@inP4", "")
        };
            new("@inP4", "")};
        try
        {
            dset = DbHelperSQL.RunProcedure("[xlsOutXunJian]", parameters, "0");
@@ -319,7 +305,7 @@
    }

    /// <summary>
    ///     导出成品检验报告
    /// 导出成品检验报告
    /// </summary>
    /// <param name="mode"></param>
    /// <returns></returns>
@@ -336,18 +322,15 @@
        dynamic m = new ExpandoObject();
        //读数据
        var dset = new DataSet();
        SqlParameter[] parameters =
        {
        SqlParameter[] parameters ={
            new("@inMainGuid", guid),
            new("@inP1", ""),
            new("@inP2", ""),
            new("@inP3", ""),
            new("@inP4", "")
        };
            new("@inP4", "")};
        try
        {
            dset = DbHelperSQL.RunProcedure("[xlsOutChengPin]", parameters,
                "0");
            dset = DbHelperSQL.RunProcedure("[xlsOutChengPin]", parameters, "0");
            if (dset == null || dset.Tables.Count <= 0)
                return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Exception,
                    "没有查询到任何数据");
@@ -364,54 +347,52 @@
                "读取失败," + ex.Message);
        }
    }

    #endregion


    #region 各种导入

    #region  各种导入
    /// <summary>
    ///     检验工具导入
    /// 检验工具导入
    /// </summary>
    /// <param name="tmpGuid"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<int?> XlsInJygj([FromBody] dynamic mode)
    {
        var dic = new Dictionary<string, string>
        Dictionary<string, string> dic = new Dictionary<string, string>
        {
            { "名称", "t1" }
            { "名称", "t1" },
        };
        string tmpGuid = mode.tmpGuid;
        int? rtnInt = (int)ReturnCode.Default;
        int? _it = 0; //返回的行数
        var _msg = ""; //返回的信息
        int? _it = 0;//返回的行数
        string _msg = "";//返回的信息
        (_it, _msg) = getTable(tmpGuid, dic);
        if (_it <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                _msg);
        var sbMsg = new StringBuilder();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
        System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
        using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[xlsInJygj]", conn))
            using (SqlCommand cmd = new SqlCommand("[xlsInJygj]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    var parameters = new[]
                    {
                        new SqlParameter("@outGuid", SqlDbType.NVarChar, 100),
                        new SqlParameter("@outMsg", SqlDbType.NVarChar, 300),
                        new SqlParameter("@inEdtUserGuid", _userGuid),
                        new SqlParameter("@tmpGuid", tmpGuid)
                    };
                    SqlParameter[] parameters = new SqlParameter[] {
                                new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
                                new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
                                new SqlParameter("@inEdtUserGuid",_userGuid),
                                new SqlParameter("@tmpGuid",tmpGuid),
                            };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    rtnInt = cmd.ExecuteNonQuery();
                    sbMsg.Append(parameters[1].Value);
                    sbMsg.Append(parameters[1].Value.ToString());
                }
                catch (Exception ex)
                {
@@ -424,57 +405,54 @@
                }
            }
        }

        if (rtnInt <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success,
            sbMsg.ToString());
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
    }


    /// <summary>
    ///     检验项目导入
    /// 检验项目导入
    /// </summary>
    /// <param name="tmpGuid"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<int?> XlsInJyxm([FromBody] dynamic mode)
    {
        var dic = new Dictionary<string, string>
        Dictionary<string, string> dic = new Dictionary<string, string>
        {
            { "名称", "t1" }
            { "名称", "t1" },
        };
        string tmpGuid = mode.tmpGuid;
        int? rtnInt = (int)ReturnCode.Default;
        int? _it = 0; //返回的行数
        var _msg = ""; //返回的信息
        int? _it = 0;//返回的行数
        string _msg = "";//返回的信息
        (_it, _msg) = getTable(tmpGuid, dic);
        if (_it <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                _msg);
        var sbMsg = new StringBuilder();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
        System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
        using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[xlsInJyxm]", conn))
            using (SqlCommand cmd = new SqlCommand("[xlsInJyxm]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    var parameters = new[]
                    {
                        new SqlParameter("@outGuid", SqlDbType.NVarChar, 100),
                        new SqlParameter("@outMsg", SqlDbType.NVarChar, 300),
                        new SqlParameter("@inEdtUserGuid", _userGuid),
                        new SqlParameter("@tmpGuid", tmpGuid)
                    };
                    SqlParameter[] parameters = new SqlParameter[] {
                                new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
                                new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
                                new SqlParameter("@inEdtUserGuid",_userGuid),
                                new SqlParameter("@tmpGuid",tmpGuid),
                            };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    rtnInt = cmd.ExecuteNonQuery();
                    sbMsg.Append(parameters[1].Value);
                    sbMsg.Append(parameters[1].Value.ToString());
                }
                catch (Exception ex)
                {
@@ -487,57 +465,54 @@
                }
            }
        }

        if (rtnInt <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success,
            sbMsg.ToString());
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
    }

    /// <summary>
    ///     工单后盖码导入
    /// 工单后盖码导入
    /// </summary>
    /// <param name="tmpGuid"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<int?> XlsInDaaHgm([FromBody] dynamic mode)
    {
        var dic = new Dictionary<string, string>
        Dictionary<string, string> dic = new Dictionary<string, string>
        {
            { "工单编号", "t1" },
            { "条码", "t2" }
           { "工单编号", "t1" },
            { "条码", "t2" },
        };
        string tmpGuid = mode.tmpGuid;
        int? rtnInt = (int)ReturnCode.Default;
        int? _it = 0; //返回的行数
        var _msg = ""; //返回的信息
        int? _it = 0;//返回的行数
        string _msg = "";//返回的信息
        (_it, _msg) = getTable(tmpGuid, dic);
        if (_it <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                _msg);
        var sbMsg = new StringBuilder();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
        System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
        using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[xlsInDaaHgm]", conn))
            using (SqlCommand cmd = new SqlCommand("[xlsInDaaHgm]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    var parameters = new[]
                    {
                        new SqlParameter("@outGuid", SqlDbType.NVarChar, 100),
                        new SqlParameter("@outMsg", SqlDbType.NVarChar, 300),
                        new SqlParameter("@inEdtUserGuid", _userGuid),
                        new SqlParameter("@tmpGuid", tmpGuid)
                    };
                    SqlParameter[] parameters = new SqlParameter[] {
                                new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
                                new SqlParameter("@outMsg",SqlDbType.NVarChar,300),
                                new SqlParameter("@inEdtUserGuid",_userGuid),
                                new SqlParameter("@tmpGuid",tmpGuid),
                            };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    rtnInt = cmd.ExecuteNonQuery();
                    sbMsg.Append(parameters[1].Value);
                    sbMsg.Append(parameters[1].Value.ToString());
                }
                catch (Exception ex)
                {
@@ -551,24 +526,21 @@
                }
            }
        }

        if (rtnInt <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success,
            sbMsg.ToString());
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
    }


    /// <summary>
    ///     物料检验项目导入
    /// 物料检验项目导入
    /// </summary>
    /// <param name="tmpGuid"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<int?> XlsInItemJyxm([FromBody] dynamic mode)
    {
        var dic = new Dictionary<string, string>
        Dictionary<string, string> dic = new Dictionary<string, string>
        {
            { "使用组织编号", "t1" },
            { "物料编号", "t2" },
@@ -580,40 +552,40 @@
            { "标准编码", "t8" },
            { "检验水平", "t9" },
            { "接受水平", "t10" },
            { "检验要求", "t11" }
            { "检验要求", "t11" },
        };
        string tmpGuid = mode.tmpGuid;
        string strType = mode.strType; //用于判断iqc,ipqc首检，ipqc巡检，fqc
        string strType = mode.strType;//用于判断iqc,ipqc首检，ipqc巡检，fqc
        int? rtnInt = (int)ReturnCode.Default;
        int? _it = 0; //返回的行数
        var _msg = ""; //返回的信息
        int? _it = 0;//返回的行数
        string _msg = "";//返回的信息
        (_it, _msg) = getTable(tmpGuid, dic);
        if (_it <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                _msg);
        var sbMsg = new StringBuilder();
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, _msg);
        System.Text.StringBuilder sbMsg = new System.Text.StringBuilder();
        using (SqlConnection conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[xlsInItemJyxm]", conn))
            using (SqlCommand cmd = new SqlCommand("[xlsInItemJyxm]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    var parameters = new[]
                    {
                        new SqlParameter("@outGuid", SqlDbType.NVarChar, 100),
                        new SqlParameter("@outMsg", SqlDbType.NVarChar, 1200),
                        new SqlParameter("@inEdtUserGuid", _userGuid),
                        new SqlParameter("@tmpGuid", tmpGuid),
                        new SqlParameter("@strType", strType)
                    };
                    SqlParameter[] parameters = new SqlParameter[] {
                                new SqlParameter("@outGuid",SqlDbType.NVarChar,100),
                                new SqlParameter("@outMsg",SqlDbType.NVarChar,1200),
                                new SqlParameter("@inEdtUserGuid",_userGuid),
                                new SqlParameter("@tmpGuid",tmpGuid),
                                new SqlParameter("@strType",strType),
                            };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    rtnInt = cmd.ExecuteNonQuery();
                    sbMsg.Append(parameters[1].Value);
                    sbMsg.Append(parameters[1].Value.ToString());
                }
                catch (Exception ex)
                {
@@ -626,30 +598,24 @@
                }
            }
        }

        if (rtnInt <= 0)
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception,
                sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success,
            sbMsg.ToString());
            return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Exception, sbMsg.ToString());
        return ReturnDto<int>.QuickReturn(rtnInt, ReturnCode.Success, sbMsg.ToString());
    }


    /// <summary>
    ///     返回dset
    /// 返回dset
    /// </summary>
    /// <param name="tmpGuid"></param>
    /// <returns></returns>
    private static (int?, string?) getTable(string tmpGuid,
        Dictionary<string, string> dic)
    private static (int?, string?) getTable(string tmpGuid, Dictionary<string, string> dic)
    {
        var _it = 0;
        var _msg = "";
        var sbSql = new StringBuilder();
        sbSql.Append(
            "select top 1 url_Path from [dbo].[MES_FILE] where parent_Guid='" +
            tmpGuid + "'");
        var files = "";
        System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
        sbSql.Append("select top 1 url_Path from [dbo].[MES_FILE] where parent_Guid='" + tmpGuid + "'");
        string files = "";
        try
        {
            files = DbHelperSQL.GetSingle(sbSql.ToString()).ToString();
@@ -665,11 +631,9 @@
            _it = 0;
            _msg = "操作失败：请先上传文件";
        }

        var savePath = AppContext.BaseDirectory +
                       AppSettingsHelper.getValueByKey("UploadPath") + "/" +
                       files;
        var dt = new DataTable();
                       AppSettingsHelper.getValueByKey("UploadPath") + "/" + files;
        System.Data.DataTable dt = new DataTable();
        try
        {
            dt = ExcelHelper.ExcelToTable(savePath);
@@ -680,32 +644,30 @@
            _msg = "操作失败，ExcelToTable读取上传文件发生错误：" + ex.Message;
            LogHelper.Debug("", "getTable:" + ex.Message);
        }

        dt.Columns.Add("tmpGuid", Type.GetType("System.Guid"));
        dt.Columns.Add("tmpIdx", Type.GetType("System.String"));
        for (var r = 0; r < dt.Rows.Count; r++)
        for (int r = 0; r < dt.Rows.Count; r++)
        {
            dt.Rows[r]["tmpGuid"] = Guid.Parse(tmpGuid);
            dt.Rows[r]["tmpIdx"] = r + 3;
        }

        var flag = false;
        using (var destinationConnection =
               new SqlConnection(DbHelperSQL.strConn))
        bool flag = false;
        using (SqlConnection destinationConnection = new SqlConnection(DbHelperSQL.strConn))
        {
            destinationConnection.Open();
            using (var bulkCopy = new SqlBulkCopy(destinationConnection))
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
            {
                try
                {
                    bulkCopy.DestinationTableName = "TMP_XLS"; //要插入的表的表名
                    bulkCopy.DestinationTableName = "TMP_XLS";//要插入的表的表名
                    bulkCopy.BatchSize = dt.Rows.Count;
                    bulkCopy.ColumnMappings.Add("tmpGuid",
                        "tmpGuid"); //映射字段名 DataTable列名 ,数据库对应的列名
                    bulkCopy.ColumnMappings.Add("tmpGuid", "tmpGuid");//映射字段名 DataTable列名 ,数据库对应的列名
                    bulkCopy.ColumnMappings.Add("tmpIdx", "tmpIdx");
                    foreach (var kvp in dic)
                    foreach (KeyValuePair<string, string> kvp in dic)
                    {
                        // bulkCopy.ColumnMappings.Add("名称", "t1");
                        bulkCopy.ColumnMappings.Add(kvp.Key, kvp.Value);
                    }
                    bulkCopy.WriteToServer(dt);
                    flag = true;
                }
@@ -717,13 +679,11 @@
                }
            }
        }

        if (!flag)
        if (flag == false)
        {
            _it = 0;
            _msg = "操作失败，bulkCopy读取上传文件发生错误：";
        }

        _it = 1;
        return (_it, _msg);
    }