C#检测离线模式(SQL Server连接) [英] C# detect offline mode (SQL Server connection)
问题描述
我正在开发一个连接到SQL Server的C#应用程序.如果网络连接中断，则应用程序应该能够进入只读模式"(脱机模式)，并且只能从本地数据库读取数据.现在，我正在尝试找出如何检测断开连接:
I am developing a C# application which connects to SQL Server. If the network connection breaks, the application should be able to go into a "read-only mode" (offline mode) and only read data from a local database. Right now, I am trying to figure out how to detect the disconnect:
public int executeNonQuery(string query, List<SqlParameter> parameters)
{
int result;
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
tryOpenSqlConnection(sqlConnection);
using (SqlCommand cmd = new SqlCommand(query, sqlConnection))
{
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
result = cmd.ExecuteNonQuery();
}
sqlConnection.Close();
}
return result;
}
private void tryOpenSqlConnection(SqlConnection sqlConnection)
{
try
{
sqlConnection.Open();
}
catch (SqlException se)
{
if (se.Number == 26)
{
catchOfflineMode(se);
}
throw se;
}
}
//...
private void catchOfflineMode(SqlException se)
{
Console.WriteLine("SqlException: " + se.Message);
Console.WriteLine("Setting offline mode...");
//...
}
我考虑过使用SQL错误代码来检测连接丢失.但是问题是有时候我只有在SqlConnection已经建立之后才得到异常，例如在执行命令期间.我得到的最后一个例外是
I thought about using the SQL error codes to detect the loss of connection. But the problem is that sometimes I get exceptions only after the SqlConnection already established, e.g. during execution of the command. The last exception I got was
错误代码121-信号灯超时时间已到期
Error Code 121 - The semaphore timeout period has expired
因此，我将必须检查可能与丢失网络连接有关的每个错误代码.
So, I would have to check every single error code that could have to do with losing network connection.
编辑:我还考虑过捕获每个SqlException，然后检查以太网连接(例如ping服务器)以检查异常是否来自丢失的连接.
EDIT: I also thought about catching every SqlException and then checking the ethernet connection (e.g. pinging the server) to check whether the exception comes from a lost connection or not.
有更好的方法吗?
推荐答案
我通过创建一个名为
ExternalServiceHandler.cs
ExternalServiceHandler.cs
用作外部服务调用的代理，以在操作失败后检测应用程序的联机和脱机状态.
which is used as a proxy for external service calls to detect the online and offline status of the application after an operation failed.
using System;
using System.Threading;
using System.Threading.Tasks;
using Application.Utilities;
namespace Application.ExternalServices
{
class ExternalServiceHandler: IExternalServiceHandler
{
public event EventHandler OnlineModeDetected;
public event EventHandler OfflineModeDetected;
private static readonly int RUN_ONLINE_DETECTION_SEC = 10;
private static ExternalServiceHandler instance;
private Task checkOnlineStatusTask;
private CancellationTokenSource cancelSource;
private Exception errorNoConnection;
public static ExternalServiceHandler Instance
{
get
{
if (instance == null)
{
instance = new ExternalServiceHandler();
}
return instance;
}
}
private ExternalServiceHandler()
{
errorNoConnection = new Exception("Could not connect to the server.");
}
public virtual void Execute(Action func)
{
if (func == null) throw new ArgumentNullException("func");
try
{
func();
}
catch
{
if(offlineModeDetected())
{
throw errorNoConnection;
}
else
{
throw;
}
}
}
public virtual T Execute<T>(Func<T> func)
{
if (func == null) throw new ArgumentNullException("func");
try
{
return func();
}
catch
{
if (offlineModeDetected())
{
throw errorNoConnection;
}
else
{
throw;
}
}
}
public virtual async Task ExecuteAsync(Func<Task> func)
{
if (func == null) throw new ArgumentNullException("func");
try
{
await func();
}
catch
{
if (offlineModeDetected())
{
throw errorNoConnection;
}
else
{
throw;
}
}
}
public virtual async Task<T> ExecuteAsync<T>(Func<Task<T>> func)
{
if (func == null) throw new ArgumentNullException("func");
try
{
return await func();
}
catch
{
if (offlineModeDetected())
{
throw errorNoConnection;
}
else
{
throw;
}
}
}
private bool offlineModeDetected()
{
bool isOffline = false;
if (!LocalMachine.isOnline())
{
isOffline = true;
Console.WriteLine("-- Offline mode detected (readonly). --");
// notify all modues that we're in offline mode
OnOfflineModeDetected(new EventArgs());
// start online detection task
cancelSource = new CancellationTokenSource();
checkOnlineStatusTask = Run(detectOnlineMode,
new TimeSpan(0,0, RUN_ONLINE_DETECTION_SEC),
cancelSource.Token);
}
return isOffline;
}
private void detectOnlineMode()
{
if(LocalMachine.isOnline())
{
Console.WriteLine("-- Online mode detected (read and write). --");
// notify all modules that we're online
OnOnlineModeDetected(new EventArgs());
// stop online detection task
cancelSource.Cancel();
}
}
public static async Task Run(Action action, TimeSpan period, CancellationToken cancellationToken)
{
while (!cancellationToken.IsCancellationRequested)
{
await Task.Delay(period, cancellationToken);
if (!cancellationToken.IsCancellationRequested)
{
action();
}
}
}
protected virtual void OnOfflineModeDetected(EventArgs e)
{
OfflineModeDetected?.Invoke(this, e);
}
protected virtual void OnOnlineModeDetected(EventArgs e)
{
OnlineModeDetected?.Invoke(this, e);
}
}
}
LocalMachine.isOnline()方法如下:
namespace Application.Utilities
{
public class LocalMachine
{
// ... //
public static bool isOnline()
{
try
{
using (var client = new WebClient())
{
string serveraddress = AppSettings.GetServerHttpAddress();
using (var stream = client.OpenRead(serveraddress))
{
return true;
}
}
}
catch
{
return false;
}
}
// ... //
}
每次调用外部服务时都可以使用helper类.在下面的示例中，由ExternalServiceHandler执行SQL非查询:
The helper class can be used every time an external service call is made. In the following example, a SQL non query is executed by the ExternalServiceHandler:
public async Task<int> executeNonQueryAsync(string query)
{
return await ExternalServiceHandler.Instance.ExecuteAsync(async () =>
{
return await DBManager.executeNonQueryAsync(query);
});
}
该解决方案对我来说很好.如果您有更好的主意，请告诉我.
The solution works fine for me. If you have any better ideas, please let me know.
这篇关于C#检测离线模式(SQL Server连接)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
