如何將查詢到的資料顯示在DataGridView中

標籤：datagridview   style   blog   class   code   c   

背景介紹：

資料庫中的T_Line_Info表中存放著學生上機的記錄，也就是我們需要查詢上機記錄的表，其中具體內容為：

介面設計如下：

右擊DataGridView控制項，選擇編輯列，設計它的列名。

代碼實現部分：

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 SubEnd 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 DataTableEnd 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 StringEnd 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

運行結果：