如何检查DB的存在？

I am wondering if there is an elegant way to check for the existence of a DB? In brief, how do test the connection of a db connection string?

我想知道是否有一种优雅的方式来检查数据库是否存在？简而言之，如何测试db连接字符串的连接？

Thanks

谢谢

8 个解决方案

#1

Set the Initial Catalog=master in the connection string and execute:

在连接字符串中设置Initial Catalog = master并执行：

select count(*) from sysdatabases where name = @name

with @name set to the name of the database.

将@name设置为数据库的名称。

If you want to check the connection string as a whole (and not existence of an independent database), try connecting to it in a try/catch block.

如果要将连接字符串作为一个整体检查（并且不存在独立数据库），请尝试在try / catch块中连接它。

#2

You could just try connecting to it. If it throws an exception, then the connection string is bad in some way, either the database doesn't exist, the password is wrong, or something else.

你可以尝试连接它。如果它抛出异常，那么连接字符串在某种程度上是错误的，数据库不存在，密码错误或其他。

DbConnection db = new SqlConnection(connection_string);
try
{
    db.Open();
}
catch ( SqlException e )
{
    // Cannot connect to database
}

#3

To cover the range of possibilities (server doesn't exist, database doesn't exist, no login, no permissions, server down, etc) - the simplest idea is simply to try to connect as normal, and perform something trivial - SELECT GETDATE() for example. If you get an exception, there is a problem!

为了覆盖各种可能性（服务器不存在，数据库不存在，没有登录，没有权限，服务器关闭等） - 最简单的想法就是尝试正常连接，并执行一些微不足道的事情 - SELECT GETDATE （）例如。如果你遇到异常，就会出现问题！

There are times (especially when dealing with out-of-process systems) when try/catch is the most pragmatic option.

有时（特别是在处理进程外系统时）try / catch是最实用的选项。

#4

Just try a DBConnection.Open() wrapped in a try block catching DBException.

只需尝试一个包含在try块中的DBConnection.Open（）捕获DBException。

About as elegant a solution as you are going to find.

关于优雅的解决方案，你会发现。

#5

try

尝试

IF NOT EXISTS(SELECT * FROM sys.databases WHERE [name] = @name)
   CREATE DATABASE @name;
GO

要么

IF db_id(@name) IS NOT NULL
   CREATE DATABASE @name;
GO

or SqlConnection.ChangeDatabase(String). I think it could use less sql server resources then new connection attempt.

或SqlConnection.ChangeDatabase（String）。我认为它可以使用更少的SQL服务器资源然后新的连接尝试。

#6

If you're using Entity Framework or have it available to you, you can simply call Database.Exists():

如果您正在使用Entity Framework或者可以使用它，则只需调用Database.Exists（）：

if (Database.Exists(connectionString))
{
    // do something
}
else
{
    // do something else
}

#7

This is what worked for me to verify the existence of any Postgres database with C#:

这对我来说用C＃来验证任何Postgres数据库的存在是有用的：

private bool chkDBExists(string connectionStr, string dbname)
{
    using (NpgsqlConnection conn = new NpgsqlConnection(connectionStr))
    {
        using (NpgsqlCommand command = new NpgsqlCommand
            ($"SELECT DATNAME FROM pg_catalog.pg_database WHERE DATNAME = '{dbname}'", conn))
        {
            try
            {
                conn.Open();
                var i = command.ExecuteScalar();
                conn.Close();
                if (i.ToString().Equals(dbname)) //always 'true' (if it exists) or 'null' (if it doesn't)
                    return true;
                else return false;
            }
            catch (Exception e) { return false; }
        }
    }
}

** The if used in the try-catch statement could simply check if the return of the ExecuteScalar is null for non-existent DB and not-null if it exists.

** try-catch语句中使用的if可以简单地检查ExecuteScalar的返回值对于不存在的DB是否为null，如果存在则不为null。

#8

you can get list of Database with below and check your db name:

你可以获得下面的数据库列表并检查你的数据库名称：

USE master
GO  
SELECT name, database_id, create_date  
FROM sys.databases ;  
GO

#1