mysql - 从mysql获取数据到dataGrid

Question

如何将数据添加到 VB.net 中的 dataGrid View (我使用的是 WPF dataGrid)。这是一个有效的代码，该代码用于登录，我的应用程序可以访问数据库并且可以操作数据:

Public Function login(ByVal username As String, ByVal password As String)
        Try
            ManageConnection(False) 'Open connection'

            Dim strQuery As String = "SELECT * FROM `dbns`.`wrks` WHERE `username` = '" + username + "' and password = '" + password + "'"
            Dim SqlCmd As New MySqlCommand(strQuery, dbCon)
            Dim reader As MySqlDataReader = SqlCmd.ExecuteReader()

            If reader.HasRows Then
                Return True
            Else
                Return False
            End If
            reader.Close()
        Catch ex As MySqlException
            Console.WriteLine("Error: " & ex.ToString())
        Finally
            ManageConnection(True)
        End Try
        Return False
    End Function

我如何实现 SELECT * FROM xxxxx WHERE xxxx并填充

<DataGrid x:Name="dataGrid" HorizontalAlignment="Left" Margin="376,167,0,0" VerticalAlignment="Top" Height="100" Width="645"/>

这是我用于打开和关闭连接的子程序:

 Public Sub ManageConnection(ByVal CloseConnection As Boolean)
        Try
            'Pripremanje konekcije i upita'
            dbCon = New MySqlConnection("server=127.0.0.1;user id=root;password=*****;database=****")
            If CloseConnection = False Then
                If dbCon.State = ConnectionState.Closed Then _
                    dbCon.Open()
            Else
                dbCon.Close()
            End If
        Catch ex As Exception
            MsgBox("Nije moguća konekcija na udaljenu bazu podataka, koristi drugu opciju.")
        End Try

    End Sub

我想知道是否可以使用现有功能连接到数据库以满足数据网格的特定需求。例如:

Public Function testniQuery()
        Dim queryString As String =
  "SELECT * FROM table"
        Dim adapter As SqlDataAdapter = New SqlDataAdapter(
          queryString, connection)

        Dim customers As DataSet = New DataSet
        adapter.Fill(customers, "Customers")

    End Function

Answer 1

我不太了解 Visual Basic，因为我使用 C#，但公共(public)库运行时 (CLR) 使我更容易理解这两种语言。

当您连接到 MySQL 时，运行您的查询，您将使用 DataAdapter 从查询中获取数据。查询结果可以保存在DataTable 中，并且您可以轻松地将DataTable 绑定(bind)到DataGrid。

因此，您运行一个查询，就可以在代码中永久使用该表。

我做的是C#。请耐心等待 C# 代码。这是一个可以做到这一点的方法

public DataTable Login(string username, string password){
//"SELECT * FROM `dbns`.`wrks` WHERE `username` = '" + username + "' and password = '" + password + "'"
    string query = "SELECT * FROM wrks WHERE username = @username AND password = @password";
    DataTable table = new DataTable();
    using (MySQlConnection connection = new MySQlConnection(connectionString))
    {
         using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
    {
            adapter.SelectCommand.Parameters.AddWithValue("@username", username);
            adapter.SelectCommand.Parameters.AddWithValue("@password", password);
            adapter.Fill(table);
            return table;
        }
    }
}

返回的 DataTable 然后可以绑定(bind)到 DataGrid，如下所示:

dataGrid1.DataSource = Login("username", "password").DefaultView;
dataGrid1.DataBind();