vb.net 使用 SQLDataReader 代替记录集
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2157143/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using SQLDataReader instead of recordset
提问by nerts
I am new to this and had this question. Can I use SQLDataReader instead of a Recordset. I want to achieve the following result in an SQLDataReader.
我对此很陌生，并有这个问题。我可以使用 SQLDataReader 而不是 Recordset。我想在 SQLDataReader 中实现以下结果。
Dim dbConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sqlstr As String = "SELECT Name,Status FROM table1 WHERE id=" + item_id.Value.ToString
rs.Open(SQL, dbConn)
While Not rs.EOF
txtName.Text = rs.Fields.Item("Name").Value
ddlstatus.SelectedIndex = 1
rs.MoveNext()
End While
rs.Close()
rs = Nothing
dbConn.Close()
dbConn = Nothing
Can I replace recordset with SQLDataReader and if I can can you please show me the changes in code?
我可以用 SQLDataReader 替换记录集，如果可以，请告诉我代码中的更改吗？
回答by Chris Haas
Its highly recommend that you use the usingpattern:
强烈建议您使用以下using模式：
Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"
Using Con As New SqlConnection(sConnection)
Con.Open()
Using Com As New SqlCommand("Select * From tablename", Con)
Using RDR = Com.ExecuteReader()
If RDR.HasRows Then
Do While RDR.Read
txtName.Text = RDR.Item("Name").ToString()
Loop
End If
End Using
End Using
Con.Close()
End Using
回答by Mitchel Sellers
You will have to swap out a few things, something similar to the following.
您将不得不更换一些东西，类似于以下内容。
Here is an example, you will need to modify this to meet your goal, but this shows the difference.
这是一个示例，您需要对其进行修改以满足您的目标，但这显示了差异。
I also recommend using a "Using" statement to manage the connection/reader. Also, a parameterized query.
我还建议使用“使用”语句来管理连接/阅读器。此外，参数化查询。
Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"
Dim objCommand As New SqlCommand
objCommand.CommandText = "Select * From tablename"
objCommand.Connection = New SqlConnection(sConnection)
objCommand.Connection.Open()
Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()
If objDataReader.HasRows Then
Do While objDataReader.Read()
Console.WriteLine(" Your name is: " & Convert.ToString(objDataReader(0)))
Loop
Else
Console.WriteLine("No rows returned.")
End If
objDataReader.Close()
objCommand.Dispose()
回答by Seth Moore
Dim rdrDataReader As SqlClient.SqlDataReader
Dim cmdCommand As SqlClient.SqlCommand
Dim dtsData As New DataSet
Dim dtbTable As New DataTable
Dim i As Integer
Dim SQLStatement as String
msqlConnection.Open()
cmdCommand = New SqlClient.SqlCommand(SQLStatement, msqlConnection)
rdrDataReader = cmdCommand.ExecuteReader()
For i = 0 To (rdrDataReader.FieldCount - 1)
dtbTable.Columns.Add(rdrDataReader.GetName(i), rdrDataReader.GetFieldType(i))
Next
dtbTable.BeginLoadData()
Dim values(rdrDataReader.FieldCount - 1) As Object
While rdrDataReader.Read
rdrDataReader.GetValues(values)
dtbTable.LoadDataRow(values, True)
End While
dtbTable.EndLoadData()
dtsData.Tables.Add(dtbTable)
msqlConnection.Close()
Return dtsData
