Caché数据库学习笔记（5）

目录

Cache数据库方法的RESTful封装

 ================================================================

因为对web service的基础理论了解不多，所以本篇笔记仅讨论在一个已有框架中添加并封装新的表方法供前端调用，工程整体框架如果以后看懂了再补吧。

首先在Ensemble里找到名为CacheNetWizard的应用程序，该程序目的是产生一个.dll文件，通过将该.dll文件添加到程序引用中，可以实现asp.net与数据库之间的通信。

如图，连接服务器，输入用户名密码并选择命名空间，选择Assembly，语言选择C#，输出文件存放到工程目录/bin/Cache.classname.dll，选择class时父子表关系的只选择父表即可。每一次改动Ensemble里的class就要重新生成一次.dll

 

封装的目的是可以通过前端调用Ensemble中的方法，和其他web开发相似，在程序中由controller与前端通信。

 1 //Controllers/ExampleController.cs
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Net;
 6 using System.Net.Http;
 7 using System.Web.Http;
 8 using SpaceBed.CommonLibrary;
 9 using System.Web.Http.OData;
10 using InterSystems.Data.CacheClient;
11 using SpaceBed.DataModels;
12 using SpaceBed.Models;
13 
14 
15 namespace SpaceBed.Controllers
16 {
17     [WebApiTracker]
18     [RESTAuthorizeAttribute]
19     public class ExampleController : ApiController
20     {
21         static readonly IExampleRepository repository = new ExampleRepository();
22         DataConnection pclsCache = new DataConnection();
23 
24 
25         /// <summary>
26         /// 插入数据 2016-10-13
27         /// </summary>
28         /// <param name="TestBedInfo"></param>
29         /// <returns></returns>
30         [Route("Api/v1/Example/SetExampleData")]
31 
32         public HttpResponseMessage SetExampleData(Example Example)
33         {
34             int ret = repository.SetExampleData(pclsCache, Example.PlanNo, Example.StartDate, Example.Status, Example.DoctorId);
35             return new ExceptionHandler().SetData(Request, ret);
36         }
37 
38         /// 根据主键获取除主键外所有信息 2016-10-13
39         ///
40         [Route("Api/v1/Example/GetPatientPlan")]
41 
42         public Example GetPatientPlan(string strPlanNo)
43         {
44             return repository.GetPatientPlan(pclsCache, strPlanNo);
45         }
46 
47         /// 根据PlanNo修改Status 2016-10-14
48         /// 
49         [Route("Api/v1/Example/ChangeStatus")]
50 
51         public HttpResponseMessage ChangeStatus(string strPlanNo, int strStatus)
52         {
53             int ret = repository.ChangeStatus(pclsCache, strPlanNo, strStatus);
54             return new ExceptionHandler().ChangeStatus(Request, ret);
55         }
56 
57         /// 根据Status和StartDate查找所有数据 2016-10-17
58         /// 
59         [Route("Api/v1/Example/GetPlan")]
60 
61         public List<Example> GetPlan(int strStatus, int strStartDate)
62         {
63             return repository.GetPlan(pclsCache, strStatus, strStartDate);
64         }
65 
66         ///添加Detail数据 2016-10-17
67         ///
68         [Route("Api/v1/Example/SetDetailData")]
69 
70         public HttpResponseMessage SetExampleDetailData(Detail Detail)
71         {
72             int ret = repository.SetExampleDetailData(pclsCache, Detail.Plan, Detail.Value, Detail.Instruction);
73             return new ExceptionHandler().SetData(Request, ret);
74         }
75     }
76 }

Controller调用model来实现与method的连接：

 1 //Models/ExampleRepository.cs
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Net;
 6 using System.Net.Http;
 7 using System.Web.Http;
 8 using System.Web;
 9 using SpaceBed.CommonLibrary;
10 using SpaceBed.DataMethod;
11 using SpaceBed.DataModels;
12 
13 namespace SpaceBed.Models
14 {
15     public class ExampleRepository : IExampleRepository
16     {
17         public int SetExampleData(DataConnection pclsCache, string PlanNo, int StartDate, int Status, string DoctorId)
18         {
19             return new ExampleMethod().SetExampleData(pclsCache, PlanNo, StartDate, Status, DoctorId);
20         }
21 
22         public Example GetPatientPlan(DataConnection pclsCache, string strPlanNo)
23         {
24             return new ExampleMethod().GetPatientPlan(pclsCache, strPlanNo);
25         }
26 
27         public int ChangeStatus(DataConnection pclsCache, string strPlanNo, int strStatus)
28         {
29             return new ExampleMethod().ChangeStatus(pclsCache, strPlanNo, strStatus);
30         }
31         public List<Example> GetPlan(DataConnection pclsCache, int strStatus, int strStartDate)
32         {
33             List<Example> Items = new List<Example>();
34             Items = new ExampleMethod().GetPlan(pclsCache, strStatus, strStartDate);
35             return Items;
36         }
37 
38         public int SetExampleDetailData(DataConnection pclsCache, string Plan, string Value, string Instruction)
39         {
40             return new ExampleMethod().SetExampleDetailData(pclsCache, Plan, Value, Instruction);
41         }
42     }
43 }

而model与controller之间需要接口，命名为IExampleRepository，也存放在model文件夹中即可：

 1 //Models/IExampleRepository.cs
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Net;
 6 using System.Net.Http;
 7 using System.Web.Http;
 8 using System.Text;
 9 using System.Threading.Tasks;
10 using SpaceBed.CommonLibrary;
11 using SpaceBed.DataModels;
12 
13 namespace SpaceBed.Models
14 {
15     public interface IExampleRepository
16     {
17         int SetExampleData(DataConnection pclsCache, string PlanNo, int StartDate, int Status, string DoctorId);
18         Example GetPatientPlan(DataConnection pclsCache, string strPlanNo);
19         int ChangeStatus(DataConnection pclsCache, string strPlanNo, int strStatus);
20         List<Example> GetPlan(DataConnection pclsCache, int strStatus, int strStartDate);
21 
22         int SetExampleDetailData(DataConnection pclsCache, string Plan, string Value, string Instruction);
23 
24     }
25 }

Model可以整合method，在前端调用多个method时可以在此处理，但是不在本文讨论范围内。Method对应的就是Ensemble里每个class中的方法

  1 //DataMethod/ExampleMethod.cs
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Linq;
  5 using System.Net;
  6 using System.Net.Http;
  7 using System.Web.Http;
  8 using System.Web;
  9 using InterSystems.Data.CacheClient;
 10 using SpaceBed.DataModels;
 11 using SpaceBed.CommonLibrary;
 12 
 13 namespace SpaceBed.DataMethod
 14 {
 15     public class ExampleMethod
 16     {
 17         #region <EG.Example> gy 2016-10-13
 18         // EG.Example表 SetData
 19         public int SetExampleData(DataConnection pclsCache, string PlanNo, int StartDate, int Status, string DoctorId)
 20         {
 21             int ret = 2;
 22             try
 23             {
 24                 if (!pclsCache.Connect())
 25                 {
 26                     return ret;
 27                 }
 28                 ret = (int)EG.Example.SetData(pclsCache.CacheConnectionObject, PlanNo, StartDate, Status, DoctorId);
 29                 return ret;
 30             }
 31             catch (Exception ex)
 32             {
 33                 HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "ExampleMethod.SetExampleData", "数据库操作异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
 34                 return ret;
 35             }
 36             finally
 37             {
 38                 pclsCache.DisConnect();
 39             }
 40         }
 41 
 42         // EG.Example表 GetPatientPlan
 43         public Example GetPatientPlan(DataConnection pclsCache, string strPlanNo)
 44         {
 45             Example PatientPlan = new Example();
 46             try
 47             {
 48                 if (!pclsCache.Connect())
 49                 {
 50                     return null;
 51                 }
 52                 InterSystems.Data.CacheTypes.CacheSysList list = null;
 53                 list = EG.Example.GetPatientPlan(pclsCache.CacheConnectionObject, strPlanNo);
 54                 if (list != null)
 55                 {
 56                     PatientPlan.StartDate = Convert.ToInt32(list[0]);
 57                     PatientPlan.Status = Convert.ToInt32(list[1]);
 58                     PatientPlan.DoctorId = list[2];
 59                 }
 60                 return PatientPlan;
 61             }
 62             catch (Exception ex)
 63             {
 64                 HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "ExampleMethod.GetPatientPlan", "数据库操作异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
 65                 return null;
 66             }
 67             finally
 68             {
 69                 pclsCache.DisConnect();
 70             }
 71         }
 72 
 73         // EG.Example表 ChangeStatus
 74         public int ChangeStatus(DataConnection pclsCache, string strPlanNo, int strStatus)
 75         {
 76             int ret = 0;
 77             try
 78             {
 79                 if (!pclsCache.Connect())
 80                 {
 81                     return ret;
 82                 }
 83                 ret = (int)EG.Example.ChangeStatus(pclsCache.CacheConnectionObject, strPlanNo, strStatus);
 84                 return ret;
 85             }
 86             catch (Exception ex)
 87             {
 88                 HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "ExampleMethod.ChangeStatus", "数据库操作异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
 89                 return ret;
 90             }
 91             finally
 92             {
 93                 pclsCache.DisConnect();
 94             }
 95         }
 96 
 97         // EG.Example表 GetPlan
 98         public List<Example> GetPlan(DataConnection pclsCache, int strStatus, int strStartDate)
 99         {
100             List<Example> items = new List<Example>();
101             CacheCommand cmd = null;
102             CacheDataReader cdr = null;
103             try
104             {
105                 if (!pclsCache.Connect())
106                 {
107                     return null;
108                 }
109                 cmd = EG.Example.GetPlan(pclsCache.CacheConnectionObject);
110                 cmd.Parameters.Add("Status", CacheDbType.NVarChar).Value = strStatus;
111                 cmd.Parameters.Add("StartDate", CacheDbType.NVarChar).Value = strStartDate;
112 
113                 cdr = cmd.ExecuteReader();
114 
115                 while (cdr.Read())
116                 {
117                     Example item = new Example();
118                     item.PlanNo = cdr["PlanNo"].ToString();
119                     item.StartDate = (int)cdr["StartDate"];
120                     item.Status = (int)cdr["Status"];
121                     item.DoctorId = cdr["DoctorId"].ToString();
122                     items.Add(item);
123                 }
124                 return items;
125             }
126             catch (Exception ex)
127             {
128                 HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "ExampleMethod.GetPatientPlan", "数据库操作异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
129                 return null;
130             }
131             finally
132             {
133                 if ((cdr != null))
134                 {
135                     cdr.Close();
136                     cdr.Dispose(true);
137                     cdr = null;
138                 }
139                 if ((cmd != null))
140                 {
141                     cmd.Parameters.Clear();
142                     cmd.Dispose();
143                     cmd = null;
144                 }
145                 pclsCache.DisConnect();
146             }
147         }
148 
149         #endregion
150 
151 
152         #region <EG.Detail> gy 2016-10-17
153         //EG.Detail表 SetData
154         public int SetExampleDetailData(DataConnection pclsCache, string Plan, string Value, string Instruction)
155         {
156             int ret = 2;
157             try
158             {
159                 if (!pclsCache.Connect())
160                 {
161                     return ret;
162                 }
163                 ret = (int)EG.Detail.SetData(pclsCache.CacheConnectionObject, Plan, Value, Instruction);
164                 return ret;
165             }
166             catch (Exception ex)
167             {
168                 HygeiaComUtility.WriteClientLog(HygeiaEnum.LogType.ErrorLog, "ExampleMethod.SetExampleData", "数据库操作异常！ error information : " + ex.Message + Environment.NewLine + ex.StackTrace);
169                 return ret;
170             }
171             finally
172             {
173                 pclsCache.DisConnect();
174             }
175         }
176         #endregion
177     }
178 
179 }

为了更加清晰地在前端将数据进行结构化显示和获取，使用datamodel将列表整合成类：

 1 //DataModels/Example.cs
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Linq;
 5 using System.Net;
 6 using System.Net.Http;
 7 using System.Web.Http;
 8 using System.Web;
 9 
10 namespace SpaceBed.DataModels
11 {
12     public class Example
13     {
14         private string strPlanNo = string.Empty;
15         private int strStartDate = 0;
16         private int strStatus = 0;
17         private string strDoctorId = string.Empty;
18 
19 
20         public string PlanNo
21         {
22             set { strPlanNo = value; }
23             get { return strPlanNo; }
24         }
25 
26         public int StartDate
27         {
28             set { strStartDate = value; }
29             get { return strStartDate; }
30         }
31 
32         public int Status
33         {
34             set { strStatus = value; }
35             get { return strStatus; }
36         }
37 
38         public string DoctorId
39         {
40             set { strDoctorId = value; }
41             get { return strDoctorId; }
42         }
43 
44     }
45 
46     public class Detail
47     {
48         private string strPlan = string.Empty;
49         private string strValue = string.Empty;
50         private string strInstruction = string.Empty;
51 
52         public string Plan
53         {
54             set { strPlan = value; }
55             get { return strPlan; }
56         }
57         public string Value
58         {
59             set;
60             get;
61         }
62         public string Instruction
63         {
64             set;
65             get;
66         }
67     }
68 }

以上就是将Ensemble方法进行RESTful封装的全过程（数据库连接与网络配置等不在讨论范围内），封装结果大部分为API中的GET和POST方法

补充1：web.config中可以修改网络配置，包括连接的服务器、端口、命名空间、用户名密码等，新导入时要注意修改；在引用中要添加产生的.dll文件，因为要依靠.dll才能与数据库中的方法取得通信。

补充2：父子表中父表多主键时，在数据库中处理relationship时，需要将所有父表的主键属性拼接起来，拼接符号为”||”，在RESTful里可以处理，在前端也可以处理。在RESTful里处理时，从controller到method的声明都需要使用所有父表主键属性，而在method里添加一句话，将字符串拼接到一起，于此同时，调用数据库的那句函数需要使用拼接起来的那个relationship

补充3：在Ensemble里打开子表的数据条目时需要在前面拼接父表的主键

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/gyjerry/p/5984576.html