Saturday, April 14, 2007

SQL Server 2005 Compact Edition

Here is my problem… I need to develop a PIM (Personal Information Management) application. It will be a small client application that users can take along with them on their laptops or USB drives or even PDAs. What are my storage options in this scenario?

Having an XML or text file to store complete data for this application is not even worth of thinking when you know that your data is growing and there will be sensitive information that needs to be stored securely and searched quickly.

Another option is MS Access mdb file. It has its own issues and the debate of whether it is a better choice is still going on. Now that I heard about SQL Server 2005 Compact Edition (CE) (previously SQL Mobile or SQL Everywhere), I would certainly choose it just because I can leverage on my SQL skills and be consitent all across.


Microsoft SQL Server 2005 Compact Edition is the next version of SQL Server Mobile adding the desktop platform. SQL Server Compact extends the SQL Server Mobile technology by offering a low maintenance, compact embedded database for single-user client applications for all Windows platforms including tablet PCs, pocket PCs, smart phones and desktops. Just as with SQL Server Mobile, SQL Server Compact is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005 for developing desktop and mobile applications.


Unlike SQL Server 2005 Express Edition, CE is much smaller (less than 2 MB) and can have a database within a password protected single file (with sdf extension). I completed its installation within a minute. I used Visual Studio 2005’s Server Explorer Data Connection to create a new database and to connect to it. Same interface can be created to design and populate tables. You can also use SQL Management Studio to mange the database.


To use it in an application, add a reference to System.Data.SqlServerCe. Then connecting to this database is a breeze.

Imports System.Data.SqlServerCe

Dim objConn As SqlCeConnection
Dim strCon As String

strCon = "Data Source=FirstDB.sdf;Encrypt Database=True;Password=Welcome123;File Mode=shared read;Persist Security Info=False;"
objConn = New SqlCeConnection(strCon)
objConn.Open()

Inserting data is also very simple:

Dim objComm As SqlCeCommand
Dim strSQL As String

strSQL = "INSERT INTO TestTable (ColName) VALUES ('" + txtName.Text.Trim() + "')"
objComm = New SqlCeCommand(strSQL, objConn)
objComm.ExecuteNonQuery()

Getting the data back:

Dim objAdapter As SqlCeDataAdapter
Dim objDataset As DataSet
Dim strSQL As String

strSQL = "SELECT ColID as [ID], ColName AS [Name] FROM TestTable"

objAdapter = New SqlCeDataAdapter(strSQL, objConn)
objDataset = New DataSet()
objAdapter.Fill(objDataset, "Products")

dgResult.DataSource = objDataset.Tables("Products")


I created a small windows .NET 2.0 application that insert data into a local CE database and populate it on a grid as shown in the above code. I could make it work in other machines where CE is not installed by copying all the CE dlls along with my executable. It is that simple and easy.
The advantages are many. It is in-proc and supports transactions. When the application grows, it is easy to migrate to SQL Server. The synchronizing features helps to get the local data synchronized with a central server using RDA (Remote Data Access) or Merge Replication.


The issues? It does not support views, triggers, stored procedures or user functions. Though you can have SQL scripts embedded in your project separated with GO statements and get them executed together. The key minds from Microsoft behind this technogy, Steve Asker and Anil Nori explined in their web cast that they are not planning to provide support for stored procs, etc, not just because that the size of this product will grow, but mainly because they dont want the business logic to be residing in the database.

Sunday, April 08, 2007

Virtual PC 2007

I first encountered with this wonderful virtualization product from Microsoft sometime back when we had to setup 20 computers with identical software installations for a training purpose. The software packages we required were really huge taking up hours to install (SQL Server 2005 and Visual Studio Team System 2005). Virtual PC helped us here. All we had to do was to setup one Virtual PC image with all of them and it was just the matter of copying these files into the computers and installing only the VPC software on them.


Now that I have a new laptop with Windows Vista, I didn’t want to install all my development tools on it to make it slower and problematic. Solution? Virtual PC! The new version 2007, which is totally free, now supports Vista as both host and guest. So I have a Windows XP Virtual PC with old favorites like VB6, and another VPC with Windows 2003 and VS 2003 and VS 2005. Another one is in making with Vista and .NET 3.0.


Virtual PC also helps me in trying out any software without disturbing my original system. And whenever I get new software that is not working in Vista, I can use them in XP or 2003. Now I am a complete Virtual PC enthusiast!

Sunday, April 01, 2007

Windows Media Player 11 And Codecs

Windows Media Player 11 that comes loaded with Windows Vista is certainly great… lot more features and interesting look and feel… but wait a minute… Does that mean I don’t have to use Winamp any more… not exactly… again its based on our personal comfort feeling… I have been very comfortable with Winamp… I thought the new version of WMP will make sure that I do not require any third party tools to play my media files… But soon I realized I needed my Winamp back to quickly load and play my MP3s with better quality.


Then it was about video files… I have a huge collection of my favorite movies in my external hard disk. It was of all kinds of file types starting from dat to rmvb, avi and mp4. Most of them didn’t work in WMP. Some played just the audio track. After spending a good time on googling for suitable codecs to make it work the way I wanted, I ended up downloading DIVX and FFDSHOW. That did the trick. Now my WMP plays all kinds of video files. It just need some breathing time in the beginning to change to proper codecs. Then there was REAL ALTERNATIVE to play all RealMedia files without the need of installing the actual Real player with nagging registration forms.