如何使用的SqlCommand来创建数据库参数数据库的名字吗？ [英] How to use SqlCommand to CREATE DATABASE with parameterized db name?

问题描述

要说得短。我有两个简单的助手：

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来创建数据库参数数据库的名字吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！