how to Select/Insert/Delete/Update records in SQL Server Compact database file(*.sdf) in VB.NET.

how to Select/Insert/Delete/Update records in SQL Server Compact database file(*.sdf) in VB.NET.

Please check the following code sample demonstrating how to Select/Insert/Delete/Update records in SQL Server Compact database file(*.sdf) in VB.NET.

I presume the SQL Server Compact database file C:\Northwind.sdf contains a Table named Users which has two fields as below: Field Name    Data Typename            Varchar(20)phone           Varchar(20)

Imports System.Data.SqlServerCe

Public Class Form1

' Shared variables

Dim con As SqlCeConnection = New SqlCeConnection("Data Source=C:\Northwind.sdf")

Dim cmd As SqlCeCommand

Dim myDA As SqlCeDataAdapter

Dim myDataSet As DataSet

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

ShowData()

End Sub

'Binding database table to DataGridView

Public Sub ShowData()

cmd = New SqlCeCommand("Select * FROM Users", con)

If con.State = ConnectionState.Closed Then con.Open()

myDA = New SqlCeDataAdapter(cmd)

myDataSet = New DataSet()

myDA.Fill(myDataSet, "MyTable")

DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView

End Sub

' Retrieve/Select records

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

cmd = New SqlCeCommand("SELECT * FROM Users Where name='Martin'", con)

If con.State = ConnectionState.Closed Then con.Open()

Dim sdr As SqlCeDataReader = cmd.ExecuteReader()

While sdr.Read = True

MessageBox.Show(sdr.Item("name") & " " & sdr.Item("phone"))

End While

sdr.Close()

End Sub

' Insert record

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

cmd = New SqlCeCommand("Insert Into Users(name, phone) Values('phenry', ‘88866677’)", con)

If con.State = ConnectionState.Closed Then con.Open()

cmd.ExecuteNonQuery()

ShowData() 'Rebinding to DataGridView and view result

End Sub

' Update record

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

cmd = New SqlCeCommand("Update Users Set phone=’34’ Where name='Martin'", con)

If con.State = ConnectionState.Closed Then con.Open()

cmd.ExecuteNonQuery()

ShowData() 'Rebinding to DataGridView and view result

End Sub

'Delete record

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

cmd = New SqlCeCommand("Delete * From Users Where name='Martin'", con)

If con.State = ConnectionState.Closed Then con.Open()

cmd.ExecuteNonQuery()

ShowData() 'Rebinding to DataGridView and view result

End Sub

' Dispose Database Connection object

Private Sub Form4_FormClosed(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles MyBase.FormClosed

con.Close()

con = Nothing

End Sub

End Class