.net 测试以查看 Entity Framework 是否已连接到某物
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5080360/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Test to see if Entity Framework is connected to something
提问by Bob Tway
When you create a new EntityCollection object, the connection doesn't attempt to open the database until you try and do something with that collection. I need to determine whether or not an Entity collection has a valid connection or not, and I can't find an efficient method of doing it.
当您创建新的 EntityCollection 对象时，连接不会尝试打开数据库，直到您尝试对该集合执行某些操作。我需要确定一个实体集合是否具有有效的连接，但我找不到一种有效的方法来做到这一点。
Currently I've got this in my code:
目前我在我的代码中有这个：
var db = new MyEntityCollection();
try
{
var checkworking = from c in db.Customers select c;
}
catch
{
ConnectToBackUp();
}
Which is not only horrible code, but very slow since it waits an age to determine whether or not the connection is active before throwing an exception.
这不仅是可怕的代码，而且非常慢，因为它在抛出异常之前等待一段时间来确定连接是否处于活动状态。
I know I can control how long it waits before giving up by using ConnectionTimeout but that's just another ugly hack that makes a bad situation worse.
我知道我可以通过使用 ConnectionTimeout 来控制它在放弃之前等待的时间，但这只是另一个丑陋的黑客，使糟糕的情况变得更糟。
Surely there's a better way of doing this?
当然有更好的方法来做到这一点吗？
采纳答案by Bob Tway
Solved this by going around the houses a bit and building a new connection string to test with ADO. Still involves using a try catch but it's a lot faster:
通过在房屋周围走动并构建新的连接字符串以使用 ADO 进行测试来解决此问题。仍然涉及使用 try catch 但它要快得多：
private bool TestConnection()
{
EntityConnectionStringBuilder b = new EntityConnectionStringBuilder();
ConnectionStringSettings entityConString = ConfigurationManager.ConnectionStrings["MyEntityConnectionString"];
b.ConnectionString = entityConString.ConnectionString;
string providerConnectionString = b.ProviderConnectionString;
SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder();
conStringBuilder.ConnectionString = providerConnectionString;
conStringBuilder.ConnectTimeout = 1;
string constr = conStringBuilder.ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
try
{
conn.Open();
return true;
}
catch
{
return false;
}
}
}
回答by Gianpiero
Simplest:
最简单：
private bool TestConnection()
{
var db = new MyEntityCollection();
int oldTimeOut = db.CommandTimeout;
try
{
db.CommandTimeout = 1;
db.Connection.Open(); // check the database connection
return true;
}
catch
{
return false;
}
finally
{
db.CommandTimeout = oldTimeOut;
}
}
Update for EF6:
EF6 更新：
using System.Data.Common;
...
public bool TestConnection() {
using (var db = new MyEntityCollection()) {
DbConnection conn = db.Database.Connection;
try {
conn.Open(); // check the database connection
return true;
}
catch {
return false;
}
}
}
回答by Stuart King
Are you just wanting to see if the DB connection is valid. If so take a look at the objectcontext.databaseExists().
你只是想看看数据库连接是否有效。如果是这样，请查看objectcontext.databaseExists().
回答by Ladislav Mrnka
Shouldn't such infrastructure be provided on the network or database server layer? Manually handling connection to backup server looks strange. Moreover it is not possible without waiting for timeout because you must try to open connection to primary server first.
网络或数据库服务器层不应该提供这样的基础设施吗？手动处理与备份服务器的连接看起来很奇怪。此外，不等待超时是不可能的，因为您必须首先尝试打开与主服务器的连接。
Connection itself is accessible on ObjectContext.Connection. This property should return EntityConnectioninstance which contains StoreConnectionproperty holding connection to real DB. You can check state of this connection.
连接本身可在 上访问ObjectContext.Connection。此属性应返回EntityConnection包含StoreConnection与真实数据库保持连接的属性的实例。您可以检查此连接的状态。
回答by Pavel Shkleinik
In my app user can change connection to database from UI. I use this code to quickly check connection with new connection data:
在我的应用程序中，用户可以从 UI 更改与数据库的连接。我使用此代码快速检查与新连接数据的连接：
public bool CheckAvailable(string sqlServerName, string login, string password)
{
var testConnectionString = GetConnectionString(sqlServerName, login, password);
if (string.IsNullOrWhiteSpace(testConnectionString))
return false;
var result = false;
try
{
var testContext = new EntityContext(testConnectionString);
result = testContext.Database.Exists();
}
catch (Exception ex)
{
log.Error(exception);
}
return result;
}
private string GetConnectionString(string sqlServerName, string login, string password)
{
var sqlConnectionString = new SqlConnectionStringBuilder
{
DataSource = sqlServerName,
InitialCatalog = ConfigurationProvider.DatabaseName,
UserID = login,
Password = password,
MultipleActiveResultSets = true,
ConnectTimeout = 2 // in seconds
};
var efConnectionString = new EntityConnectionStringBuilder
{
Provider = ProviderName,
Metadata = ConnectionMetadata,
ProviderConnectionString = sqlConnectionString.ToString()
};
return efConnectionString.ConnectionString;
}
ConnectTimeoutproperty of the SqlConnectionStringBuildertype is very important to perform the check lightening fast. If you know, that your database should be available and should response fast you can set this value to 1 (with SQL server installed locally it takes 100-200 milliseconds to check). Do not set 0, because it means no limit and should be avoided in connection strings.
SqlConnectionStringBuilder类型的ConnectTimeout属性对于快速执行检查是非常重要的。如果您知道您的数据库应该可用并且响应速度应该很快，您可以将此值设置为 1（如果 SQL 服务器安装在本地，则需要 100-200 毫秒来检查）。不要设置 0，因为它意味着没有限制，应该避免在连接字符串中。
回答by Leniel Maccaferri
I did an extension methodto use with EntityFramework Core.
我做了一个与 EntityFramework Core 一起使用的扩展方法。
Here's the code:
这是代码：
using Microsoft.EntityFrameworkCore;
using System.Data.Common;
namespace TerminalInventory
{
public static class ExtensionMethods
{
public static bool TestConnection(this DbContext context)
{
DbConnection conn = context.Database.GetDbConnection();
try
{
conn.Open(); // Check the database connection
return true;
}
catch
{
return false;
}
}
}
}
Now you just have to call:
现在你只需要调用：
if (!context.TestConnection())
{
logger.LogInformation("No database connection. Check the connection string in settings.json. {0}", configuration["connectionString"]);
return;
}
