C# 测试实体框架数据库连接

Testing an Entity Framework database connection

提问by Steven Wood

I have an app that connects to a MYSQL database through the entity framework. It works 100% perfectly, but I would like to add a small piece of code that will test the connection to the database upon app startup.

我有一个通过实体框架连接到 MYSQL 数据库的应用程序。它 100% 完美运行，但我想添加一小段代码，用于在应用程序启动时测试与数据库的连接。

I had the idea of simply running a tiny command to the database and catching any exceptions, however if there is a problem (eg App.Config missing or Database server down) the app takes a huge amount of time to run this code and then throw the exception (~1 min). I imagine this is due to connection timeouts etc but I have fiddled with such properties to no avail.

我的想法是简单地对数据库运行一个小命令并捕获任何异常，但是如果出现问题（例如 App.Config 丢失或数据库服务器关闭），应用程序需要大量时间来运行此代码然后抛出例外（~1 分钟）。我想这是由于连接超时等原因，但我已经摆弄了这些属性无济于事。

Would anyone be able to assist with any ideas as to where to go?

任何人都可以就去哪里的任何想法提供帮助吗？

采纳答案by Tauseef

Are you just wanting to see if the DB connection is valid? If so take a look at the

你只是想看看数据库连接是否有效？如果是这样，请查看

using (DatabaseContext dbContext = new DatabaseContext())
{
     dbContext.Database.Exists();
}

http://msdn.microsoft.com/en-us/library/gg696617(v=vs.103).aspx

http://msdn.microsoft.com/en-us/library/gg696617(v=vs.103).aspx

and for checking if a server machine is up, DB server or web services server , try this:

并检查服务器机器是否已启动，数据库服务器或网络服务服务器，试试这个：

public PingReply Send( string hostNameOrAddress )

public PingReply Send( string hostNameOrAddress )

http://msdn.microsoft.com/en-us/library/7hzczzed.aspx

http://msdn.microsoft.com/en-us/library/7hzczzed.aspx

回答by Danilo Breda

I use this code for my project:

我将此代码用于我的项目：

private bool TestConnectionEF()
        {
            using (var db = new SistemaContext())
            {
                try
                {
                    db.Database.Connection.Open();
                    if (db.Database.Connection.State == ConnectionState.Open)
                    {
                        Console.WriteLine(@"INFO: ConnectionString: " + db.Database.Connection.ConnectionString 
                            + "\n DataBase: " + db.Database.Connection.Database 
                            + "\n DataSource: " + db.Database.Connection.DataSource 
                            + "\n ServerVersion: " + db.Database.Connection.ServerVersion 
                            + "\n TimeOut: " + db.Database.Connection.ConnectionTimeout);
                        db.Database.Connection.Close();
                        return true;
                    }
                    return false;
                }
                catch(Exception ex)
                {
                    throw ex;
                }
            }
        }

回答by Sandor

The solution as @Danilo Breda pointed out is to call the DbContext.Database.Connection.Open()

@Danilo Breda 指出的解决方案是调用 DbContext.Database.Connection.Open()

It is tested with EF6.

它使用 EF6 进行测试。

My implementaion:

我的实现：

    public static bool CheckConnection()
    {
        try
        {
            MyContext.Database.Connection.Open();
            MyContext.Database.Connection.Close();
        }
        catch(SqlException)
        {
            return false;
        }
        return true;
    }

回答by Leniel Maccaferri

I used the answer from @Sandor and did an extension methodto use with EntityFramework Core.

我使用了@Sandor 的答案，并做了一个扩展方法来与 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;
}

回答by user2457870

In EntityFramework Core you can simply call: Database.CanConnect();.

在核心的EntityFramework，你可以简单地调用：Database.CanConnect();。

(using EF Core 2.2.1)

（使用 EF Core 2.2.1）

Summary: Determines whether or not the database is available and can be connected to.

摘要：确定数据库是否可用以及是否可以连接。

Note that being able to connect to the database does not mean that it is up-to-date with regard to schema creation, etc.

请注意，能够连接到数据库并不意味着它在模式创建等方面是最新的。

回答by Evgeniy

I am using the following code for MS SQL connection. Maybe, it will be useful for MySQL too. You don't even need to use an EF or EF Core.

我使用以下代码进行 MS SQL 连接。也许，它对 MySQL 也很有用。您甚至不需要使用 EF 或 EF Core。

    public bool IsDbConnectionOK()
    {
        SqlConnectionStringBuilder conStr = new SqlConnectionStringBuilder
        {
            DataSource = ButtonServerName.Text,  // <-- My Form Elements
            InitialCatalog = ButtonDBName.Text, // <-- My Form Elements
            UserID = EditUserName.Text, // <-- My Form Elements
            Password = EditPassword.Text, // <-- My Form Elements
            IntegratedSecurity = false,
            ConnectTimeout = 30
        };

        string connectionstring = conStr.ToString();

        try
        {
            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionstring))
            {
                connection.Open();
                return true;
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            MessageBox.Show(ex.Message + Environment.NewLine +
                "Error line: " + ex.LineNumber + Environment.NewLine +
                "Procedure name: " + ex.Procedure);
            return false;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }