From 770c681572301c938d7b9adcc60d9aad9d1dd604 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 19 九月 2023 17:37:03 +0800
Subject: [PATCH] 1.新增基本资料功能接口(erp车间/部门、erp仓库、erp用户信息) 2.报工接口添加字段入库条码 3.增加末道工序报工后提交入库接口(对接erp) 4.增加生成系统条码接口(自定义)
---
VueWebApi/Tools/DapperHelper.cs | 409 +++++++++++++++++++++++++++++++++++++++++++++++++---------
1 files changed, 344 insertions(+), 65 deletions(-)
diff --git a/VueWebApi/Tools/DapperHelper.cs b/VueWebApi/Tools/DapperHelper.cs
index 2d4cf04..1d5423b 100644
--- a/VueWebApi/Tools/DapperHelper.cs
+++ b/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, skip, pageIndex * pageSize);
+ 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,130 @@
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)
+ }
+
+ /// <summary>
+ /// Dapper鎵ц瀛樺偍杩囩▼杩斿洖datatable鏁版嵁(甯﹀弬鏁�)
+ /// </summary>
+ /// <param name="sql">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parm">鍙傛暟</param>
+ /// <returns></returns>
+ public static DataTable selectProcedure(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);
+ var res = conn.ExecuteReader(sql, parm, commandType: CommandType.StoredProcedure);//sql 瀛樺偍杩囩▼
+ table.Load(res);
+ return table;
+ }
+ 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>
@@ -223,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;
}
}
@@ -245,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>
@@ -281,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;
@@ -294,25 +467,66 @@
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;
}
}
/// <summary>
- /// Dapper鎻掑叆 杩斿洖鑷涓婚敭Id
+ /// Dapper鎻掑叆 杩斿洖鑷涓婚敭Id锛堝浠斤級
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <param name="tableName">寰呮彃鍏ユ暟鎹殑琛ㄥ悕</param>
/// <returns></returns>
- public static int insertReturnId(string sql, Object parameter, String tableName)
+ public static int insertReturnId0(string sql, Object parameter, String tableName)
{
int result = 0;
- try
+ using (IDbConnection conn = sqlConnection())
{
- using (IDbConnection conn = sqlConnection())
+ try
{
result = conn.Execute(sql, parameter);
if (result != 0)
@@ -320,13 +534,78 @@
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;
+ }
+
+ /// <summary>
+ /// Dapper鎻掑叆 杩斿洖鑷涓婚敭Id
+ /// </summary>
+ /// <param name="tableName">寰呮彃鍏ユ暟鎹殑琛ㄥ悕</param>
+ /// <returns></returns>
+ public static int insertReturnId(String tableName)
+ {
+
+ int result = 0;
+ using (IDbConnection conn = sqlConnection())
+ {
+ try
+ {
+ if (result == 0)
+ {
+ result = conn.Query<int>("select ident_current(@table)", new { table = tableName }).Single<int>();
+ }
+ }
+ catch (Exception ex)
+ {
+ //鍒涘缓鏃ュ織璁板綍缁勪欢瀹炰緥
+ LogHelper.WriteLog(ex);
+ throw ex;
+ }
+ finally
+ {
+ conn.Dispose();
+ conn.Close();
+ }
+ }
+ return result;
+ }
+
+ public static DataTable lissql(string sql)
+ {
+ //sql璇彞
+ using (IDbConnection conn = sqlConnection())
+ {
+ try
+ {
+ DataTable table = new DataTable();
+ //var data = conn.ExecuteReader(sql, parm);
+ var res = conn.ExecuteReader(sql);//sql 瀛樺偍杩囩▼
+ table.Load(res);
+ return table;
+ }
+ catch (Exception ex)
+ {
+ //鍒涘缓鏃ュ織璁板綍缁勪欢瀹炰緥
+ LogHelper.WriteLog(ex);
+ throw ex;
+ }
+ finally
+ {
+ conn.Dispose();
+ conn.Close();
+ }
}
}
}
--
Gitblit v1.9.3