大岛车间综合看板接口调整、车间公告开发、节拍工价导入接口开发

yl

2022-09-06 c48eb7425ab857ac6e1fee2f548971e87969fa84

 VueWebApi/Tools/DapperHelper.cs

@@ -29,8 +29,9 @@
            return connection;
        }


        /// <summary>
        /// Dapper查询返回List<T>
        /// Dapper查询返回List
        /// </summary>
        /// <typeparam name="T">需要返回的对象类型</typeparam>
        /// <param name="sql">Sql语句</param>
@@ -52,7 +53,12 @@
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }

            }
            return list;
        }
@@ -81,6 +87,11 @@
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return result;
        }
@@ -108,6 +119,11 @@
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            return t;
        }
@@ -122,25 +138,24 @@
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList<T>(string sql,Object parm, string orderBy,string sort, int pageIndex, int pageSize, out int total)
        public static IEnumerable<T> GetPageList<T>(string sql, Object parm, string orderBy, string sort, int pageIndex, int pageSize, out int total)
        {
            try
            {
                //int skip = 1;
                //if (pageIndex > 0)
                //{
                //    skip = (pageIndex - 1) * pageSize + 1;
                //}

                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
                sb.AppendFormat(@"SELECT *
            //int skip = 1;
            //if (pageIndex > 0)
            //{
            //    skip = (pageIndex - 1) * pageSize + 1;
            //}
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql);
            sb.AppendFormat(@"SELECT *
                        FROM(SELECT *,ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNum
                             FROM  ({0}) AS Temp) AS Result
                        WHERE  RowNum >= {3} AND RowNum <= {4}
                        ORDER BY {1} {2}", sql, orderBy, sort, pageIndex, pageSize);  //skip, pageIndex * pageSize

                using (IDbConnection conn = sqlConnection())
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    using (var reader = conn.QueryMultiple(sb.ToString(), parm))
                    {
@@ -148,14 +163,19 @@
                        return reader.Read<T>();
                    }
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }

            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
            }
           
        }

        /// <summary>
@@ -167,23 +187,59 @@
        public static DataTable selectdata(string sql, Object parm)
        {
            //sql语句
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql, parm);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            catch (Exception ex)
        }

        /// <summary>
        /// Dapper查询返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parm"></param>
        /// <returns></returns>
        public static DataTable selectlist(string sql, Object parm)
        {
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql, parm);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
           
        }

        /// <summary>
@@ -194,23 +250,27 @@
        public static DataTable selecttable(string sql)
        {
            //sql语句
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    DataTable table = new DataTable();
                    var data = conn.ExecuteReader(sql);
                    table.Load(data);
                    return table;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
            }

        }

        /// <summary>
@@ -222,9 +282,9 @@
        public static DataTable selectProcedure(string sql, Object parm)
        {
            //sql语句
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
@@ -232,14 +292,88 @@
                    table.Load(res);
                    return table;
                }
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }

        }

        /// <summary>
        /// Dapper执行存储过程返回datatable数据(带参数)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <param name="parm">参数</param>
        /// <returns></returns>
        public static bool IsProcedure(string sql, Object parm)
        {
            bool result;
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 存储过程 
                    result = true;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    result = false;
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// Dapper执行存储过程返回datatable数据(不带参数)
        /// </summary>
        /// <param name="sql">存储过程名</param>
        /// <returns></returns>
        public static bool IsProcedureNo(string sql)
        {
            bool result;
            //sql语句
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    DataTable table = new DataTable();
                    //var data = conn.ExecuteReader(sql, parm);
                    var res = conn.ExecuteReader(sql);//sql 存储过程 
                    result = true;
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    result = false;
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
@@ -252,19 +386,24 @@
        {
            //sql语句
            int result = 0;
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    result = conn.Query(sql, parm).Count();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
            }
        }

@@ -274,24 +413,29 @@
        /// <param name="sql"></param>
        /// <param name="parametere"></param>
        /// <returns></returns>
        public static int SQL(string sql,Object parametere)
        public static int SQL(string sql, Object parametere)
        {
            //sql语句
            int result = 0;
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    result = conn.Execute(sql, parametere);
                }
                return result;
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
            }   
            return result;
        }

        /// <summary>
@@ -310,7 +454,7 @@
                    tranction = conn.BeginTransaction();
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null),tranction);
                        conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
                    }
                    tranction.Commit();
                    result = true;
@@ -322,6 +466,47 @@
                    result = false;
                    tranction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
        }

        /// <summary>
        /// 增加，删除，修改使用的 事务方法  Sqllist为依次执行
        /// </summary>
        /// <param name="sqlList"></param>
        /// <returns></returns>
        public static int DoTransactionCont(List<object> sqlList)
        {
            int result = 0;
            IDbTransaction tranction = null;
            using (IDbConnection conn = sqlConnection())
            {
                try
                {
                    tranction = conn.BeginTransaction();
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        result += conn.Execute(sqlList[i].GetType().GetProperty("str").GetValue(sqlList[i], null).ToString(), sqlList[i].GetType().GetProperty("parm").GetValue(sqlList[i], null), tranction);
                    }
                    tranction.Commit();
                }
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    tranction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
                return result;
            }
@@ -339,9 +524,9 @@
        {

            int result = 0;
            try
            using (IDbConnection conn = sqlConnection())
            {
                using (IDbConnection conn = sqlConnection())
                try
                {
                    result = conn.Execute(sql, parameter);
                    if (result != 0)
@@ -349,14 +534,19 @@
                        result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
                    }
                }
                return result;
                catch (Exception ex)
                {
                    //创建日志记录组件实例
                    LogHelper.WriteLog(ex);
                    throw ex;
                }
                finally
                {
                    conn.Dispose();
                    conn.Close();
                }
            }
            catch (Exception ex)
            { 
                //创建日志记录组件实例
                LogHelper.WriteLog(ex);
                throw ex;
            }
            return result;
        }
    }
}