From ae6bbf378b56b3a583527c546f8050b9813c1d1b Mon Sep 17 00:00:00 2001
From: 南骏 池 <chiffly@163.com>
Date: 星期四, 13 三月 2025 14:38:54 +0800
Subject: [PATCH] 1.生产入库数据源优化
---
service/Wom/WomdaaManager.cs | 68 +++--
entity/ItemDetailModel.cs | 4
util/DbHelperSQL.cs | 688 ++++++++++++++++++++++++++++++++++++++++++++++++++++
Dto/service/ProductionPickDto.cs | 7
4 files changed, 739 insertions(+), 28 deletions(-)
diff --git a/Dto/service/ProductionPickDto.cs b/Dto/service/ProductionPickDto.cs
index ccc8072..14390dc 100644
--- a/Dto/service/ProductionPickDto.cs
+++ b/Dto/service/ProductionPickDto.cs
@@ -1,4 +1,5 @@
-锘縰sing NewPdaSqlServer.entity;
+锘縰sing MES.Service.Modes;
+using NewPdaSqlServer.entity;
namespace NewPdaSqlServer.Dto.service;
@@ -19,6 +20,10 @@
//杩斿洖鍙傛暟绫诲瀷 -1锛氬け璐� 1锛氭垚鍔�
public string? result { get; set; }
+ public List<ItemDetailModel>? items { get; set; }
+
+ public List<ItemDetailModel>? Ysitems { get; set; }
+
public List<Womdab>? totals { get; set; }
public List<WwGdDetail>? totals1 { get; set; }
diff --git a/entity/MesDbModel.cs b/entity/ItemDetailModel.cs
similarity index 84%
rename from entity/MesDbModel.cs
rename to entity/ItemDetailModel.cs
index 79ced70..ff3ac10 100644
--- a/entity/MesDbModel.cs
+++ b/entity/ItemDetailModel.cs
@@ -10,6 +10,10 @@
public string ItemModel { get; set; } // 鐗╁搧鍨嬪彿
public decimal? FQty { get; set; } // 鐢宠鏁伴噺
public decimal? SQty { get; set; } // 璋冨嚭宸叉壂鏁伴噺
+
+ public string? RecoKw { get; set; } // 鎺ㄨ崘搴撲綅
+
+ public decimal? DSQty { get; set; } // 寰呮壂鏁伴噺
public decimal? RQty { get; set; } // 璋冨叆宸叉壂鏁伴噺
public string Pid { get; set; } // 鍏宠仈瀛楁
public string FMaterialId { get; set; } // 鏉愭枡 ID
diff --git a/service/Wom/WomdaaManager.cs b/service/Wom/WomdaaManager.cs
index 425e39d..4e169d9 100644
--- a/service/Wom/WomdaaManager.cs
+++ b/service/Wom/WomdaaManager.cs
@@ -1,6 +1,8 @@
锘縰sing System.Data;
using System.Data.SqlClient;
using Masuit.Tools;
+using MES.Service.Modes;
+using Microsoft.SqlServer.Server;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
@@ -172,31 +174,43 @@
if (womdaa?.Daa001 == null) throw new Exception("宸ュ崟鍙蜂笉瀛樺湪");
- var womdabs = Db.Queryable<Womdaa, Womdab, MesItems, Womcab>(
- (a, b, c, d) =>
- new JoinQueryInfos(
- JoinType.Left,
- a.Guid == b.DaaGuid,
- JoinType.Inner,
- c.Id.ToString() ==
- b.Dab003,
- JoinType.Inner, b.Erpid == d.Erpid
- ))
- .Where((a, b, c, d) =>
- a.Daa001 == query.daa001 && d.IssueType == "1")
- .Select((a, b, c, d) => new Womdab
- {
- //Pid = b.Pid,
- Dab003 = b.Dab003,
- Dab006 = b.Dab006,
- Dab007 = b.Dab007,
- wNum = b.Dab006 - b.Dab007, // 璁＄畻瀛楁 W_NUM
- ItemName = c.ItemName, // 鍔ㄦ�佸瓧娈� ITEM_NAME
- ItemNo = c.ItemNo // 鍔ㄦ�佸瓧娈� ITEM_NO
- })
- .ToList();
+ //var womdabs = Db.Queryable<Womdaa, Womdab, MesItems, Womcab>(
+ // (a, b, c, d) =>
+ // new JoinQueryInfos(
+ // JoinType.Left,
+ // a.Guid == b.DaaGuid,
+ // JoinType.Inner,
+ // c.Id.ToString() ==
+ // b.Dab003,
+ // JoinType.Inner, b.Erpid == d.Erpid
+ // ))
+ // .Where((a, b, c, d) =>
+ // a.Daa001 == query.daa001 && d.Iss1ueType == "1")
+ // .Select((a, b, c, d) => new ItemDetailModel
+ // {
+ // ItemNo = c.ItemNo,
+ // ItemName = c.ItemName,
+ // ItemModel = c.ItemModel,
+ // FQty = b.Dab006, // 鐢宠鏁伴噺
+ // SQty = b.Dab007, // 宸叉壂鏁伴噺
+ // DSQty = b.Dab006 - b.Dab007, // 宸叉壂鏁伴噺
+ // })
+ // .ToList();
- var list = womdabs.Where(s => s.wNum > 0).ToList();
+ var sql =string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,b.dab006 FQty,b.dab007 SQty,b.dab006 - b.dab007 DSQty ,
+ dbo.F_QX_GETRECODEPOTSE(B.dab003) as RecoKw
+ FROM WOMDAB B
+ LEFT JOIN WOMDAA A ON A.guid = B.daaGuid
+ LEFT JOIN MES_ITEMS C ON B.dab003 = C.item_id
+ LEFT JOIN WOMCAB D ON B.erpid = D.ERPID
+ WHERE daa001 = '{0}' AND ISSUE_TYPE = 1 ORDER BY DAB002", query.daa001);
+
+ var womdabs = Db.Ado.SqlQuery<ItemDetailModel>(sql);
+
+
+ var DS_list = womdabs.Where(s => s.DSQty > 0).ToList();
+
+ var YS_list = womdabs.Where(s => s.SQty > 0).ToList();
var mesInvItemOutCDetailsList = Db
.Queryable<MesInvItemOutCDetails, MesItems, MesDepots>
@@ -230,9 +244,9 @@
{
daa001 = womdaa.Daa001,
PlanNo = womcaa.Caa020,
- totals = womdabs,
- daisao = list,
- yisao = mesInvItemOutCDetailsList
+ items = DS_list,
+ Ysitems = YS_list
+ // yisao = mesInvItemOutCDetailsList
};
return dto;
diff --git a/util/DbHelperSQL.cs b/util/DbHelperSQL.cs
new file mode 100644
index 0000000..468dd88
--- /dev/null
+++ b/util/DbHelperSQL.cs
@@ -0,0 +1,688 @@
+锘縰sing System.Collections;
+using System.Data;
+using System.Data.SqlClient;
+
+namespace Gs.Toolbox;
+
+/// <summary>
+/// 鏁版嵁璁块棶绫�
+/// </summary>
+public abstract class DbHelperSQL
+{
+ //鏁版嵁搴撹繛鎺ュ瓧绗︿覆(web.config鏉ラ厤缃�)
+ public static readonly string strConn =
+ getValueByKey("DataBaseConn");
+
+ #region 鍏敤鏂规硶
+
+ /// <summary>
+ /// 鍙栧埌鏈�澶у瓧娈靛��
+ /// </summary>
+ /// <param name="FieldName"></param>
+ /// <param name="TableName"></param>
+ /// <returns></returns>
+ public static int GetMaxID(string FieldName, string TableName)
+ {
+ var strsql = "select max(" + FieldName + ") from " + TableName;
+ var obj = GetSingle(strsql);
+ if (obj == null)
+ return 0;
+ return int.Parse(obj.ToString());
+ }
+
+ /// <summary>
+ /// 鏄惁瀛樺湪鏌愭潯璁板綍
+ /// </summary>
+ /// <param name="strSql"></param>
+ /// <param name="cmdParms"></param>
+ /// <returns></returns>
+ public static bool Exists(string strSql, params SqlParameter[] cmdParms)
+ {
+ var obj = GetSingle(strSql, cmdParms);
+ int cmdresult;
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ cmdresult = 0;
+ else
+ cmdresult = int.Parse(obj.ToString());
+ if (cmdresult == 0)
+ return false;
+ return true;
+ }
+
+ #endregion
+
+ #region 鎵ц绠�鍗昐QL璇彞
+
+ /// <summary>
+ /// 鎵цSQL璇彞锛岃繑鍥炲奖鍝嶇殑璁板綍鏁�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand(SQLString, connection))
+ {
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ connection.Close();
+ throw new Exception(E.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">澶氭潯SQL璇彞</param>
+ public static void ExecuteSqlTran(ArrayList SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ var cmd = new SqlCommand();
+ cmd.Connection = conn;
+ var tx = conn.BeginTransaction();
+ cmd.Transaction = tx;
+ try
+ {
+ for (var n = 0; n < SQLStringList.Count; n++)
+ {
+ var strsql = SQLStringList[n].ToString();
+ if (strsql.Trim().Length > 1)
+ {
+ cmd.CommandText = strsql;
+ cmd.ExecuteNonQuery();
+ }
+ }
+
+ tx.Commit();
+ }
+ catch (SqlException E)
+ {
+ tx.Rollback();
+ throw new Exception(E.Message);
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц甯︿竴涓瓨鍌ㄨ繃绋嬪弬鏁扮殑鐨凷QL璇彞銆�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <param name="content">鍙傛暟鍐呭,姣斿涓�涓瓧娈垫槸鏍煎紡澶嶆潅鐨勬枃绔狅紝鏈夌壒娈婄鍙凤紝鍙互閫氳繃杩欎釜鏂瑰紡娣诲姞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString, string content)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand(SQLString, connection);
+ var myParameter = new SqlParameter("@content", SqlDbType.NText);
+ myParameter.Value = content;
+ cmd.Parameters.Add(myParameter);
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ finally
+ {
+ cmd.Dispose();
+ connection.Close();
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鍚戞暟鎹簱閲屾彃鍏ュ浘鍍忔牸寮忕殑瀛楁(鍜屼笂闈㈡儏鍐电被浼肩殑鍙︿竴绉嶅疄渚�)
+ /// </summary>
+ /// <param name="strSQL">SQL璇彞</param>
+ /// <param name="fs">鍥惧儚瀛楄妭,鏁版嵁搴撶殑瀛楁绫诲瀷涓篿mage鐨勬儏鍐�</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand(strSQL, connection);
+ var myParameter = new SqlParameter("@fs", SqlDbType.Image);
+ myParameter.Value = fs;
+ cmd.Parameters.Add(myParameter);
+ try
+ {
+ connection.Open();
+ var rows = cmd.ExecuteNonQuery();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ finally
+ {
+ cmd.Dispose();
+ connection.Close();
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц涓�鏉¤绠楁煡璇㈢粨鏋滆鍙ワ紝杩斿洖鏌ヨ缁撴灉锛坥bject锛夈��
+ /// </summary>
+ /// <param name="SQLString">璁＄畻鏌ヨ缁撴灉璇彞</param>
+ /// <returns>鏌ヨ缁撴灉锛坥bject锛�</returns>
+ public static object GetSingle(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand(SQLString, connection))
+ {
+ try
+ {
+ connection.Open();
+ var obj = cmd.ExecuteScalar();
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ return null;
+ return obj;
+ }
+ catch (SqlException e)
+ {
+ connection.Close();
+ throw new Exception(e.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濻qlDataReader
+ /// </summary>
+ /// <param name="strSQL">鏌ヨ璇彞</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader ExecuteReader(string strSQL)
+ {
+ var connection = new SqlConnection(strConn);
+ var cmd = new SqlCommand(strSQL, connection);
+ try
+ {
+ connection.Open();
+ var myReader = cmd.ExecuteReader();
+ return myReader;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濪ataSet
+ /// </summary>
+ /// <param name="SQLString">鏌ヨ璇彞</param>
+ /// <returns>DataSet</returns>
+ public static DataSet Query(string SQLString)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var ds = new DataSet();
+ try
+ {
+ connection.Open();
+ var command = new SqlDataAdapter(SQLString, connection);
+ command.Fill(ds, "ds");
+ }
+ catch (SqlException ex)
+ {
+ throw new Exception(ex.Message);
+ }
+
+ return ds;
+ }
+ }
+
+ #endregion
+
+ #region 鎵ц甯﹀弬鏁扮殑SQL璇彞
+
+ /// <summary>
+ /// 鎵цSQL璇彞锛岃繑鍥炲奖鍝嶇殑璁板綍鏁�
+ /// </summary>
+ /// <param name="SQLString">SQL璇彞</param>
+ /// <returns>褰卞搷鐨勮褰曟暟</returns>
+ public static int ExecuteSql(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand())
+ {
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var rows = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+ return rows;
+ }
+ catch (SqlException E)
+ {
+ throw new Exception(E.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓簊ql璇彞锛寁alue鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static void ExecuteSqlTran(Hashtable SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = myDE.Key.ToString();
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+
+ //trans.Commit();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟銆�
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓簊ql璇彞锛寁alue鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static int ExecuteSqlTranRtn(Hashtable SQLStringList)
+ {
+ var d = 0;
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = myDE.Key.ToString();
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ d = d + val;
+ cmd.Parameters.Clear();
+
+ //trans.Commit();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+
+ return d;
+ }
+
+ /// <summary>
+ /// 鎵ц澶氭潯SQL璇彞锛屽疄鐜版暟鎹簱浜嬪姟
+ /// </summary>
+ /// <param name="SQLStringList">SQL璇彞鐨勫搱甯岃〃锛坘ey涓烘爣璇嗭紝value鏄璇彞鐨凷qlParameter[]锛�</param>
+ public static void ExecuteSqlTran(string sqltxt, Hashtable SQLStringList)
+ {
+ using (var conn = new SqlConnection(strConn))
+ {
+ conn.Open();
+ using (var trans = conn.BeginTransaction())
+ {
+ var cmd = new SqlCommand();
+ try
+ {
+ //寰幆
+ foreach (DictionaryEntry myDE in SQLStringList)
+ {
+ var cmdText = sqltxt;
+ var cmdParms = (SqlParameter[])myDE.Value;
+ PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
+ var val = cmd.ExecuteNonQuery();
+ cmd.Parameters.Clear();
+ }
+
+ trans.Commit();
+ }
+ catch
+ {
+ trans.Rollback();
+ throw;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц涓�鏉¤绠楁煡璇㈢粨鏋滆鍙ワ紝杩斿洖鏌ヨ缁撴灉锛坥bject锛夈��
+ /// </summary>
+ /// <param name="SQLString">璁＄畻鏌ヨ缁撴灉璇彞</param>
+ /// <returns>鏌ヨ缁撴灉锛坥bject锛�</returns>
+ public static object GetSingle(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ using (var cmd = new SqlCommand())
+ {
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var obj = cmd.ExecuteScalar();
+ cmd.Parameters.Clear();
+ if (Equals(obj, null) || Equals(obj, DBNull.Value))
+ return null;
+ return obj;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濻qlDataReader
+ /// </summary>
+ /// <param name="strSQL">鏌ヨ璇彞</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader ExecuteReader(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ var connection = new SqlConnection(strConn);
+ var cmd = new SqlCommand();
+ try
+ {
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ var myReader = cmd.ExecuteReader();
+ cmd.Parameters.Clear();
+ return myReader;
+ }
+ catch (SqlException e)
+ {
+ throw new Exception(e.Message);
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц鏌ヨ璇彞锛岃繑鍥濪ataSet
+ /// </summary>
+ /// <param name="SQLString">鏌ヨ璇彞</param>
+ /// <returns>DataSet</returns>
+ public static DataSet Query(string SQLString,
+ params SqlParameter[] cmdParms)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var cmd = new SqlCommand();
+ PrepareCommand(cmd, connection, null, SQLString, cmdParms);
+ using (var da = new SqlDataAdapter(cmd))
+ {
+ var ds = new DataSet();
+ try
+ {
+ da.Fill(ds, "ds");
+ cmd.Parameters.Clear();
+ }
+ catch (SqlException ex)
+ {
+ throw new Exception(ex.Message);
+ }
+
+ return ds;
+ }
+ }
+ }
+
+ //private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
+ //{
+
+ // if (conn.State != ConnectionState.Open)
+ // conn.Open();
+ // cmd.Connection = conn;
+ // cmd.CommandText = cmdText;
+ // if (trans != null)
+ // cmd.Transaction = trans;
+ // cmd.CommandType = CommandType.Text;//cmdType;
+ // if (cmdParms != null)
+ // {
+ // foreach (SqlParameter parameter in cmdParms)
+ // {
+ // if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
+ // (parameter.Value == null))
+ // {
+ // parameter.Value = DBNull.Value;
+ // }
+ // cmd.Parameters.Add(parameter);
+ // }
+ // }
+ //}
+ private static void PrepareCommand(SqlCommand cmd, SqlConnection conn,
+ SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
+ {
+ if (conn.State != ConnectionState.Open)
+ conn.Open();
+ cmd.Connection = conn;
+ cmd.CommandText = cmdText;
+ if (trans != null)
+ cmd.Transaction = trans;
+ cmd.CommandType = CommandType.Text; //cmdType;
+ if (cmdParms != null)
+ foreach (var parm in cmdParms)
+ {
+ //cmd.Parameters.Add(parm);
+ if ((parm.Direction == ParameterDirection.InputOutput ||
+ parm.Direction == ParameterDirection.Input) &&
+ parm.Value == null)
+ parm.Value = DBNull.Value;
+ cmd.Parameters.Add(parm);
+ }
+ }
+
+ #endregion
+
+ #region 瀛樺偍杩囩▼鎿嶄綔
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlDataReader</returns>
+ public static SqlDataReader RunProcedure(string storedProcName,
+ IDataParameter[] parameters)
+ {
+ var connection = new SqlConnection(strConn);
+ SqlDataReader returnReader;
+ connection.Open();
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.CommandType = CommandType.StoredProcedure;
+ returnReader = command.ExecuteReader();
+ return returnReader;
+ }
+
+ /// <summary>
+ /// 鎵ц甯﹁緭鍑哄弬鏁扮殑瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟,OutName 杈撳嚭鍙傛暟鐨勫悕瀛楋紙濡傛灉鏄涓互@涓哄垎鍓茬锛�</param>
+ /// <returns>string(浠鍒嗗壊鐨勮緭鍑哄弬鏁扮殑鍊�)</returns>
+ public static string RunProcedureOut(string storedProcName,
+ IDataParameter[] parameters, string outName)
+ {
+ var connection = new SqlConnection(strConn);
+ var returnstring = string.Empty;
+ connection.Open();
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.CommandType = CommandType.StoredProcedure;
+ command.ExecuteNonQuery();
+ var str = string.Empty;
+ //瑙ｆ瀽绌胯繃鏉ョ殑鍙傛暟
+ var Arraystr = outName.Split('|');
+ foreach (var st in Arraystr)
+ {
+ var a = st;
+ if (str == string.Empty)
+ str = command.Parameters[a].Value.ToString();
+ else
+ str = str + "|" + command.Parameters[a].Value;
+ }
+ return str;
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <param name="tableName">DataSet缁撴灉涓殑琛ㄥ悕</param>
+ /// <returns>DataSet</returns>
+ public static DataSet RunProcedure(string storedProcName,
+ IDataParameter[] parameters, string tableName)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ var dataSet = new DataSet();
+ connection.Open();
+ var sqlDA = new SqlDataAdapter();
+ sqlDA.SelectCommand =
+ BuildQueryCommand(connection, storedProcName, parameters);
+ sqlDA.Fill(dataSet, tableName);
+ connection.Close();
+ return dataSet;
+ }
+ }
+
+ /// <summary>
+ /// 鏋勫缓 SqlCommand 瀵硅薄(鐢ㄦ潵杩斿洖涓�涓粨鏋滈泦锛岃�屼笉鏄竴涓暣鏁板��)
+ /// </summary>
+ /// <param name="connection">鏁版嵁搴撹繛鎺�</param>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlCommand</returns>
+ private static SqlCommand BuildQueryCommand(SqlConnection connection,
+ string storedProcName, IDataParameter[] parameters)
+ {
+ var command = new SqlCommand(storedProcName, connection);
+ command.CommandType = CommandType.StoredProcedure;
+ foreach (SqlParameter parameter in parameters)
+ command.Parameters.Add(parameter);
+ return command;
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼锛岃繑鍥炲奖鍝嶇殑琛屾暟
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <param name="rowsAffected">褰卞搷鐨勮鏁�</param>
+ /// <returns></returns>
+ public static int RunProcedure(string storedProcName,
+ IDataParameter[] parameters, out int rowsAffected)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ int result;
+ connection.Open();
+ var command =
+ BuildIntCommand(connection, storedProcName, parameters);
+ rowsAffected = command.ExecuteNonQuery();
+ result = (int)command.Parameters["ReturnValue"].Value;
+ //Connection.Close();
+ return result;
+ }
+ }
+
+ /// <summary>
+ /// 鎵ц瀛樺偍杩囩▼锛岃繑鍥炲奖鍝嶈鏁�
+ /// </summary>
+ /// <param name="storedProcName">string绫诲瀷瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">SqlParameters[]瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>int 褰卞搷琛屾暟</returns>
+ public static int RunProcedure_NonQuery(string storedProcName,
+ IDataParameter[] parameters)
+ {
+ using (var connection = new SqlConnection(strConn))
+ {
+ connection.Open();
+ var command =
+ BuildQueryCommand(connection, storedProcName, parameters);
+ return command.ExecuteNonQuery();
+ }
+ }
+
+ /// <summary>
+ /// 鍒涘缓 SqlCommand 瀵硅薄瀹炰緥(鐢ㄦ潵杩斿洖涓�涓暣鏁板��)
+ /// </summary>
+ /// <param name="storedProcName">瀛樺偍杩囩▼鍚�</param>
+ /// <param name="parameters">瀛樺偍杩囩▼鍙傛暟</param>
+ /// <returns>SqlCommand 瀵硅薄瀹炰緥</returns>
+ private static SqlCommand BuildIntCommand(SqlConnection connection,
+ string storedProcName, IDataParameter[] parameters)
+ {
+ var command = BuildQueryCommand(connection, storedProcName, parameters);
+ command.Parameters.Add(new SqlParameter("ReturnValue",
+ SqlDbType.Int, 4, ParameterDirection.ReturnValue,
+ false, 0, 0, string.Empty, DataRowVersion.Default, null));
+ return command;
+ }
+
+ #endregion
+
+
+ public static string getValueByKey(string key)
+ {
+ // 鍒涘缓閰嶇疆鏋勫缓鍣�
+ var builder = new ConfigurationBuilder()
+ .SetBasePath(Directory.GetCurrentDirectory())
+ .AddJsonFile("appsettings.json", true, true);
+ // 鏋勫缓閰嶇疆
+ var configuration = builder.Build();
+ return configuration[key];
+ }
+
+}
\ No newline at end of file
--
Gitblit v1.9.3