Development | .NET Compact Framework

Developing an e-wallet using .NET Compact framework & SQL Server CE
Written by Gerald Schinagl  [author's bio]  [read 47589 times]
Edited by Derek

         

Page 1  Page 2 

Why did I start coding the application ?

I work in a consulting company, so I usually have to deal with a large number of different user names and passwords of my customers systems, that are changing constantly. I have one set of passwords for customers intranets, another set of passwords for different users in different applications and databases, and of course I have a lot of customers.

Confusion reigns every time a login screen tells me my password was not accepted. This is usually the point at which an internal quiz starts; did I provide the password of User x in Database y of company z or was it the former Windows NT Password of my home network ?

Believe me, I have been groveling and pleading to Sysadmins more than once because I locked myself out of the system as I used the wrong passwords. And there is another factor that complicates issues. Most of my customers have strict rules on their passwords affecting what size the password must be, whether the password must contain numeric characters or not and whether the password can be the same as one of the last x passwords you have used. So I end up sitting in front of of the computer and racking my brain as to what were the last 9 passwords on that system I am now locked out of.

Starting to solve the problem

Thinking of a solution for that problem I decided to code a Password Database Application for my PDA, since I carry it with me almost all of the time. The PDA is secured with a PIN, so the device is more secure than plain paper and it is also possible to encrypt the database with another password. By using the Application I just have to remember my PDA´s PIN and (if I like to be more secure) one additional password instead of at least 300 passwords I had to remember before.

First I decided to write the application in eVB as a prototype, but when I signed up for the Mobility Developers Conference in London I decided to wait for the .NET Compact Framework (CF) Beta and to code that application as my first CF Application, written in VB.NET. Some days after getting my hands on the Beta I started coding the application (in fact I developed the data model at Heathrow Airport, waiting for my delayed flight back home). After installing the .NET Compact Framework on my machine I started to implement the SQL CE 2.0 Database and the application. Sometime later, whilst in the midst of coding I had to redesign the database, as some features that I know from the Desktop Version of SQL Server are not implemented in the CE Version (for example correlated subqueries and the TOP Clause) or are poor performers like complex views.

As I have installed the Smart Device Extensions on my computer, I have some additional features when starting a new Project. I decided to do a new SmartDevice Application in VB.NET. After the initial configuration Dialog (deciding whether the application is for Pocket PC or general CE 3.0) Visual Studio starts with a blank Form. As a next step the Namespace System.Data.SQLServerCE has to be added to the Project (Add reference...) From this point on all SQL CE methods and properties can be accessed. The really cool piece here is, that Visual Studio does all the installation/deployment of the needed SQL CE files to the device for you. As I don't own a permanent CF card for my PDA my database resides in the device´s RAM (keeping the Database on a CF card would be much better in terms of data-loss). As shown in the following snippet, Application checks whether a database already exists or not on initialization. If not (usually not on the first deployment) a predefined Database-File is created. After the creation of the data file a connection to the particular file is opened and the necessary tables and constraints are created.

If Not System.IO.File.Exists("\my documents &_
\delphip.sdf") Then
  Dim en As New Engine("data source =\ &_
      my documents\delphip.sdf")
  en.CreateDatabase()
  'The Connection Object (cn) is instantiated &_
      outside that Snippet !
  cn.Open()
  'Create the Database
  Dim command As New SqlCeCommand("CREATE TABLE &_
   company (comp_id int IDENTITY (1, 1) unique &_
   not null, company national character varying &_
  (30) Primary Key NOT NULL)", cn)
  command.ExecuteNonQuery()
  command.CommandText = "CREATE TABLE &_
   application (app_id int IDENTITY (1, 1) &_
   Primary Key NOT NULL ,comp_id int NOT NULL ,&_
   applikation national character varying (30) &_
   NOT NULL) "
  command.ExecuteNonQuery()
  command.CommandText = "CREATE TABLE passwords &_
   (user_id int NOT NULL , create_date datetime &_
   NOT NULL ,password national character varying &_
   (100) NOT NULL, active bit not null ) "
  command.ExecuteNonQuery()
  command.CommandText = "CREATE TABLE username &_
   (user_id int IDENTITY (1, 1) NOT NULL ,app_id &_
   int NOT NULL ,username national character &_
   varying (30) NOT NULL , last_pwd smallint NOT &_
   NULL , min_chr smallint NULL , numeric_in bit &_
   NOT NULL) "
  command.ExecuteNonQuery()
  command.CommandText = "CREATE UNIQUE INDEX &_
   idxapplikation ON application (comp_id, &_
   applikation)"
  command.ExecuteNonQuery()
  cn.Close()
End If

After you start debugging you will se a new icon (SQLCE Query) in the Start Menu of the Device that opens a Query Analyzer for windows CE. I won´t tell you more about that nice tool in this article, but one remember this: "Don't close the Query Analyzer by using the x (Analyzer is set to background) but use the Exit Command in the Tools Menu. Otherwise other applications will fail as SQL CE 2.0 supports only connection at a time (and the Query Analyser is will still be running in the background).

Next Page 

Back to .NET Compact Framework | [Article Index]