回到目錄
上一講中基本實作了對資料庫的讀寫分離，而在選擇隻讀資料庫上隻是随機選擇，并沒有去檢測資料庫伺服器是否有效，如伺服器挂了，ＳＱＬ服務停了，端口被封了等等，而本講主要對以上功能進行一個實作，并對配置檔案也進行了一些優化，讓它更好的支援多個資料庫伺服器，分别配置各個的賬号和密碼及資料庫服務端口等等，接下來，就來看一下主要的代碼吧。
一 配置檔案
<!-- ef實作對sql讀寫分離的配置，sqlserver端采用釋出與訂閱實作 -->
<add key="readDb" value="
192.168.2.71|1433|background_read1|sa|zzl123,
192.168.2.71|1433|TestWrite_Read_Zzl|sa|zzl123,
192.168.2.29|1433|TestWrite_Read_Zzl|sa|1"
/>
<!-- 隻讀伺服器的sql連接配接串配置模版-->
<add key ="readDbConnectioin" value="data source={0};initial catalog={1};persist security info=True;user id={2};password={3};multipleactiveresultsets=True;application name=EntityFramework"/> 二 資料庫配置實體類
/// <summary>
/// 隻讀資料庫配置實體
/// </summary>
public class ReadDbConfig
{
public ReadDbConfig()
{
Port = 1433;
UserId = "sa";
}
public string Ip { get; set; }
public int Port { get; set; }
public string DbName { get; set; }
public string UserId { get; set; }
public string Password { get; set; }
} 三 對ＳＱＬ攔截器進行優化，添加了TCP的心跳檢測
lock (lockObj)
{
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);
}
}
}
} 四 對于資料庫庫端還是這前通過釋出和訂閱實作的，需要注意的是，這些功能需要使用“機器名”進行連結，使用ip和域名都是無效的
五 下面貢獻一下完成的攔截器代碼
/// <summary>
/// SQL指令攔截器
/// 主要實作EF的讀寫分離
/// </summary>
public class SqlCommandInterceptor : DbCommandInterceptor
{
static SqlCommandInterceptor()
{
InitConfig();
initSysTimer.Enabled = true;
initSysTimer.Elapsed += initSysTimer_Elapsed;
initSysTimer.Start();
sysTimer.Enabled = true;
sysTimer.Elapsed += sysTimer_Elapsed;
sysTimer.Start();
}
private static object lockObj = new object();
/// <summary>
/// 定期找沒有線上的資料庫伺服器
/// </summary>
private static Timer sysTimer = new Timer(6);
/// <summary>
/// 系統配置檔案輪訓讀時間間隔
/// </summary>
private static Timer initSysTimer = new Timer(60000 * 10);
/// <summary>
/// 讀庫，從庫叢集，寫庫不用設定走預設的EF架構
/// </summary>
private static List<ReadDbConfig> readConnList;
/// <summary>
/// 配置初始化
/// </summary>
private static void InitConfig()
{
lock (lockObj)
{
var temp = new List<ReadDbConfig>();
var str = System.Configuration.ConfigurationManager.AppSettings["readDb"] ?? string.Empty;
var readList = str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
if (readList != null && readList.Any())
{
foreach (var item in readList)
{
var configArr = item.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
temp.Add(new ReadDbConfig
{
Ip = configArr[0],
Port = int.Parse(configArr[1]),
DbName = configArr[2],
UserId = configArr[3],
Password = configArr[4],
});
}
}
readConnList = temp;
}
}
#region Private Methods
private static void initSysTimer_Elapsed(object sender, ElapsedEventArgs e)
{
InitConfig();
}
/// <summary>
/// 輪詢服務
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private static void sysTimer_Elapsed(object sender, ElapsedEventArgs e)
{
lock (lockObj)
{
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["readDbConnectioin"]
, resultConn.Ip
, resultConn.DbName
, resultConn.UserId
, resultConn.Password);
}
return string.Empty;
}
/// <summary>
/// 隻讀庫的選擇,加工command對象
/// </summary>
/// <param name="command"></param>
private void ReadDbSelect(DbCommand command)
{
if (!string.IsNullOrWhiteSpace(GetReadConn()))//如果配置了讀寫分離，就去實作
{
if (!command.CommandText.StartsWith("insert", StringComparison.InvariantCultureIgnoreCase))
{
command.Connection.Close();
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
} View Code
作者：倉儲大叔，張占嶺，
榮譽：微軟MVP
QQ：853066980
支付寶掃一掃，為大叔打賞!