SQL Server Compact 3.5 Database
If you need to store structured data (for example, suppose you have hundreds of customers' information that you need to store on your Windows Mobile device), a database solution is more appropriate for you. For the Windows Mobile platform, your database of choice would be SQL Server Compact 3.5. This is a free, embedded database engine that lets developers build robust Windows desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone.
Using a SQL Compact Edition Database
When you install Visual Studio 2008, a sample SQL Server Compact database named Northwind.sdf is also installed automatically in the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples folder. This sample database is useful for developers just getting started with SQL Server Compact 3.5.
To use the SQL Server Compact 3.5 Northwind.sdf database, add an existing item to your project and navigate to the folder containing the sample database. Once it is added, a wizard will appear, asking you to choose the table that you want to use (see Figure 1). Essentially, this creates a typed DataSet for you.
|Figure 1. The Database Wizard: Choosing the table(s) you want to use in your project.|
Once you've selected the table(s) you want to use, a typed Dataset (NorthwindDataSet, in this case) is created for you. You can use the code shown in Listing 2 to load the Employees table into a DataSet.
The SqlCeConnection class represents a connection to a SQL Server Compact database and the SqlCeCommand class represents a SQL statement to execute against a data source. The SqlCeDataAdapter class represents a set of commands and a database connection that can be used to fill a DataSet object. If you are familiar with ADO.NET, then the above few classes should not be too alien to you. Once you've loaded the table(s) into a DataSet, you can bind the DataSet object to a DataGrid control, retrieve the rows within the tables in the DataSet, or modify the records directly in the DataSet.
If you do not wish to use the typed DataSet that Visual Studio 2008 created for you, you can also use the generic DataSet object, like this:
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
DataSet ds = new DataSet();
foreach (DataRow r in ds.Tables["Employees"].Rows)
textBox1.Text += r.ToString() + Environment.NewLine;
To persist the content of the DataSet
object to storage, use the DataSet class' WriteXml()
method. This saves the DataSet
as an XML file:
To load the DataSet
from an XML file, use the ReadXml()
If you simply want to print out the values of some fields in the table, it might be easier to use the SqlCeDataReader class, which provides a forward-only stream of data from a data source:
SqlCeCommand cmd = new SqlCeCommand(
"SELECT * FROM Employees", conn);
reader = cmd.ExecuteReader();
//---read all the Employee ID, First Name, and Last Name---
reader["Employee ID"].ToString() + " - " +
reader["First Name"].ToString() + ", " +
The above code uses the SqlCeCommand class' ExecuteReader()
method to return a SqlCeReader
object. The SqlCeReader
object is then used to display the EmployeeID, First Name
, and Last Name
fields of the Employees
table in a ComboBox
control. The SqlCeDataReader class' Read()
method advances the reader to the next row of data and returns true as long as there are more data to read.
To update a record, use the SqlCeCommand class' ExecuteNonQuery() method to execute an SQL statement, like this:
string sql = "UPDATE Employees " +
"SET [First Name]='Wei-Meng' ,[Last Name]='Lee' " +
"WHERE [Employee ID]=1";
cmd.CommandText = sql;