C# 创建前检查数据库是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2232227/
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
Check if Database Exists Before Creating
提问by
This seems pretty trivial, but it is now frustrating me.
这看起来很微不足道，但现在让我感到沮丧。
I am using C# with SQL Server 2005 Express.
我在 SQL Server 2005 Express 中使用 C#。
I am using the following code. I want to check if a database exists before creating it. However, the integer returned is -1 and this is how MSDN defines what ExecuteNonQuery() will return as well. Right now, the database does exist but it still returns -1. Having said that, how can I make this work to get the desired result?
我正在使用以下代码。我想在创建数据库之前检查它是否存在。但是，返回的整数是 -1，这也是 MSDN 定义 ExecuteNonQuery() 将返回什么的方式。现在，数据库确实存在，但它仍然返回-1。话虽如此，我怎样才能使这项工作得到想要的结果？
private static void checkInventoryDatabaseExists(ref SqlConnection tmpConn, ref bool databaseExists)
{
string sqlCreateDBQuery;
try
{
tmpConn = new SqlConnection("server=(local)\SQLEXPRESS;Trusted_Connection=yes");
sqlCreateDBQuery = "SELECT * FROM master.dbo.sysdatabases where name =
\'INVENTORY\'";
using (tmpConn)
{
tmpConn.Open();
tmpConn.ChangeDatabase("master");
using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
{
int exists = sqlCmd.ExecuteNonQuery();
if (exists <= 0)
databaseExists = false;
else
databaseExists = true;
}
}
}
catch (Exception ex) { }
}
采纳答案by marc_s
As of SQL Server 2005, the old-style sysobjectsand sysdatabasesand those catalog views have been deprecated. Do this instead - use the sys.schema - views like sys.databases
由于SQL Server 2005中的，老式的sysobjects和sysdatabases那些目录视图已过时。改为这样做 - 使用sys.架构 - 像这样的视图sys.databases
private static bool CheckDatabaseExists(SqlConnection tmpConn, string databaseName)
{
string sqlCreateDBQuery;
bool result = false;
try
{
tmpConn = new SqlConnection("server=(local)\SQLEXPRESS;Trusted_Connection=yes");
sqlCreateDBQuery = string.Format("SELECT database_id FROM sys.databases WHERE Name
= '{0}'", databaseName);
using (tmpConn)
{
using (SqlCommand sqlCmd = new SqlCommand(sqlCreateDBQuery, tmpConn))
{
tmpConn.Open();
object resultObj = sqlCmd.ExecuteScalar();
int databaseID = 0;
if (resultObj != null)
{
int.TryParse(resultObj.ToString(), out databaseID);
}
tmpConn.Close();
result = (databaseID > 0);
}
}
}
catch (Exception ex)
{
result = false;
}
return result;
}
This will work with any database name you pass in as a parameter, and it will return a bool true = database exists, false = database does not exist (or error happened).
这将适用于您作为参数传入的任何数据库名称，它将返回 bool true = 数据库存在，false = 数据库不存在（或发生错误）。
回答by SQLMenace
shouldn't this
这不应该
"SELECT * FROM master.dbo.sysdatabases where name = \'INVENTORY\'"
be this?
是这个？
"SELECT * FROM master.dbo.sysdatabases where name = 'INVENTORY'"
Also According to MSDN
也根据MSDN
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
对于 UPDATE、INSERT 和 DELETE 语句，返回值是受命令影响的行数。当插入或更新的表上存在触发器时，返回值包括受插入或更新操作影响的行数以及受触发器影响的行数。对于所有其他类型的语句，返回值为 -1。如果发生回滚，则返回值也是 -1。
You are doing a SELECT not an DML statement. Why don't you use a ExecuteReader method instead?
您正在执行 SELECT 而不是 DML 语句。为什么不使用 ExecuteReader 方法呢？
回答by gbn
You can't use ExecuteNonQuerybecause it will always return -1 for SELECT, as the MSDN link shows.
您不能使用ExecuteNonQuery，因为它总是为 SELECT 返回 -1，如 MSDN 链接所示。
You'll have to use process a resultset eg SELECT DB_ID('INVENTORY') AS DatabaseIDor use a variable/parameter: SELECT @DatabaseID = DB_ID('INVENTORY')
您必须使用处理结果集，例如SELECT DB_ID('INVENTORY') AS DatabaseID或使用变量/参数：SELECT @DatabaseID = DB_ID('INVENTORY')
回答by Daniel James Bryars
An alternative to querying the system views is to use the function db_id which returns the Id of the database if it exists, otherwise null. Example T-SQL below:
查询系统视图的另一种方法是使用函数 db_id，它返回数据库的 Id（如果存在），否则返回 null。下面的示例 T-SQL：
if (db_id('INVENTORY') is null)
begin
return 0
end
else
begin
return 1
end
回答by Stelloy
Reading this a few years on and there's a cleaner way of expressing this:
几年后阅读本文，有一种更清晰的表达方式：
public static bool CheckDatabaseExists(string connectionString, string databaseName)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand($"SELECT db_id('{databaseName}')", connection))
{
connection.Open();
return (command.ExecuteScalar() != DBNull.Value);
}
}
}
回答by HockeyJ
回答by Brandon
Took Stephen Lloyd's code and added some async and sql injection mitigation.
使用 Stephen Lloyd 的代码并添加了一些异步和 sql 注入缓解。
public static async Task<bool> TestDatabase(string connectionString, string databaseName)
{
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("SELECT db_id(@databaseName)", connection))
{
command.Parameters.Add(new SqlParameter("databaseName", databaseName));
connection.Open();
return (await command.ExecuteScalarAsync() != DBNull.Value);
}
}
