检查数据库是否存在 MSSQL C# [英] Check if Database exists MSSQL C#

问题描述

我有这样的代码:

private bool CheckDatabase(string databaseName, bool bRet)
    {
        string connString = "Server=localhost\\SQLEXPRESS;Integrated Security=SSPI;database=master";
        string cmdText = "select * from master.dbo.sysdatabases where name=\'" + databaseName + "\'";



        using (SqlConnection sqlConnection = new SqlConnection(connString))
        {
            sqlConnection.Open();
            using (SqlCommand sqlCmd = new SqlCommand(cmdText, sqlConnection))
            {
                int nRet = sqlCmd.ExecuteNonQuery();
                // regMessage.Text = nRet.ToString();
                if (nRet <= 0)
                {
                    bRet = false;
                }
                else
                {
                    bRet = true;
                }
            }
        }
        return bRet;
    }

不过

nRet

结果总是-1，就好像数据库不存在一样(确实存在).是不是因为数据库是空的?或者如果创建了数据库，它应该返回> 0，即使它是空的?顺便说一下，字符串 databaseName 是正确的.

results always -1, as if the database did not exist(It does). Is it the problem because database is empty yet? Or should it return >0 if database was created, even if it is empty? the string databaseName is correct by the way.

编辑>我还在CREATE DATABASE @database" ssql 命令上遇到了意外错误:

EDIT> I also get strage error on "CREATE DATABASE @database" ssql command:

'@userDatabase' 附近的语法不正确

'Incorrect syntax near '@userDatabase'

代码是这样的:

    var connString = "Server=localhost\\SQLEXPRESS;Integrated Security = SSPI; database = master";
                string cmdText = "CREATE DATABASE @userDatabase";
                using (var sqlConnection = new SqlConnection(connString))
                {
                    using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
                    {
                        sqlCmd.Parameters.Add("@userDatabase", System.Data.SqlDbType.NVarChar).Value = databaseName;=
                        sqlConnection.Open();
                        sqlCmd.ExecuteNonQuery();
                    }
                }

推荐答案

1. 使用参数化查询.
2. 使用 Select count(*) 而不是 Select *.
3. 使用 ExecuteScalar 而不是 ExecuteNonQuery
4. 注意代码上的注释，它们解释了我所做的更改.

1. Use parameterized queries.
2. Use Select count(*) instead of Select *.
3. Use ExecuteScalar instead of ExecuteNonQuery
4. Note the remarks on the code, they explain the changes I've made.

// No point of passing a bool if all you do is return it...
private bool CheckDatabase(string databaseName)
{
    // You know it's a string, use var
    var connString = "Server=localhost\\SQLEXPRESS;Integrated Security=SSPI;database=master";
    // Note: It's better to take the connection string from the config file.

    var cmdText = "select count(*) from master.dbo.sysdatabases where name=@database";

    using (var sqlConnection = new SqlConnection(connString))
    {
        using (var sqlCmd = new SqlCommand(cmdText, sqlConnection))
        {
            // Use parameters to protect against Sql Injection
            sqlCmd.Parameters.Add("@database", System.Data.SqlDbType.NVarChar).Value = databaseName;

            // Open the connection as late as possible
            sqlConnection.Open();
            // count(*) will always return an int, so it's safe to use Convert.ToInt32
            return Convert.ToInt32( sqlCmd.ExecuteScalar()) == 1;
        }
    }

}

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