As we are evolving in the “Internet of things” era, intelligent devices, analytics and big data are import keys to new generation of application and devices.  In this new era, key companies now realize the tremendous hidden value in data.  

In Part-6 of this Windows Embedded Compact 7 (Compact 7) getting started series, we will show how you can develop database application for Compact 7 device, using SQL Server Compact database technology.

Note:

The Compact 7 getting started series consist of 9 different articles, covering the following subjects:

1.        Introduction.

2.        Development Environment & Tools.

3.        Develop a Compact 7 OS Design.

4.        Develop Managed Code Application for Compact 7.

5.        Develop Native Code Application for Compact 7.

6.        Develop SQL Compact Database Application for Compact 7.

7.        Develop Silverlight for Windows Embedded Application for Compact 7.

8.        Debug and Remote Tools.

9.        Deploy OS Runtime to Target Device for Distribution.

For more information about this series, visit:

    http://embedded101.com/Compact7/  

Prerequisites:

To work through the exercises in this guide, we need to have the following:

·         Successfully compiled a Compact 7 OS image with support for managed code application and deployed the image to a target device, which includes the following SQL Compact components:

o   SQL Compact 3.5

o   SQL Compact 3.5 Managed Provider

·         Generated an SDK from the OS design and installed the SDK to the development PC.

(For this application note, we use the MyCompact7_SDK generated from the OS design project, in Part-3 of this series.)

·         Both the development PC and the target device are connected to the same LAN.

Target Device:  eBox-3300MX

The exercise in this guide is created using an eBox-3300MX as the target device.  Refer to the following URL for more information about the eBox-3300MX:

http://www.embeddedpc.net/eBox3300MX/

Alternative Target Device:  Virtual PC

You can work through the exercise in this guide, using other hardware or virtual PC as the target device.

Develop SQL Server Compact Database Application

The main objective for this getting started series is to provide simple and easy to understand information, with step-by-step illustration where possible, to help developer new to Compact 7 to become familiar with the development environment, to develop SQL Server Compact 3.5 (SQL Compact) database application for a Compact 7 device.

Note:

In addition to the SQL Compact database, there are two other databases provided as part of the Compact 7 OS, CEDB and EDB.  Refer to the following URL for more information:

    http://msdn.microsoft.com/en-us/library/ee489739

To avoid confusion and deliver easy to follow contents, a simple SQL Compact database application is used as example for the exercises in this application note, covering the following:

·         Create SQL Compact database programmatically

·         Create a database table programmatically

·         Insert data to the database

·         Retrieve and display data

Create a Smart Device Managed Code Application

To start, we need to create a smart device managed code application.  The application note in Part-4 of this series covers the steps to develop a smart device managed code application in detail, please refer to the following blog entry for more information:

http://www.embedded101.com/Blogs/SamuelPhung/tabid/72/entryid/211/Compact-7-Getting-Started-Part-4-Develop-Managed-Code-Application.aspx

Work through the following steps to create a managed code application for Compact 7:

1.       From the VS2008 File menu, select New and click on Project to bring up the new project wizard.

2.       On the left pane, expand the Other Languages\Visual C# node and select Smart Device as project type.

3.       On the right pane, select the Smart Device Project template.

4.       Enter Compact7DatabaseApp as the project name.

5.       Enter C:\Lab.Compact7 as the location for the project.

Note:

You can choose a different folder for the project other than C:\Lab.Compact7.

6.       Click OK to continue and bring up the Add New Smart Device Project screen.

7.       From the Target platform selection, select MyCompact7_SDK.

Note:

The MyCompact7_SDK is generated from the OS design from Part-3 of the Compact 7 getting started series, using an eBox-3300MX as the target device.

If you are using an alternative target device, select the SDK for your device.

8.       From the .NET Compact Framework versions selection, select .NET Compact Framework 3.5.

9.       From the Templates selection, select Device Application.

10.   Click OK to complete the steps to create a new smart device application project.

After the new project wizard step is completed, the initial Compact7DatabaseApp application project workspace is created in the following folder:

C:\Lab.Compact7\Compact7DatabaseApp

The initial application project is created with a blank form, Form1.cs, and a blank module, Program.cs.

Adding SQL Compact Support, Name Space and Variables

The OS runtime for the target device is built with SQL Compact components provided as part of the Platform Builder.  To develop SQL Compact application for the device, we need to include reference to the SQL Compact DLL associates with the components that are included with the OS runtime image.

Work through the following steps to add reference to the SQL Compact DLL:

1.       From the VS2008 Project menu, click on Add Reference to bring up the Add Reference screen.

2.       Click on the Browse tab and navigate to the following directory:

C:\WINCE700\Others\sqlcompact\managed\retail\

3.       Select the following file:

System.data.sqlserverce.dll

4.       Click on OK to select and close the Add Reference screen.

Add the following namespace to the Form1.cs code file:

    using System.Data.SqlServerCe;

    using System.IO;

    using System.Reflection;

Add the following variables to the Form1.cs code file:

        string appPath;                   // Current application path

        string conString;                 // Connection string

        string databaseName;      // database file name

        string myPassword;         // database password

        string tableName;              // table name

        bool tableCreated;             // flag to indicate data table is created

        bool dbConnected;            // flag to indicate database connection is established       

        bool newData = false;       // flag to indicate new data

       

        SqlCeConnection cn;          //  Connection object

        SqlCeResultSet myData;   //  SQL Compact result set

Application User Interface

In this section, we will put together the user interface for the application:

1.       The mainMenu1 control is not needed for this exercise, delete this control.

2.       Change Form1’s caption to Database Application for Compact 7.

3.       Resize Form1 to 420 x 360.

4.       Place a label control on the form and change the text property to AppPath.

5.       Place a textbox on the form and change the name to txtAppPath.

6.       Place a tab control on the form, add another tab to the control, change first tab’s caption to “Create Database”, change the second tab’s caption to “Create Table” and the third tab’s caption to “Display Data”, as shown in Figure 1.

Fig-01

Figure 1

7.       Place the following controls on the “Create Database” tab, as shown in Figure 2:

o   Label control – Change the caption to “Database Name:”.

o   Label control – Change the caption to “Password:”.

o   Textbox – Change the name to txtDatabaseName, change the text property to MySQLCompact.sdf (This will be the default database name).

o   Textbox, change the name to txtPassword, change the text property to embedded101 (This will be the default password for the database).

o   Button, change the caption to “Create Database” and change the name to btnCreateDatabase.

o   Label control – Change the name to “lblFileDeleted”.

(This label is used to provide visual indication an existing file is deleted.)

o   Label control – Change the name to “lblDatabase”.

(This label is used to provide visual indication a new database is created.)

Fig-02

Figure 2

8.       Place the following controls on the “Create Table” tab, as shown in Figure 3:

o   Label control, change the caption to “Table Name:

o   Textbox – change the name to txtTableName, change the text property to MyTable (This will be the default table name).

o   Button – change the caption to “Create Table” and change the name to btnCreateTable.

o   Button – change the caption to “Generate Test Data” and change the name to btnGenerateTestData.

o   Label control – Change the name to “lblTable”.

(This label is used to provide visual indication a new table is created.)

Fig-03

Figure 3

9.       Place the following controls on the “Display Data” tab, as shown in Figure 4:

o   Label control – Change the caption to “UserID:

o   Textbox – Change the name to txtUserID.

o   Label control – Change the caption to “FirstName:

o   Textbox – Change the name to txtFirstName.

o   Label control – Change the caption to “LastName:

o   Textbox – Change the name to txtLastName.

o   Label control – Change the caption to “Website:

o   Textbox – Change the name to txtWebsite.

o   Button – Change the caption to “New Record” and change the name to btnNew.

o   Button – Change the caption to “Show Data” and change the name to btnShowData.

o   Button – Change the caption to “Prev” and change the name to btnPrev.

o   Button – Change the caption to “Next” and change the name to btnNext.

o   Button – Change the caption to “Save Data to Table” and change the name to btnSaveData.

o   Label control – Change the name to lblDataAdded.

Fig-04

Figure 4

10.   Place a command button just below the tab control, on the right, change the caption to “Close” and change the name to btnClose.


 

Add Codes to Initialize the Application

Add the following codes to the form load event to initialize the application’s user interface (UI).

private void Form1_Load(object sender, EventArgs e)

        {

            // Initialize the UI

            lblDatabase.Text = "";

            lblTable.Text = "";

            lblFileDeleted.Text = "";

            lblDataAdded.Text = "";         

 

            // Retrieve current application path           

            appPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);

            appPath = appPath + "\\";

            txtAppPath.Text = appPath;

 

            // disable the save button

            btnSaveData.Enabled = false;

        }

Add Codes to Create the Database

Add the following codes to the btnCreateDatabase_Click event handler to create the database:

private void btnCreateDatabase_Click(object sender, EventArgs e)

        {

            //  Clear existing database-created and table-created messages

            lblDatabase.Text = "";

            lblTable.Text = "";

            lblFileDeleted.Text = "";

 

            databaseName = txtDatabaseName.Text;     // Retrieve database name

            myPassword = txtPassword.Text;           // Retrieve password

 

            // Connection string

            conString = "Data Source=" + appPath + databaseName + "; Password=" + myPassword;

 

            // Check to see whether the file already there

            if (File.Exists(appPath + databaseName))

            {

                DialogResult result = MessageBox.Show("Found existing " + appPath + databaseName

                                                            + ", delete and create new one?", "Database found", MessageBoxButtons.YesNo,

                                                            MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);

                if (result == DialogResult.Yes)

                {

                    // delete existing database file

                    File.Delete(appPath + databaseName);

                    lblFileDeleted.Text = "Existing database deleted.";

                }

                else

                {

                    // keeping existing database

                    lblFileDeleted.Text = "Keep existing database.";

                    return;

                }

            }

 

            //  Code to create the database

            SqlCeEngine en = new SqlCeEngine(conString);

            en.CreateDatabase();

            lblDatabase.Text = "Database created " + appPath + databaseName;

        }

The above code creates a SQL Compact database file, MySQLCompact.sdf, protected by the password, embedded101, in the application’s current directory.

Both the database file name and password can be changed from the application UI before clicking on the Create Database command button.

Add Codes to Create the Table

Add the following codes to the btnCreateTable_Click event handler to create the table:

private void btnCreateTable_Click(object sender, EventArgs e)

        {

            tableName = txtTableName.Text;

            cn = new SqlCeConnection(conString);

            if (cn.State == ConnectionState.Closed)

            {

                cn.Open();

                dbConnected = true;

            }

 

            SqlCeCommand cmd;

            string sql = "create table " + tableName + "("

                    + "UserID nvarchar (8) not null,"

                    + "LastName nvarchar (30) not null,"

                    + "FirstName nvarchar (30) not null,"

                    + "WebSite nvarchar (50))";

 

            cmd = new SqlCeCommand(sql, cn);

            try

            {

                cmd.ExecuteNonQuery();

                tableCreated = true;

                lblTable.Text = "Table Created.";

            }

            catch (SqlCeException sqlexception)

            {

                MessageBox.Show(sqlexception.Message, "Error Create Table - SqlCeException");

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, "Error Create Table - ex");

            }

            finally

            {

                cn.Close();

                dbConnected = false;               

            }

        }

The above code creates a table for the SQL Compact database, MyTable.

You can create a table with different name by entering a new name to the application UI prior to clicking on the Create Table command button.

Add Codes to Insert Test Data

Add the following function to the Form1.cs code file, a routine to insert data to MyTable:

private void insertMyTable(string userID, string FirstName, string LastName, string WebSite)

        {

            if (!dbConnected)

            {

                cn = new SqlCeConnection(conString);

                if (cn.State == ConnectionState.Closed)

                {

                    cn.Open();

                    dbConnected = true;                  

                }

            }

 

            SqlCeCommand mCmd;

            string sql = "insert into MyTable "

                + "(UserID, FirstName, LastName, WebSite)"

                + "values(@UserID, @FirstName, @LastName, @WebSite)";

 

            mCmd = new SqlCeCommand(sql, cn);

            try

            {

                mCmd.Parameters.AddWithValue("@UserID", userID);

                mCmd.Parameters.AddWithValue("@FirstName", FirstName);

                mCmd.Parameters.AddWithValue("@LastName", LastName);

                mCmd.Parameters.AddWithValue("@WebSite", WebSite);

                mCmd.ExecuteNonQuery();

            }

            catch (SqlCeException sqlexception)

            {

                MessageBox.Show(sqlexception.Message, "Error Adding data - SqlCeException");

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message, "Error Adding data - ex");

            }

            finally

            {

                cn.Close();

                dbConnected = false;               

            }

        }

Add the following codes to the btnGenerateTestData_Click event handler to insert test data:

private void btnGenerateTestData_Click(object sender, EventArgs e)

        {

            if (tableCreated)

            {

                // Insert test data to database                       

                insertMyTable("001", "John", "Smith", "http://www.mysite1.com");

                insertMyTable("002", "Jane", "Smith", "http://www.mysite2.com");

                insertMyTable("003", "John", "Doe", "http://www.mysite3.com");

                insertMyTable("004", "Jane", "Doe", "http://www.mysite4.com");

                insertMyTable("005", "Johnny", "Depp", "http://www.mysite1.com");

 

                lblTable.Text = "Test data generated.";

            }

            else

            {

                MessageBox.Show("Data table not created yet.", "Error Adding Data");

            }

        }

Add Codes to Display Data

Add the following function to the Form1.cs code file, a routine to display data from MyTable:

private void displaydataResultSet(SqlCeResultSet mdata)

        {

            txtUserID.Text = (string)mdata;

            txtFirstName.Text = (string)mdata;

            txtLastName.Text = (string)mdata;

            txtWebsite.Text = (string)mdata;

            newData = false;

        }

Add the following code to retrieve data from the database and display the data, in the btnShowData_Click event handler:

private void btnShowData_Click(object sender, EventArgs e)

        {

            if (!dbConnected)

            {

                cn = new SqlCeConnection(conString);

                if (cn.State == ConnectionState.Closed)

                {

                    cn.Open();

                    dbConnected = true;                   

                }

            }

 

            SqlCeCommand cmd = new SqlCeCommand("SELECT * from MyTable", cn);

            cmd.CommandType = CommandType.Text;

            myData = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

            // Check to see whether the datareader retrieved any data

            if (myData.HasRows)

            {

                // Display the first row of data

                myData.ReadFirst();

                displaydataResultSet(myData);

            }

            else

            {

                // The data table is empty

                MessageBox.Show("Data table is empty.", "Display data");

            }

        }      

Add Codes to Display the Next Record

Add the following codes to the btnNext_Click event handler to move the data cursor to the next record and display the data:

private void btnNext_Click(object sender, EventArgs e)

        {

            try

            {

                myData.ReadRelative(1);

                displaydataResultSet(myData);

            }

            catch (SqlCeException sqlexception)

            {

                MessageBox.Show(sqlexception.Message, "Error reading data - SqlCeException");

            }

            catch (Exception ex)

            {

                if (ex.Message == "No data exists for the row/column.")

                {

                    MessageBox.Show("End of dataset - No more record.", "Display Data");

                    // Cursor moved past the last record.

                    // Step back to the last record.

                    myData.ReadRelative(-1);

                }

                else

                {

                    MessageBox.Show(ex.Message, "Error Display Data - ex");

                }

            }

        }

Add Codes to Display the Previous Record

Add the following codes to the btnPrev_Click event handler to move the data cursor to the previous record and display the data:

private void btnPrev_Click(object sender, EventArgs e)

        {

            try

            {

                myData.ReadRelative(-1);

                displaydataResultSet(myData);

            }

            catch (SqlCeException sqlexception)

            {

                MessageBox.Show(sqlexception.Message, "Error reading data - SqlCeException");

            }

            catch (Exception ex)

            {

                if (ex.Message == "No data exists for the row/column.")

                {

                    MessageBox.Show("Begining of dataset - Click Next.", "Display Data");

                    // Cursor move past the first record

                    // Move to the first record

                    myData.ReadFirst();

                }

                else

                {

                    MessageBox.Show(ex.Message, "Error Display Data - ex");

                }

            }

        }

Add Codes to Insert New Record to the Database

Add the following codes to the btnNew_Click event handler to clear the UI and configure the application to accept new data:

private void btnNew_Click(object sender, EventArgs e)

        {

            // Clear textbox contents

            txtUserID.Text = "";

            txtFirstName.Text = "";

            txtLastName.Text = "";

            txtWebsite.Text = "";

 

            newData = true;

            lblDataAdded.Text = "";

 

            // Creating new data, enable the save button

            btnSaveData.Enabled = true;

        }

Add the following codes to the btnSaveData_Click event handler to insert the new data to the database:

private void btnSaveData_Click(object sender, EventArgs e)

        {

            string strUserID = txtUserID.Text;

            string strFirstName = txtFirstName.Text;

            string strLastName = txtLastName.Text;

            string strWebSite = txtWebsite.Text;

 

            if (newData)

            {

                if (!dbConnected)

                {

                    cn = new SqlCeConnection(conString);

                    if (cn.State == ConnectionState.Closed)

                    {

                        cn.Open();

                        dbConnected = true;                       

                    }

                }

 

                // Insert new data to MyTable

                insertMyTable(strUserID, strFirstName, strLastName, strWebSite);

                newData = false;

            }

        }

Steps to Use this Application

Here are the steps to use this app:

1.       Click on "Create Database" button to create the SQL Compact database.

2.       Click on the "Create Table" tab.

3.       Click on the "Create Table" button to create the table.

4.       Click on the "Generate Test Data" button to insert test data to table.

5.       Click on the "Display Data" tab.

6.       Click on the "Show Data" button to display data.

7.       Click on the "Next" or "Prev" button to navigate between records.

8.       Click on the "New" button to set the UI to accept new data.

9.       Enter relevant data and click on the "Save Data to Table" button.

Summary

A SQL Compact database can be as large as 4 GB, which is huge in the embedded device domain.  As you can see from this exercise, the Compact 7 development environment along with the SQL Compact provides an effective and simple to develop environment to create database application.

The example in this application note provides a small subset of SQL Compact development resources.  SQL Compact can also be used in a Windows desktop, Windows Embedded Standard, Windows Mobile and Windows Phone application.

For more information about SQL Server Compact 3.5, visit the following URLs:

http://msdn.microsoft.com/en-us/library/aa983321

Download

The sample app in this article is available for download from the following URL:

http://embedded101.com/Compact7/Download/Part6_Compact7_Database_App.zip