如何将表名传递给SqlCommand? [英] How can I Pass a Table Name to SqlCommand?

查看:99
本文介绍了如何将表名传递给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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆