创建前检查数据库是否存在 [英] Check if Database Exists Before Creating

问题描述

这似乎很琐碎，但是现在让我感到沮丧。

This seems pretty trivial, but it is now frustrating me.

我在SQL Server 2005 Express中使用C＃。

I am using C# with SQL Server 2005 Express.

我正在使用以下代码。我想在创建数据库之前检查它是否存在。但是，返回的整数是-1，这就是MSDN定义ExecuteNonQuery（）也将返回的方式。目前，该数据库确实存在，但仍返回-1。话虽如此，我如何才能使这项工作获得期望的结果？

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?

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) { }

}

推荐答案

从SQL Server 2005开始，旧式 sysobjects 和 sysdatabases 和那些目录视图已被弃用。改为执行此操作-使用 sys。架构- sys.databases

As of SQL Server 2005, the old-style sysobjects and sysdatabases and those catalog views have been deprecated. Do this instead - use the sys. schema - views like 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;
}

这将与您作为参数传递的任何数据库名称一起使用，并且将返回布尔值true =数据库存在，false =数据库不存在（或发生错误）。

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).

这篇关于创建前检查数据库是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！