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?
6条答案
按热度按时间xnifntxz1#
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)
42fyovps2#
Sadly you can accomplish this by wrapping your DDL operation in a DML operation.
bvjveswy3#
You can only use parameters in places where SQL Server supports them. Unfortunately SQL Server does not support parameterised
CREATE DATABASE
statements (although I have a feeling the filename parts may support parameters).You'll need to construct the SQL yourself:
CAUTION: this is susceptable to SQL-injection attacks so caremust be taken; if you don't trust the source of the database name, don't do this!
You'll need to make similar changes to the filename parts if those can't be parameterised either.
wwtsj6pe4#
As a bit of a combination of both Daniel's and Rich's answer. By running a DML query to
sp_executesql
you can have a dynamically built query, also by usingQUOTENAME
it should escape any attempts at sql injection someone may pass in.This should execute the following DML sql query with the proper parameters passed.
nkhmeac65#
I solved this problem by creating an extension method to wrap all entities appropriately.
Usage:
i1icjdpr6#
I solved this task by calling the build in stored precedure 'sp_executesql'. The connectionstring used to create DB points to 'master'. The complete SQL statement is part of parameter value: