如何将表名传递给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
支持 数据处理语言 操作不 数据定义语言 操作。
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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!