希望達成以下目的
1. Repository使用EF操作DB
2. Service為外部呼叫的對象 (並且要對送進來的參數做一些客製化的檢查，確認沒問題才送進Repository使用) ... 會用到一些Reflection
3. 希望DAL是由誰呼叫 可以在DB端留下記錄 (下 sp who 可以看到 你的 program name)
4. 有些客制化的 DB 存取限制 (例如: 單次取出上限50000筆、超過 60 秒算timeout)
5. 當發生 Exception 時 自動記錄Log (包含 Sql Script 與資料內容) ... 我使用Service Stack將資料內容記錄成 csv 檔
6. 希望取資料時是 with (nolock)
7. 希望未來同一個 function 查詢參數增加時 不要影響現有舊程式的運作
假設我們的專案結構如下
有一個專案叫DAL => 給所有程式呼叫的資料存取層
有一個專案叫Console => 一個拿來呼叫 DAL 的範例
記得要從Nuget安裝 1. Entity Framework 2. Service Stack
並且將Service.Transactions加入參考
而Console內的程式碼很簡單
我們希望最終外部呼叫DAL時 會長的像這樣
using MySample.DAL.RepoParams;
using MySample.DAL.Service;
using System.Collections.Generic;
namespace MySample.Console
{
class Program
{
static void Main(string[] args)
{
// var myService = new SampleService(new SampleRepository("MySampleExe"));
var myService = new SampleService(new SampleRepositoryMock("MySampleExe"));
var result = myService.GetTable(new pMySampleTable { NAME = new List<string> { "John", "Mary" } });
}
}
}
接下來我們來看看DAL的程式碼
Models > MySampleTable
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace MySample.DAL.Models.Sample
{
public class MySampleTable
{
[Key]
[Column(Order = 0)] //當 Table 是複合主鍵時必定義 Order
public int ID { get; set; }
public string NAME { get; set; }
public DateTime BIRTHDAY { get; set; }
public double HEIGHT { get; set; }
public double WEIGHT { get; set; }
}
}
Models > SampleContext
using MySample.DAL.Models.Sample;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SqlClient;
namespace MySample.DAL.Models
{
public class SampleContext : DbContext
{
public DbSet<MySampleTable> MySampleTable { get; set; }
public SampleContext(string connStr) : base(new SqlConnection(connStr), true)
{
Database.SetInitializer<SampleContext>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
var instance = System.Data.Entity.SqlServer.SqlProviderServices.Instance;
}
}
}
RepoParams > pMySampleTable
using System.Collections.Generic;
namespace MySample.DAL.RepoParams.Sample
{
/// <summary>
/// 當未來有第2、3、4...個參數時 只要修改 Repository 即可 不影響外部程式叫用
/// </summary>
public class pMySampleTable
{
public IEnumerable<string> NAME { get; set; }
}
}
Repository > SampleRepository
using MySample.DAL.Extensions;
using MySample.DAL.Extensions.Sample;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
namespace MySample.DAL.RepoParams
{
public class SampleRepository : ISampleRepository
{
private string _projName;
private static readonly int TimeOut = 60; //通常放在config中
public SampleRepository(string projName)
{
_projName = projName;
}
public SampleContext ContextInit(StringBuilder sb)
{
//通常連線字串會放在 config 中
var dbContext = new SampleContext(@"Data Source=資料庫名稱;user id=帳號;password=密碼;Initial Catalog=SampleDb;Application Name={" + _projName + "}");
//綁定資料庫 timeout 限制秒數
if (TimeOut > 0) dbContext.Database.CommandTimeout = TimeOut;
//寫出 Ef 自動產生的 Sql Script
dbContext.Database.Log = log => sb.Append(log);
return dbContext;
}
/// <summary>
/// 因為希望錯誤 Log 可以明確指出是哪個 function name 壞掉
/// </summary>
[MethodImpl(MethodImplOptions.NoInlining)]
internal static string GetFuncName()
{
return new StackTrace().GetFrame(1).GetMethod().Name;
}
public List<MySampleTable> GetTable(pMySampleTable param)
{
var sb = new StringBuilder();
using (SampleContext dbContext = ContextInit(sb))
{
IQueryable<MySampleTable> result = dbContext.MySampleTable;
if (param.NAME.IsValid()) result = result.Where(q => param.NAME.Contains(q.NAME));
return result.ToListWithNoLock(_projName, GetFuncName(), sb);
}
}
}
}
Service > SampleService
using MySample.DAL.Extensions.Sample;
using MySample.DAL.RepoParams;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
namespace MySample.DAL.Service
{
public class SampleService
{
ISampleRepository repo;
public SampleService(ISampleRepository pRepo)
{
repo = pRepo;
}
public List<MySampleTable> GetTable(pMySampleTable param)
{
//移除非法參數
param = CheckRepoParam(param);
return IsAllParamNull(param) ? new List<MySampleTable>() : repo.GetTable(param);
}
private static T CheckRepoParam<T>(T obj)
{
//重定義大家傳入的參數
obj = RemoveInvalidParam(obj);
return obj;
}
/// <summary>
/// 移除不合法的查詢參數
/// </summary>
private static T RemoveInvalidParam<T>(T obj)
{
var props = obj.GetType().GetProperties();
foreach (var prop in props)
{
if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnumerable)) && prop.PropertyType != typeof(String))
{
var typeArg = prop.PropertyType.GetGenericArguments().First();
var type = typeof(List<>).MakeGenericType(typeArg);
var newList = (IList)Activator.CreateInstance(type);
var value = prop.GetValue(obj);
if (value == null) continue;
//var result = ((IEnumerable)value).Cast<object>().Where(q => q != null && !string.IsNullOrWhiteSpace(q.ToString())).Distinct(); //如果參數內有 null 是你要移除的
var result = ((IEnumerable)value).Cast<object>().Distinct(); //如果參數內有 null 是你要留著的
foreach (var item in result)
{
newList.Add(item);
}
prop.SetValue(obj, newList);
}
}
return obj;
}
/// <summary>
/// 是否傳入的參數全為空
/// </summary>
private static bool IsAllParamNull<T>(T obj)
{
var props = obj.GetType().GetProperties();
foreach (var prop in props)
{
var value = prop.GetValue(obj, null);
if (value != null) return false;
}
return true;
}
}
}
Extensions > MyExtension
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using System.IO;
using ServiceStack.Text;
namespace MySample.DAL.Extentions
{
internal static class MyExtension
{
//通常放在config中
private static readonly int MaxRecordCount = 50000;
internal static List<T> ToListWithNoLock<T>(this IQueryable<T> query, string projName, string funcName, StringBuilder sb)
{
List<T> result;
try
{
using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
result = query.Take(MaxRecordCount).ToList();
}
}
catch (Exception ex)
{
ExportEfLog(projName, ("~Error_" + funcName), sb.Append("\n\n" + ex.ToString())); //若出錯必寫log
throw ex;
}
//補捉各種自訂錯誤 (ex: 超過50000筆)
ErrorHandler(projName, funcName, sb, result);
// SQL → TXT
ExportEfLog(projName, funcName, sb);
// Data → CSV
ExportEfResult(projName, funcName, result);
sb = null;
return result;
}
/// <summary>
/// SQL 查詢字串匯出為 txt 檔
/// </summary>
private static void ExportEfLog(string projName, string funcName, StringBuilder sb)
{
string fullPath = CreateDirAndGetFullPath(projName, funcName, "txt");
try
{
File.WriteAllText(fullPath, sb.ToString(), new UTF8Encoding(true));
}
catch
{
//do something
}
finally
{
sb.Clear();
}
}
/// <summary>
/// 若資料夾不存在則創建資料夾，並且回傳完整檔案路徑
/// </summary>
private static string CreateDirAndGetFullPath(string projName, string funcName, string format)
{
DateTime dt = DateTime.Now;
string dir = @"D:\SQL_ERROR\" + projName + @"\" + dt.ToString("yyyyMMdd") + @"\";
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string fileName = funcName + "_" + dt.ToString("yyyyMMdd_HHmmss_fffffff") + "." + format;
return (dir + fileName);
}
/// <summary>
/// SQL 查詢結果匯出為 csv 檔
/// </summary>
internal static void ExportEfResult<T>(string projName, string funcName, List<T> obj)
{
string fullPath = CreateDirAndGetFullPath(projName, funcName, "csv");
//針對時間格式做格式化
JsConfig<DateTime>.SerializeFn = time => new DateTime(time.Ticks).ToString("yyyy/MM/dd HH:mm:ss");
try
{
//使用 Service Stack 來幫我們處理 List → csv 的序列化
var data = CsvSerializer.SerializeToCsv<T>(obj);
File.WriteAllText(fullPath, data, new UTF8Encoding(true));
}
catch
{
}
}
private static void ErrorHandler<T>(string projName, string funcName, StringBuilder sb, List<T> result)
{
if (result.Count >= MaxRecordCount)
{
ExportEfLog(projName, "~OverCountLimit_" + funcName, sb); //出錯必寫log
throw new OutOfMemoryException("取出資料筆數，超過上限" + MaxRecordCount.ToString() + "筆");
}
}
internal static bool IsValid<T>(this IEnumerable<T> param)
{
return param != null;
}
}
}
