我需要开发一个单一的例程,每5分钟触发一次,以检查SQL服务器列表(10到12个)是否正在运行。
有没有一种简单的方式可以用最少的代码和SQL操作要求从C#中“ping”一个SQL服务器?
我需要开发一个单一的例程,每5分钟触发一次,以检查SQL服务器列表(10到12个)是否正在运行。
有没有一种简单的方式可以用最少的代码和SQL操作要求从C#中“ping”一个SQL服务器?
当连接服务器停止或暂停时,我在使用EF时遇到了困难,并提出了同样的问题。为了完整性起见,这里是代码。
/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
private static bool IsServerConnected(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
return true;
}
catch (SqlException)
{
return false;
}
}
}
connection.Close();
,因为using
语句会在结束时自动关闭连接。 - user915331执行 SELECT 1
并检查执行 ExecuteScalar 后是否返回 1。
请查看GitHub上的以下项目:https://github.com/ghuntley/csharp-mssql-connectivity-tester
try
{
Console.WriteLine("Connecting to: {0}", AppConfig.ConnectionString);
using (var connection = new SqlConnection(AppConfig.ConnectionString))
{
var query = "select 1";
Console.WriteLine("Executing: {0}", query);
var command = new SqlCommand(query, connection);
connection.Open();
Console.WriteLine("SQL Connection successful.");
command.ExecuteScalar();
Console.WriteLine("SQL Query execution successful.");
}
}
catch (Exception ex)
{
Console.WriteLine("Failure: {0}", ex.Message);
}
建立与数据库的连接不是能够自动完成这个过程吗?如果数据库没有启动,你将无法建立连接。
在端口1433上查找一个开放的监听器(默认端口)。如果在创建tcp连接后收到任何响应,则服务器可能已经启动。
你知道吗,我最初是在2010年写的。今天,我会尝试实际连接服务器。
public static class SqlConnectionExtension
{
#region Public Methods
public static bool ExIsOpen(
this SqlConnection connection, MessageString errorMsg = null)
{
if (connection == null) { return false; }
if (connection.State == ConnectionState.Open) { return true; }
try
{
connection.Open();
return true;
}
catch (Exception ex) { errorMsg?.Append(ex.ToString()); }
return false;
}
public static bool ExIsReady(
this SqlConnection connction, MessageString errorMsg = null)
{
if (connction.ExIsOpen(errorMsg) == false) { return false; }
try
{
using (var command = new SqlCommand("select 1", connction))
{ return ((int)command.ExecuteScalar()) == 1; }
}
catch (Exception ex) { errorMsg?.Append(ex.ToString()); }
return false;
}
#endregion Public Methods
}
public class MessageString : IDisposable
{
#region Protected Fields
protected StringBuilder _messageBuilder = new StringBuilder();
#endregion Protected Fields
#region Public Constructors
public MessageString()
{
}
public MessageString(int capacity)
{
_messageBuilder.Capacity = capacity;
}
public MessageString(string value)
{
_messageBuilder.Append(value);
}
#endregion Public Constructors
#region Public Properties
public int Length {
get { return _messageBuilder.Length; }
set { _messageBuilder.Length = value; }
}
public int MaxCapacity {
get { return _messageBuilder.MaxCapacity; }
}
#endregion Public Properties
#region Public Methods
public static implicit operator string(MessageString ms)
{
return ms.ToString();
}
public static MessageString operator +(MessageString ms1, MessageString ms2)
{
MessageString ms = new MessageString(ms1.Length + ms2.Length);
ms.Append(ms1.ToString());
ms.Append(ms2.ToString());
return ms;
}
public MessageString Append<T>(T value) where T : IConvertible
{
_messageBuilder.Append(value);
return this;
}
public MessageString Append(string value)
{
return Append<string>(value);
}
public MessageString Append(MessageString ms)
{
return Append(ms.ToString());
}
public MessageString AppendFormat(string format, params object[] args)
{
_messageBuilder.AppendFormat(CultureInfo.InvariantCulture, format, args);
return this;
}
public MessageString AppendLine()
{
_messageBuilder.AppendLine();
return this;
}
public MessageString AppendLine(string value)
{
_messageBuilder.AppendLine(value);
return this;
}
public MessageString AppendLine(MessageString ms)
{
_messageBuilder.AppendLine(ms.ToString());
return this;
}
public MessageString AppendLine<T>(T value) where T : IConvertible
{
Append<T>(value);
AppendLine();
return this;
}
public MessageString Clear()
{
_messageBuilder.Clear();
return this;
}
public void Dispose()
{
_messageBuilder.Clear();
_messageBuilder = null;
}
public int EnsureCapacity(int capacity)
{
return _messageBuilder.EnsureCapacity(capacity);
}
public bool Equals(MessageString ms)
{
return Equals(ms.ToString());
}
public bool Equals(StringBuilder sb)
{
return _messageBuilder.Equals(sb);
}
public bool Equals(string value)
{
return Equals(new StringBuilder(value));
}
public MessageString Insert<T>(int index, T value)
{
_messageBuilder.Insert(index, value);
return this;
}
public MessageString Remove(int startIndex, int length)
{
_messageBuilder.Remove(startIndex, length);
return this;
}
public MessageString Replace(char oldChar, char newChar)
{
_messageBuilder.Replace(oldChar, newChar);
return this;
}
public MessageString Replace(string oldValue, string newValue)
{
_messageBuilder.Replace(oldValue, newValue);
return this;
}
public MessageString Replace(char oldChar, char newChar, int startIndex, int count)
{
_messageBuilder.Replace(oldChar, newChar, startIndex, count);
return this;
}
public MessageString Replace(string oldValue, string newValue, int startIndex, int count)
{
_messageBuilder.Replace(oldValue, newValue, startIndex, count);
return this;
}
public override string ToString()
{
return _messageBuilder.ToString();
}
public string ToString(int startIndex, int length)
{
return _messageBuilder.ToString(startIndex, length);
}
#endregion Public Methods
}
感谢@27k1
https://dev59.com/OnE95IYBdhLWcg3wI6ft#9943871
我在这里添加了一些代码:
/// <summary>
/// Test that the server is connected
/// </summary>
/// <param name="connectionString">The connection string</param>
/// <returns>true if the connection is opened</returns>
public static (bool isConnected, string sqlErrorMessage) IsServerConnected(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
return (true, "");
}
catch (SqlException ex)
{
return (false, ex.Message);
}
}
}
然后调用
string connectionString = $@"Server={txtServerName.Text.Trim()};Database={txtDatabaseName.Text.Trim()};
User Id={txtLogin.Text.Trim()};Password={txtPassword.Text.Trim()};";
var response = IsServerConnected(connectionString);
if (response.isConnected)
{
lblConnectionState.Text = "✔";
txtSqlConnectionError.Text = "";
}
else
{
lblConnectionState.Text = "❌";
txtSqlConnectionError.Text = response.sqlErrorMessage;
}
我通常通过打开连接来实现这一点,但有些情况下,通过Open
进行简单测试会导致AccessViolationException
using (SqlConnection db = new SqlConnection(conn))
{
db.Open(); // -- Access Violation caused by invalid Server in Connection String
}
所以我按照Joel Coehoorn的建议,在打开之前进行了TCP检查。C#代码可能是:
string targetAddress = "";
try
{
targetAddress = GetServerFromConnectionString();
IPAddress ipAddress = Dns.GetHostEntry(targetAddress).AddressList[0];
IPEndPoint ipEndPoint = new IPEndPoint(ipAddress, 1433);
using (TcpClient tcpClient = new TcpClient())
{
tcpClient.Connect(ipEndPoint);
}
}
catch (Exception ex)
{
LogError($"TestViaTcp to server {targetAddress} failed '{ex.GetType().Name}': {ex.Message}");
}
sql-server
标签上悬停鼠标,你会发现它是 Microsoft 特定的。我也会说 mssql 和 mysql 等等... - NappingRabbit