如何对SAP数据库表进行增删改查操作(2)?

本篇接着上一篇博文，继续讲解如何在 SAP 系统外部，方便地对 SAP 的数据库表进行增删改查操作。推荐的方式：

SAP 暴露 OData 服务供外部调用SAP 暴露 Restful Service 供外部调用

总的来说，OData 是比较新的 Restful Service 规范，在 SAP 端编写代码相对容易，但早期版本不支持 OData; Restful Service 基本上较早的版本也可以实现。关于 OData 对 Netweaver 版本要求，请参考我另外一篇博文：

SAPUI5 (34) - OData Model 连接后端 SAP 系统 (上)

对于异构系统的接口，我比较喜欢服务化这个表述，Restful Service 能够很好地体现服务化思想。站在第三方系统的角度，不管是 push 还是 pull 都能实现。

本文介绍基于 .net 平台的 WinForm 框架如何实现 SAP 表的维护界面。在前端技术日新月异的今天，WinForm 少有人用，但 WinForm 作为 Microsoft 早期的平台技术，技术成熟，使用简单，而且 Office 的 开发技术 VSTO ，允许使用 WinForm 框架，所以可以在 Office 中编写界面，这也是极为有用的一个应用场景。

RestSharp

我选择开源的 RestSharp 框架作为调用 Restful Service 的技术，RestSharp 使用起来灵活、强大。为了减少后续代码量，我对 HTTP 的 GET / POST / PUT / DELETE 请求进行封装，认证方式选择 Http Basic Authentication:

using RestSharp; using RestSharp.Authenticators; using System; namespace RestSharpCRUD { public class RestSharpHelper { private String username; private String password; public String BaseUrl { get; set; } private RestClient client; public RestSharpHelper(String baseUrl, String username, String password) { this.BaseUrl = baseUrl; this.username = username; this.password = password; // construct RestClient object client = new RestClient(this.BaseUrl) { Authenticator = new HttpBasicAuthenticator(username, password) }; } public IRestResponse Get(String resource) { var req = new RestRequest(resource, Method.GET); var resp = client.Execute(req); return resp; } public IRestResponse Post(String resource, String payload) { var req = new RestRequest(resource, Method.POST); req.RequestFormat = DataFormat.Json; req.AddJsonBody(payload); req.AddParameter("application/json", payload, ParameterType.RequestBody); var resp = client.Execute(req); return resp; } public IRestResponse Put(String resource, String payload) { var req = new RestRequest(resource, Method.PUT); req.RequestFormat = DataFormat.Json; req.AddJsonBody(payload); req.AddParameter("application/json", payload, ParameterType.RequestBody); var resp = client.Execute(req); return resp; } public IRestResponse Delete(String resource) { var req = new RestRequest(resource, Method.DELETE); var resp = client.Execute(req); return resp; } } }

Model 类

Model 类比较简单，代码如下：

using System; namespace RestSharpCRUD { public class EmpEntity { public EmpEntity() { MANDT = "001"; } public String MANDT { get; set; } public String EMPID { get; set; } public String EMPNAME { get; set; } public String EMPADDR { get; set; } } }

调用 Restful Service

封装了 Http 方法后，接下来编写一个类，实现通过调用 Restful Service 来对 SAP zemployee 表增删改查操作：

using Newtonsoft.Json; using System; using System.Collections.Generic; namespace RestSharpCRUD { public class EmpService { private String baseUrl = "http://sapecc6:8000"; private String username = "stone"; private String password = "123456"; private RestSharpHelper restSharpHelper; public EmpService() { restSharpHelper = new RestSharpHelper(baseUrl, username, password); } public IList<EmpEntity> ListAll() { IList<EmpEntity> employees = null; var resp = restSharpHelper.Get("/zrest/employees"); if (!resp.IsSuccessful) { throw new Exception(resp.ErrorMessage); } employees = JsonConvert.DeserializeObject<List<EmpEntity>>(resp.Content); return employees; } public bool Create(EmpEntity emp) { String payload = JsonConvert.SerializeObject(emp); // Call POST method var resp = restSharpHelper.Post("/zrest/employees/create", payload); if (!resp.IsSuccessful) { throw new Exception(resp.Content); } return true; } public bool Update(EmpEntity emp) { String payload = JsonConvert.SerializeObject(emp); // Call POST method var resource = String.Format("/zrest/employees/{0}", emp.EMPID); var resp = restSharpHelper.Put(resource, payload); if (!resp.IsSuccessful) { throw new Exception(resp.Content); } return true; } public bool Delete(String empId) { bool rv = false; var resource = String.Format("/zrest/employees/{0}", empId); var resp = restSharpHelper.Delete(resource); if (resp.IsSuccessful) rv = true; return rv; } } }

界面实现

我通过两个 Form 的配合来实现增删改查操作。第一个 Form 罗列所有的 employee，允许在 Form 中进行导航，双击跳转到维护的 Form，可以在这个 Form 中进行删除操作。设计时的界面如下：

代码如下：

using System; using System.Collections.Generic; using System.ComponentModel; using System.Windows.Forms; namespace RestSharpCRUD { public partial class EmpListForm : Form { public EmpListForm() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { // 加载数据 IList<EmpEntity> employees = null; try { employees = LoadEmployees(); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } // 数据绑定到控件 // List绑定到DataGridView不能进行增删改查，所以将List转换为BindingList // DataGridView.DataSource = new BindingList<T>(List<T>); bindingSource1.DataSource = new BindingList<EmpEntity>(employees); dataGridView1.DataSource = bindingSource1; bindingNavigator1.BindingSource = bindingSource1; } private void DataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e) { // 双击打开EmpSingleForm var empSingleForm = new EmpSingleForm(bindingSource1); empSingleForm.ShowDialog(); } private void BindingNavigatorAddNewItem_Click(object sender, EventArgs e) { this.bindingSource1.AddNew(); var empSingleForm = new EmpSingleForm(bindingSource1, true); empSingleForm.ShowDialog(); } private void BindingNavigatorDeleteItem_Click(object sender, EventArgs e) { DoDelete(); } private IList<EmpEntity> LoadEmployees() { var empService = new EmpService(); IList<EmpEntity> employees = empService.ListAll(); return employees; } private void DoDelete() { if (bindingSource1.Current == null) return; if (MessageBox.Show("确定删除这个员工吗？", "删除", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { var empId = (bindingSource1.Current as EmpEntity).EMPID; var empService = new EmpService(); bool rv = empService.Delete(empId); if (rv) { bindingSource1.RemoveCurrent(); // 保持界面同步 } } } } }

UI 层与 Service 层交互，使用的是 IList<T> 格式，使用这种格式，在界面中要将 IList<T>，转换为 BindingList<T>，否则不支持 CRUD 操作。

在 EmpSingleForm 表单中，对单笔记录进行更新和保存，表单设计时界面如下：

两个表单都基于 BindingSource 控件对数据进行绑定，并且通过 BindingSource 控件在 Form 中交换数据，从而减少代码量，为此，在 EmpSingleForm 中，特意实现另外两个构造函数：

#region constructors public EmpSingleForm() { InitializeComponent(); } public EmpSingleForm(BindingSource bs) : this() { empBs = bs; //设置数据绑定 SetBinding(); } public EmpSingleForm(BindingSource bs, bool addNew) : this(bs) { isAddNewMode = addNew; } #endregion

SetBinding() 方法负责数据的绑定：

private void SetBinding() { txtEmpID.DataBindings.Add("Text", empBs, "EMPID", true); txtName.DataBindings.Add("Text", empBs, "EMPNAME", true); txtAddress.DataBindings.Add("Text", empBs, "EMPADDR", true); }

将更新的数据保存到 SAP 后端，无非就是调用 EmpService 类的方法：

private void BtnSave_Click(object sender, System.EventArgs e) { bool rv = false; // return value var emp = new EmpEntity { MANDT = "001", EMPID = txtEmpID.Text.Trim(), EMPNAME = txtName.Text.Trim(), EMPADDR = txtAddress.Text.Trim() }; var empService = new EmpService(); if (isAddNewMode) { try { rv = empService.Create(emp); } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { rv = empService.Update(emp); } if (rv) { empBs.EndEdit(); this.Close(); } }

以下是 EmpSingleForm 的完整代码：

using System; using System.Windows.Forms; namespace RestSharpCRUD { public partial class EmpSingleForm : Form { private bool isAddNewMode = false; #region constructors public EmpSingleForm() { InitializeComponent(); } public EmpSingleForm(BindingSource bs) : this() { empBs = bs; //设置数据绑定 SetBinding(); } public EmpSingleForm(BindingSource bs, bool addNew) : this(bs) { isAddNewMode = addNew; } #endregion private void EmpSingleForm_FormClosed(object sender, FormClosedEventArgs e) { this.empBs.CancelEdit(); } private void BtnSave_Click(object sender, System.EventArgs e) { bool rv = false; // return value var emp = new EmpEntity { MANDT = "001", EMPID = txtEmpID.Text.Trim(), EMPNAME = txtName.Text.Trim(), EMPADDR = txtAddress.Text.Trim() }; var empService = new EmpService(); if (isAddNewMode) { try { rv = empService.Create(emp); } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { rv = empService.Update(emp); } if (rv) { empBs.EndEdit(); this.Close(); } } private void EmpSingleForm_Load(object sender, EventArgs e) { txtEmpID.Enabled = (isAddNewMode == true); } private void SetBinding() { txtEmpID.DataBindings.Add("Text", empBs, "EMPID", true); txtName.DataBindings.Add("Text", empBs, "EMPNAME", true); txtAddress.DataBindings.Add("Text", empBs, "EMPADDR", true); } } }

程序源码

该工程完整的代码在 github。