如何将表名传递给 SqlCommand? [英] How can I Pass a Table Name to 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/Data_manipulation_language" rel="noreferrer">数据操作语言 操作不是 数据定义语言操作.

即使您使用 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

也可以使用 using 语句处置你的 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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！