How to use SqlCommand to CREATE DATABASE with parameterized db name?
ado.net, c#, sql-server
Solution
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 = Data Definition Language (create, drop, alter....)
DML = Data Manipulation Language (select, update, delete, insert)
Problem
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; } ``` This executes OK: ``` 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(); ``` But this doesn't: ``` 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(); ``` Why?