如何将查询到的数据显示在DataGridView中

待我称王封你为后i 2022-08-27 05:40 155阅读 0赞

**背景介绍：**

数据库中的T\_Line\_Info表中存放着学生上机的记录，也就是我们需要查询上机记录的表，其中具体内容为：

![SouthEast][]

界面设计如下：

![SouthEast 1][]

右击DataGridView控件，选择编辑列，设计它的列名。

![SouthEast 2][]

**代码实现部分：**

U层：

Public Class frmQueryOnRecord
    
        Private Sub btn_Query_Click(sender As Object, e As EventArgs) Handles btn_Query.Click
            Dim student As New Login.Model.LineInfo
            Dim BUser As New Login.BLL.LoginManager
            '将输入的卡号传给实体
            student.cardno = txtcardno.Text
            DataGridView1.Visible = True
    
            '在数据库中查到该卡号的上机信息
            Dim uTable = BUser.QueryOnRecord(student)
            If uTable.Rows.Count > 0 Then
                '将数据显示在DataGridView中
    
                DataGridView1.Item(1, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("studentname")
                DataGridView1.Item(2, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("ondate")
                DataGridView1.Item(3, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("ontime")
                DataGridView1.Item(4, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("offdate")
                DataGridView1.Item(5, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("ontime")
                DataGridView1.Item(6, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("moeny")
                DataGridView1.Item(7, DataGridView1.NewRowIndex).Value = uTable.Rows(0)("consumemoney")
    
    
            Else
                '没有查到内容时
                DataGridView1.Rows.Clear()
                MsgBox("没有找到记录！")
            End If
        End Sub
    
    End Class

B层：

Public Function QueryOnRecord(student As Login.Model.LineInfo) As DataTable
            Dim f As New DFactory.DBFactory
            Dim istudent As Login.Interface.ILineInfo
    
            istudent = f.QueryOnlineInfo
            '接收DataTable
            Table = istudent.Query_OnRecord(student)
            '把Table返回到U层
            Return Table
    
    
        End Function

接口：

Public Interface ILineInfo
        '返回的类型是DataTable
        Function Query_OnRecord(student As Login.Model.LineInfo) As DataTable
    End Interface

实体：

Public Class LineInfo
        '与上机记录表中的字段一一对应
        Public cardno As String
        Public studentname As String
        Public ondate As String
        Public ontime As String
        Public offdate As String
        Public offtime As String
        Public money As String
        Public consumemoeny As String
    
    End Class

sqlHelper中的GetDataTable方法

Public Function GetDataTable(ByVal cmdTxt As String, ByVal cmdType As CommandType, ByVal paras As SqlParameter()) As DataTable
            Dim strConnStr As String = System.Configuration.ConfigurationManager.AppSettings("ConnStr")
            '定义一个数据库连接对象  
            Dim conn As SqlConnection = New SqlConnection(strConnStr)
            Dim cmd As New SqlCommand '定义一个命令对象  
            Dim adataset As DataSet
            Dim adaptor As SqlDataAdapter '定义一个适配器对象  
            cmd = New SqlCommand(cmdTxt, conn)
            adaptor = New SqlDataAdapter(cmd)
            adataset = New DataSet
            cmd.CommandType = cmdType
            cmd.Parameters.AddRange(paras)
            Try
                '打开数据库连接  
                If conn.State = ConnectionState.Closed Then
                    conn.Open()
    
                End If
                '填充数据集  
                adaptor.Fill(adataset)
                Return adataset.Tables(0)
            Catch ex As Exception
                MsgBox(ex.Message, , "数据库操作")
            Finally
                '关闭数据库连接  
                If conn.State = ConnectionState.Open Then
                    conn.Close()
    
                End If
            End Try
    
    
        End Function

D层：

Public Function QueryOnRecord(student As Login.Model.LineInfo) As DataTable Implements ILineInfo.Query_OnRecord
            '按卡号在上机记录表中查找
            Dim sql As String = "select * from T_Line_Info where cardno=" & student.cardno
            Dim cmd As SqlCommand = New SqlCommand(sql, conn)
            Dim helper As New Login.DAL.sqlHelper
            '设置参数
            Dim paras As SqlParameter() = {New SqlParameter("@cardno", student.cardno)}
            Try
                conn.Open()
                '调用sqlhelper中的GetDataTable方法
                Return helper.GetDataTable(sql, CommandType.Text, paras)
           
            Catch ex As Exception
                MsgBox(ex.Message, "")
    
            End Try
        End Function

运行结果：

![Center][]

[SouthEast]: /images/20220824/40f2758b010d44eebc6e438d414f0f15.png
[SouthEast 1]: /images/20220824/5231db5a01484f9da5070ab66d7b4ac8.png
[SouthEast 2]: /images/20220824/1f6c7ddf21b24898ace4454637204132.png
[Center]: /images/20220824/7637721fc4234ef5bc4ef8b4c293e65f.png