VB.NET : Access to SQL Server Compact Edition

Accessing SQL Server Compact Edition Database using VB.NET


SQL Server Compact Edition, or simply SQL Server CE, allows a very convenient way of fetching data stored locally as cache. It is usually used in situations where commonly used data are stored locally and accessed directly instead of making queries to fetch the data again and again from the main database server. Accessing data stored locally can be many times faster compared to accessing data stored in the main database located somewhere on the network. Hence, by implementing and taking the full advantage of SQL Server CE, you can make your application runs faster.


Firstly, add the following reference at the top of your module programming code


Imports System.Data.SqlServerCe


Next, add the following as global variables so that you can call them anytime, anywhere in your code.


Friend SQLcommCE As SqlCeCommand 'SQL command
Friend daCE As SqlCeDataAdapter 'SQL adapter
Friend SQLConnCETempDB As SqlCeConnection 'SQL connection linking your local database


Then, add the following codes to establish the database connection to where your application loads when it runs. Data Source points to the path where your SQL Server CE database extension *.sdf is stored, and provides the password (if any) in the codes shown below.


SQLConnCETempDB = New SqlCeConnection("Data Source=C:\TempDB.sdf;Password='123'")
SQLConnCETempDB.Open()



Finally, in the programming code where you want to perform query to the database, you can simply use the codes shown as follows.


Dim SQLStm as String

SQLStm = "DELETE FROM LocalTable WHERE MYTABLE_ID = '123'"
SQLcommCE = New SqlCeCommand(SQLStm, SQLConnCETempDB) 'execute the query
SQLcommCE.ExecuteNonQuery()

That will delete the records with MYTABLE_ID = '123' in your LocalTable.
For INSERT INTO, UPDATE, DELETE or any other NonQuery database access, you can use

.ExecuteNonQuery

If you intend to query the table using the SELECT statement, you have to use the following code instead

.CommandType = CommandType.Text


See the example below:


Dim SQLStm as String
Dim ds as DataSet
Dim Rank, Name as String

SQLStm = "SELECT Rank, Name FROM LocalTable WHERE MYTABLE_ID = '123'"
SQLcommCE = New SqlCeCommand(SQLStm, SQLConnCETempDB) 'SQL query
SQLcommCE.CommandType = CommandType.Text

daCE = New SqlCeDataAdapter(SQLcommCE) 'Retrieve data from SQL Server CE
ds = New DataSet
daCE.Fill(ds) 'Populate dataset with retrieved data.

If ds.Tables(0).Rows.Count > 0 Then ' If record exists in LocalTable
Rank = ds.Tables(0).Rows(0).Item("Rank").ToString
Name = ds.Tables(0).Rows(0).Item("Name").ToString
Else 'record not exists in LocalTable
Rank = ""
Name = ""
End If


Now you can start implementing SQL Server CE in your applications