DataGridView查询数据

机房收费中查询记录，肯定会用到DataGridView，下面是我实现的一个小功能：查询学生上机记录。

一、时序图

二、代码

U层

Public Class frmOnlineQuery

    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click

        Dim offlineModel As New OffLineModel
        Dim onlinequeryBLL As New OnlineQueryBLL
        Dim strResult As String
        Dim strCardNo As String
        strCardNo = Trim(txtCardNo.Text)
        strResult = onlinequeryBLL.isExit(strCardNo)
        Select Case strResult
            Case "卡号不存在"
                MsgBox("卡号不存在")
                DataGridView1.DataSource = vbNull
                txtCardNo.Text = ""
            Case "卡号存在"
                
                Dim table As New DataTable

                table = onlinequeryBLL.getData(strCardNo)
                DataGridView1.AutoGenerateColumns = False
                DataGridView1.DataSource = table
                DataGridView1.Refresh()
                
        End Select
    End Sub

B层

Public Class OnlineQueryBLL
    
    Public Function isExit(ByVal strCardNo As String) As String
        Dim onlinequeryDAL As New OnlineQueryDAL
        Dim offlineModel As New OffLineModel

        If onlinequeryDAL.IsExit(strCardNo) = False Then
            Return ("卡号不存在")
        Else
            Return "卡号存在"
        End If

    End Function

    Public Function getData(ByVal strCardNo As String) As DataTable
        Dim onlinequeryDAl As New OnlineQueryDAL
        Dim datatable As New DataTable
        datatable = onlinequeryDAl.GetData(strCardNo)
        Return datatable
    End Function
End Class

D层

Imports Model
Imports System.Data.SqlClient
Imports System.Data.DataTable
Public Class OnlineQueryDAL
    Dim con As String = System.Configuration.ConfigurationSettings.AppSettings("con")
    ''' <summary>
    ''' 判断卡号是否存在
    ''' </summary>
    ''' <param name="strCardNo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExit(ByVal strCardNo As String) As Boolean
        Dim sqlconnection1 As SqlClient.SqlConnection '执行数据库连接
        Dim cmd As SqlClient.SqlCommand '使用command对象执行命令并返回

        sqlconnection1 = New SqlClient.SqlConnection(con)
        cmd = sqlconnection1.CreateCommand() '创建插入命令
        cmd.CommandText = "select * from T_OffLine where cardNo=@cardNo "
        cmd.CommandType = CommandType.Text

        cmd.Parameters.Add(New SqlClient.SqlParameter("@cardNo", strCardNo))
        sqlconnection1.Open()

        Dim reader As SqlDataReader
        reader = cmd.ExecuteReader()
        If reader.Read() Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' 从下机表中获取数据
    ''' </summary>
    ''' <param name="strCardNo">卡号</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetData(ByVal strCardNo As String) As DataTable

        Dim adapter As SqlDataAdapter
        adapter = New SqlDataAdapter("select * from T_OffLine where cardNo= '" & strCardNo & "'", con)
        Dim cmdBuilder As New SqlCommandBuilder(adapter)
        Dim table As New DataTable
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        adapter.Fill(table)
        Return table
    End Function
End Class

DataGridView这个控件的使用学习了一天，终于弄出来了，虽然用的不是很好但做出来了就很增长信心，以后许多重复的功能就容易上手多了