frm_categorymanage.vb

图书管理系统 VB加SQL 数据库在文件里的DATA里 进到SQL里附加进去就可以使用了

        '
        Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "图书类别表", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("类别编号", "类别编号"), New System.Data.Common.DataColumnMapping("类别名称", "类别名称")})})
        Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT 类别编号, 类别名称 FROM 图书类别表"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO 图书类别表(类别编号, 类别名称) VALUES (@类别编号, @类别名称); SELECT 类别编号, 类别名称 FROM 图书类别表" & _
        " WHERE (类别编号 = @类别编号)"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@类别编号", System.Data.SqlDbType.VarChar, 10, "类别编号"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@类别名称", System.Data.SqlDbType.VarChar, 30, "类别名称"))
        '
        'SqlUpdateCommand1
        '
        Me.SqlUpdateCommand1.CommandText = "UPDATE 图书类别表 SET 类别编号 = @类别编号, 类别名称 = @类别名称 WHERE (类别编号 = @Original_类别编号) AND (类别" & _
        "名称 = @Original_类别名称); SELECT 类别编号, 类别名称 FROM 图书类别表 WHERE (类别编号 = @类别编号)"
        Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@类别编号", System.Data.SqlDbType.VarChar, 10, "类别编号"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@类别名称", System.Data.SqlDbType.VarChar, 30, "类别名称"))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_类别编号", System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "类别编号", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_类别名称", System.Data.SqlDbType.VarChar, 30, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "类别名称", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlDeleteCommand1
        '
        Me.SqlDeleteCommand1.CommandText = "DELETE FROM 图书类别表 WHERE (类别编号 = @Original_类别编号) AND (类别名称 = @Original_类别名称)"
        Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_类别编号", System.Data.SqlDbType.VarChar, 10, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "类别编号", System.Data.DataRowVersion.Original, Nothing))
        Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_类别名称", System.Data.SqlDbType.VarChar, 30, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "类别名称", System.Data.DataRowVersion.Original, Nothing))
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=""HTTP-66EFF41994"";packet size=4096;integrated security=SSPI;data s" & _
        "ource=""HTTP-66EFF41994"";persist security info=False;initial catalog=tushugl"
        '
        'Frm_CategoryManage
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
        Me.ClientSize = New System.Drawing.Size(512, 357)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.GroupBox1)
        Me.Name = "Frm_CategoryManage"
        Me.Text = "图书类别管理"
        Me.GroupBox1.ResumeLayout(False)
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private db As New DataBase
    Private ds As New DataSet
    Public bmData As BindingManagerBase

    '加载
    Private Sub Frm_CategoryManage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlConnection1.ConnectionString = "Data Source=" & compname & ";Integrated Security=SSPI;Initial Catalog=tushugl"
        ds = db.RunSelectDataSet("select * from 图书类别表")
        '定义一个DataGrid表的样式
        Dim MyTable As New DataGridTableStyle
        '设置DataGrid1中奇数行的背景颜色
        MyTable.AlternatingBackColor = Color.LightGray
        '不允许单击列标头对网格进行重新排序
        MyTable.AllowSorting = False
        '将表样式MyTable添加到DataGrid1中
        DataGrid1.TableStyles.Add(MyTable)
        MyTable.MappingName = ds.Tables(0).TableName
        DataGrid1.DataSource = ds.Tables(0)
        bmData = Me.BindingContext(ds.Tables(0))
    End Sub
    '首记录
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If bmData.Position <> 0 Then
            bmData.Position = 0
        End If
    End Sub
    '上一条
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If bmData.Position <> 0 Then
            bmData.Position -= 1
        End If
    End Sub
    '下一条
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        If bmData.Position <> bmData.Count - 1 Then
            bmData.Position += 1
        End If
    End Sub
    '尾记录
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        If bmData.Position <> bmData.Count - 1 Then
            bmData.Position = bmData.Count - 1
        End If
    End Sub
    '保存
    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        If ds.HasChanges Then
            '通过数据适配器把数据集中更新过的数据写回数据库
            Dim Row As Integer
            Row = DataGrid1.CurrentCell.RowNumber
            DataGrid1.CurrentCell = New DataGridCell(Row + 1, 0)
            Try
                SqlDataAdapter1.Update(ds.Tables(0))
                ds.AcceptChanges()
                MessageBox.Show("数据保存成功！", "提示", MessageBoxButtons.OK)
            Catch ex As System.Exception
                MessageBox.Show("数据保存失败！", "提示", MessageBoxButtons.OK)
            End Try
        Else
            MessageBox.Show("数据表无更新数据！", "提示", MessageBoxButtons.OK)
        End If
    End Sub
    '删除
    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        Dim MyRow As Integer = DataGrid1.CurrentRowIndex   '取得选中当前行的行号
        '设计方法：用函数Delete()删除记录
        If MessageBox.Show("你确要要删除此记录吗？", "确认", MessageBoxButtons.YesNo) = DialogResult.Yes Then
            Try
                '在数据集(即DataGrid1)中删除选中的当前行
                ds.Tables(0).Rows(MyRow).Delete()
            Catch ex As System.Exception
                MessageBox.Show("删除数据失败！", "提示", MessageBoxButtons.OK)
                Exit Sub
            End Try
        Else
            Exit Sub
        End If
        If ds.HasChanges Then
            Try
                SqlDataAdapter1.Update(ds.Tables(0))
                ds.AcceptChanges()
                MessageBox.Show("删除记录成功！", "提示", MessageBoxButtons.OK)
            Catch ex As System.Exception
                MessageBox.Show("删除记录失败！", "提示", MessageBoxButtons.OK)
            End Try
        Else
            MessageBox.Show("数据表无要删除的数据！", "提示", MessageBoxButtons.OK)
        End If
    End Sub
    '刷新
    Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        ds = db.RunSelectDataSet("select * from 图书类别表")
        DataGrid1.DataSource = ds.Tables(0)
    End Sub
    '退出
    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Me.Close()
    End Sub
End Class