开发实践教程1：试卷生成系统6.4 考题管理（FormExam）

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

用户权限可新增题目，仅可查看、查询、修改、停用自己创建的题目，管理员权限则不限于自己的题目。

窗体设计如下：

图1-14

如果题目较多，一次性显示可能会影响性能和交互体验，因此在显示时对数据进行了分页。关于如何分页显示数据，请参看教程第19.5.3.7节《分页显示数据》。

通常情况下，sql查询分为四个部分：select、from、where、order by，因此每一次sql查询，实际就是这四部分的组合，在本节代码以及后面几节代码中将频繁地使用以下四个变量（对不同的查询可能会有不同变量名称），分别表示sql查询的四部分。

    Dim sqlQuerySelect As String

    Dim sqlQueryFrom As String

    Dim sqlQueryOrder As String

    Dim sqlQueryWhere As String

同时，定义变量 sqlFlag 来标识当前应该使用的sql查询语句。

为了方便用户操作，提供了双击datagridview中某行（MouseDoubleClick事件），直接跳出对应题目修改操作（FormExamSingle）窗口。

另外，考虑到直接删除题目，可能会造成保存试卷数据的混乱（通常是该试卷已经引用该题目），因此只能将题目停用。题目停用后，再试卷组卷时，将不会显示该题目，但已经引用该题目的试卷不受影响。

具体代码如下：

Imports System.Data.SqlClient


Public Class FormExam

    Dim connection As SqlConnection
    Dim ds As DataSet
    Dim adapter As SqlDataAdapter
    Dim dt As DataTable


    Const pagesize As Integer = 50
    Dim maxPage As Integer
    Dim currentPage As Integer

    Dim F_Main As FormMain

    Dim sqlMySelect As String
    Dim sqlMyFrom As String
    Dim sqlMyOrder As String
    Dim sqlMyWhere As String

    Dim sqlQuerySelect As String
    Dim sqlQueryFrom As String
    Dim sqlQueryOrder As String
    Dim sqlQueryWhere As String

    Dim sqlFlag As String

    Public customWhere As String

    Dim dgvCanResize As Boolean = False

    Private Sub FormExam_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        connection = New SqlConnection(databaseConnString)
        connection.Open()

        F_Main = CType(Me.MdiParent, FormMain)

        sqlMySelect = "SELECT 题表.编号, 考试类型表三级.类型名称, 题类型表.类型名称 as 题目类型, 题表.是否启用, 题表.题目 "
        sqlMyFrom = "FROM (题表 INNER JOIN ((考试类型表三级 INNER JOIN 考试类型表二级 ON 考试类型表三级.二级类型 = 考试类型表二级.编号) INNER JOIN 考试类型表一级 ON 考试类型表二级.一级类型 = 考试类型表一级.编号) ON 题表.考试类型 = 考试类型表三级.编号) INNER JOIN 题类型表 ON 题表.题类型 = 题类型表.编号 "
        sqlMyWhere = IIf(permissions = 0, " ", "where 题表.录入人ID=" & loginId & " ")
        sqlMyOrder = "order by 题表.录入时间 desc"

        sqlQuerySelect = "SELECT 题表.编号, 考试类型表三级.类型名称, 题类型表.类型名称 as 题目类型, 题表.是否启用, 题表.题目 "
        sqlQueryFrom = "FROM (题表 INNER JOIN ((考试类型表三级 INNER JOIN 考试类型表二级 ON 考试类型表三级.二级类型 = 考试类型表二级.编号) INNER JOIN 考试类型表一级 ON 考试类型表二级.一级类型 = 考试类型表一级.编号) ON 题表.考试类型 = 考试类型表三级.编号) INNER JOIN 题类型表 ON 题表.题类型 = 题类型表.编号 "
        sqlQueryWhere = ""
        sqlQueryOrder = "order by 题表.录入时间 desc"

        sqlFlag = "my"

        Call showGrid(sqlFlag)
    End Sub


    Public Sub showGrid(ByVal tableFlag As String)
        ds = New DataSet(tableFlag)
        Dim command As New SqlCommand()

        Dim sqlSelect As String = ""
        Dim sqlFrom As String = ""
        Dim sqlWhere As String = ""
        Dim sqlOrder As String = ""

        Select Case tableFlag
            Case "my"
                sqlSelect = sqlMySelect
                sqlFrom = sqlMyFrom
                sqlWhere = sqlMyWhere
                sqlOrder = sqlMyOrder

            Case "custom"
                sqlSelect = sqlQuerySelect
                sqlFrom = sqlQueryFrom
                sqlWhere = sqlQueryWhere
                sqlOrder = sqlQueryOrder

        End Select
        command.CommandText = "select count(*) " & sqlFrom & sqlWhere
        command.Connection = connection

        Dim count As Integer = command.ExecuteScalar


        F_Main.tsslInfo.Text = "数据总数：" & count & " 条"

        If count < pagesize Then
            tsbFirstPage.Enabled = False
            tsbPrevPage.Enabled = False
            tsbNextPage.Enabled = False
            tsbLastPage.Enabled = False
        Else
            tsbFirstPage.Enabled = True
            tsbPrevPage.Enabled = True
            tsbNextPage.Enabled = True
            tsbLastPage.Enabled = True
        End If

        command.CommandText = sqlSelect &
                        sqlFrom &
                        sqlWhere &
                        sqlOrder

        adapter = New SqlDataAdapter(command)

        maxPage = Math.Ceiling(count / pagesize)
        currentPage = 0
        adapter.Fill(ds, currentPage * pagesize, pagesize, tableFlag)
        dgv.DataSource = Nothing
        dgv.DataSource = ds.Tables(tableFlag)
        tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString

        dgv.Columns(0).Visible = False

        dgvCanResize = True

        If dgvCanResize = True Then dgv.Columns(4).Width = dgv.Width - 410
    End Sub

    Private Sub tsbMyExqm_Click(sender As Object, e As EventArgs) Handles tsbMyExqm.Click
        sqlFlag = "my"
        Call showGrid(sqlFlag)

        tsbAdd.Enabled = True
        tsbEdit.Enabled = True
        tsbNoUse.Enabled = True
    End Sub


    Private Sub tsbFirstPage_Click(sender As Object, e As EventArgs) Handles tsbFirstPage.Click
        currentPage = 0
        ds.Tables(sqlFlag).Clear()
        adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
        dgv.DataSource = ds.Tables(sqlFlag)

        tslRecord.Text = (1).ToString & "/" & maxPage.ToString
    End Sub
    Private Sub tsbPrevPage_Click(sender As Object, e As EventArgs) Handles tsbPrevPage.Click
        If currentPage = 0 Then
            MessageBox.Show("已经是第一页了")
            Exit Sub
        End If
        currentPage -= 1
        ds.Tables(sqlFlag).Clear()
        adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
        dgv.DataSource = ds.Tables(sqlFlag)
        tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
    End Sub
    Private Sub tsbNextPage_Click(sender As Object, e As EventArgs) Handles tsbNextPage.Click
        If currentPage = maxPage - 1 Then
            MessageBox.Show("已经是最后一页了")
            Exit Sub
        End If
        currentPage += 1
        ds.Tables(sqlFlag).Clear()
        Dim recordcount As Integer = adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
        dgv.DataSource = ds.Tables(sqlFlag)
        tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
    End Sub
    Private Sub tsbLastPage_Click(sender As Object, e As EventArgs) Handles tsbLastPage.Click
        currentPage = maxPage - 1
        ds.Tables(sqlFlag).Clear()
        adapter.Fill(ds, currentPage * pagesize, pagesize, sqlFlag)
        dgv.DataSource = ds.Tables(sqlFlag)
        tslRecord.Text = (currentPage + 1).ToString & "/" & maxPage.ToString
    End Sub


    Private Sub tsbQuery_Click(sender As Object, e As EventArgs) Handles tsbQuery.Click
        Dim fExamSingle As New FormExamQuery
        fExamSingle.ShowDialog(Me)

        If customWhere = "" Then
            Exit Sub
        End If

        sqlQueryWhere = "where " & customWhere & " "
        sqlFlag = "custom"

        Call showGrid(sqlFlag)
    End Sub

    Private Sub tsbAdd_Click(sender As Object, e As EventArgs) Handles tsbAdd.Click
        Dim fExamSingle As New FormExamSingle(0, 0)
        fExamSingle.ShowDialog()

        Call showGrid(sqlFlag)
    End Sub

    Private Sub tsbEdit_Click(sender As Object, e As EventArgs) Handles tsbEdit.Click
        If dgv.SelectedRows.Count = 0 Then
            MessageBox.Show("请先选择一道题目")
            Exit Sub
        End If

        Dim examId As Integer
        examId = CType(dgv.SelectedRows(0).Cells(0).Value, Integer)
        Dim fExamSingle As New FormExamSingle(examId, 1)
        fExamSingle.ShowDialog()
    End Sub

    Private Sub tsbNoUse_Click(sender As Object, e As EventArgs) Handles tsbNoUse.Click
        If dgv.SelectedRows.Count = 0 Then
            MessageBox.Show("请先选择一道题目")
            Exit Sub
        End If

        Dim examId As Integer
        examId = CType(dgv.SelectedRows(0).Cells(0).Value, Integer)

        If MessageBox.Show("确实要停用此题目吗？", "停用考题", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
            Exit Sub
        End If
        Dim command As New SqlCommand()
        command.Connection = connection
        command.CommandText = "update 题表 set 是否启用='否' where 编号=" & examId
        command.ExecuteNonQuery()

        dgv.SelectedRows(0).Cells(4).Value = "否"
        tsbNoUse.Enabled = False
        tsbUse.Enabled = True






    End Sub

    Private Sub tsbUse_Click(sender As Object, e As EventArgs) Handles tsbUse.Click
        If dgv.SelectedRows.Count = 0 Then
            MessageBox.Show("请先选择一道题目")
            Exit Sub
        End If

        Dim examId As Integer
        examId = CType(dgv.SelectedRows(0).Cells(0).Value, Integer)

        If MessageBox.Show("确实要启用此题目吗？", "启用考题", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
            Exit Sub
        End If

        Dim command As New SqlCommand()
        command.Connection = connection
        command.CommandText = "update 题表 set 是否启用='是' where 编号=" & examId
        command.ExecuteNonQuery()

        dgv.SelectedRows(0).Cells(4).Value = "是"
        tsbNoUse.Enabled = True
        tsbUse.Enabled = False
    End Sub

    Private Sub dgv_Resize(sender As Object, e As EventArgs) Handles dgv.Resize
        If dgvCanResize = True Then dgv.Columns(4).Width = dgv.Width - 410
    End Sub

    Private Sub tsbHome_Click(sender As Object, e As EventArgs) Handles tsbHome.Click
        F_Main.tsslInfo.Text = ""
        Me.Close()
    End Sub

    Private Sub dgv_MouseDoubleClick(sender As Object, e As MouseEventArgs) Handles dgv.MouseDoubleClick
        Dim dgvhti As DataGridView.HitTestInfo = dgv.HitTest(e.X, e.Y)
        Dim selectedIndex As Integer
        If dgvhti.Type = DataGridViewHitTestType.Cell Or dgvhti.Type = DataGridViewHitTestType.RowHeader Then
            selectedIndex = dgvhti.RowIndex
            Dim examId As Integer
            examId = CType(dgv.Rows(selectedIndex).Cells(0).Value, Integer)
            Dim fExamSingle As New FormExamSingle(examId, 1)
            fExamSingle.ShowDialog()
        Else
            Exit Sub
        End If
    End Sub

    Private Sub dgv_SelectionChanged(sender As Object, e As EventArgs) Handles dgv.SelectionChanged
        If dgv.SelectedRows.Count < 1 Then Exit Sub

        Dim SelectedRow As DataGridViewRow
        SelectedRow = dgv.SelectedRows(0)

        If SelectedRow.Cells(3).Value.ToString = "是" Then
            tsbNoUse.Enabled = True
            tsbUse.Enabled = False
        Else
            tsbNoUse.Enabled = False
            tsbUse.Enabled = True
        End If
    End Sub

End Class

由于.net平台下C#和vb.NET很相似，本文也可以为C#爱好者提供的参考。

学习更多vb.net知识，请参看 vb.net 教程 目录