基於ASP.NET WEB API實現分散式數據訪問中間層（提供對資料庫的CRUD）

一些小的C/S項目（winform、WPF等），因需要訪問操作資料庫，但又不能把DB連接配置在客戶端上，原因有很多，可能是DB連接無法直接訪問，或客戶端不想安裝各種DB訪問組件，或DB連接不想暴露在客戶端（即使加密連接字元串仍有可能被破解的情況），總之都是出於安全考慮，同時因項目小，也無需採用分散式 ...

一些小的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.Decr