| WebAPI/App_Start/WebApiConfig.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Controllers/NewApiController.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Dapper/SqlHelper.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Dapper/SqlPools.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Models/M_StationBillMail.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Service/YqnDal.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Service/YqnQbService.cs | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/Web.config | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/WebAPI.csproj | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/WebAPI.csproj.user | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| WebAPI/packages.config | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 |
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( 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); } } WebAPI/Dapper/SqlHelper.cs
New file @@ -0,0 +1,179 @@ using 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可以是SQL参数也可以是匿名对象 /// </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> /// 获取一个数据模型实体类，param可以是SQL参数也可以是匿名对象 /// </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> /// 获取符合条件的所有数据模型实体类列表，param可以是SQL参数也可以是匿名对象 /// </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】)，param可以是SQL参数也可以是匿名对象 /// </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命令（CRUD），param可以是SQL参数也可以是要添加的实体类 /// </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; } } } WebAPI/Dapper/SqlPools.cs
New file @@ -0,0 +1,37 @@ using 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]);//从字典取出数据库链接字符串 } } } WebAPI/Models/M_StationBillMail.cs
New file @@ -0,0 +1,18 @@ using 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; } } } WebAPI/Service/YqnDal.cs
New file @@ -0,0 +1,47 @@ using 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; } } } 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(); 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" /> 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> 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 /> 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" />
