sql.txt

一个基于Client/Server模式的高校图书管理系统的设计与实现。运用Visual Basic.Net结合Microsoft SQL Server 2000开发的登录模块主要用于验证用户身份

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        '首先清除控件中的内容然后调用AddNew方法添加新记录如果出错显示信息
        '调用函数显示记录数和当前记录位置
        Try
            Me.BindingContext(objLib读者信息, "读者信息").EndCurrentEdit()
            Me.BindingContext(objLib读者信息, "读者信息").AddNew()
        Catch eEndEdit As System.Exception
            System.Windows.Forms.MessageBox.Show(eEndEdit.Message)
        End Try
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        '将在窗体中对数据的更改保存到数据库中
        Try
            Me.UpdateDataSet()
        Catch eUpdate As System.Exception
            System.Windows.Forms.MessageBox.Show(eUpdate.Message)
        End Try
        Me.objLib读者信息_PositionChanged()
    End Sub
    Public Sub UpdateDataSet()
        Dim objDataSetChanges As 图书馆管理.Lib读者信息 = _
        New 图书馆管理.Lib读者信息
        '创建一个新数据集来保存对主数据集所做的更改
        Me.BindingContext(objLib读者信息, "读者信息").EndCurrentEdit()
        '停止当前的任何编辑
        objDataSetChanges = CType(objLib读者信息.GetChanges, 图书馆管理.Lib读者信息)
        If (Not (objDataSetChanges) Is Nothing) Then
            Try
                Me.UpdateDataSource(objDataSetChanges)
                '调用UpdateDataSource函数将更改合并到数据集中
                objLib读者信息.Merge(objDataSetChanges)
                objLib读者信息.AcceptChanges()
            Catch eUpdate As System.Exception
                Throw eUpdate
            End Try
        End If
    End Sub
    Public Sub UpdateDataSource(ByVal ChangedRows As 图书馆管理.Lib读者信息)
        '打开数据连接，使用OleDbDataAdapter的Update方法更新数据库
        Try
            If (Not (ChangedRows) Is Nothing) Then
                Me.OleDbConnection1.Open()
                Me.OleDbDataAdapter1.Update(ChangedRows)
            End If
        Catch updateException As System.Exception
            Throw updateException
        Finally
            Me.OleDbConnection1.Close()
        End Try
    End Sub
    Private Sub btnNavFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavFirst.Click
        Me.BindingContext(objLib读者信息, "读者信息").Position = 0
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.BindingContext(objLib读者信息, "读者信息").CancelCurrentEdit()
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        If (Me.BindingContext(objLib读者信息, "读者信息").Count > 0) Then
            Me.BindingContext(objLib读者信息, "读者信息").RemoveAt _
            (Me.BindingContext(objLib读者信息, "读者信息").Position)
        End If
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        Me.BindingContext(objLib读者信息, "读者信息").Position = _
        (Me.BindingContext(objLib读者信息, "读者信息").Count - 1)
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnNavPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavPrev.Click
        Me.BindingContext(objLib读者信息, "读者信息").Position = _
        (Me.BindingContext(objLib读者信息, "读者信息").Position - 1)
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnNavNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNavNext.Click
        Me.BindingContext(objLib读者信息, "读者信息").Position = _
        (Me.BindingContext(objLib读者信息, "读者信息").Position + 1)
        Me.objLib读者信息_PositionChanged()
    End Sub
    Private Sub btnCancelAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancelAll.Click
        Me.objLib读者信息.RejectChanges()
    End Sub
End Class
**鉴于“读者信息”，“管理者信息”，“出版社信息”，“书籍信息”，“借阅信息”模块除连接数据源不同外，设计思路步骤均相同，此处省略“管理者信息”，“出版社信息”，“书籍信息”，“借阅信息”四个模块的代码。
附1.3管理者信息窗体设计代码（略）
附1.4出版社信息窗体设计代码（略）
附1.5书籍信息窗体设计代码如（略）
附1.6借阅信息窗体设计代码如（略）
附1.7综合查询窗体设计代码如下：
Public Class 综合查询
    Inherits System.Windows.Forms.Form
#Region " Windows 窗体设计器生成的代码 "
‘此处省略窗体设计代码
#End Region
    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()
        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()
            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
    Dim DataType(100) As String
    '该数组用来存储字段的数据类型
    Dim SqlString As String
    Dim OrderString As String
    Dim QueryString As String
    '这三个字符串变量用来存储查询语句
    'SqlString变量用来存储查询语句中除了ORDER BY子句之外的部分
    'OrderString变量用来存储ORDER BY子句
    'QueryString变量存储查询语句
    Private Sub 综合查询_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '初始化列表“cmbBox查询表”
        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
        '在“cmbBox查询表”中选择一项作为查询表
        '则在“cmbBox查询依据”“cmbBox排序依据”将自动填充
        '查询表中的所有字段
        '保持数据表与字段二者的一致性
        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
    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.OleDbInsertCommand2.CommandText = QueryString
        End If
        If Me.cmbBox查询表.Text = "管理者信息" Then
            Me.OleDbInsertCommand3.CommandText = QueryString
        End If
        If Me.cmbBox查询表.Text = "借阅信息" Then
            Me.OleDbInsertCommand4.CommandText = QueryString
        End If
        If Me.cmbBox查询表.Text = "书籍信息" Then
            Me.OleDbInsertCommand5.CommandText = QueryString
        End If
        If Me.cmbBox查询表.Text = "出版社信息" Then
            Me.OleDbInsertCommand1.CommandText = QueryString
        End If
        Try
            Me.LoadDataSet()			 '尝试加载数据集
            Me.DataGrid1.DataMember = Me.cmbBox查询表.Text
        Catch eLoad As System.Exception
            System.Windows.Forms.MessageBox.Show(eLoad.Message)
        End Try
    End Sub
End Class