booksearch.vb

vb.net写的图书管理系统,功能完备,能正常使用,可用于二次开发.

        Me.Label1.AutoSize = True
        Me.Label1.Location = New System.Drawing.Point(24, 56)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(29, 17)
        Me.Label1.TabIndex = 27
        Me.Label1.Text = "书名"
        '
        'btnSearch
        '
        Me.btnSearch.Location = New System.Drawing.Point(464, 80)
        Me.btnSearch.Name = "btnSearch"
        Me.btnSearch.Size = New System.Drawing.Size(72, 24)
        Me.btnSearch.TabIndex = 26
        Me.btnSearch.Text = "查询"
        '
        'lsvResult
        '
        Me.lsvResult.Columns.AddRange(New System.Windows.Forms.ColumnHeader() {Me.ColumnHeader1, Me.ColumnHeader2, Me.ColumnHeader3, Me.ColumnHeader4, Me.ColumnHeader5})
        Me.lsvResult.Location = New System.Drawing.Point(8, 144)
        Me.lsvResult.Name = "lsvResult"
        Me.lsvResult.Size = New System.Drawing.Size(566, 224)
        Me.lsvResult.TabIndex = 28
        Me.lsvResult.View = System.Windows.Forms.View.Details
        '
        'ColumnHeader1
        '
        Me.ColumnHeader1.Text = "ID"
        '
        'ColumnHeader2
        '
        Me.ColumnHeader2.Text = "名称"
        Me.ColumnHeader2.Width = 140
        '
        'ColumnHeader3
        '
        Me.ColumnHeader3.Text = "作者"
        Me.ColumnHeader3.Width = 80
        '
        'ColumnHeader4
        '
        Me.ColumnHeader4.Text = "出版社"
        Me.ColumnHeader4.Width = 160
        '
        'ColumnHeader5
        '
        Me.ColumnHeader5.Text = "定价"
        '
        'btnCancel
        '
        Me.btnCancel.Location = New System.Drawing.Point(496, 376)
        Me.btnCancel.Name = "btnCancel"
        Me.btnCancel.Size = New System.Drawing.Size(80, 24)
        Me.btnCancel.TabIndex = 30
        Me.btnCancel.Text = "退出"
        '
        'btnSiMu
        '
        Me.btnSiMu.Location = New System.Drawing.Point(424, 376)
        Me.btnSiMu.Name = "btnSiMu"
        Me.btnSiMu.Size = New System.Drawing.Size(72, 24)
        Me.btnSiMu.TabIndex = 29
        Me.btnSiMu.Text = "结果分析"
        '
        'BookSearch
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(585, 405)
        Me.Controls.Add(Me.btnCancel)
        Me.Controls.Add(Me.btnSiMu)
        Me.Controls.Add(Me.lsvResult)
        Me.Controls.Add(Me.GroupBox1)
        Me.Name = "BookSearch"
        Me.Text = "书籍信息查询"
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim sql As String
    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim strSQL As String
        Dim mydet As DataSet
        '检查输入值的合法性
        If Not checkData() Then
            Exit Sub
        End If
        '得到SQL语句
        strSQL = getSql()
        '若SQL语句为空，退出
        If strSQL = "" Then
            Exit Sub
        End If
        '执行SQL语句，取出结果集
        mydet = DBManager.executeSelectQuery(strSQL, "BookInfo")
        '如果结果集为空，退出
        If mydet Is Nothing OrElse mydet.Tables(0).Rows.Count = 0 Then
            InfoMSG("没有符合要求的记录")
            Exit Sub
        End If
        '清空显示列表
        lsvResult.Items.Clear()
        btnSiMu.Enabled = True
        Dim item As ListViewItem
        Dim subItem1 As ListViewItem.ListViewSubItem
        Dim subItem2 As ListViewItem.ListViewSubItem
        Dim subItem3 As ListViewItem.ListViewSubItem
        Dim subItem4 As ListViewItem.ListViewSubItem

        Dim i As Integer
        Dim str As String
        '显示书籍信息
        For i = 0 To mydet.Tables(0).Rows.Count - 1
            With mydet.Tables(0).Rows(i)
                item = New ListViewItem
                subItem1 = New ListViewItem.ListViewSubItem
                subItem2 = New ListViewItem.ListViewSubItem
                subItem3 = New ListViewItem.ListViewSubItem
                subItem4 = New ListViewItem.ListViewSubItem
                'ISBN
                item.Text = .Item("ISBN") & ""
                '书籍名称
                subItem1.Text = .Item("NAME") & ""
                '作者
                subItem2.Text = .Item("AUTHOR") & ""
                '出版社
                subItem3.Text = .Item("PRESS") & ""
                '价格
                subItem4.Text = .Item("PRICE") & ""
            End With
            item.SubItems.Add(subItem1)
            item.SubItems.Add(subItem2)
            item.SubItems.Add(subItem3)
            item.SubItems.Add(subItem4)
            lsvResult.Items.Add(item)
        Next
    End Sub

    Private Function getSql() As String
        Dim querySql As String
        '不同条件间的逻辑关系
        Dim logic As String
        '如果选中“或关系”，则不同条件间的逻辑关系为“或”；否则为“与”
        If chkOr.Checked Then
            logic = " or "
        Else
            logic = " and "
        End If
        '基本SQL语句
        querySql = "select ISBN,Name,Author,Type,Press,Price,PressDate from BookInfo where "
        'ISBN条件
        If txtBookISBN.Text.Trim <> "" Then
            querySql = querySql + "ISBN=" + txtBookISBN.Text.Trim + logic
        End If
        '书籍名称条件
        If txtBookName.Text.Trim <> "" Then
            '根据是否模糊查询生成不同SQL语句
            If chkFuzzy.Checked Then
                querySql = querySql + "Name like '%" + txtBookName.Text.Trim + "%'" + logic
            Else
                querySql = querySql + "Name= '" + txtBookName.Text.Trim + "'" + logic
            End If
        End If
        '作者条件
        If txtAuthorName.Text.Trim <> "" Then
            '根据是否模糊查询生成不同SQL语句
            If chkFuzzy.Checked Then
                querySql = querySql + "Author like '%" + txtAuthorName.Text.Trim + "%'" + logic
            Else
                querySql = querySql + "Author= '" + txtAuthorName.Text.Trim + "'" + logic
            End If
        End If
        '书籍类型条件
        If cmbBookType.Text.Trim <> "" Then
            querySql = querySql + "Type=" + CStr(cmbBookType.SelectedValue) + logic
        End If
        '出版社条件
        If txtPress.Text.Trim <> "" Then
            '根据是否模糊查询生成不同SQL语句
            If chkFuzzy.Checked Then
                querySql = querySql + "Press like '%" + txtPress.Text.Trim + "%'" + logic
            Else
                querySql = querySql + "Press= '" + txtPress.Text.Trim + "'" + logic
            End If
        End If
        '价格条件
        '同时有最高价格和最低价格
        If txtFromPrice.Text.Trim <> "" And txtToPrice.Text.Trim <> "" Then
            querySql = querySql + "(Price>=" + txtFromPrice.Text.Trim + " and Price<=" + txtToPrice.Text.Trim + ") " + logic
            '只有最低价格
        ElseIf txtFromPrice.Text.Trim <> "" Then
            querySql = querySql + "Price>=" + txtFromPrice.Text.Trim + logic
            '只有最高价格
        ElseIf txtToPrice.Text.Trim <> "" Then
            querySql = querySql + "Price<=" + txtToPrice.Text.Trim + logic
        End If
        '去掉SQL语句末端的逻辑操作符
        querySql = querySql.Substring(0, querySql.Length - logic.Length)
        '加上排序语句
        querySql = querySql + " order by ISBN"
        Return querySql
    End Function
    Private Function checkData() As Boolean
        '输入值是否为空
        If txtBookISBN.Text.Trim = "" And txtBookName.Text.Trim = "" And txtAuthorName.Text.Trim = "" And cmbBookType.Text.Trim = "" And txtPress.Text.Trim = "" And txtFromPrice.Text.Trim = "" And txtToPrice.Text.Trim = "" Then
            InfoMSG("查询条件不能为空")
            Return False
        End If
        '按价格查询时，最低价格是否为数字
        If txtFromPrice.Text.Trim <> "" And Not IsNumeric(txtFromPrice.Text.Trim) Then
            ErrorMSG("定价必须为数字！")
            Return False
        End If
        '按价格查询时，最高价格是否为数字
        If txtToPrice.Text.Trim <> "" And Not IsNumeric(txtToPrice.Text.Trim) Then
            ErrorMSG("定价必须为数字！")
            Return False
        End If
        Return True
    End Function
    Private Sub btnSiMu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSiMu.Click
        Dim frm As New BookInfoSimulation(sql)
        frm.ShowDialog()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub BookSearch_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '初始化时没有查询结果，设置【结果分析】按钮不可用
        btnSiMu.Enabled = False

        Dim myType As DataSet
        Dim strSQL As String
        '填充书籍类别信息
        strSQL = "select code , name from BookType"
        myType = DBManager.executeSelectQuery(strSQL, "BookType")
        cmbBookType.DataSource = myType.Tables("BookType")
        cmbBookType.DisplayMember = "name"
        cmbBookType.ValueMember = "code"
        cmbBookType.SelectedItem = Nothing
    End Sub
End Class