sqlce数据操作vb例程

sqlce数据操作vb例程

Imports System.data
Imports System.data.SqlServerCe
Imports System.IO
    '''' <summary>
    '''' 创建数据库
    '''' </summary>
    '''' <remarks></remarks>
    Sub CreateDB()
        If Not File.Exists(DataSource) Then
            Dim sqlEngine As New SqlCeEngine("Data Source=\Storage Card\JiChao.Sdf")
            sqlEngine.CreateDatabase()
            Dim cmd As New SqlCeCommand("CREATE TABLE Phone(Name nvarchar(20) Primary Key NOT NULL,Number nvarchar(20))", sqlce)
            sqlce.Open()
            cmd.ExecuteNonQuery()
            sqlce.Close()
        End If
    End Sub
    Sub AddData()
        Try
            If File.Exists(DataSource) Then
                Dim cmd As New SqlCeCommand("", sqlce)
                sqlce.Open()
                cmd.CommandText = "INSERT INTO Phone(Name,Number) VALUES ('abcde','12345')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Phone(Name,Number) VALUES ('bcdef','23456')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Phone(Name,Number) VALUES ('abcdx','12ds5')"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO Phone(Name,Number) VALUES ('bcdet','23sdfa56')"
                cmd.ExecuteNonQuery()
                sqlce.Close()
            End If
        Catch
            sqlce.Close()
        End Try
    End Sub '显示数据库中的数据
    Sub List()
        If File.Exists(DataSource) Then
            sqlce.Open()
            Dim reader As SqlCeDataReader
            Dim cmd As New SqlCeCommand("SELECT APP_NO,CP_NO,CP_ADDR,TMNL_TYPE FROM CJDY WHERE CP_NO = 16405734902 ", sqlce)
            reader = cmd.ExecuteReader
            Me.txbTEST.Text = ""
            While reader.Read
                Me.txbTEST.Text += "申请编号：" & reader.Item("APP_NO") & "采集点编号：" & reader.Item("CP_NO") & "地址：" & reader.Item("CP_ADDR") _
            & "类型：" & reader.Item("TMNL_TYPE") & vbCrLf
            End While
            sqlce.Close()
        Else
            'status = status + "数据库尚未创建！" & vbCrLf
        End If
    End Sub
    '删除数据库的数据
    Sub DeleteData()
        If File.Exists(DataSource) Then
            sqlce.Open()
            Dim cmd As New SqlCeCommand("DELETE FROM Phone", sqlce)
            cmd.ExecuteNonQuery()
            sqlce.Close()
            Me.txbTEST.Text = Me.lbmsgshow.Text + "数据库的记录已经删除！" & vbCrLf
        Else
            Me.txbTEST.Text = Me.lbmsgshow.Text + "数据库尚未创建！" & vbCrLf
        End If
    End Sub
    '修改数据库的数据
    Sub ModifyData()
        If File.Exists(DataSource) Then
            Dim cmd As New SqlCeCommand("UPDATE Phone SET Name='Hello', Number='87654321' WHERE Name='abcde' AND Number='12345'", sqlce)
            cmd.ExecuteNonQuery()
            sqlce.Close()
            Me.txbTEST.Text = Me.lbmsgshow.Text + "数据库的记录已经修改！" & vbCrLf
        Else
            Me.txbTEST.Text = Me.lbmsgshow.Text + "数据库尚未创建！" & vbCrLf
        End If
    End Sub