回到目錄
本講是通過DbCommand攔截器來實作讀寫分離的最後一講，對之前幾篇文章做了一個優化，無論是程式可讀性還是實用性上都有一個提升，在配置資訊這塊，去除了字元串方式的拼接，取而代之的是section數組，這樣在修改配置時更加清晰了；而實用性上，徹底改變了讀和寫不能共用一個倉儲對象的缺點，并且在一個事務裡可以讀寫并存，并為了資料的一緻性，使事務裡的curd操作指向主庫，這一點很重要！
前幾篇文章的目錄
EF架構~通過EF6的DbCommand攔截器來實作資料庫讀寫分離~再續~添加對各隻讀伺服器的心跳檢測 (2015-01-09 17:52)
EF架構~通過EF6的DbCommand攔截器來實作資料庫讀寫分離~續~添加事務機制 (2015-01-08 14:08)
EF架構~通過EF6的DbCommand攔截器來實作資料庫讀寫分離 (2015-01-07 17:31)
功能架構圖如下
下面我們來分塊看一下這次的修改
一 配置檔案的修改
<configSections>
<section name="DistributedReadWriteSection" type="Project.DistributedReadWriteForEF.DistributedReadWriteSectionHandler, Project.DistributedReadWriteForEF"/>
</configSections>
<DistributedReadWriteSection>
<add key="readDb1" Ip="192.168.2.71" Port="1433" DbName="background_read1" UserId="sa" Password="zzl123" />
<add key="readDb2" Ip="192.168.2.71" Port="1433" DbName="TestWrite_Read_Zzl" UserId="sa" Password="zzl123" />
<add key="readDb3" Ip="192.168.2.29" Port="1433" DbName="TestWrite_Read_Zzl" UserId="sa" Password="1" />
</DistributedReadWriteSection>
<appSettings>
<!-- 隻讀伺服器的sql連接配接串配置模版-->
<add key ="readDbConnection" value="data source={0};initial catalog={1};persist security info=True;user id={2};password={3};multipleactiveresultsets=True;application name=EntityFramework"/>
<add key ="writeDbConnection" value="data source=.;initial catalog=background;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"/>
</appSettings> /// <summary>
/// redis配置資訊加載
/// </summary>
internal class DistributedReadWriteManager
{
/// <summary>
/// 配置資訊實體
/// </summary>
public static IList<DistributedReadWriteSection> Instance
{
get
{
return GetSection();
}
}
private static IList<DistributedReadWriteSection> GetSection()
{
var dic = ConfigurationManager.GetSection("DistributedReadWriteSection") as Dictionary<string, DistributedReadWriteSection>;
return dic.Values.ToList();
}
} /// <summary>
/// DistributedReadWriteForEFSection塊，在web.config中提供DistributedReadWriteForEFSection塊定義
/// </summary>
internal class DistributedReadWriteSection : ConfigurationSection
{
/// <summary>
/// 主機位址
/// </summary>
[ConfigurationProperty("Ip", DefaultValue = "127.0.0.1")]
public string Ip
{
get { return (string)this["Ip"]; }
set { this["Ip"] = value; }
}
/// <summary>
/// 端口号
/// </summary>
[ConfigurationProperty("Port", DefaultValue = "1433")]
public int Port
{
get { return (int)this["Port"]; }
set { this["Port"] = value; }
}
/// <summary>
/// 資料庫名稱
/// </summary>
[ConfigurationProperty("DbName", DefaultValue = "Test")]
public string DbName
{
get { return (string)this["DbName"]; }
set { this["DbName"] = value; }
}
/// <summary>
/// 資料庫賬号
/// </summary>
[ConfigurationProperty("UserId", DefaultValue = "sa")]
public string UserId
{
get { return (string)this["UserId"]; }
set { this["UserId"] = value; }
}
/// <summary>
/// 資料庫賬号
/// </summary>
[ConfigurationProperty("Password", DefaultValue = "sa")]
public string Password
{
get { return (string)this["Password"]; }
set { this["Password"] = value; }
}
} internal class DistributedReadWriteSectionHandler : IConfigurationSectionHandler
{
#region IConfigurationSectionHandler 成員
public object Create(object parent, object configContext, System.Xml.XmlNode section)
{
Dictionary<string, DistributedReadWriteSection> names = new Dictionary<string, DistributedReadWriteSection>();
string _key = string.Empty;
string _ip = string.Empty;
string _dbName = string.Empty;
string _userId = string.Empty;
string _password = string.Empty;
int _port = 1433;
foreach (XmlNode childNode in section.ChildNodes)
{
if (childNode.Attributes["key"] != null)
{
_key = childNode.Attributes["key"].Value;
if (childNode.Attributes["Ip"] != null)
{
_ip = childNode.Attributes["Ip"].Value;
}
if (childNode.Attributes["Port"] != null)
{
_port = Convert.ToInt32(childNode.Attributes["Port"].Value);
}
if (childNode.Attributes["DbName"] != null)
{
_dbName = childNode.Attributes["DbName"].Value;
}
if (childNode.Attributes["UserId"] != null)
{
_userId = childNode.Attributes["UserId"].Value;
}
if (childNode.Attributes["Password"] != null)
{
_password = childNode.Attributes["Password"].Value;
}
names.Add(_key, new DistributedReadWriteSection { Ip = _ip, Port = _port, DbName = _dbName, UserId = _userId, Password = _password });
}
}
return names;
}
#endregion
} 二 倉儲大叔事務塊修改
public static void UsingNoMsdtc(IUnitOfWork db, bool isOutest, Action action)
{
var objectContext = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)db).ObjectContext;
try
{
objectContext.Connection.Close();
//強制将所有curd操作維持到主庫
Project.DistributedReadWriteForEF.CommandInterceptor.IsTransactionScope = true;
//重新設定連結串
if (System.Configuration.ConfigurationManager.AppSettings["writeDbConnection"] != null)
objectContext.TransactionHandler.DbContext.Database.Connection.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["writeDbConnection"];
objectContext.Connection.Open();
using (TransactionScope trans = new TransactionScope())
{
action();
trans.Complete();
Project.DistributedReadWriteForEF.CommandInterceptor.IsTransactionScope = false;//事務結束将走讀寫分離
}
}
finally
{
if (isOutest)//如果是最外層事務，再将連接配接關閉！内部事務與外部事務需要共用一個Connection的連接配接
objectContext.Connection.Close(); //隻能關閉,不能dispose,因為dispose之後,上下文就無法得到連結串了
}
} 三 DbCommand攔截器的修改
/// <summary>
/// SQL指令攔截器
/// 主要實作EF的讀寫分離
/// </summary>
public class CommandInterceptor : DbCommandInterceptor
{
static CommandInterceptor()
{
readConnList = DistributedReadWriteManager.Instance;
sysTimer.Enabled = true;
sysTimer.Elapsed += sysTimer_Elapsed;
sysTimer.Start();
}
/// <summary>
/// 是否在一個事務中，如果是select,insert,update,delete都走主庫
/// ThreadStatic辨別它隻在目前線程有效
/// </summary>
[ThreadStatic]
public static bool IsTransactionScope = false;
/// <summary>
/// 鎖住它
/// </summary>
private static object lockObj = new object();
/// <summary>
/// 定期找沒有線上的資料庫伺服器
/// </summary>
private static Timer sysTimer = new Timer(5000);
/// <summary>
/// 讀庫，從庫叢集，寫庫不用設定走預設的EF架構
/// </summary>
private static IList<DistributedReadWriteSection> readConnList;
#region Private Methods
private static void sysTimer_Elapsed(object sender, ElapsedEventArgs e)
{
if (readConnList != null && readConnList.Any())
{
foreach (var item in readConnList)
{
//心跳測試，将死掉的伺服器IP從清單中移除
var client = new TcpClient();
try
{
client.Connect(new IPEndPoint(IPAddress.Parse(item.Ip), item.Port));
}
catch (SocketException)
{
//異常，沒有連接配接上
readConnList.Remove(item);
}
if (!client.Connected)
{
readConnList.Remove(item);
}
}
}
}
/// <summary>
/// 處理讀庫字元串
/// </summary>
/// <returns></returns>
private string GetReadConn()
{
if (readConnList != null && readConnList.Any())
{
var resultConn = readConnList[Convert.ToInt32(Math.Floor((double)new Random().Next(0, readConnList.Count)))];
return string.Format(System.Configuration.ConfigurationManager.AppSettings["readDbConnection"]
, resultConn.Ip
, resultConn.DbName
, resultConn.UserId
, resultConn.Password);
}
return string.Empty;
}
/// <summary>
/// 隻讀庫的選擇,加工command對象
/// 說明：事務中,所有語句都走主庫，事務外select走讀庫,insert,update,delete走主庫
/// 希望：一個ＷＥＢ請求中，讀與寫的倉儲使用一個，不需要在程式中去重新定義
/// </summary>
/// <param name="command"></param>
private void ReadDbSelect(DbCommand command)
{
if (!string.IsNullOrWhiteSpace(GetReadConn()))//如果配置了讀寫分離，就去實作
{
command.Connection.Close();
if (!command.CommandText.StartsWith("insert", StringComparison.InvariantCultureIgnoreCase) && !IsTransactionScope)
command.Connection.ConnectionString = GetReadConn();
command.Connection.Open();
}
}
#endregion
#region Override Methods
/// <summary>
/// Linq to Entity生成的update,delete
/// </summary>
/// <param name="command"></param>
/// <param name="interceptionContext"></param>
public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
base.NonQueryExecuting(command, interceptionContext);//update,delete等寫操作直接走主庫
}
/// <summary>
/// 執行sql語句，并傳回第一行第一列，沒有找到傳回null,如果資料庫中值為null，則傳回 DBNull.Value
/// </summary>
/// <param name="command"></param>
/// <param name="interceptionContext"></param>
public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
ReadDbSelect(command);
base.ScalarExecuting(command, interceptionContext);
}
/// <summary>
/// Linq to Entity生成的select,insert
/// 發送到sqlserver之前觸發
/// warning:在select語句中DbCommand.Transaction為null，而ef會為每個insert添加一個DbCommand.Transaction進行包裹
/// </summary>
/// <param name="command"></param>
/// <param name="interceptionContext"></param>
public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
ReadDbSelect(command);
base.ReaderExecuted(command, interceptionContext);
}
/// <summary>
/// 發送到sqlserver之後觸發
/// </summary>
/// <param name="command"></param>
/// <param name="interceptionContext"></param>
public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
base.ReaderExecuted(command, interceptionContext);
}
#endregion
} 好了，到這裡，通過攔截器來實作資料庫讀寫分離的方案就徹底完成了，這個版本應該算是個終級了吧，呵呵！感謝您的閱讀！
作者：倉儲大叔，張占嶺，
榮譽：微軟MVP
QQ：853066980
支付寶掃一掃，為大叔打賞!
