sql查询中列的变量选择

Question

在ASP.NET中，‘select-查询’能像下面这样是动态的吗？这会抛出一个错误，这是如何实现的？

SelectCommand="SELECT TOP 30 [date],**[@tc]** FROM [tbl_TC] WHERE ([ID] = ?) ORDER BY [date]">
<SelectParameters>
**<asp:QueryStringParameter Name="@tc" QueryStringField="tc" />**
<asp:QueryStringParameter Name="ID" QueryStringField="pid" Type="String" />
</SelectParameters>

Answer 1

在SQL中，您不能这样做，更不用说ASP.NET了。您必须通过将列名插入字符串来生成SQL。

我不知道有什么方法可以以声明的方式完成--您可能需要在代码隐藏中完成绑定并构建SQL动态代码：

string SQL = "SELECT TOP 30 [date], [" + tc + "] FROM [tbl_TC] WHERE ([ID] = ?) ORDER BY [date]";

Answer 2

使用命令对象并将占位符文本替换为列名。

Dim sqlConnection1 As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader

Dim qryString = "SELECT TOP 30 [date],**[@tc]** FROM [tbl_TC] WHERE ([ID] = [?]) ORDER BY [date]"

qryString = qryString.Replace("**[@tc]**", "yourcolumnnameshere").replace("[?]", "datequerystringhere")

cmd.CommandText = qryString
cmd.CommandType = CommandType.Text
cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.

sqlConnection1.Close()

参考文献：http://msdn.microsoft.com/en-us/library/fksx3b4f.aspx