Programming with .NET Compact Framework 1.0 and SQL CE 2.0 : Part I


Recently I have developed few applications target to Pocket PC using .NET and SQL CE 2.0 .In this article I am sharing my experience on .NET compact framework and SQL CE .Its really very interesting and tuff to develop application for smart devices such PDA or smart phone .This article covers various major components for developing application for PDA .Before developing application  targets to PDA device its very important to understand .NET compact framework, supported/unsupported features for smart device development, Difference between .NET compact framework and .NET framework and  of course SQL CE if you want to store application data in SQL database.

.NET Compact Framework 1.0 is a framework for developing mobile device application .It provides the power to run managed code and consume XML web services in smart devices. The most interesting point is the developers never feel much differences while developing compact edition (CE) application because compact framework is nothing but the subset of .NET framework that means it provides the same benefits what .NET framework is giving. We will discuss the differences between this two frameworks later in this article. 

The device must support Windows CE to run the  applications build on .NET compact framework but exceptions for Microsoft pocket PC and Microsoft Pocket PC 2002 that supports .NET compact framework. Some of the benefits of compact framework are:

  • Subset of .NET framework design for memory constrained devices  such as Pocket PC.
  • Automatic deployment while executing the application once the development done. 
  • Easily migrate to different mobile devices which supports .NET compact framework without recompile.
  • Leverage the secure examination when running or loading the assembly.
  • Getting the core benefit of garbage collector and JIT compiler. 
  • The same IDE (Visual Studio .NET IDE) use for develop and deploy the application. 
  • Uses the same naming conventions as in the .NET Framework. 
  • Capable to call web service or communicate through SOAP.

Now I am going to talk about some of the unsupported controls, events etc of .NET compact framework .The .NET compact framework primarily support for  thick client (rich clients).When you are developing win forms using .NET compact framework few controls are unsupported like RichTextBox ,GroupBox, PrintControl, FontDialog , LinkLabel, ToolTip , Splitter, ColorDialog, CheckedListBox etc .Apart from this some of the events are unsupported like DoubleClick, Invalidated, FontChanged  and few others like wise .NET compact framework does not support few properties such as Achor,Dock,CanFocus etc.

Lets talk about data storage the most preferable repository to store data in Pocket PC is SQL database .But the most important point is that you can not install the desktop SQL Server in Pocket PC due to memory constrains , for this Microsoft come-up with SQL Server CE (compact edition) now its called is Mobile SQL. Microsoft SQL Server CE is a very compact relation database that runs on the smart devices and built-in support for the .NET compact framework. It can support database up to 2GB in size also support binary large objects (BLOBS) up to 1GB . Keep in mind the size of the local database is limited by the storage size available on the smart device.

SQL Server CE is an ideal storage mechanism due to its capabilities for encryption for security purposes. This is also great for data connections that are wireless connections and/or online/offline connections. The most interesting point is SQL CE data provider provides almost similar kind of flavor what we are having for SQL Server 2000 data provider. As a developer you will not find much different while working with data providers for SQL CE.The major component of SQL CE is database engine , client agent and query analyzer (isql.exe) . The Query Analyzer program allows a graphical interface to create and structure a database in a treeview form (this allows a graphical representation of the database objects). You can use the Query Analyzer to perform updates to data, a selection of data, or insert/delete data in the local database and on top of it expose the data as XML document for use with web-based application .SQL CE supports data types likes interger,numeric , binary,nchar/nvarchar etc for more details go through the SQL CE books online (http://www.microsoft.com/sql/ce/techinfo/20bol.asp).

Till now we have discussed about .NET compact framework and SQL Server CE , as previously I have mention data providers helps to interact with database , .NET compact framework provides ADOCE.NET to interact with CE edition of database ( in our discussion its SQL Server CE 2.0) .Now I am going to talk about ADOCE.NET.

As we all very much familiar with ADO.NET the namespaces available for SQL server data provider are System.Data and System.Data.SqlClient similarly in ADOCE.NET for SQL CE data provider are System.Data and System.Data.SqlserverCE. The process to develop an application to use SQL Server CE is

  • Check for database available or not if not create the database through SqlCeEngine object as below

    if
    (!File.Exists(\\My Documents\\EmpDB.sdf))
    {
    SqlCeEngine empEngine = new
    SqlCeEngine("Data Source = \\My Documents\\EmpDB.sdf");
    empEngine.CreateDatabase();}

    Note : the database file name must have .sdf extension

  • After create the database is created next step is to create the required tables

    SqlCeConnection empCon;
    SqlCeCommand empCom;
    empCon =
    new SqlCeConnection("Data Source = \\My Documents\\EmpDB.sdf");
    empCon.Open();
    empCom = empCon.CreateCommand();
    string
    strQuery = "CREATE TABLE Employee (EmpID int IDENTITY(0,1) PRIMARY KEY,EmpName nvarchar(10),Salary int)";
    empCom.CommandText=strQuery;
    empCom.ExecuteNonQuery();

    This code snippet create a table with three columns ( empid,emp name  & salary )

  • Once you setup the database then further operations are store or fetch data  , ii can be done through SqlCeDataReader as below

    string strQuery = "SELECT * FROM Employee ORDER By EmpName ASC";
    empCom.CommandText = strQuery;
    SqlCeDataReader empReader = empCom.ExecuteReader();

Before we conclude our discussion lets talk about the differences between .NET framework and compact framework . I have mentions few important points  which we are using extensively in our day to day development process

Functionality

Supported/Unsupported

Remarks

Late bindings (Reflection) 

Not supported on .NET Compact Framework
Serialization

Note: you can save the data in text format

Due to performance considerations, .NET Compact Framework does not support binary serialization or SOAP serialization
ASP.NET you can not host asp.net application but can access web application through HTML/WML Not supported on .NET Compact Framework
Web services NET Compact Framework applications can consume Web services, they can't provide them due to the lack of  hosting web server
XML support .NET Compact Framework applications will have full support for reading and writing XML using the reader and writer classes, but they won't have support for XSLT or XPATH
Com Introp Unsupported in .NET Compact framework
OLE DB Wrapper Not supported in .NET Compact Framework
.NET Remoting Unsupported in .NET Compact framework but XML services are to be used as alternative for this
Win32 Calls Note: One way communication is supported NET Compact Framework does not support calls from Win32; you can make calls from Compact Framework into Win32. Communication between Win32 and Compact Framework is done by MessageWindows class.
Datagrids Not built in to the editing support as in the .NET Framework. The .NET Compact Framework can only bind Datagrids to a single table object. The .NET Compact Framework only displays rows from a single table, and these are not able to be expanded as in the .NET Framework
Tooltips This GUI control unsupported in .NET compact framework but supported to .NET framework
Window Form controls Note : Around out of 35 controls .NET compact framework supports  approximately only 28 controls No drag and drop support, no printing, no checked list box ,no ActiveX support, and no GDI+ in the .NET Compact Framework
File change notifications This is supported on the .NET Framework but not supported on .NET Compact Framework due OS constraints
nGen (Install-time JIT) Unsupported in .NET Compact framework

For demonstration I have developed a sample application called "Employee Appraisal". The main purpose of this application is lets assume you are the project manager , you are planning to propose a good hikes for your team members .To discuss about your team members appraisal in the HR meeting you need to have the current salary of your team members, this application helps you to store the salary information of  team. I have used Win Forms,ADOCE.NET and MS-SQL CE 2.0 to develop this application. Let me explain the inside of this application.

This application has two entry screen (forms) one is Employee , this form use for enter the employee name and salary , The ADD button add the employee information to the database and view button helps to display all employee information available in the database. Empview form responsible for display list of employees  and there respective salarys.To interact with SQL CE I have developed SqlCeHleper class which have the functionality such as

  • SetupDatabase() : Creates the database if it is not present in the target device/emulator ,opens the SqlCe connection and instantiate the SqlCecommand object.

  • GetConnection() :  if the database already present it opens the Connection and instantiate the Command object.

  • GetUserList()() : Returns the DataReader contains all the emp information

  • AddEmployee (string strEmpName,int sal) :  Add the employee information to the database.

  • DisConnectDatabase(): Close the database connection .

The client (forms) interacts with the sqlce helper class to communicate with the database. I have not implement the full functionality what actually I am looking for is you to enhance this application like adding roles/responsibility of the employee , expected salary etc in employee entry screen or implement DataAdapter instead of DataReader to make loose coupling of database .You can say it's a beta version  and try to give a high quality shape to this application .Download the application for enhancement or for reference purpose .

Those who are new to smart device application can go through the  below easy steps to create application for Pocket PC.

Step1: Click on File->New Project menu option and select your desired language to develop the application and choose  smart device application from the template

 

           [Smart device application template to develop application for PDA]

Step2:  Select the target platform ( in our example its Pocket PC) and select the type of project  ( in our example its windows application ) .You can choose class library if you want to develop service component for smart device  or if you want to develop console based application choose Non-graphical application 



[Choose platform type and project type]

Conclusion

The .NET Compact Framework is no doubt a much smaller set of classes of library than the .NET Framework, and .NET Compact Framework is tuned for size and performance for devices. Binary sizes are also distinct as the .NET Framework is about 20 times larger. The .NET Framework binary size is about 30MB, whereas the .NET Compact Framework is about 1.5MB.

Both the .NET Framework and the .NET Compact Framework provide a consistent and familiar programming model. There are key similarities such as namespaces, classes, method names, properties, and data types. Programming is simplified and more secure due to the architecture and design of these platforms. Developers familiar with .NET Framework programming will find it easier adopting the .NET Compact Framework and will enjoy the same experience as developers who are familiar with the .NET Framework on top of this .NET compact framework provides automatically deployment of application  and all dependencies ( in our example .NET compact framework, SQL CE and application itself) without users intervene.

Now Microsoft is coming with still much more compact version of .NET library is called TinyCLR. TinyCLR. This is a version of the CLR that is smaller than the desktop version and even smaller than the Compact Framework version. It is the really tiner version and MS is able to put the TinyCLR on an integrated circuit running an ARM processor. This is what is in the watches today.  The watch also has an FM radio reciver in it and this is how it receives information over the air to know more about it refer more information section of this article.

More information

http://samples.gotdotnet.com/quickstart/compactframework
http://msdn.microsoft.com/windowsmobile
http://msdn.microsoft.com/msdnmag/issues/03/01/SQLServerCE20/default.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/netcfperf.asp
http://msdn.microsoft.com/msdnmag/issues/02/03/NETCF/default.aspx
http://msdn.microsoft.com/msdnmag/issues/04/12/NETCompactFramework/
http://www.pocketpcdn.com/sections/dotnet.html
http://msdn.microsoft.com/smartclient/default.aspx?pull=/library/en-us/dnnetcomp/html/instnetcfapp.asp

TinyCLR links

http://dotnet.sys-con.com/read/84123.htm
http://wintellect.com/WEBLOGS/wintellect/archive/2004/09/18/464.aspx
http://www.windowsfordevices.com/news/NS5768190239.html

Up Next
    Ebook Download
    View all
    Learn
    View all