如何将表名传递给SqlCommand？

问题描述：

我正在尝试通过 SqlCommand 将表名作为参数传递给查询，但似乎不起作用。
这是我的代码；

I am trying to pass a table name as a parameter to my query through SqlCommand but it doesn't seems to be working. Here is my code;

SqlConnection con = new SqlConnection( "server=.;user=sa;password=12345;database=employee" );
con.Open( );
SqlCommand cmd = new SqlCommand( "drop table @tbName" , con );
cmd.Parameters.AddWithValue( "@tbName" , "SampleTable" );
cmd.ExecuteNonQuery( );
con.Close( );

答

SqlCommand.Parameters 支持 数据处理语言 操作不 数据定义语言 操作。

SqlCommand.Parameters are supported for Data manipulation language operations not Data definition language operations.

即使使用DML，也无法参数化表名或列名等。您只能参数化值。

Even if you use DML, you can't parameterize your table names or column names etc.. You can parameterize only your values.

数据操作语言=

SELECT ... FROM ... WHERE ...
INSERT INTO ... VALUES ...
UPDATE ... SET ... WHERE ...
DELETE FROM ... WHERE ...

数据定义语言=

CREATE TABLE ... 
DROP TABLE ... ;
ALTER TABLE ... ADD ... INTEGER;

您不能使用带有参数的 DROP 语句。

You can't use DROP statement with parameters.

如果您确实必须使用drop语句，则可能需要在 SqlCommand 上使用字符串连接。（请注意 SQL注入。）您可能需要看看称为动态SQL

If you really have to use drop statement, you might need to use string concatenation on your SqlCommand. (Be aware about SQL Injection) You might need to take a look at the term called Dynamic SQL

也请使用 使用语句处理您的 SqlConnection 和 SqlCommand 喜欢；

Also use using statement to dispose your SqlConnection and SqlCommand like;

using(SqlConnection con = new SqlConnection(ConnectionString))
using(SqlCommand cmd = con.CreateCommand())
{
   cmd.CommandText = "drop table " + "SampleTable";
   con.Open()
   cmd.ExecuteNonQuery();
}

如何将表名传递给SqlCommand？

相关推荐