Targeting Handhelds

Last month, we started off with .NET for pocket devices. This time, we’ll have a look at some more tips on how to go about developing for the same.

Data Access



One of the foremost distinctions between the .NET Framework and the .NET Compact Framework comes up when you write data-driven applications. Unlike the standard framework, where you have the options of connecting to the data source using OLEDB, ODBC, or specific managed provider clients like those of Oracle or SQL Server, .NET Compact Framework supports connecting to SQL Server only.

There are two varieties of SQL Server that you can connect to: the standard SQL Server, to which we connect using the classes in the System.Data.SqlClient namespace, and the SQL Server CE, to which we connect using the classes under the System.Data.Sql ServerCe namespace. In fact, the latter namespace contains a representation of the SQL Server CE engine in the form of the SqlCeEngine class that can be used to create CE databases at runtime.

Once a database is in place, either created at runtime or already existing, System.Data.SqlServerCe has classes that are almost identical to the standard SqlClient classes and thus, the learning to write applications using them is greatly reduced. To understand these concepts better, let’s write an application that will:

• Create a database at runtime and create a table within it.
• Allow the user to add data to the database and also search for the same within it.

To keep things simple, we will have a single table in the database, with just one column, Name,

of the ntext datatype. Like last month’s project, we will create a new Smart Device project and select the WinCE platform as the application target.

Next, we add a reference to the System.Data.SqlServerCe.dll assembly so that we have the functionality to connect to a SQL Server CE database and work with it:

Now that the basics are in the place, we will design the UI to look simple.

Clicking the Create button shall create a new database at runtime. If the database file already exists, it will be deleted before the new one is created. Next, depending upon which radio button is selected, clicking the Execute button shall either insert a name in the database table, or search the database for the same.

Now, let’s see how we do it.

SQL Server CE Client



In our code, we start off by indicating the use of SqlServerCe client:

using System.Data.SqlServerCe;






Next, we use the SqlCeEngine class to create a new SQL Server CE database, and then add a new table into it:





// form the connection string to create the database file..


_strConString = "Data Source = " + _strDBFile + ";" ;


// instantiate the SQL Server CE engine to create the database


SqlCeEngine eng = new SqlCeEngine(_strConString);


// create the database


eng.CreateDatabase(); 


// release the engine


eng.Dispose();

If you notice, creating a new CE database is just a matter of instantiating a SqlCeEngine object with the correct connection string and invoking the CreateDatabase command. Similarly, creating the table in the database involves opening a new connection to the created database using the SqlCeConnection class.

// now, create a table in the database



SqlCeConnection con = new SqlCeConnection(_strConString);


try


{


con.Open();


}


catch


{


MessageBox.Show("Unable to connect to database to structure it!");


return;


}

Now follow this by using a SqlCeCommand object to create the table with a relevant SQL DDL statement:

// SQL to create a table



_strSQL = "Create Table PCQCE(Name ntext)";





// create the command object to create the table..


SqlCeCommand com = new SqlCeCommand(_strSQL,con);





// create the table..


com.ExecuteNonQuery();

Voila! We have a new SQL Server CE Database with a blank table in it.

Search for data 



Once a particular radio button has been selected in our application, it shall both search for a name in the database and tell us whether it exists or not.

…or insert it in the database:



Let’s have a look at the implementation of the Execute button’s click event:

We start off by establishing a connection to the CE database using SqlCeConnection object. Then, depending upon which radio button is selected, we formulate a SQL DML statement to insert or query

the database for the name specified by the user. If the name is being inserted, we use a SqlCeCommand object to execute the SQL against the database and

insert the name in it. Note that, since this is just an illustration of concept, we are not performing any checks like, whether the name is

already in the database or not.

On the other hand, if we have been asked to search for the name, we invoke the ExecuteReader of SqlCeCommand object, get a reference to the SqlCeDataReader instance and then loop through it to search for the name in question. A Boolean flag indicates whether or not the name was found in the database. Finally, the data reader is closed, followed by the release of the command and the connection objects.

Once you deploy the application, one difference that you will notice is that during the deployment of the application, there will also be a deployment of the SQL Server CE managed provider client DLLs. And those are the basics of connecting to SQL Server CE databases from pocket devices. The accompanying source code, a VS.NET 2003 solution, exemplifies the entire application implementation that you can use. 

Kumar Gaurav Khanna, Microsoft MVP,  runs wintoolzone.com