Programming SQL Server 2005 Compact Edition with ADO - Page 7

Listing 7.12 SqlCeException C#

try
{
SqlCeConnection myConnection = null;
myConnection = new SqlCeConnection("Data Source=\\Mobile\\Northwind.sdf;");
myConnection.Open();
SqlCeCommand myCommand = myConnection.CreateCommand();
SqlCeDataReader myDataReader ;
myCommand.CommandText = "Products";
myCommand.CommandType = CommandType.TableDirect;
myDataReader = myCommand.ExecuteReader();
while (myDataReader.Read()){
listbox1.Items.Add(myDataReader.GetValue(0));
}
myDataReader.Close();
}
catch (SqlCeException MyExp)
{
  foreach (SqlCeError MyError in MyExp.Errors)
  {
    MessageBox.Show(MyError.Message);
  }
}
finally
{
  myConnection.Close();
}
}

Listing 7.12 SqlCeException VB.NET

    Dim myConnection As SqlCeConnection
    Try

      myConnection = New SqlCeConnection("Data Source=\Program Files\ 
SqlCeExceptionError\Northwind.sdf;")
      myConnection.Open()
      Dim myCommand As SqlCeCommand = myConnection.CreateCommand()
      myCommand.CommandText = "Products"
      myCommand.CommandType = CommandType.TableDirect
      Dim myDataReader As SqlCeDataReader = myCommand.ExecuteReader()
      While myDataReader.Read()
        listbox1.Items.Add(myDataReader.GetValue(0))
      End While
      myDataReader.Close()

    Catch MyExp As SqlCeException
      Dim myError As SqlCeError
      For Each myError In MyExp.Errors
        MessageBox.Show(myError.Message)
      Next
    Finally
      myConnection.Close()
    End Try

Building Applications

This section explains the following types of solutions that you can build using the SQL Server Compact Edition data provider (System.Data.SqlServerCe) and the SQL Server data provider (System.Data.Sqlclient ) in .NET Compact Framework:

• Desktop applications using local cache in an SQL Server Compact Edition database.

• Smart device applications using local cache in an SQL Server Compact Edition database.

• Smart device applications connected to a backend SQL Server using SQL Server Data Provider (SqlClient namespace).

• Smart device applications that use an SQL Server Compact Edition database as a local store and occasionally connect to a backend SQL Server to synchronize data.

---

NOTE - You should use the .NET Framework provider for connecting desktop applications to an SQL Server.

---

Developing Desktop Applications with SQL Server Compact Edition

SQL Server Compact Edition is ideal for many desktop-based applications where an application is needed to store data locally. For example, Outlook stores email in a .pst file. In the future, an email client can download data to an SQL Server Compact Edition database file (.sdf) instead of using a .pst file. The advantage of using a .sdf file is that you can use database relational capabilities to find database information. The .sdf file can also be transferred to a mobile device where emails can be viewed.

Create an Email .sdf File

To demonstrate this scenario, the following example uses an Email.sdf file that contains emails.

Create the Emails table in the Email database. Fill the table with a set of emails.

Store the Email.sdf file and note down the path it uses in a Windows application. In the following example, the Email.sdf file path is C:\Database\Email\Email.sdf.

Create a Windows Project

1. Start Visual Studio 2005 from the Start menu.

2. Click File | New | Project.

3. Click on Visual C# or Visual Basic.

4. Click on Windows Project and select Windows Application.

5. Enter the Name and Location of the project.

6. Click OK on the New Project dialog box.

7. Design the user interface for an email project as shown in Figure 7.5

Figure 7.5
Desktop Email Application

Add a reference to the System.Data.SqlServerCe namespace in the project. In Solution Explorer, right click on References and select the Add Reference option. In the Add Reference dialog box, select System.Data.SqlServerCe dll.

Listing 7.9 has a complete code for this exercise. The code has the following methods:

• The Form_Load method loads the form and sets the initial query to select all emails.

• The btnFind_Click method triggers when a user clicks the Find button. This method opens a connection to the Email database and calls the FillEmailList method.

• The FillEmailList method executes a query given by the user. The result set of the query is shown back to the user in the List box.

Listing 7.13 Desktop Email C#.NET

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace Email
{
  public partial class Form1 : Form
  {
    SqlCeConnection myConn;
    public Form1()
    {
      InitializeComponent();     
     }
    private void Form1_Load(object sender, EventArgs e)
    {
      txtSQL.Text = "Select Sent, FromEmail, ToEmail, Sub, Content From Emails";
      // Use the DataDirectory macro to get database path.
      AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\Database\Email");
    }
    private void btnFind_Click(object sender, EventArgs e)
    {
      myConn = new SqlCeConnection
       ("Data Source=|DataDirectory|\\Email.sdf;");
      myConn.Open();
      FillEmailList();
      myConn.Close();
    }
    private void FillEmailList()
    {
      SqlCeDataReader myEmailDataReader;
      // Opens a data reader and fetch the rows
      listEmail.Items.Clear();
      SqlCeCommand myCmd = myConn.CreateCommand();
      myCmd.CommandText = txtSQL.Text;
      myEmailDataReader = myCmd.ExecuteReader();
      while (myEmailDataReader.Read())
      {
        listEmail.Items.Add
           (myEmailDataReader.GetDateTime(0).ToShortDateString() + " "
           + myEmailDataReader.GetSqlString(1) + " "
           + myEmailDataReader.GetSqlString(2) + " "
           + myEmailDataReader.GetSqlString(3) + " "
           + myEmailDataReader.GetSqlString(4) + " "
           );       
      }
    }
  }
}

Listing 7.13 Desktop Email VB.NET

Imports system.Data
Imports System.Data.SqlServerCe
Public Class Form1
  Dim myConn As SqlCeConnection
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) 
Handles MyBase.Load
    'Set the initial query.
    txtSQL.Text = _
      "Select Sent, FromEmail, ToEmail, Sub, Content From Emails"
    ' Use the DataDirectory macro to get database path.
    AppDomain.CurrentDomain.SetData _
      ("DataDirectory", "C:\Database\Email")
  End Sub
  Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles btnFind.Click
    'Open a connection to local .sdf file that contains Emails
    myConn = New SqlCeConnection _
     ("Data Source=|DataDirectory|\\Email.sdf;")

    myConn.Open()
    'Find the matching emails and fill the list box.
    FillEmailList()
    'Close the connection
    myConn.Close()
  End Sub
  Private Sub FillEmailList()
    Dim myEmailDataReader As SqlCeDataReader
    Dim myCmd As SqlCeCommand
    'Opens a data reader and fetch the rows
    listEmail.Items.Clear()
    myCmd = myConn.CreateCommand()
    'Read the query types by user
    myCmd.CommandText = txtSQL.Text
    myEmailDataReader = myCmd.ExecuteReader()
    'Display the matching rows in a listbox.
    While (myEmailDataReader.Read())
      listEmail.Items.Add( _
      (myEmailDataReader.GetDateTime(0).ToShortDateString() + " " _
           + myEmailDataReader.GetSqlString(1) + " " _
           + myEmailDataReader.GetSqlString(2) + " " _
           + myEmailDataReader.GetSqlString(3) + " " _
           + myEmailDataReader.GetSqlString(4) + " " _
           ))
    End While
  End Sub  
End Class

To execute the email program, type an SQL statement to find an email and press the Find button. The emails matching the specified criteria will be displayed in a list box as shown in Figure 7.5.

Developing Disconnected Smart Device Applications with SQL Server Compact Edition

You will use the same code to develop an email application for a mobile device. Create a Smart device project using a Windows mobile platform. First develop a user interface for a mobile device application. Copy the code used in Listing 7.14 into your device project. Change the path in the code to point to the database file.

Listing 7.14 Device Email C#.NET

  myConn = new SqlCeConnection
   ("DataSource=\\Program Files\\Email_Device\\Email.sdf") 

Listing 7.14 Device Email VB.NET

   myConn = New SqlCeConnection _
    ("DataSource=Program Files\Email_Device\Email.sdf")

Using these simple steps, you can use an email application on a Smart device as well.

Transfer the Email.sdf file that you created in the previous section to your Smart device and note down the path it uses in the device application. In the following example, the Email.sdf file path is \Program Files\Email_Device\Email.sdf.

Build and deploy the program onto a Smart device or emulator. To execute the email program, type an SQL statement to find an email and press the Find button. The emails matching the specified criteria will be displayed in a list box as shown in Figure 7.6.

Figure 7.6
Device Email Application

The application that you created for a desktop and a device uses the local data store in SQL Server Compact Edition. The enterprise applications need to synchronize the data with a backend SQL Server. In Chapter 12, Synchronizing Data with Merge Replication, you will create an application that synchronizes data with a backend SQL Server.

The application that works on a local SQL Server Compact Edition database and synchronizes data with a backend Server is termed as an occasionally connected application. From a device, you can directly access and manipulate data on a backend server. These types of applications are called connected solutions as they require connectivity to a server while accessing the database. Chapter 12 will also demonstrate how to use an Sqlclient namespace of the .NET Compact Framework to develop a connected solution.

Summary

ADO.NET consists of a set of objects. One way to learn ADO.NET is to learn all the objects together. Instead of describing all the objects together, we first discussed the main objects that are needed to connect to the SQL Server database and execute SQL Commands to fetch data. We discussed Data Set in detail. After understanding the framework of ADO.NET, we discussed objects that use parameterized queries, transactions, and exception handling.

This chapter will not make you an ADO.NET expert. However, it will give you a fundamental understanding of ADO.NET objects for SQL Server Compact Edition.