在VB.Net中添加ORDER BY子句

Question

如何连接

ORDER BY

VB.NET中的子句？

以下是我尝试过的：

Using command As New SqlCommand()
command.Connection = conn
Dim parameterNames As New List(Of String)(dt_data.RowCount - 2)
For i As Integer = 0 To dt_data.RowCount - 3
Dim parameterName As String = "@meter_num_" & i
Dim meter_number As String = dt_data.Rows(i).Cells(3).Value
command.Parameters.AddWithValue(parameterName, meter_number)
parameterNames.Add(parameterName)
Next
command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cycle and meter_num IN ({0})", String.Join(",", parameterNames), ("ORDER BY Client_Name ASC"))
command.Parameters.AddWithValue("@cycle", cycle2last)
Dim da As New SqlDataAdapter(command)
Dim ds As New DataSet
da.Fill(ds, "customer")
Compare_Reading.dt_last2month.DataSource = ds.Tables(0)
End Using

我希望它像这个

Select * from table_name 
where column_name = @column and column2 = @column2 
ORDER BY column_name ASC

Answer 1

要在一条指令中做到这一点，需要替换

Compare_Reading.dt_last2month.DataSource = ds.Tables(0)

带有：

Compare_Reading.dt_last2month.DataSource = (From existing As DataRow In ds.Tables(0).Select Order By existing.Item("your_filed_name_to_order_here") Ascending).CopyToDataTable

Answer 2

List(of T(的一个好处是在添加项目之前不需要知道大小。它将根据需要进行扩展。因此，我从构造函数中删除了int32。

我认为您希望dt_data.RowCount -2在For循环中，否则将跳过最后一行。我把你的.AddWithValue改成了.Add。您必须检查数据库中的正确数据类型(我猜是这样(，并相应地调整代码。

我认为你的主要问题是String.Format。您添加了2个参数，但忽略了放入{1}。

我添加了一个Debug.Print，这样你就可以检查你的Select是否像你期望的那样。当您在调试中运行时，这将显示在即时窗口中。它不会影响发布版本。

您不需要DataAdapter或DataSet，只需要DataTable。在Using块之外分配.DataSource。

我不认为有任何关于sql注入的问题，因为您已经为所有变量正确地使用了参数。

Private Sub OPCode(cycle2last As String)
Dim dt As New DataTable
Using conn As New SqlConnection("Your connection string")
Using command As New SqlCommand()
command.Connection = conn
Dim parameterNames As New List(Of String)
For i As Integer = 0 To DataGridView1.RowCount - 2
Dim parameterName As String = "@meter_num_" & i
Dim meter_number As String = DataGridView1.Rows(i).Cells(3).Value.ToString
command.Parameters.Add(parameterName, SqlDbType.VarChar).Value = meter_number
parameterNames.Add(parameterName)
Next
command.CommandText = String.Format("SELECT * FROM customer WHERE cycle = @cycle and meter_num IN ({0}) {1}", String.Join(",", parameterNames), ("ORDER BY Client_Name ASC;"))
command.Parameters.Add("@cycle", SqlDbType.VarChar).Value = cycle2last
Debug.Print(command.CommandText)
conn.Open()
dt.Load(command.ExecuteReader)
End Using
End Using
DataGridView2.DataSource = dt
End Sub