检查数据库是否存在MSSQL C＃

admin

检查数据库是否存在MSSQL C＃

sql

我有这样的代码：

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是正确的。

编辑>编辑：我也收到“ CREATE DATABASE @database” ssql命令的存储错误：

‘@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();
                    }
                }

阅读 154

2021-07-01

共1个答案

admin

使用参数化查询。
使用Select count(*)代替Select *。
使用ExecuteScalar代替ExecuteNonQuery

注意代码上的注释，它们解释了我所做的更改。

// 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;
    }
}

}

2021-07-01