数据库开发035我的笔记本

平时喜欢读点历史，或人文地理方面的东东。苦于记忆力有限，所以做了一个简单的日记本，也许能治治我的老年痴呆。

创建一个数据库

 

打开VS2010，创建一个类

Imports System.Data.SqlClient
Public Class note
    '编号
    Private _id As String
    Public Property id() As String
        Get
            Return _id
        End Get
        Set(ByVal value As String)
            _id = value
        End Set
    End Property
    'time
    Private _time As String
    Public Property time() As String
        Get
            Return _time
        End Get
        Set(ByVal value As String)
            _time = value
        End Set
    End Property
    'dynasty
    Private _dynasty As String
    Public Property dynasty() As String
        Get
            Return _dynasty
        End Get
        Set(ByVal value As String)
            _dynasty = value
        End Set
    End Property
    'address
    Private _address As String
    Public Property address() As String
        Get
            Return _address
        End Get
        Set(ByVal value As String)
            _address = value
        End Set
    End Property
    'person
    Private _person As String
    Public Property person As String
        Get
            Return _person
        End Get
        Set(ByVal value As String)
            _person = value
        End Set
    End Property
    'story
    Private _story As String
    Public Property story As String
        Get
            Return _story
        End Get
        Set(ByVal value As String)
            _story = value
        End Set
    End Property

    '获取连接对象
    Public Function GetConnection() As sqlConnection
        Return New SqlConnection(My.Settings.historynoteConnectionString)
    End Function

    '添加
    Public Sub Insert()
        Dim conn As sqlConnection = GetConnection()
        Try
            conn.Open()
            Dim sql As String = _
                "INSERT INTO note(time, dynasty, address, person, story) " & _
                "VALUES(@time,@dynasty,@address,@person,@story)"
            Dim cmd As sqlCommand = New sqlCommand(sql, conn)

            Dim p1 As sqlParameter = New sqlParameter("@time", time)
            Dim p2 As sqlParameter = New sqlParameter("@dynasty", dynasty)
            Dim p3 As sqlParameter = New sqlParameter("@address", address)
            Dim p4 As sqlParameter = New sqlParameter("@person", person)
            Dim p5 As sqlParameter = New sqlParameter("@story", story)

            cmd.Parameters.Add(p1)
            cmd.Parameters.Add(p2)
            cmd.Parameters.Add(p3)
            cmd.Parameters.Add(p4)
            cmd.Parameters.Add(p5)

            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            conn.Close()
        End Try
    End Sub

    '删除
    Public Sub Delete(ByVal id As String)
        Dim conn As sqlConnection = GetConnection()
        Try
            conn.Open()
            Dim sql As String = String.Format("DELETE FROM note WHERE id='{0}'", id)
            Dim cmd As sqlCommand = New sqlCommand(sql, conn)
            cmd.ExecuteNonQuery()

            'Catch ex As Exception
        Finally
            conn.Close()
        End Try
    End Sub

    '更新
    Public Sub Update()
        Dim conn As sqlConnection = GetConnection()
        Try
            conn.Open()
            Dim sql As String = _
            "UPDATE note " & _
            "SET time=@time,dynasty=@dynasty,address=@address, person=@person,story=@story " & _
            "WHERE id=" & id.ToString
            Dim cmd As sqlCommand = New sqlCommand(sql, conn)

            Dim p1 As sqlParameter = New sqlParameter("@time", time)
            Dim p2 As sqlParameter = New sqlParameter("@dynasty", dynasty)
            Dim p3 As sqlParameter = New sqlParameter("@address", address)
            Dim p4 As sqlParameter = New sqlParameter("@person", person)
            Dim p5 As sqlParameter = New sqlParameter("@story", story)

            cmd.Parameters.Add(p1)
            cmd.Parameters.Add(p2)
            cmd.Parameters.Add(p3)
            cmd.Parameters.Add(p4)
            cmd.Parameters.Add(p5)

            cmd.ExecuteNonQuery()
            'Catch ex As Exception
        Finally
            conn.Close()
        End Try
    End Sub

    '获取指定记录
    Public Function GetnoteFromStory(ByVal str As String) As DataTable
        Dim conn As SqlConnection = GetConnection()
        Dim dt As New DataTable

        Try
            conn.Open()
            Dim sql As String = String.Format("SELECT * FROM note WHERE story like '%" & str & "%'")
            Dim cmd As SqlCommand = New SqlCommand(sql, conn)
            Dim dr As SqlDataReader = cmd.ExecuteReader
            dt.Load(dr)
        Finally
            conn.Close()
        End Try

        Return dt
    End Function

    '获取指定记录
    Public Sub GetnoteFromId(ByVal str As String)
        Dim conn As SqlConnection = GetConnection()
        Dim dt As New DataTable

        Try
            conn.Open()
            Dim sql As String = String.Format("SELECT * FROM note WHERE id={0}", id)
            Dim cmd As SqlCommand = New SqlCommand(sql, conn)
            Dim dr As SqlDataReader = cmd.ExecuteReader
            If dr.Read Then
                id = dr("id")
                time = dr("time")
                dynasty = dr("dynasty")
                address = dr("address")
                person = dr("person")
                story = dr("story")
            End If
        Finally
            conn.Close()
        End Try

    End Sub
    '获取所有成员
    Public Function GetAll() As DataTable
        Dim conn As sqlConnection = GetConnection()
        conn.Open()
        Dim sqlStr As String = "SELECT * FROM note"
        Dim myDataAdapter As sqlDataAdapter = New sqlDataAdapter(sqlStr, conn)
        Dim dt As New DataTable
        myDataAdapter.Fill(dt)
        conn.Close()
        Return dt
    End Function

End Class

客户端界面

Imports 读史笔记.note
Imports System.Data.SqlClient
Public Class Form1

    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet           '必须加“NEW”关键字
    Dim dt As DataTable

    Private Function GetConnection() As SqlConnection
        Return New sqlConnection(My.Settings.HistoryNoteConnectionString)
    End Function

    Private Sub DisplayData1(ByVal str As String, ByVal dgv As DataGridView) '用DataReader对象
        datagridview1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        Dim comm As sqlCommand = New sqlCommand(str, conn)
        Dim dr As sqlDataReader = comm.ExecuteReader
        dt = New DataTable
        dt.Load(dr)
        conn.Close()

        dgv.DataSource = dt
    End Sub

    Private Sub DisplayData2(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        da = New sqlDataAdapter(str, conn)
        da.Fill(ds, "dt")

        conn.Close()

        DataGridView1.DataSource = ds.Tables("dt")
    End Sub

    Private Sub DisplayData3(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        datagridview1.DataSource = Nothing

        conn = GetConnection()
        Dim comm As New sqlCommand(str, conn)
        da = New sqlDataAdapter()
        da.SelectCommand = comm
        conn.Open()

        ds = New DataSet
        da.Fill(ds, "note")
        conn.Close()

        datagridview1.DataSource = ds.Tables("note")

    End Sub

    Private Sub DisplayData4(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New sqlDataAdapter("Select * from note", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        dgv.ColumnHeadersVisible = True
        dgv.RowHeadersVisible = True

        conn.Close()
    End Sub

    Private Sub UpdateData()
        conn = GetConnection()
        conn.Open()
        Dim comm As New sqlCommand
        Dim trans As sqlTransaction
        trans = conn.BeginTransaction
        comm.Connection = conn
        comm.Transaction = trans
        Try
            comm.CommandText = "Update note set person=95 Where 姓名 like '%周%'"
            comm.ExecuteNonQuery()
            comm.CommandText = "Update note set person=65 Where 姓名 like '%张%'"
            comm.ExecuteNonQuery()

            trans.Commit()
            'Label1.Text = "事务运行成功"
        Catch ex As Exception
            trans.Rollback()
            'Label1.Text = "有错"
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub InsertRecord1()
        'conn = GetConnection()
        'conn.Open()
        'Dim strsql As String = "Insert into grade(学号,姓名,语文,数学,英语) " & _
        '"Values('" & txtId.Text & "','" & txtName.Text & "','" & _
        'txtChinese.Text & "','" & txtMaths.Text & "','" & txtEnglish.Text & "')"
        'Dim comm As New sqlCommand(strsql, conn)
        'comm.ExecuteNonQuery()
        'conn.Close()

    End Sub

    Private Sub InserRecord2()
        conn = GetConnection()

        Dim strInsertsql As String = "Insert into grade(学号,姓名,数学) Values(27,'于谦',59)"
        Dim strUpdatesql As String = "Update grade Set 数学=60 where 学号=27"
        Dim strSelectsql As String = "Select * From grade where 学号='27'"

        Dim InsertComm As New sqlCommand(strInsertsql, conn)
        Dim UpdateComm As New sqlCommand(strUpdatesql, conn)
        Dim SelectComm As New sqlCommand(strSelectsql, conn)

        da = New sqlDataAdapter
        conn.Open()

        da.InsertCommand = InsertComm
        da.UpdateCommand = UpdateComm
        da.SelectCommand = SelectComm

        ds = New DataSet

        da.InsertCommand.ExecuteNonQuery()
        da.Fill(ds, "grade1")
        da.UpdateCommand.ExecuteNonQuery()
        da.Fill(ds, "grade2")
        da.Fill(ds, "grade3")

        '显示插入结果
        'DataGridView2.DataSource = ds.Tables("grade1")
        '显示更新结果
        'DataGridView3.DataSource = ds.Tables("grade2")
        '显示查询结果
        'DataGridView4.DataSource = ds.Tables("grade3")

        conn.Close()

    End Sub

    Private Sub RunningCreateDataSet(ByVal dgv As DataGridView)
        Dim myds As New DataSet
        Dim mydt As New DataTable("Squares")
        Dim mydr As DataRow
        Dim i As Integer
        mydt.Columns.Add(New DataColumn("数字", GetType(Integer)))
        mydt.Columns.Add(New DataColumn("平方", GetType(Integer)))
        For i = 0 To 10
            mydr = mydt.NewRow
            mydr(0) = i
            mydr(1) = i * i
            mydt.Rows.Add(mydr)
        Next
        myds.Tables.Add(mydt)
        dgv.DataSource = myds.Tables("Squares")

        myds.WriteXml("Squares.xml")
    End Sub

    Private Sub SortDataView(ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()
        da = New sqlDataAdapter
        ds = New DataSet
        Dim strsql As String = "Select * from grade"
        Dim comm As New sqlCommand(strsql, conn)
        da.SelectCommand = comm
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"))
        dv.RowFilter = "语文>60"
        dv.Sort = "学号 asc,语文 desc"

        Label1.Text = "满足条件的记录有：" + dv.Count.ToString + "条"

        dgv.DataSource = dv

        conn.Close()
    End Sub

    Private Sub SearchfromDataView()
        conn = GetConnection()
        conn.Open()
        Dim strsql As String = "Select * from Grade"
        Dim comm As New sqlCommand(strsql, conn)
        da = New sqlDataAdapter()
        da.SelectCommand = comm
        ds = New DataSet
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"), "", "姓名", DataViewRowState.CurrentRows)
        'Dim rowIndex As Integer = dv.Find(TextBox1.Text)
        'If rowIndex = -1 Then
        'Label2.Text = "没找到"
        'Else
        'Label2.Text = "姓名=" & dv(rowIndex)("姓名").ToString & Space(5) & _
        '"语文=" & dv(rowIndex)("语文").ToString & Space(5) & _
        ' "数学=" & dv(rowIndex)("数学").ToString & Space(5) & _
        '"英语=" & dv(rowIndex)("英语").ToString

        'End If

        conn.Close()
    End Sub

    Private Sub myFilter(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New sqlDataAdapter("Select * from grade order by 数学 asc", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        conn.Close()

    End Sub

    '设置控件可用
    Private Sub setControlEnable()
        txtTime.Enabled = True
        txtDynasty.Enabled = True
        txtAddress.Enabled = True
        txtPerson.Enabled = True
        txtStory.Enabled = True
    End Sub
    '设置控件不可用
    Private Sub SetControlUnable()
        txtTime.Enabled = False
        txtDynasty.Enabled = False
        txtAddress.Enabled = False
        txtPerson.Enabled = False
        txtStory.Enabled = False
    End Sub


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SetControlUnable()

        Dim myNote As New note
        Dim dt As New DataTable
        dt = myNote.GetAll

        DataGridView1.DataSource = dt

        '去掉最后一个空行
        DataGridView1.AllowUserToAddRows = False
        '交替色
        Dim i As Integer
        If DataGridView1.Rows.Count <> 0 Then
            For i = 0 To DataGridView1.Rows.Count - 1 Step 2
                DataGridView1.Rows(i).DefaultCellStyle.BackColor = Color.Pink
            Next
        End If
        DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill


    End Sub
    '清空文本框
    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        txtTime.Text = ""
        txtDynasty.Text = ""
        txtAddress.Text = ""
        txtPerson.Text = ""
        txtStory.Text = ""

        setControlEnable()

        Dim myNote As New note
        DataGridView1.DataSource = myNote.GetAll
    End Sub

    '新增记录
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim myNote As New note
        myNote.time = txtTime.Text
        myNote.dynasty = txtDynasty.Text
        myNote.address = txtAddress.Text
        myNote.person = txtPerson.Text
        myNote.story = txtStory.Text

        myNote.Insert()

        txtTime.Text = ""
        txtDynasty.Text = ""
        txtAddress.Text = ""
        txtPerson.Text = ""
        txtStory.Text = ""
        SetControlUnable()

        DataGridView1.DataSource = myNote.GetAll

    End Sub

    '更改
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim myNote As note = New note
        myNote.time = txtTime.Text.Trim
        myNote.dynasty = txtDynasty.Text.Trim
        myNote.address = txtAddress.Text.Trim
        myNote.person = txtPerson.Text.Trim
        myNote.story = txtStory.Text.Trim

        Dim row As DataGridViewRow = DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
        Dim id As String = row.Cells(0).Value
        myNote.id = id

        myNote.Update()

        txtTime.Text = ""
        txtDynasty.Text = ""
        txtAddress.Text = ""
        txtPerson.Text = ""
        txtStory.Text = ""

        DataGridView1.DataSource = Nothing
        DataGridView1.DataSource = myNote.GetAll
    End Sub

    '删除记录
    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        'If DataGridView1.SelectedRows.Count = 0 Then
        'Exit Sub
        'End If

        Dim row As DataGridViewRow = DataGridView1.Rows(DataGridView1.CurrentCell.RowIndex)
        Dim id As String = row.Cells(0).Value

        Dim myNote As note = New note
        Dim Result As DialogResult
        Result = MessageBox.Show("删除吗？", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning)
        If Result = DialogResult.Yes Then

            myNote.Delete(id)

        Else
            Exit Sub
        End If



        txtTime.Text = ""
        txtDynasty.Text = ""
        txtAddress.Text = ""
        txtPerson.Text = ""
        txtStory.Text = ""
        SetControlUnable()

        DataGridView1.DataSource = myNote.GetAll
    End Sub

    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim myNote As New note

        Dim row As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
        Dim id As String = row.Cells(0).Value
        myNote.id = id

        myNote.GetnoteFromId(id)
        txtTime.Text = myNote.time
        txtDynasty.Text = myNote.dynasty
        txtAddress.Text = myNote.address
        txtPerson.Text = myNote.person
        txtStory.Text = myNote.story

        setControlEnable()
    End Sub

    Private Sub DataGridView1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.SelectionChanged

        If DataGridView1.SelectedRows.Count = 0 Then
            Exit Sub
        End If

        Dim myNote As New note
        Dim row As DataGridViewRow = DataGridView1.SelectedRows(0)
        Dim id As String = row.Cells(0).Value
        myNote.id = id

        myNote.GetnoteFromId(id)
        txtTime.Text = myNote.time
        txtDynasty.Text = myNote.dynasty
        txtAddress.Text = myNote.address
        txtPerson.Text = myNote.person
        txtStory.Text = myNote.story

        setControlEnable()
    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim myNote As New note
        Dim dt As DataTable = myNote.GetnoteFromStory(txtCondition.Text)

        DataGridView1.DataSource = dt

    End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub
End Class