From c5d2a942907529f1ff31dde1e630de9838e574de Mon Sep 17 00:00:00 2001
From: ouyangqing <ouyangqing@DESKTOP-B85SG1D>
Date: 星期四, 21 一月 2021 13:17:35 +0800
Subject: [PATCH] dapper
---
WebAPI/Dapper/SqlPools.cs | 37 ++++++
WebAPI/Service/YqnQbService.cs | 38 ++++++
WebAPI/Service/YqnDal.cs | 47 +++++++
WebAPI/Web.config | 4
WebAPI/WebAPI.csproj.user | 2
WebAPI/packages.config | 1
WebAPI/Dapper/SqlHelper.cs | 179 +++++++++++++++++++++++++++++
WebAPI/WebAPI.csproj | 7 +
WebAPI/App_Start/WebApiConfig.cs | 2
WebAPI/Controllers/NewApiController.cs | 24 +++
WebAPI/Models/M_StationBillMail.cs | 18 +++
11 files changed, 356 insertions(+), 3 deletions(-)
diff --git a/WebAPI/App_Start/WebApiConfig.cs b/WebAPI/App_Start/WebApiConfig.cs
index aea38b0..511fe89 100644
--- a/WebAPI/App_Start/WebApiConfig.cs
+++ b/WebAPI/App_Start/WebApiConfig.cs
@@ -3,6 +3,7 @@
using System.Linq;
using System.Web.Http;
using System.Web.Http.Cors;
+using WebAPI.Dapper;
namespace WebAPI
{
@@ -14,6 +15,7 @@
GlobalConfiguration.Configuration.Formatters.XmlFormatter.SupportedMediaTypes.Clear();
// Web API 璺敱
config.MapHttpAttributeRoutes();
+ new SqlPools();
var corsAttr = new EnableCorsAttribute("*", "*", "*");
config.EnableCors(corsAttr);
config.Routes.MapHttpRoute(
diff --git a/WebAPI/Controllers/NewApiController.cs b/WebAPI/Controllers/NewApiController.cs
index d1626a1..eff6c61 100644
--- a/WebAPI/Controllers/NewApiController.cs
+++ b/WebAPI/Controllers/NewApiController.cs
@@ -77,7 +77,28 @@
{
return YqnQbService.SetStationInBill(oMain);
}
-
+ /// <summary>
+ /// 鑾峰彇杩涚珯鍗曞垪琛�
+ /// </summary>
+ /// <param name="sWhere"></param>
+ /// <returns></returns>
+ [Route("api/newBill/getStationInBillList")]
+ [HttpGet]
+ public ApiResult<DataSet> GetStationInBillList(string sWhere)
+ {
+ return YqnQbService.GetStationInBillList(sWhere);
+ }
+ /// <summary>
+ /// 鑾峰彇鍑虹珯鍗曞垪琛�
+ /// </summary>
+ /// <param name="sWhere"></param>
+ /// <returns></returns>
+ [Route("api/newBill/getStationOutBillList")]
+ [HttpGet]
+ public ApiResult<DataSet> GetStationOutBillList(string sWhere)
+ {
+ return YqnQbService.GetStationOutBillList(sWhere);
+ }
/// <summary>
/// 鍑虹珯绔欐帴鏀跺崟
/// </summary>
@@ -87,7 +108,6 @@
[HttpPost]
public ApiResult SetStationOutBill(StationOutBillView oMain)
{
-
return YqnQbService.SetStationOutBill(oMain);
}
}
diff --git a/WebAPI/Dapper/SqlHelper.cs b/WebAPI/Dapper/SqlHelper.cs
new file mode 100644
index 0000000..60b0c3a
--- /dev/null
+++ b/WebAPI/Dapper/SqlHelper.cs
@@ -0,0 +1,179 @@
+锘縰sing Dapper;
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.SqlClient;
+using System.Linq;
+using System.Web;
+
+namespace WebAPI.Dapper
+{
+ public class SqlHelper
+ {
+ private readonly string _connStr;
+ public SqlHelper(string connStr)
+ {
+ _connStr = connStr;
+ }
+ IDbConnection GetConnection()
+ {
+ IDbConnection db = new SqlConnection(_connStr);
+ db.Open();
+ return db;
+ }
+ /// <summary>
+ /// 鑾峰彇涓�涓�硷紝param鍙互鏄疭QL鍙傛暟涔熷彲浠ユ槸鍖垮悕瀵硅薄
+ /// </summary>
+ /// <typeparam name="T"></typeparam>
+ /// <param name="sql"></param>
+ /// <param name="param"></param>
+ /// <param name="transaction"></param>
+ /// <param name="commandTimeout"></param>
+ /// <param name="commandType"></param>
+ /// <returns></returns>
+ public T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
+ {
+ using (var connection = GetConnection())
+ {
+ return connection.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
+ }
+ }
+ /// <summary>
+ /// 浜嬪姟鎵ц
+ /// </summary>
+ /// <param name="sql1"></param>
+ /// <param name="sql2"></param>
+ /// <param name="obj1"></param>
+ /// <param name="obj2"></param>
+ /// <returns></returns>
+ public int Trans(string sql1, string sql2, object obj1, object obj2)
+ {
+ using (var connection = GetConnection())
+ {
+ IDbTransaction transaction = connection.BeginTransaction();
+ try
+ {
+ connection.Execute(sql1, obj1);
+ connection.Execute(sql2, obj2);
+ transaction.Commit();
+ return 1;
+ }
+ catch (Exception)
+ {
+ transaction.Rollback();
+ return -1;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇涓�涓暟鎹ā鍨嬪疄浣撶被锛宲aram鍙互鏄疭QL鍙傛暟涔熷彲浠ユ槸鍖垮悕瀵硅薄
+ /// </summary>
+ /// <typeparam name="T"></typeparam>
+ /// <param name="sql"></param>
+ /// <param name="param"></param>
+ /// <param name="transaction"></param>
+ /// <param name="commandTimeout"></param>
+ /// <param name="commandType"></param>
+ /// <returns></returns>
+ public T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
+ {
+ using (var connection = GetConnection())
+ {
+ return connection.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
+ }
+ }
+ public int GetExecuteScalar(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
+ {
+ using (var connection = GetConnection())
+ {
+ var result = connection.ExecuteScalar<int>(sql, param, null, commandTimeout, commandType);
+ return result;
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇绗﹀悎鏉′欢鐨勬墍鏈夋暟鎹ā鍨嬪疄浣撶被鍒楄〃锛宲aram鍙互鏄疭QL鍙傛暟涔熷彲浠ユ槸鍖垮悕瀵硅薄
+ /// </summary>
+ /// <typeparam name="T"></typeparam>
+ /// <param name="sql"></param>
+ /// <param name="param"></param>
+ /// <param name="transaction"></param>
+ /// <param name="buffered"></param>
+ /// <param name="commandTimeout"></param>
+ /// <param name="commandType"></param>
+ /// <returns></returns>
+ public List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
+ {
+ using (var connection = GetConnection())
+ {
+ return connection.Query<T>(sql, param, null, buffered, commandTimeout, commandType).AsList();
+ }
+ }
+
+
+ /// <summary>
+ /// 鑾峰彇绗﹀悎鏉′欢鐨勬墍鏈夋寚瀹氳繑鍥炵粨鏋滃璞＄殑鍒楄〃(澶嶅悎瀵硅薄銆愬锛�1瀵瑰锛�1瀵�1銆�)锛宲aram鍙互鏄疭QL鍙傛暟涔熷彲浠ユ槸鍖垮悕瀵硅薄
+ /// </summary>
+ /// <typeparam name="T"></typeparam>
+ /// <param name="sql"></param>
+ /// <param name="types"></param>
+ /// <param name="map"></param>
+ /// <param name="param"></param>
+ /// <param name="transaction"></param>
+ /// <param name="buffered"></param>
+ /// <param name="splitOn"></param>
+ /// <param name="commandTimeout"></param>
+ /// <param name="commandType"></param>
+ /// <returns></returns>
+
+ public List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
+ {
+ using (var connection = GetConnection())
+ {
+ return connection.Query<T>(sql, types, map, param, null, buffered, splitOn, commandTimeout, commandType).AsList();
+ }
+ }
+
+ /// <summary>
+ /// 鎵цSQL鍛戒护锛圕RUD锛夛紝param鍙互鏄疭QL鍙傛暟涔熷彲浠ユ槸瑕佹坊鍔犵殑瀹炰綋绫�
+ /// </summary>
+ /// <param name="sql"></param>
+ /// <param name="param"></param>
+ /// <param name="transaction"></param>
+ /// <param name="commandTimeout"></param>
+ /// <param name="commandType"></param>
+ /// <returns></returns>
+ public int ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
+ {
+ using (var connection = GetConnection())
+ {
+
+ int result = connection.Execute(sql, param, null, commandTimeout, commandType);
+ return result;
+ }
+ }
+
+
+ public bool ExecuteCommandByTrans(Action<IDbCommand> action)
+ {
+ using (var connection = GetConnection())
+ {
+ IDbTransaction transaction = connection.BeginTransaction();
+ IDbCommand cmd = connection.CreateCommand();
+ cmd.Transaction = transaction;
+ try
+ {
+ action.Invoke(cmd);
+ transaction.Commit();
+ return true;
+ }
+ catch
+ {
+ transaction.Rollback();
+ }
+ }
+ return false;
+ }
+ }
+}
\ No newline at end of file
diff --git a/WebAPI/Dapper/SqlPools.cs b/WebAPI/Dapper/SqlPools.cs
new file mode 100644
index 0000000..300e9d2
--- /dev/null
+++ b/WebAPI/Dapper/SqlPools.cs
@@ -0,0 +1,37 @@
+锘縰sing System;
+using System.Collections.Generic;
+using System.Configuration;
+using System.Linq;
+using System.Web;
+
+namespace WebAPI.Dapper
+{
+ public class SqlPools
+ {
+ public SqlPools()
+ {
+ SecConnStr();
+ }
+ static Dictionary<string, string> sqlDic = new Dictionary<string, string>();
+ /// <summary>
+ /// 灏嗛厤缃枃浠跺す涓殑鏁版嵁搴撻摼鎺ュ叏閮ㄩ亶鍘嗚嚦瀛楀吀
+ /// </summary>
+ private void SecConnStr()
+ {
+ ConnectionStringSettingsCollection seetings = ConfigurationManager.ConnectionStrings;//鑾峰彇閰嶇疆鏂囦欢connectionstring鎵�鏈夎妭鐐�
+ for (int i = 0; i < seetings.Count; i++)
+ {
+ sqlDic.Add(seetings[i].Name, seetings[i].ConnectionString);//瀛樺叆瀛楀吀
+ }
+ }
+ /// <summary>
+ /// sqlhelper瀹炰緥鍖�
+ /// </summary>
+ /// <param name="dbName"></param>
+ /// <returns></returns>
+ public static SqlHelper GetInstance(string dbName)
+ {
+ return new SqlHelper(sqlDic[dbName]);//浠庡瓧鍏稿彇鍑烘暟鎹簱閾炬帴瀛楃涓�
+ }
+ }
+}
\ No newline at end of file
diff --git a/WebAPI/Models/M_StationBillMail.cs b/WebAPI/Models/M_StationBillMail.cs
new file mode 100644
index 0000000..eab8a75
--- /dev/null
+++ b/WebAPI/Models/M_StationBillMail.cs
@@ -0,0 +1,18 @@
+锘縰sing System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Web;
+
+namespace WebAPI.Models
+{
+ public class M_StationBillMail
+ {
+ public string HBillType { get; set; }
+ public int HInterID { get; set; }
+ public DateTime HDate { get; set; }
+ public string HBillNo { get; set; }
+ public string HICMOBillNo { get; set; }
+ public int HEmpID { get; set; }
+ public int HProcID { get; set; }
+ }
+}
\ No newline at end of file
diff --git a/WebAPI/Service/YqnDal.cs b/WebAPI/Service/YqnDal.cs
new file mode 100644
index 0000000..e60f7ee
--- /dev/null
+++ b/WebAPI/Service/YqnDal.cs
@@ -0,0 +1,47 @@
+锘縰sing System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Web;
+using WebAPI.Dapper;
+
+namespace WebAPI.Service
+{
+ public class YqnDal
+ {
+ /// <summary>
+ /// 鏍规嵁鍗曟嵁鍙锋垨鑰呮祦杞崱鏌ユ壘杩涚珯鍗�
+ /// </summary>
+ /// <param name="billNo"></param>
+ /// <param name="icmoBillNo"></param>
+ /// <returns></returns>
+ public static Models.M_StationBillMail GetStationInBillMail(string billNo, string icmoBillNo)
+ {
+ StringBuilder sqlBuilder = new StringBuilder();
+ sqlBuilder.Append("select top 1 HBillType,HInterID,HDate,HBillNo,HICMOBillNo,HEmpID,HProcID from Sc_StationInBillMain where HBillType='3790' ");
+ if (!string.IsNullOrEmpty(billNo))
+ sqlBuilder.Append(" and HBillNo=@billNo ");
+ if (!string.IsNullOrEmpty(icmoBillNo))
+ sqlBuilder.Append(" and HICMOBillNo=@icmoBillNo");
+ var model = SqlPools.GetInstance("YqnConn").GetModel<Models.M_StationBillMail>(sqlBuilder.ToString(), new { billNo, icmoBillNo });
+ return model;
+ }
+ /// <summary>
+ /// 鏍规嵁鍗曟嵁鍙锋垨鑰呮祦杞崱鏌ユ壘鍑虹珯鍗�
+ /// </summary>
+ /// <param name="billNo"></param>
+ /// <param name="icmoBillNo"></param>
+ /// <returns></returns>
+ public static Models.M_StationBillMail GetStationOutBillMail(string billNo, string icmoBillNo)
+ {
+ StringBuilder sqlBuilder = new StringBuilder();
+ sqlBuilder.Append("select top 1 HBillType,HInterID,HDate,HBillNo,HICMOBillNo,HEmpID,HProcID from Sc_StationOutBillMain where HBillType in ('3791','4788') ");
+ if (!string.IsNullOrEmpty(billNo))
+ sqlBuilder.Append(" and HBillNo=@billNo ");
+ if (!string.IsNullOrEmpty(icmoBillNo))
+ sqlBuilder.Append(" and HICMOBillNo=@icmoBillNo");
+ var model = SqlPools.GetInstance("YqnConn").GetModel<Models.M_StationBillMail>(sqlBuilder.ToString(), new { billNo, icmoBillNo });
+ return model;
+ }
+ }
+}
\ No newline at end of file
diff --git a/WebAPI/Service/YqnQbService.cs b/WebAPI/Service/YqnQbService.cs
index 197aded..9ec208b 100644
--- a/WebAPI/Service/YqnQbService.cs
+++ b/WebAPI/Service/YqnQbService.cs
@@ -100,6 +100,30 @@
return new ApiResult { code = 1, msg = "鎿嶄綔鎴愬姛" };
}
/// <summary>
+ /// 鑾峰彇杩涚珯鍗曞垪琛�
+ /// </summary>
+ /// <param name="sWhere"></param>
+ /// <returns></returns>
+ public static ApiResult<DataSet> GetStationInBillList(string sWhere)
+ {
+ var dataSet = GetStationInBill(sWhere);
+ if (dataSet == null || dataSet.Tables[0].Rows.Count == 0)
+ return new ApiResult<DataSet> { code = -1, msg = "鏈煡璇㈠埌鏁版嵁" };
+ return new ApiResult<DataSet> { code = 1, msg = "鏌ヨ鎴愬姛", data = dataSet };
+ }
+ /// <summary>
+ /// 鑾峰彇鍑虹珯鍗曞垪琛�
+ /// </summary>
+ /// <param name="sWhere"></param>
+ /// <returns></returns>
+ public static ApiResult<DataSet> GetStationOutBillList(string sWhere)
+ {
+ var dataSet = GetStationOutBill(sWhere);
+ if (dataSet == null || dataSet.Tables[0].Rows.Count == 0)
+ return new ApiResult<DataSet> { code = -1, msg = "鏈煡璇㈠埌鏁版嵁" };
+ return new ApiResult<DataSet> { code = 1, msg = "鏌ヨ鎴愬姛", data = dataSet };
+ }
+ /// <summary>
/// 鍑虹珯鎺ユ敹鍗�
/// </summary>
public static ApiResult SetStationOutBill(StationOutBillView omodel)
@@ -110,6 +134,20 @@
return result;
}
#region sql璇彞
+ public static DataSet GetStationOutBill(string strWhere)
+ {
+ var sql = "Select top 5000 * from h_v_MES_StationOutBillList Where 1=1 " + strWhere;
+ SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
+ var dataSet = oCN.RunProcReturn(sql, "h_v_MES_StationOutBillList");
+ return dataSet;
+ }
+ public static DataSet GetStationInBill(string strWhere)
+ {
+ var sql = "Select top 5000 * from h_v_MES_StationInBillList Where 1=1 " + strWhere;
+ SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
+ var dataSet = oCN.RunProcReturn(sql, "h_v_MES_StationInBillList");
+ return dataSet;
+ }
public static DataSet GetBarCodeDb(string billBarCode)
{
SQLHelper.ClsCN oCN = new SQLHelper.ClsCN();
diff --git a/WebAPI/Web.config b/WebAPI/Web.config
index d298339..959ee45 100644
--- a/WebAPI/Web.config
+++ b/WebAPI/Web.config
@@ -8,7 +8,11 @@
<sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<section name="WebAPI.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</sectionGroup>
+
</configSections>
+ <connectionStrings>
+ <add name="YqnConn" connectionString="Data Source=.;Initial Catalog=HX_LimsSys;Integrated Security=True" />
+ </connectionStrings>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
diff --git a/WebAPI/WebAPI.csproj b/WebAPI/WebAPI.csproj
index 1dba599..313f4c9 100644
--- a/WebAPI/WebAPI.csproj
+++ b/WebAPI/WebAPI.csproj
@@ -55,6 +55,9 @@
<SpecificVersion>False</SpecificVersion>
<HintPath>DLL\DAL.dll</HintPath>
</Reference>
+ <Reference Include="Dapper, Version=1.50.2.0, Culture=neutral, processorArchitecture=MSIL">
+ <HintPath>..\packages\Dapper.1.50.2\lib\net45\Dapper.dll</HintPath>
+ </Reference>
<Reference Include="DBUtility, Version=1.0.0.0, Culture=neutral, processorArchitecture=x86">
<SpecificVersion>False</SpecificVersion>
<HintPath>DLL\DBUtility.dll</HintPath>
@@ -312,10 +315,13 @@
<Compile Include="App_Start\SwaggerConfig.cs" />
<Compile Include="App_Start\WebApiConfig.cs" />
<Compile Include="Controllers\NewApiController.cs" />
+ <Compile Include="Dapper\SqlHelper.cs" />
+ <Compile Include="Dapper\SqlPools.cs" />
<Compile Include="DbUntil\DataFormatUntil.cs" />
<Compile Include="Models\ApiConfig.cs" />
<Compile Include="Models\ApiResult.cs" />
<Compile Include="Models\DocumentsView.cs" />
+ <Compile Include="Models\M_StationBillMail.cs" />
<Compile Include="Models\StationOutBillView.cs" />
<Compile Include="Properties\Resources.Designer.cs">
<AutoGen>True</AutoGen>
@@ -327,6 +333,7 @@
<DesignTimeSharedInput>True</DesignTimeSharedInput>
<DependentUpon>Settings.settings</DependentUpon>
</Compile>
+ <Compile Include="Service\YqnDal.cs" />
<Compile Include="Service\YqnQbService.cs" />
<Compile Include="Web References\WebS\Reference.cs">
<AutoGen>True</AutoGen>
diff --git a/WebAPI/WebAPI.csproj.user b/WebAPI/WebAPI.csproj.user
index 6abc680..1d7d7d6 100644
--- a/WebAPI/WebAPI.csproj.user
+++ b/WebAPI/WebAPI.csproj.user
@@ -8,7 +8,7 @@
<WebStackScaffolding_LayoutPageFile />
<WebStackScaffolding_IsAsyncSelected>False</WebStackScaffolding_IsAsyncSelected>
<NameOfLastUsedPublishProfile>PDAWeb</NameOfLastUsedPublishProfile>
- <LastActiveSolutionConfig>Release|Any CPU</LastActiveSolutionConfig>
+ <LastActiveSolutionConfig>Debug|Any CPU</LastActiveSolutionConfig>
<UseIISExpress>false</UseIISExpress>
<Use64BitIISExpress />
<IISExpressSSLPort />
diff --git a/WebAPI/packages.config b/WebAPI/packages.config
index 30504ec..a7f3198 100644
--- a/WebAPI/packages.config
+++ b/WebAPI/packages.config
@@ -2,6 +2,7 @@
<packages>
<package id="Antlr" version="3.4.1.9004" targetFramework="net45" />
<package id="bootstrap" version="3.0.0" targetFramework="net45" />
+ <package id="Dapper" version="1.50.2" targetFramework="net45" />
<package id="jQuery" version="1.10.2" targetFramework="net45" />
<package id="jQuery.Validation" version="1.11.1" targetFramework="net45" />
<package id="Microsoft.AspNet.Cors" version="5.2.7" targetFramework="net45" />
--
Gitblit v1.9.1