c# - 如何使用 SqlCommand 以参数化数据库名称创建数据库？

Question

简而言之。我有两个简单的助手:

    private SqlCommand CreateCommand(string text)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = text;
        return cmd;
    }

    void SetParameter(SqlCommand cmd, string p, string dbName)
    {
        cmd.Parameters.Add(p, SqlDbType.NVarChar);
        cmd.Parameters[p].Value = dbName;
    }

这执行正常:

var cmd = CreateCommand("CREATE DATABASE Demo "+
            @"ON (FILENAME = N'c:\demo_data.mdf') "+ 
            @"LOG ON (FILENAME = N'c:\demo_data.mdf.LDF') "+
            "FOR ATTACH " +
            "GO");
cmd.ExecuteNonQuery();

但这不是:

string dataBaseAttachText = "CREATE DATABASE @dbname " +
                              "ON (FILENAME = @filename) " +
                              "LOG ON (FILENAME = @filenamelog) " +
                              "FOR ATTACH GO";
var cmd = CreateCommand(dataBaseAttachText);

SetParameter(cmd, "@dbname", "Demo");
SetParameter(cmd, "@filename", @"c:\demo_data.mdf");
SetParameter(cmd, "@filenamelog", @"c:\demo_data.mdf.LDF");

cmd.ExecuteNonQuery();

为什么？

Answer 1

DML 操作支持参数，DDL 操作不支持参数，DDL 操作没有执行计划。你将需要使用动态 SQL

DDL = 数据定义语言(创建、删除、更改....)

DML = 数据操作语言(选择、更新、删除、插入)