综合查询.vb

本页只为图象处理爱好者交流之用, 未经本人同意, 请勿下载. 另,本书及所附source已于1999年由清华大学出版社出版,请支持正版书籍及软件,谢谢.

        '
        '综合查询
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(448, 413)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.GroupBox1, Me.DataGrid})
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "综合查询"
        Me.Text = "综合查询"
        CType(Me.DataGrid, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.objLib综合查询, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox1.ResumeLayout(False)
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim DataType(100) As String
    '该数组用来存储字段的数据类型
    Dim SqlString As String
    Dim OrderString As String
    Dim QueryString As String
    '这三个字符串变量用来存储查询语句
    'SqlString变量用来存储查询语句中除了ORDERBY子句之外的部分
    'OrderString变量用来存储ORDERBY子句
    'QueryString变量存储查询语句

    Sub QueryData(ByVal SqlString As String, ByVal OrderString As String)
        QueryString = SqlString
        If OrderString <> "" Then
            QueryString = SqlString + " Order By " + OrderString
        End If
    End Sub

    Public Sub LoadDataSet()
        '创建一个新数据集以保存从 FillDataSet 调用返回的记录。
        '使用了一个临时数据集，这是因为填充现有的数据集
        '需要重新绑定数据绑定。
        Dim objDataSetTemp As 图书馆管理.Lib综合查询
        objDataSetTemp = New 图书馆管理.Lib综合查询()
        Try
            '尝试填充临时数据集。
            Me.FillDataSet(objDataSetTemp)
        Catch eFillDataSet As System.Exception
            '在此处添加错误处理代码。
            Throw eFillDataSet
        End Try
        Try
            '清空数据集中的旧记录。
            objLib综合查询.Clear()
            '将记录合并到主数据集中。
            objLib综合查询.Merge(objDataSetTemp)
        Catch eLoadMerge As System.Exception
            '在此处添加错误处理代码。
            Throw eLoadMerge
        End Try
    End Sub

    Public Sub FillDataSet(ByVal dataSet As 图书馆管理.Lib综合查询)
        '在填充数据集前关闭约束检查。
        '这允许适配器填充数据集而不用考虑
        '表之间的依赖项。
        dataSet.EnforceConstraints = False
        Try
            '打开连接。
            If Me.OleDbConnection1.State <> ConnectionState.Closed Then
                Me.OleDbConnection1.Close()
            End If
            Me.OleDbConnection1.Open()
            '尝试通过 OleDbDataAdapter1 填充数据集。
            Me.OleDbDataAdapter1.Fill(dataSet)
            Me.OleDbDataAdapter2.Fill(dataSet)
            Me.OleDbDataAdapter3.Fill(dataSet)
            Me.OleDbDataAdapter4.Fill(dataSet)
            Me.OleDbDataAdapter5.Fill(dataSet)
        Catch fillException As System.Exception
            '在此处添加错误处理代码。
            Throw fillException
        Finally
            '重新打开约束检查。
            dataSet.EnforceConstraints = True
            '无论是否引发了异常都关闭连接。
            Me.OleDbConnection1.Close()
        End Try

    End Sub

    Private Sub btn_查询_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_查询.Click
        If DataType(Me.CmbBox查询依据.SelectedIndex) = "Int32" Then
            SqlString = "SELECT * FROM " + Me.CmbBox查询表.Text + " Where "
            SqlString = SqlString + Me.CmbBox查询依据.Text + " " + Me.CmbBox运算符.Text + " " + "(" + Me.Edit指定值.Text + ")"
        ElseIf DataType(Me.CmbBox查询依据.SelectedIndex) = "String" Then
            SqlString = "SELECT * FROM " + Me.CmbBox查询表.Text + " Where "
            SqlString = SqlString + Me.CmbBox查询依据.Text + " " + Me.CmbBox运算符.Text + " " + "'" + Me.Edit指定值.Text + "'"
        ElseIf DataType(Me.CmbBox查询依据.SelectedIndex) = "Date" Then
            SqlString = "SELECT * FROM " + Me.CmbBox查询表.Text + " Where "
            SqlString = SqlString + Me.CmbBox查询依据.Text + Me.CmbBox运算符.Text + "CDate(" + Me.Edit指定值.Text + ")"
        End If

        OrderString = Me.CmbBox排序依据.Text
        QueryData(SqlString, OrderString)

        If Me.CmbBox查询表.Text = "读者信息" Then
            Me.OleDbSelectCommand2.CommandText = QueryString
        End If
        If Me.CmbBox查询表.Text = "管理者信息" Then
            Me.OleDbSelectCommand3.CommandText = QueryString
        End If
        If Me.CmbBox查询表.Text = "借阅信息" Then
            Me.OleDbSelectCommand4.CommandText = QueryString
        End If
        If Me.CmbBox查询表.Text = "书籍信息" Then
            Me.OleDbSelectCommand5.CommandText = QueryString
        End If
        If Me.CmbBox查询表.Text = "出版社信息" Then
            Me.OleDbSelectCommand1.CommandText = QueryString
        End If

        Try
            '尝试加载数据集。
            Me.LoadDataSet()
            Me.DataGrid.DataMember = Me.CmbBox查询表.Text
        Catch eLoad As System.Exception
            '在此处添加错误处理代码。
            '显示错误信息（如果有）。
            System.Windows.Forms.MessageBox.Show(eLoad.Message)
        End Try
    End Sub

    Private Sub 综合查询_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.CmbBox查询表.Items.Add("读者信息")
        Me.CmbBox查询表.Items.Add("管理者信息")
        Me.CmbBox查询表.Items.Add("借阅信息")
        Me.CmbBox查询表.Items.Add("书籍信息")
        Me.CmbBox查询表.Items.Add("出版社信息")
        Me.CmbBox查询表.SelectedIndex = 0
    End Sub

    Private Sub CmbBox查询表_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmbBox查询表.SelectedIndexChanged

        Dim str_Table As String
        str_Table = Me.CmbBox查询表.SelectedItem
        If str_Table = "" Then Return
        Me.CmbBox查询依据.Items.Clear()
        Me.CmbBox排序依据.Items.Clear()

        Dim mySelectQuery As String = "SELECT * FROM " + str_Table
        Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, Me.OleDbConnection1)
        If Me.OleDbConnection1.State = ConnectionState.Closed Then
            Me.OleDbConnection1.Open()
        End If
        Dim myReader As OleDb.OleDbDataReader
        myReader = myCommand.ExecuteReader()
        Dim i As Integer
        For i = 0 To myReader.FieldCount - 1
            DataType(i) = myReader.GetFieldType(i).Name
            Me.CmbBox排序依据.Items.Add(myReader.GetName(i).ToString)
            Me.CmbBox查询依据.Items.Add(myReader.GetName(i).ToString)
        Next

        myReader.Close()

        Me.CmbBox查询依据.SelectedIndex = 0
        Me.CmbBox排序依据.SelectedIndex = 0
    End Sub
End Class