In order to work directly with a SQL Server CE database (without using a dataset or mapping tables as classes), we need to directly manipulate objects of classes like
SqlCeConnection or
SqlCeCommand.
SqlCeConnection is an class that allows you to
establish a direct connection to the database. In order for the SqlCeConnection object to identify the database you want on your harddisk (since it's a local/desktop database), he needs to have an
connection string. The connection string is formatted string that contains, among others specifiers, the path to the your database file.
SqlCeCommand is a class that allows you to execute queries and non-query commands (like INSERT, UPDATE, DELETE, etc). In order to create an SqlCeCommand you need to specify a SqlCeConnection and a string containing the command you want to execute.
SQL statements can be grouped in 3 categories:
-table queries (ex. "SELECT * FROM myTable")
-
single (scalar) queries (ex. "SELECT AVG(salary) from Employees)
-
non-queries (ex. "INSERT INTO myTable VALUES(1,"John")" ).
A simple class that allows communication with an SQL Server CE database could be implemented like:
As you probably observed by now, every method is placed around a try/catch block. This was done because if the connection string is wrong, the method SqlCeConnection open() will trigger an exception. Also incorrect SQL statements can also trigger exceptions.
Imports System.Data.SqlServerCe
Public Class SimpleSqlCeAdapter
'''
''' The connection object used for communicating with the database
'''
Private sqlConnection As SqlCeConnection
'''
''' Creates a new SimpleSqlCeAdapter object for an unencrypted
''' database.
'''
''' The path to the database file
Public Sub New(ByVal path As String)
Dim connectionString As String = "Data Source=" & path
sqlConnection = New SqlCeConnection(connectionString)
End Sub
'''
''' Creates a new SimpleSqlCeAdapter object
'''
''' The connection string used for
''' connecting to the database
''' The path to the database file
Public Sub New(ByVal path As String, ByVal connection As String)
Dim connectionString As String = connection & path
sqlConnection = New SqlCeConnection(connectionString)
End Sub
'''
''' Queries the database for a DataTable object
'''
''' A SQL SELECT statment
''' A datatable if the sql statment is correct.
''' Otherwise it returns Nothing
Public Function QueryDatabaseForTable(ByVal queryCommand As String) _
As DataTable
Try
Dim table1 As New DataTable("Table1")
Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
sqlConnection.Open()
Dim sqlReader = sqlCommand.ExecuteReader()
table1.Load(sqlReader)
sqlReader.Close()
Return table1
Catch ex As Exception
Console.Error.Write(ex.Message)
Return Nothing
Finally
sqlConnection.Close()
End Try
End Function
'''
''' Queries the database for a String object.
'''
''' A SQL SELECT statement that
''' returns a single value.
'''
Public Function QueryDatabaseForScalar(ByVal queryCommand As String) _
As String
Try
Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
sqlConnection.Open()
Return CType(sqlCommand.ExecuteScalar(), String)
Catch ex As Exception
Console.Error.Write(ex.Message)
Return Nothing
Finally
sqlConnection.Close()
End Try
End Function
'''
''' Executes a SQL statement which returns no value (Which is not
''' a query).
'''
''' The SQL statement which will be
''' executed
Public Sub ExecuteNonQuery(ByVal queryCommand As String)
Try
Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
sqlConnection.Open()
sqlCommand.ExecuteNonQuery()
Catch ex As Exception
Console.Error.Write(ex.Message)
Finally
sqlConnection.Close()
End Try
End Sub
End Class
It is very important to note that the SqlCeConnection must be closed even if an exception was triggered. Because of this the method SqlCeConnection.close() was called inside a finally block.
In order to see the use of this class let's take an example:
We shall consider that we have SQL Server CE database called MyDatabase. This database consists of a single table called Table1 who contains the fields ID (numeric) and Name (nvarchar). The table contains the following 3 records:
1 John
2 Bill
3 James
The database is unencrypted (has no password) and its path is "D:\MyDatabase".
Public Class DataClient
Public Shared Sub Main(ByVal args As String())
'Creating the simple SqlceAdapter by specifying the path
Dim di As New SimpleSqlCeAdapter("D:\MyDatabase.sdf")
'Inserting a new record
di.ExecuteNonQuery("INSERT INTO table1 VALUES(4,'Bob')")
'A table query
Dim dt As DataTable = di.QueryDatabaseForTable("SELECT * FROM table1")
For Each dr As DataRow In dt.Rows
Console.WriteLine("{0} {1}", dr(0), dr(1))
Next
'A simple scalar query
Dim s As String = di.QueryDatabaseForScalar("SELECT AVG(ID) FROM table1")
Console.WriteLine(s)
Console.Read()
'Output
'1 John
'2 Bill
'3 James
'4 Bob
'2.500000
End Sub
End Class
A simple program that would insert a new record and then show the table and the average id would be:
