文章出處
文章列表
一些小的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<string>(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<SqlCmdInfo> 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<ApiResultInfo> 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<string, object> { { "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(); } }
2.SqlInjectionFilterAttribute (防止SQL注入、危險關鍵字攻擊過濾器)
[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, Inherited = true, AllowMultiple = false)] public class SqlInjectionFilterAttribute : ActionFilterAttribute { public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext) { if (actionContext.ActionArguments.ContainsKey("sqlCmd")) { var sqlCmd = actionContext.ActionArguments["sqlCmd"] as SqlCmdInfo; if (BaseUtil.IsIncludeDangerSql(sqlCmd.SqlCmdText)) { throw new Exception("存在SQL注入風險，禁止操作！"); } } base.OnActionExecuting(actionContext); } }
IsIncludeDangerSql：判斷是否包含危險關鍵字
/// <summary> /// 判斷是否包含危險的SQL關鍵詞 /// </summary> /// <param name="sqlCmdText"></param> /// <returns>包含返回true，否則false</returns> public static bool IsIncludeDangerSql(string sqlCmdText) { if (string.IsNullOrWhiteSpace(sqlCmdText)) return false; sqlCmdText = sqlCmdText.Replace("[", " ").Replace("]", " "); //string dangerSqlObjs = @"sys\.columns|sys\.tables|sys\.views|sys\.objects|sys\.procedures|sys\.indexes|INFORMATION_SCHEMA\.TABLES|INFORMATION_SCHEMA\.VIEWS|INFORMATION_SCHEMA\.COLUMNS|GRANT|DENY|SP_HELP|SP_HELPTEXT"; //dangerSqlObjs += @"|object_id|syscolumns|sysobjects|sysindexes|drop\s+\w+|alter\s+\w+|create\s+\w+"; string dangerSqlObjs = @"sys\.\w+|INFORMATION_SCHEMA\.\w+|GRANT|DENY|SP_HELP|SP_HELPTEXT|sp_executesql"; dangerSqlObjs += @"|object_id|syscolumns|sysobjects|sysindexes|exec\s+\(.+\)|(create|drop|alter)\s+(database|table|index|procedure|view|trigger)\s+\w+(?!#)"; string patternStr = string.Format(@"(^|\s|,|\.)({0})(\s|,|\(|;|$)", dangerSqlObjs); bool mathed = Regex.IsMatch(sqlCmdText, patternStr, RegexOptions.IgnoreCase); if (mathed) { //TODO:記錄到危險請求表中，以便后續追查 LogUitl.Warn("檢測到包含危險的SQL關鍵詞語句：" + sqlCmdText, "IsIncludeDangerSql"); } return mathed; }
3.SqlCmdInfo （ACTION參數對象，SQL命令信息類）
[Serializable] public class SqlCmdInfo { public string SqlCmdText { get; set; } public ArrayList Parameters { get; set; } public bool IsSPCmdType { get; set; } public int DbType { get; set; } public CommandType GetCommandType() { return IsSPCmdType ? CommandType.StoredProcedure : CommandType.Text; } }
4.CompressionAttribute（壓縮返回內容過濾器，當返回的是大量數據時，可以標記該過濾器，以便提高響應速度）
/// <summary> /// 壓縮返回信息 /// </summary> [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, Inherited = true, AllowMultiple = false)] public class CompressionAttribute : ActionFilterAttribute { public override void OnActionExecuted(HttpActionExecutedContext actionExecutedContext) { var content = actionExecutedContext.Response.Content; #region 根據請求是否壓縮，暫時不用 ////var acceptEncoding = actionExecutedContext.Request.Headers.AcceptEncoding. //// Where(x => x.Value == "gzip" || x.Value == "deflate").ToList(); ////if (acceptEncoding.HasItem() && content != null && actionExecutedContext.Request.Method != HttpMethod.Options) ////{ //// var first = acceptEncoding.FirstOrDefault(); //// if (first != null) //// { //// var bytes = content.ReadAsByteArrayAsync().Result; //// switch (first.Value) //// { //// case "gzip": //// actionExecutedContext.Response.Content = new ByteArrayContent(CompressionHelper.GZipBytes(bytes)); //// actionExecutedContext.Response.Content.Headers.Add("Content-Encoding", "gzip"); //// break; //// case "deflate": //// actionExecutedContext.Response.Content = new ByteArrayContent(CompressionHelper.DeflateBytes(bytes)); //// actionExecutedContext.Response.Content.Headers.Add("Content-encoding", "deflate"); //// break; //// } //// } ////} #endregion //只要使用了CompressionAttribute，則默認使用GZIP壓縮 var bytes = content.ReadAsByteArrayAsync().Result; actionExecutedContext.Response.Content = new ByteArrayContent(CompressionHelper.GZipBytes(bytes)); actionExecutedContext.Response.Content.Headers.Add("Content-Encoding", "gzip"); base.OnActionExecuted(actionExecutedContext); } } /// <summary> /// 壓縮幫助類 /// </summary> internal static class CompressionHelper { public static byte[] DeflateBytes(byte[] bytes) { if (bytes == null || bytes.Length == 0) { return null; } using (var output = new MemoryStream()) { using (var compressor = new DeflateStream(output, CompressionMode.Compress, false)) { compressor.Write(bytes, 0, bytes.Length); } return output.ToArray(); } } public static byte[] GZipBytes(byte[] bytes) { if (bytes == null || bytes.Length == 0) { return null; } using (var output = new MemoryStream()) { using (var compressor = new GZipStream(output, CompressionMode.Compress, false)) { compressor.Write(bytes, 0, bytes.Length); } return output.ToArray(); } } }
5.RequestAuthenticationHandler （驗證請求合法性處理管道（包含請求內容解密），即：未正確登錄則不能調API操作數據庫）
public class RequestAuthenticationHandler : DelegatingHandler { private const string rsaPrivateKey = "私鑰字符串"; protected async override System.Threading.Tasks.Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, System.Threading.CancellationToken cancellationToken) { try { //驗證TOKEN HttpRequestHeaders headers = request.Headers; IEnumerable<string> tokenHeaders = null; if (headers.TryGetValues("AccessToken", out tokenHeaders) && tokenHeaders.Any()) { string loginID = TokenVerification(tokenHeaders.ElementAt(0)); if (!string.IsNullOrEmpty(loginID)) { var principal = new GenericPrincipal(new GenericIdentity(loginID, "token"), null); Thread.CurrentPrincipal = principal; if (HttpContext.Current != null) { HttpContext.Current.User = principal; } } } IEnumerable<string> encryptHeaders=null; if (headers.TryGetValues("Encryption", out encryptHeaders) && encryptHeaders.Any()) { if (encryptHeaders.ElementAt(0) == "1") { //私鑰解密請求體內容 var originContent = request.Content; string requestData = await request.Content.ReadAsStringAsync(); string deContentStr = EncryptUtility.RSADecrypt(rsaPrivateKey, requestData); request.Content = new StringContent(deContentStr); request.Content.Headers.Clear(); foreach (var header in originContent.Headers) { request.Content.Headers.Add(header.Key, header.Value); } } } } catch (Exception ex) { LogUitl.Error(ex, "Api.RequestAuthenticationHandler"); } HttpResponseMessage response = await base.SendAsync(request, cancellationToken); return response; } private string TokenVerification(string token) { if (string.IsNullOrEmpty(token)) { return null; } string loginID = null; if (HttpRuntime.Cache[Constants.CacheKey_SessionTokenPrefix + token] == null) //如果過期，則嘗試從DB中恢復授權狀態 { using (var da = BaseUtil.CreateDataAccess()) { //loginID = 根據Token獲取登錄用戶ID邏輯 if (!string.IsNullOrEmpty(loginID)) { HttpRuntime.Cache.Insert(Constants.CacheKey_SessionTokenPrefix + token, loginID, null, Cache.NoAbsoluteExpiration, TimeSpan.FromHours(1)); } } } else { loginID = HttpRuntime.Cache[Constants.CacheKey_SessionTokenPrefix + token].ToNotNullString(); } return loginID; } }
6.HandleExceptionFilterAttribute（全局異常處理過濾器，只要某個ACTION發生異常就會報被該過濾器捕獲并處理）
/// <summary> /// 統一全局異常過濾處理 /// </summary> public class HandleExceptionFilterAttribute : ExceptionFilterAttribute { public override void OnException(HttpActionExecutedContext actionExecutedContext) { string ctrllerName = actionExecutedContext.ActionContext.ControllerContext.ControllerDescriptor.ControllerName; string actionName = actionExecutedContext.ActionContext.ActionDescriptor.ActionName; string sqlCmd = null; if (actionExecutedContext.ActionContext.ActionArguments.ContainsKey("sqlCmd")) { sqlCmd = BaseUtil.SerializeToJson(actionExecutedContext.ActionContext.ActionArguments["sqlCmd"] as SqlCmdInfo); } //記錄到日志表中 LogUitl.Error(actionExecutedContext.Exception.Message, "Api.HandleExceptionFilterAttribute", string.Format("SqlCmdInfo:{0};StackTrace:{1}", sqlCmd, actionExecutedContext.Exception.StackTrace)); var errResult = new ApiResultInfo(false, sqlCmd, actionName + "Err", actionExecutedContext.Exception.Message); errResult.ExtendedData["StackTrace"] = actionExecutedContext.Exception.StackTrace; actionExecutedContext.Response = actionExecutedContext.ActionContext.Request.CreateResponse(HttpStatusCode.OK, errResult, "application/json"); } }
7.ApiResultInfo（API返回結果實體類）
[Serializable] public class ApiResultInfo { public bool Stauts { get; set; } public object Data { get; set; } public string ErrCode { get; set; } public string ErrMsg { get; set; } public Dictionary<string, object> ExtendedData { get; set; } public ApiResultInfo() { this.ExtendedData = new Dictionary<string, object>(); } public ApiResultInfo(bool status, object data = null, string errCode = null, string errMsg = null, Dictionary<string, object> extData = null) { this.Stauts = status; this.Data = data; this.ErrCode = errCode; this.ErrMsg = errMsg; this.ExtendedData = extData; if (this.ExtendedData == null) { this.ExtendedData = new Dictionary<string, object>(); } } /// <summary> /// 構建成功結果對象 /// </summary> /// <param name="data"></param> /// <param name="extData"></param> /// <returns></returns> public static ApiResultInfo BuildOKResult(object data = null, Dictionary<string, object> extData = null) { return new ApiResultInfo(true, data, extData: extData); } /// <summary> /// 構建錯誤結果對象 /// </summary> /// <param name="errCode"></param> /// <param name="errMsg"></param> /// <param name="extData"></param> /// <returns></returns> public static ApiResultInfo BuildErrResult(string errCode = null, string errMsg = null, Dictionary<string, object> extData = null) { return new ApiResultInfo(false, errCode: errCode, errMsg: errMsg, extData: extData); } }
8.非對稱加解密算法（允許客戶端請求時進行公鑰加密請求內容，然后服務端API中通過RequestAuthenticationHandler自定義驗證管道解密請求內容）
/// <summary> /// 生成公鑰及私鑰對 /// </summary> /// <param name="publickey"></param> /// <param name="privatekey"></param> public static void GeneratePublicAndPrivateKey(out string publickey, out string privatekey) { RSACryptoServiceProvider crypt = new RSACryptoServiceProvider(); publickey = crypt.ToXmlString(false);//公鑰 privatekey = crypt.ToXmlString(true);//私鑰 } /// <summary> /// 分段使用公鑰加密 /// </summary> /// <param name="publicKey"></param> /// <param name="rawInput"></param> /// <returns></returns> 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());//轉化為字節流方便傳輸 } } } /// <summary> /// 分段使用私鑰解密 /// </summary> /// <param name="privateKey"></param> /// <param name="encryptedInput"></param> /// <returns></returns> public static string RSADecrypt(string privateKey, string encryptedInput) { if (string.IsNullOrEmpty(encryptedInput)) { return string.Empty; } if (string.IsNullOrWhiteSpace(privateKey)) { throw new ArgumentException("Invalid Private Key"); } using (var rsaProvider = new RSACryptoServiceProvider()) { var inputBytes = Convert.FromBase64String(encryptedInput); rsaProvider.FromXmlString(privateKey); int bufferSize = rsaProvider.KeySize / 8; 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 rawBytes = rsaProvider.Decrypt(temp, false); outputStream.Write(rawBytes, 0, rawBytes.Length); } return Encoding.UTF8.GetString(outputStream.ToArray()); } } }
9.LogUitl（基于NLOG.MONGO組件簡單封裝實現MONGODB日志功能）--后期有機會再單獨講MONGODB的相關知識
public static class LogUitl { private static NLog.Logger _Logger = null; private const string cacheKey_NLogConfigFlag = "NLogConfigFlag"; private static Logger GetLogger() { if (_Logger == null || HttpRuntime.Cache[cacheKey_NLogConfigFlag] == null) { LoggingConfiguration config = new LoggingConfiguration(); string connSetStr = ConfigUtility.GetAppSettingValue("MongoDbConnectionSet"); MongoTarget mongoTarget = new MongoTarget(); mongoTarget.ConnectionString = EncryptUtility.Decrypt(connSetStr); mongoTarget.DatabaseName = "KYELog"; mongoTarget.CollectionName = "KYCallCenterLog"; mongoTarget.IncludeDefaults = false; AppendLogMongoFields(mongoTarget.Fields); LoggingRule rule1 = new LoggingRule("*", LogLevel.Debug, mongoTarget); config.LoggingRules.Add(rule1); LogManager.Configuration = config; _Logger = LogManager.GetCurrentClassLogger(); HttpRuntime.Cache.Insert(cacheKey_NLogConfigFlag, "Nlog", new System.Web.Caching.CacheDependency(HttpContext.Current.Server.MapPath("~/Web.config"))); } return _Logger; } private static void AppendLogMongoFields(IList<MongoField> mongoFields) { mongoFields.Clear(); Type logPropertiesType = typeof(SysLogInfo.LogProperties); foreach (var pro in typeof(SysLogInfo).GetProperties(BindingFlags.Public | BindingFlags.Instance)) { if (pro.PropertyType == logPropertiesType) continue; string layoutStr = string.Empty; //"${event-context:item=" + pro.Name + "}"; if (pro.Name.Equals("ThreadID") || pro.Name.Equals("Level") || pro.Name.Equals("MachineName")) { layoutStr = "${" + pro.Name.ToLower() + "}"; } else if (pro.Name.Equals("LogDT")) { layoutStr = "${date:format=yyyy-MM-dd HH\\:mm\\:ss}"; } else if (pro.Name.Equals("Msg")) { layoutStr = "${message}"; } if (!string.IsNullOrEmpty(layoutStr)) { mongoFields.Add(new MongoField(pro.Name, layoutStr, pro.PropertyType.Name)); } } } private static LogEventInfo BuildLogEventInfo(LogLevel level, string msg, string source, string detailTrace = null, string other1 = null, string other2 = null, string other3 = null) { var eventInfo = new LogEventInfo(); eventInfo.Level = level; eventInfo.Message = msg; eventInfo.Properties["DetailTrace"] = detailTrace ?? string.Empty; eventInfo.Properties["Source"] = source ?? string.Empty; eventInfo.Properties["Other1"] = other1 ?? string.Empty; eventInfo.Properties["Other2"] = other2 ?? string.Empty; eventInfo.Properties["Other3"] = other3 ?? string.Empty; string uid = string.Empty; if (HttpContext.Current.User != null) { uid = HttpContext.Current.User.Identity.Name; } eventInfo.Properties["UserID"] = uid; return eventInfo; } public static void Info(string msg, string source, string detailTrace = null, string other1 = null, string other2 = null, string other3 = null) { try { var eventInfo = BuildLogEventInfo(LogLevel.Info, msg, source, detailTrace, other1, other2, other3); var logger = GetLogger(); logger.Log(eventInfo); } catch { } } public static void Warn(string msg, string source, string detailTrace = null, string other1 = null, string other2 = null, string other3 = null) { try { var eventInfo = BuildLogEventInfo(LogLevel.Warn, msg, source, detailTrace, other1, other2, other3); var logger = GetLogger(); logger.Log(eventInfo); } catch { } } public static void Error(string msg, string source, string detailTrace = null, string other1 = null, string other2 = null, string other3 = null) { try { var eventInfo = BuildLogEventInfo(LogLevel.Error, msg, source, detailTrace, other1, other2, other3); var logger = GetLogger(); logger.Log(eventInfo); } catch { } } public static void Error(Exception ex, string source, string other1 = null, string other2 = null, string other3 = null) { try { var eventInfo = BuildLogEventInfo(LogLevel.Error, ex.Message, source, ex.StackTrace, other1, other2, other3); var logger = GetLogger(); logger.Log(eventInfo); } catch { } } } public class SysLogInfo { public DateTime LogDT { get; set; } public int ThreadID { get; set; } public string Level { get; set; } public string Msg { get; set; } public string MachineName { get; set; } public LogProperties Properties { get; set; } public class LogProperties { public string Source { get; set; } public string DetailTrace { get; set; } public string UserID { get; set; } public string Other1 { get; set; } public string Other2 { get; set; } public string Other3 { get; set; } } }
10.其它一些用到的公共實用方法
//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<T>(string json) { return JsonConvert.DeserializeObject<T>(json); } //===================================== /// <summary> /// 類型擴展方法集合 /// </summary> public static class TypeExtension { /// <summary> /// 轉換為不為空的字符串（即：若為空，則返回為空字符串，而不是Null） /// </summary> /// <param name="obj"></param> /// <returns></returns> public static string ToNotNullString(this object obj) { if (obj == null || obj == DBNull.Value) { return string.Empty; } return obj.ToString(); } /// <summary> /// 判斷列表中是否存在項 /// </summary> /// <param name="list"></param> /// <returns></returns> public static bool HasItem(this IEnumerable<object> list) { if (list != null && list.Any()) { return true; } return false; } /// <summary> /// 從字答串左邊起取出指定長度的字符串 /// </summary> /// <param name="str"></param> /// <param name="length"></param> /// <returns></returns> public static string Left(this string str, int length) { if (string.IsNullOrEmpty(str)) { return string.Empty; } return str.Substring(0, length); } /// <summary> /// 從字答串右邊起取出指定長度的字符串 /// </summary> /// <param name="str"></param> /// <param name="length"></param> /// <returns></returns> public static string Right(this string str, int length) { if (string.IsNullOrEmpty(str)) { return string.Empty; } return str.Substring(str.Length - length); } /// <summary> /// 判斷DataSet指定表是否包含記錄 /// </summary> /// <param name="ds"></param> /// <param name="tableIndex"></param> /// <returns></returns> public static bool HasRows(this DataSet ds, int tableIndex = 0) { if (ds != null && ds.Tables[tableIndex].Rows.Count > 0) { return true; } else { return false; } } /// <summary> /// 通用類型轉換方法，EG:"".As<String>() /// </summary> /// <typeparam name="T"></typeparam> /// <param name="obj"></param> /// <returns></returns> public static T As<T>(this object obj) { T result; try { Type type = typeof(T); if (type.IsNullableType()) { if (obj == null || obj.ToString().Length == 0) { result = default(T); } else { type = type.GetGenericArguments()[0]; result = (T)Convert.ChangeType(obj, type); } } else { if (obj == null) { if (type == typeof(string)) { result = (T)Convert.ChangeType(string.Empty, type); } else { result = default(T); } } else { result = (T)Convert.ChangeType(obj, type); } } } catch { result = default(T); } return result; } /// <summary> /// 判斷是否為可空類型 /// </summary> /// <param name="type"></param> /// <returns></returns> public static bool IsNullableType(this Type type) { return (type.IsGenericType && type.GetGenericTypeDefinition().Equals (typeof(Nullable<>))); } /// <summary> /// 嘗試將ArrayList轉換為Array，如果為空則轉換為null /// </summary> /// <param name="arrList"></param> /// <returns></returns> public static object[] TryToArray(this ArrayList arrList) { return arrList != null ? arrList.ToArray() : null; } }
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）
public class DataService : BaseService { #region 私有方法 private static SqlCmdInfo BuildSqlCmdInfo(string sqlCmdText, bool isSPCmdType = false, int dbType = 0, params object[] sqlParams) { var sqlCmdInfo = new SqlCmdInfo() { SqlCmdText = sqlCmdText, DbType = dbType, IsSPCmdType = isSPCmdType }; if (sqlParams != null && sqlParams.Length > 0) { sqlCmdInfo.Parameters = new ArrayList(sqlParams); } return sqlCmdInfo; } private static string GetRrequestApiUrl(string action) { string requestApiUrl = string.Format("http://{0}/api/Data/{1}", ApiHost, action); return requestApiUrl; } #endregion public static ApiResultInfo<string> Login(string uid, string pwd, string mac, string pcName) { var result = WebApiUtil.GetResultFromWebApi<string>(null, new[] { uid, pwd, mac, pcName }, GetRrequestApiUrl("Login")); if (result.Stauts) { SessionToken = result.Data; } return result; } public static void LogOut() { WebApiUtil.GetResultFromWebApi<string>(AddHeadersWithToken(), string.Format("{\"\":\"{0}\"}", SessionToken), GetRrequestApiUrl("LogOut")); } public static T GetValue<T>(string sqlCmdText, object[] sqlParams = null, bool isSPCmdType = false, int dbType = 0) { var sqlCmdInfo = BuildSqlCmdInfo(sqlCmdText, isSPCmdType, dbType, sqlParams); var result = WebApiUtil.GetResultFromWebApi<T>(AddHeadersWithToken(), sqlCmdInfo, GetRrequestApiUrl("GetValue")); if (result.Stauts) { return result.Data; } throw new Exception(result.ErrCode + ":" + result.ErrMsg); } public static DataSet GetDataSet(string sqlCmdText, object[] sqlParams = null, bool isSPCmdType = false, int dbType = 0) { var sqlCmdInfo = BuildSqlCmdInfo(sqlCmdText, isSPCmdType, dbType, sqlParams); var result = WebApiUtil.GetResultFromWebApi<DataSet>(AddHeadersWithToken(), sqlCmdInfo, GetRrequestApiUrl("GetDataSet")); if (result.Stauts) { return result.Data; } throw new Exception(result.ErrCode + ":" + result.ErrMsg); } public static DataTable GetDataTable(string sqlCmdText, object[] sqlParams = null, bool isSPCmdType = false, int dbType = 0) { var sqlCmdInfo = BuildSqlCmdInfo(sqlCmdText, isSPCmdType, dbType, sqlParams); var result = WebApiUtil.GetResultFromWebApi<DataTable>(AddHeadersWithToken(), sqlCmdInfo, GetRrequestApiUrl("GetDataTable")); if (result.Stauts) { return result.Data; } throw new Exception(result.ErrCode + ":" + result.ErrMsg); } public static int ExecuteCommand(string sqlCmdText, object[] sqlParams = null, bool isSPCmdType = false, int dbType = 0) { var sqlCmdInfo = BuildSqlCmdInfo(sqlCmdText, isSPCmdType, dbType, sqlParams); var result = WebApiUtil.GetResultFromWebApi<int>(AddHeadersWithToken(), sqlCmdInfo, GetRrequestApiUrl("ExecuteCommand")); if (result.Stauts) { return result.Data; } throw new Exception(result.ErrCode + ":" + result.ErrMsg); } public static bool BatchExecuteCommand(IEnumerable<SqlCmdInfo> sqlCmdInfos) { var result = WebApiUtil.GetResultFromWebApi<bool>(AddHeadersWithToken(), sqlCmdInfos, GetRrequestApiUrl("BatchExecuteCommand")); if (result.Stauts) { return result.Data; } throw new Exception(result.ErrCode + ":" + result.ErrMsg); } public static void ExecuteCommandAsync(string sqlCmdText, object[] sqlParams = null, bool isSPCmdType = false, int dbType = 0, Action<ApiResultInfo<object>> callBackAction = null) { var sqlCmdInfo = BuildSqlCmdInfo(sqlCmdText, isSPCmdType, dbType, sqlParams); Func<SqlCmdInfo, ApiResultInfo<object>> execCmdFunc = new Func<SqlCmdInfo, ApiResultInfo<object>>((sqlCmdObj) => { var result = WebApiUtil.GetResultFromWebApi<object>(AddHeadersWithToken(), sqlCmdObj, GetRrequestApiUrl("ExecuteCommandAsync")); return result; }); execCmdFunc.BeginInvoke(sqlCmdInfo, new AsyncCallback((ar) => { ApiResultInfo<object> apiResult = null; try { var func = ar.AsyncState as Func<SqlCmdInfo, ApiResultInfo<object>>; apiResult = func.EndInvoke(ar); } catch (Exception ex) { apiResult = new ApiResultInfo<object>(false, ex, "ExecuteCommandAsyncErr", ex.Message); } if (callBackAction != null) { callBackAction(apiResult); } }), execCmdFunc); } public static void SaveLogAsync(string logType, string msg, string source, string detailTrace = null, string other1 = null, string other2 = null, string other3 = null) { string[] logInfo = new[] { logType, msg, source, detailTrace, other1, other2, other3 }; Task.Factory.StartNew((o) => { try { string[] logInfoObj = o as string[]; var result = WebApiUtil.HttpRequestToString(AddHeadersWithToken(), logInfoObj, GetRrequestApiUrl("SaveLog")); } catch { } }, logInfo); } } public abstract class BaseService { public static string SessionToken = null; public static string ApiHost = null; protected static Dictionary<string, string> AddHeadersWithToken() { return new Dictionary<string, string> { {"AccessToken",SessionToken} }; } }
2.WebApiUtil（WEB API請求工具類）
/// <summary> /// WebApi實用工具類 /// Author:Zuowenjun /// Date:2017/11/3 /// </summary> public static class WebApiUtil { private const string rsaPublicKey = "公鑰字符串"; static WebApiUtil() { System.Net.ServicePointManager.DefaultConnectionLimit = 512; } /// <summary> /// 獲取API結果 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="requestHeaders"></param> /// <param name="requestMsg"></param> /// <param name="apiUrl"></param> /// <param name="requestMethod"></param> /// <returns></returns> public static ApiResultInfo<T> GetResultFromWebApi<T>(Dictionary<string, string> requestHeaders, object requestMsg, string apiUrl, string requestMethod = "POST") { string retString = HttpRequestToString(requestHeaders, requestMsg, apiUrl, requestMethod); return JsonConvert.DeserializeObject<ApiResultInfo<T>>(retString); } /// <summary> /// 發送Http請求，模擬訪問指定的Url，返回響應內容轉換成JSON對象 /// </summary> /// <param name="requestHeaders"></param> /// <param name="requestMsg"></param> /// <param name="apiUrl"></param> /// <param name="requestMethod"></param> /// <returns></returns> public static JObject HttpRequestToJson(Dictionary<string, string> requestHeaders, object requestMsg, string apiUrl, string requestMethod = "POST") { string retString = HttpRequestToString(requestHeaders, requestMsg, apiUrl, requestMethod); return JObject.Parse(retString); } /// <summary> /// 發送Http請求，模擬訪問指定的Url，返回響應內容文本 /// </summary> /// <param name="requestHeaders">請求頭</param> /// <param name="requestData">請求體（若為GetMethod時，則該值應為空）</param> /// <param name="apiUrl">要訪問的Url</param> /// <param name="requestMethod">請求方式</param> /// <param name="isEncryptBody">是否對請求體內容進行公鑰加密</param> /// <returns>響應內容(響應頭、響應體)</returns> public static string HttpRequestToString(Dictionary<string, string> 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());//轉化為字節流方便傳輸 } } } }
3.ApiResultInfo（API返回結果類）、SqlCmdInfo（SQL命令信息類） 與服務端的同名類基本相同，只是少了一些方法，因為這些方法在客戶端用不到所有無需再定義了：
[Serializable] public class SqlCmdInfo { public string SqlCmdText { get; set; } public ArrayList Parameters { get; set; } public bool IsSPCmdType { get; set; } public int DbType { get; set; } } [Serializable] public class ApiResultInfo<T> { public bool Stauts { get; set; } public T Data { get; set; } public string ErrCode { get; set; } public string ErrMsg { get; set; } public Dictionary<string, object> ExtendedData { get; set; } public ApiResultInfo() { this.ExtendedData = new Dictionary<string, object>(); } public ApiResultInfo(bool status, T data, string errCode = null, string errMsg = null, Dictionary<string, object> extData = null) { this.Stauts = status; this.Data = data; this.ErrCode = errCode; this.ErrMsg = errMsg; this.ExtendedData = extData; if (this.ExtendedData == null) { this.ExtendedData = new Dictionary<string, object>(); } } }
客戶端使用方法如下：
//直接使用DataService的相關公共靜態方法即可，就像本地直接使用ADO.NET操作數據庫一樣；例如： DataTable dt = DataService.GetDataTable("SQL語句", new object[] { 參數}); DataService.ExecuteCommand("SQL語句", new Object[] { 參數 });
以上就是基于ASP.NET WEB API實現分布式數據訪問中間層，這個分布式數據訪問中間層雖簡單，但我包含了如下幾個核心內容：
1.身份驗證：未經登錄授權是無法訪問API，當然上述的驗證非常簡單，這個可以根據實際情況進行擴展，比如：OA2.0驗證，摘要驗證等
2.請求內容加密：一些重要的請求內容必需要加密，而且防止被破解，這里使用公鑰加密，私鑰解密，從客戶端是無法截獲加密的請求內容
3.壓縮響應報文：如果返回的內容過大，則需要進行壓縮，以提高響應速度
4.防SQL注入：通過自定義過濾器，對傳入的SQL語句利用正則進行分析，若包含非法關鍵字則直接不予執行且報錯，執行SQL語句時也是全面采用ADO.NET的參數化，確保整個執行安全可靠。
5.全局異常捕獲：對于每個可能發生的異常一個都不放過，全部記錄到日志中
6.MONGODB日志記錄：利用NLOG.MONGO組件實現日志記錄，不影響正常的DB
好了本文總結就到這里了，之前由于工作忙，項目較多，沒有時間寫文章，今天剛好利用晚上加班的稍空閑時期對近期我獨立寫的項目進行總結與分享，希望能幫助大家，謝謝！
文章列表
全站熱搜
留言列表
