如何使用 SqlCommand 创建具有参数化数据库名称的数据库?

How to use SqlCommand to CREATE DATABASE with parameterized db name?(如何使用 SqlCommand 创建具有参数化数据库名称的数据库?)

问题描述

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

To put it short. I've got two simple helpers:

    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();

为什么?

推荐答案

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

Parameters are supported for DML operations not DDL operations, there are no execution plans for DDL operations. you will need to use dynamic SQL

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

DDL = Data Definition Language (create, drop, alter....)

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

DML = Data Manipulation Language (select, update, delete, insert)

这篇关于如何使用 SqlCommand 创建具有参数化数据库名称的数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！