vb.net 在 VB 中从 SQL 检索数据（第 2 部分）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14917829/
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
retrieving data from SQL in VB (part 2)
提问by morgred
I am trying to populate a listbox by retrieving data from a database through sql. I have asked this question earlier but i was using a different configuration and the one i'm using now isn't giving any results.
我正在尝试通过 sql 从数据库中检索数据来填充列表框。我之前问过这个问题，但我使用的是不同的配置，而我现在使用的配置没有给出任何结果。
retrieving data in VB from SQL
That is my old post. I will now provide the code to the newer version of my attempt.
那是我的旧帖子。我现在将向我尝试的较新版本提供代码。
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim conn As New SqlConnection
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
Dim dt As New DataTable
dt.Load(reader)
ListBox1.Items.Add(dt)
End Sub
End Class
If anyone would be willing to help me out i'd greatly appreciate it. If possible, use a practical approach when trying to enlighten me, as that works best.
如果有人愿意帮助我，我将不胜感激。如果可能，请在尝试启发我时使用实用的方法，因为这样效果最好。
edit 1
编辑 1
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connString As String = "Data Source=THE_SHOGUNATE\SQLEXPRESS;Initial Catalog=le_database;Integrated Security=True"
Dim conn As New SqlConnection(connString)
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
Dim dt As New DataTable
dt.Load(reader)
ListBox1.DataSource = dt
End Sub
End Class
With this code the listbox populates with 6 instances of "System.Data.DataRowView" strings, 6 being the number of items in my table. How do i get the actual values?
使用此代码，列表框会填充 6 个“System.Data.DataRowView”字符串实例，其中 6 个是我表中的项目数。我如何获得实际值？
回答by syed mohsin
You missed the connectionString
If you want to populate list from DB there are many ways
你错过了connectionString
如果你想从数据库填充列表有很多方法
With DataReader
使用数据阅读器
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Sourec=localhost;........."
Dim conn As New SqlConnection(connectionString)
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
/* As it is not working i commented this
listBox1.ItemsSource = dt; // use this instead of ListBox1.Items.Add(dt)
//because Add event add only one item in the list.
*/
Dim i As Integer
i=0
while reader.read()
listbox1.Items.Add(dr(i).ToString);
i++
End While
End Sub
End Class
With DataTable
带数据表
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Sourec=localhost;........."
Dim conn As New SqlConnection(connectionString)
conn.Open()
// Create new DataAdapter
SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c)
// Use DataAdapter to fill DataTable
DataTable dt = new DataTable();
a.Fill(dt);
ListBox1.DataSource = dt;
ListBox1.DataTextField = "name";
End Sub
End Class
EDIT:
Other parameters of connection string depends on your security and all that. You must see this link Connection strings for SQL Server 2008
编辑：
连接字符串的其他参数取决于您的安全性和所有这些。您必须看到此链接SQL Server 2008 的连接字符串
回答by rs.
Set the DisplayMemberproperty after DataSourcebinding:
绑定DisplayMember后设置属性DataSource：
ListBox1.DataSource = dt
ListBox1.DisplayMember="name"
回答by Andre Ranieri
The last solution I saw should work, but there are a couple important best practices to keep in mind regarding SQL Server.
我看到的最后一个解决方案应该可以工作，但是关于 SQL Server 有几个重要的最佳实践需要牢记。
1) Avoid Select * whenever possible, instead name your columns explicitly. Select * causes SQL to perform extra work if you do not intend to pull down all the columns in the table. It's also not future-proof, since a dba could add a VARBINARY(MAX) column in the future, and populate it with gigs worth of blob data, This scenario would make your query as written slow down substantially and unnecessarily.
1) 尽可能避免 Select *，而是明确命名您的列。如果您不打算下拉表中的所有列，则 Select * 会导致 SQL 执行额外的工作。它也不是面向未来的，因为 dba 可以在未来添加一个 VARBINARY(MAX) 列，并用大量的 blob 数据填充它，这种情况会使您的查询大大和不必要地变慢。
2) Always remember to close your SQLConnection when you're done with it. This will free up a SQL connection and resources.
2) 永远记住在完成 SQLConnection 后关闭它。这将释放 SQL 连接和资源。
if (cn.State != ConnectionState.Closed)
cn.Close();
Another cool trick is to use the USING directive which will dispose of the SqlConnection object when execution passes out of scope.
另一个很酷的技巧是使用 USING 指令，该指令将在执行超出范围时处理 SqlConnection 对象。
using (SqlConnection cn = new SqlConnection(sConnectionString))
{
if (cn.State != ConnectionState.Open)
cn.Open();
// add query code here.
if (cn.State != ConnectionState.Closed)
cn.Close();
}
don't forget to close your SqlDataReader after the read loop is complete.
if (!dr.IsClosed) dr.Close();
不要忘记在读取循环完成后关闭 SqlDataReader。
if (!dr.IsClosed) dr.Close();
I hope this helps.
我希望这有帮助。
Andre Ranieri
安德烈·拉涅利
