一些小的C/S项目(winform、WPF等),因需要访问操作数据库,但又不能把DB连接配置在客户端上,原因有很多,可能是DB连接无法直接访问,或客户端不想安装各种DB访问组件,或DB连接不想暴露在客户端(即使加密连接字符串仍有可能被破解的情况),总之都是出于安全考虑,同时因项目小,也无需采用分布式架构来将业务操作封装到服务端,但又想保证客户端业务的正常处理,这时我们就可以利用ASP.NET WEB API框架开发一个简单的提供对数据库的直接操作(CRUD)框架,简称为:分布式数据访问中间层。
实现方案很简单,就是利用ASP.NET WEB API框架编写于一个DataController,然后在DataController分别实现CRUD相关的公开ACTION方法即可,具体实现代码如下:(因为逻辑简单,一看就懂,故下面不再详细说明逻辑,文末会有一些总结)
ASP.NET WEB API服务端相关核心代码:
1.DataController代码:
[SqlInjectionFilter] [Authorize] public class DataController : ApiController { [AllowAnonymous] [HttpPost] public ApiResultInfo Login([FromBody]string[] loginInfo) { ApiResultInfo loginResult = null; try { if (loginInfo == null || loginInfo.Length != 4) { throw new Exception("登录信息不全。"); } using (var da = BaseUtil.CreateDataAccess()) { if (用户名及密码判断逻辑) { throw new Exception("登录名或密码错误。"); } else { string token = Guid.NewGuid().ToString("N"); HttpRuntime.Cache.Insert(Constants.CacheKey_SessionTokenPrefix + token, loginInfo[0], null, Cache.NoAbsoluteExpiration, TimeSpan.FromHours(1)); //登录成功后需要处理的逻辑 loginResult = ApiResultInfo.BuildOKResult(token); } } } catch (Exception ex) { LogUitl.Error(ex, "Api.Data.Login", BaseUtil.SerializeToJson(loginInfo)); loginResult = ApiResultInfo.BuildErrResult("LoginErr", ex.Message); } return loginResult; } [HttpPost] public ApiResultInfo LogOut([FromBody] string token) { try { if (!string.IsNullOrEmpty(token)) { if (HttpRuntime.Cache[Constants.CacheKey_SessionTokenPrefix + token] != null) { HttpRuntime.Cache.Remove(token); } using (var da = BaseUtil.CreateDataAccess()) { //登出后需要处理的逻辑 } } } catch { } return ApiResultInfo.BuildOKResult(); } [HttpPost] public ApiResultInfo GetValue([FromBody]SqlCmdInfo sqlCmd) { using (var da = BaseUtil.CreateDataAccess()) { var result = da.ExecuteScalar(sqlCmd.SqlCmdText, sqlCmd.GetCommandType(), sqlCmd.Parameters.TryToArray()); return ApiResultInfo.BuildOKResult(result); } } [Compression] [HttpPost] public ApiResultInfo GetDataSet([FromBody]SqlCmdInfo sqlCmd) { using (var da = BaseUtil.CreateDataAccess()) { var ds = da.ExecuteDataSet(sqlCmd.SqlCmdText, sqlCmd.GetCommandType(), sqlCmd.Parameters.TryToArray()); return ApiResultInfo.BuildOKResult(ds); } } [Compression] [HttpPost] public ApiResultInfo GetDataTable([FromBody]SqlCmdInfo sqlCmd) { using (var da = BaseUtil.CreateDataAccess()) { var table = da.ExecuteDataTable(sqlCmd.SqlCmdText, sqlCmd.GetCommandType(), sqlCmd.Parameters.TryToArray()); return ApiResultInfo.BuildOKResult(table); } } [HttpPost] public ApiResultInfo ExecuteCommand([FromBody]SqlCmdInfo sqlCmd) { using (var da = BaseUtil.CreateDataAccess()) { int result = da.ExecuteCommand(sqlCmd.SqlCmdText, sqlCmd.GetCommandType(), sqlCmd.Parameters.TryToArray()); return ApiResultInfo.BuildOKResult(result); } } [HttpPost] public ApiResultInfo BatchExecuteCommand([FromBody] IEnumerable sqlCmds) { using (var da = BaseUtil.CreateDataAccess()) { int execCount = 0; da.UseTransaction(); foreach (var sqlCmd in sqlCmds) { execCount += da.ExecuteCommand(sqlCmd.SqlCmdText, sqlCmd.GetCommandType(), sqlCmd.Parameters.TryToArray()); } da.Commit(); return new ApiResultInfo(execCount > 0); } } [HttpPost] public async Task ExecuteCommandAsync([FromBody]SqlCmdInfo sqlCmd) { return await Task.Factory.StartNew((arg) => { var sqlCmdObj = arg as SqlCmdInfo; string connName = BaseUtil.GetDbConnectionName(sqlCmdObj.DbType); using (var da = BaseUtil.CreateDataAccess(connName)) { try { int result = da.ExecuteCommand(sqlCmdObj.SqlCmdText, sqlCmdObj.GetCommandType(), sqlCmdObj.Parameters.TryToArray()); return ApiResultInfo.BuildOKResult(result); } catch (Exception ex) { LogUitl.Error(ex, "Api.Data.ExecuteCommandAsync", BaseUtil.SerializeToJson(sqlCmdObj)); return ApiResultInfo.BuildErrResult("ExecuteCommandAsyncErr", ex.Message, new Dictionary { { "StackTrace", ex.StackTrace } }); } } }, sqlCmd); } [HttpPost] public IHttpActionResult SaveLog([FromBody]string[] logInfo) { if (logInfo == null || logInfo.Length < 3) { return Ok(); } string[] saveLogInfo = new string[7]; for (int i = 1; i < logInfo.Length; i++) { if (saveLogInfo.Length > i + 1) { saveLogInfo[i] = logInfo[i]; } } switch (saveLogInfo[0].ToUpperInvariant()) { case "ERR": { LogUitl.Error(saveLogInfo[1], saveLogInfo[2], saveLogInfo[3], saveLogInfo[4], saveLogInfo[5], saveLogInfo[6]); break; } case "WARN": { LogUitl.Warn(saveLogInfo[1], saveLogInfo[2], saveLogInfo[3], saveLogInfo[4], saveLogInfo[5], saveLogInfo[6]); break; } case "INFO": { LogUitl.Info(saveLogInfo[1], saveLogInfo[2], saveLogInfo[3], saveLogInfo[4], saveLogInfo[5], saveLogInfo[6]); break; } } return Ok(); } }
//BaseUtil: public static DataAccess CreateDataAccess(string connName = "DefaultConnectionString") { return new DataAccess(connName, EncryptUtility.Decrypt); } public static string SerializeToJson(object obj) { return JsonConvert.SerializeObject(obj); } public static JObject DeserializeObject(string json) { return JObject.Parse(json); } public static T DeserializeObject(string json) { return JsonConvert.DeserializeObject(json); }//===================================== /// /// 类型扩展方法集合 /// public static class TypeExtension { /// /// 转换为不为空的字符串(即:若为空,则返回为空字符串,而不是Null) /// /// /// public static string ToNotNullString(this object obj) { if (obj == null || obj == DBNull.Value) { return string.Empty; } return obj.ToString(); } /// /// 判断列表中是否存在项 /// /// /// public static bool HasItem(this IEnumerable
WebApiConfig增加上述定义的一些过滤器、处理管道,以便实现拦截处理:
public static class WebApiConfig { public static void Register(HttpConfiguration config) { // Web API 配置和服务 // Web API 路由 config.MapHttpAttributeRoutes(); config.Routes.MapHttpRoute( name: "DefaultApi", routeTemplate: "api/{controller}/{action}/{id}", defaults: new { id = RouteParameter.Optional } ); config.Filters.Add(new HandleExceptionFilterAttribute());//添加统一处理异常过滤器 config.MessageHandlers.Add(new RequestAuthenticationHandler());//添加统一TOKEN身份证码 GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new DefaultContractResolver { IgnoreSerializableAttribute = true }; } }
客户端调用上述API相关核心代码:
1.DataService(客户端访问API通用类,通过该类公共静态方法可以进行数据库的CRUD)
2.WebApiUtil(WEB API请求工具类)
/// /// WebApi实用工具类 /// Author:Zuowenjun /// Date:2017/11/3 /// public static class WebApiUtil { private const string rsaPublicKey = "公钥字符串"; static WebApiUtil() { System.Net.ServicePointManager.DefaultConnectionLimit = 512; } /// /// 获取API结果 /// /// /// /// /// /// /// public static ApiResultInfo GetResultFromWebApi(Dictionary requestHeaders, object requestMsg, string apiUrl, string requestMethod = "POST") { string retString = HttpRequestToString(requestHeaders, requestMsg, apiUrl, requestMethod); return JsonConvert.DeserializeObject>(retString); } /// /// 发送Http请求,模拟访问指定的Url,返回响应内容转换成JSON对象 /// /// /// /// /// /// public static JObject HttpRequestToJson(Dictionary requestHeaders, object requestMsg, string apiUrl, string requestMethod = "POST") { string retString = HttpRequestToString(requestHeaders, requestMsg, apiUrl, requestMethod); return JObject.Parse(retString); } /// /// 发送Http请求,模拟访问指定的Url,返回响应内容文本 /// /// 请求头 /// 请求体(若为GetMethod时,则该值应为空) /// 要访问的Url /// 请求方式 /// 是否对请求体内容进行公钥加密 /// 响应内容(响应头、响应体) public static string HttpRequestToString(Dictionary requestHeaders, object requestMsg, string apiUrl, string requestMethod = "POST", bool isEncryptBody = true) { HttpWebRequest request = (HttpWebRequest)WebRequest.Create(apiUrl); request.Method = requestMethod; request.KeepAlive = false; request.Proxy = null; request.ServicePoint.UseNagleAlgorithm = false; request.AllowWriteStreamBuffering = false; request.ContentType = "application/json"; if (requestHeaders != null) { foreach (var item in requestHeaders) { request.Headers.Add(item.Key, item.Value); } } request.Headers.Set("Pragma", "no-cache"); if (requestMsg != null) { string dataStr = JsonConvert.SerializeObject(requestMsg); if (isEncryptBody) { request.Headers.Add("Encryption", "1"); dataStr = RSAEncrypt(rsaPublicKey, dataStr);//加密请求内容 } byte[] data = Encoding.UTF8.GetBytes(dataStr); request.ContentLength = data.Length; using (Stream myRequestStream = request.GetRequestStream()) { myRequestStream.Write(data, 0, data.Length); myRequestStream.Close(); } } string retString = null; using (HttpWebResponse response = (HttpWebResponse)request.GetResponse()) { retString = GetResponseBody(response); } request = null; return retString; } private static string GetResponseBody(HttpWebResponse response) { string responseBody = string.Empty; if (response.ContentEncoding.ToLower().Contains("gzip")) { using (GZipStream stream = new GZipStream(response.GetResponseStream(), CompressionMode.Decompress)) { using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) { responseBody = reader.ReadToEnd(); } } } else if (response.ContentEncoding.ToLower().Contains("deflate")) { using (DeflateStream stream = new DeflateStream( response.GetResponseStream(), CompressionMode.Decompress)) { using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) { responseBody = reader.ReadToEnd(); } } } else { using (Stream stream = response.GetResponseStream()) { using (StreamReader reader = new StreamReader(stream, Encoding.UTF8)) { responseBody = reader.ReadToEnd(); } } } return responseBody; } public static string RSAEncrypt(string publicKey, string rawInput) { if (string.IsNullOrEmpty(rawInput)) { return string.Empty; } if (string.IsNullOrWhiteSpace(publicKey)) { throw new ArgumentException("Invalid Public Key"); } using (var rsaProvider = new RSACryptoServiceProvider()) { var inputBytes = Encoding.UTF8.GetBytes(rawInput);//有含义的字符串转化为字节流 rsaProvider.FromXmlString(publicKey);//载入公钥 int bufferSize = (rsaProvider.KeySize / 8) - 11;//单块最大长度 var buffer = new byte[bufferSize]; using (MemoryStream inputStream = new MemoryStream(inputBytes), outputStream = new MemoryStream()) { while (true) { //分段加密 int readSize = inputStream.Read(buffer, 0, bufferSize); if (readSize <= 0) { break; } var temp = new byte[readSize]; Array.Copy(buffer, 0, temp, 0, readSize); var encryptedBytes = rsaProvider.Encrypt(temp, false); outputStream.Write(encryptedBytes, 0, encryptedBytes.Length); } return Convert.ToBase64String(outputStream.ToArray());//转化为字节流方便传输 } } } }