SQL Server CE Programming with Pocket PC and Visual Studio.NET 2003: Databases and Tables
This article is focused on developing SQL Server CE based applications using a Pocket PC emulator with Visual Studio.NET 2003. We will program to create databases and tables for SQL Server CE using Pocket PC together with Visual Studio.NET 2003.
I suggest that you download and install Pocket PC 2003 SDK (available free) and the additional latest Pocket PC emulators from the Microsoft website. I use them in this article.
If you are new to mobile application development (or confused with Windows mobile, smart phone, Windows CE, Pocket PC, and so on), I strongly suggest you go through the article I already contributed on “Developing Pocket PC applications using Visual Studio.NET 2003” before proceeding further.
If you are new to SQL Server CE based development using Pocket PC, I strongly suggest you go through another introductory article of mine here.
Creating SQL Server CE databases and tables in Pocket PC programmatically: Layout
In my previous articles, I introduced developing simple applications and Microsoft SQL Server CE based applications using Pocket PC emulator with Visual Studio.NET 2003.
In this article, I shall deal with creating databases and tables in SQL Server CE database programmatically (or dynamically).
Let us begin now:
Open Microsoft Visual Studio.NET 2003.
Go to File -> New -> Project.
Within the “New Project” Dialog, select “Visual Basic Projects” from the “Project Types” and “Smart Device Application” from the “templates.” I left the name of the application “SmartDeviceApplication1.” You can change it according to your requirements.
Once you click “OK,” you will be presented with the “Smart Device Application Wizard.” Select “Pocket PC” as the platform of target and “Windows application” as “project type.”
Click “OK” to accept your settings. You will be presented with a form (very similar to a Visual Basic 6 form) where you can design with the controls available in toolbox.
Right click on your project and go to “Add References.” Select “System.data.common” and “System.data.SqlServerCE,” click “select” and finally click on “OK.”
Drag a “TabControl” from your toolbox onto the form and add a tab using the property “TabPages” in the “Properties” window. Name the tab “Create.”
Drag a “Button” onto the tab and give it the caption “CreateTable” (name it “btnCreateTable”). By this time, your form should look as follows (fig1):
Actually “TabControl” is not necessary. I simply wanted to demonstrate the tab control, we shall extend its use in my future articles! In the next section we shall write the code for that button.
Make sure that you have completed all the steps in the previous section before proceeding further. Go through the following steps to write the code.
Double click on the “Create Table” button to go to the code window.
Include the following two lines at the top:
Imports System.Data.Common
Imports System.Data.SqlServerCe
Just before the “btnCreateTable_click” event, declare a variable to hold the connection string (as follows):
Dim connStr As String = "Data Source = emp.sdf; Password = jagchat"
You can modify the connection string according to your requirements. Within the button click event, copy the code in such a way that it looks like this:
Imports System.Data.Common
Imports System.Data.SqlServerCe
Public Class Form1
Inherits System.Windows.Forms.Form
.
.
.
.
Dim connStr As String = "Data Source = emp.sdf; Password = jagchat"
Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTable.Click
Try
'creating database
Dim engine As SqlCeEngine
engine = New SqlCeEngine(connStr)
engine.CreateDatabase()
engine.Dispose()
'creating table
Dim cn As New SqlCeConnection(connStr)
Dim sql As String
sql = "CREATE TABLE emp "
sql &= "("
sql &= " empno int PRIMARY KEY,"
sql &= " ename nvarchar(20),"
sql &= " sal float,"
sql &= " deptno int"
sql &= ")"
Dim cmd As New SqlCeCommand(sql, cn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Dispose()
cn.Dispose()
MessageBox.Show("Succesfully created")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
End Class
In the next section, we shall see how to execute the above application.
Before proceeding with the following steps, make sure that you completed all the steps in the previous section.
Execute your application by pressing F5 and you should be looking at something like the following image (Fig2).
If necessary, you can also click the “Set As Default” button and switch off the check box to not to show in the future.
Finally click on “Deploy.”
After a few seconds, you can observe the installation of the .NET Compact Framework and SQL Server CE installation (which is after the .NET Compact Framework).
Finally, after few more seconds, we will see our application with the button. Once you click on that button, it simply shows a message something like the one in the following image (fig3).
The above process gets repeated every time you execute your application. If you face any deployment errors, just close any existing emulators and try pressing F5 again (without switching on emulators manually by yourself).
Where is the database and table? You need to open the database “emp.sdf” (by simply clicking on it) available in root (or at “My Device”). Once you open it, it simply gives you a "wrong password" message and prompts for the password to open. You provide the password as specified in the previous source code. Once everything is successful, you should be able to see the database and table as something like the following image (fig4):
If you don’t know how to open the database, I suggest that you go through my previous article.
In this section, I shall explain to you the source code I presented in the previous sections. I shall explain it part by part. Let us start with the following:
Imports System.Data.Common
Imports System.Data.SqlServerCe
To work with Microsoft SQL Server CE in .NET, we need to add a reference and import the above two namespaces (or assemblies). It is quite different from the standard SQL Server 7.0/2000/2005 editions. The namespace “System.Data.SQLClient” would never be used to work with SQL Server CE databases.
Further proceeding, we have the following:
Dim connStr As String = "Data Source = emp.sdf; Password = jagchat"
The above statement specifies the connection string for the database. It would be a bit different from the traditional connection string we use with a traditional SQL Server database. Unlike the traditional SQL Server connection string, we need to provide the path of the database file. Further proceeding we have the following:
'creating database
Dim engine As SqlCeEngine
engine = New SqlCeEngine(connStr)
engine.CreateDatabase()
engine.Dispose()
The above code fragment simply creates an object based on the class “SqlCeEngine” available in the “System.Data.SqlServerCe” namespace. We provided a connection string as the parameter while creating the object. The database gets created simply by calling the method “CreateDatabase” related to the “SqlCeEngine” class.
Further proceeding we have the following:
Dim cn As New SqlCeConnection(connStr)
Dim sql As String
sql = "CREATE TABLE emp "
sql &= "("
sql &= " empno int PRIMARY KEY,"
sql &= " ename nvarchar(20),"
sql &= " sal float,"
sql &= " deptno int"
sql &= ")"
To work with SQL Server CE, we need to open a database connection to an existing database. For the traditional SQL Server, we will generally use “SQLConnection.” In this scenario, we use “SqlCeConnection” which is specifically dedicated for SQL Server CE databases. The above code fragment also frames a “CREATE TABLE” statement to create our own table.
Further proceeding, we have the following:
Dim cmd As New SqlCeCommand(sql, cn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Dispose()
cn.Dispose()
To work with any SQL command with a traditional SQL Server, we will work with the “SQLCommand” object. In this scenario, we use “SqlCeCommand” which is specifically dedicated for SQL Server CE databases. The above code fragment simply creates a “command” object (accepting our framed SQL statement and database connection as parameters) and executes it. Before executing the command, we need to open the connection to the database (which is achieved in the second statement in the code fragment above).
That completes our understanding of creating databases and tables using Pocket PC for SQL Server CE.
I developed the application using Microsoft Windows Server 2003 Standard Edition with Microsoft Visual Studio.NET 2003 Enterprise Architect and Pocket PC SDK 2003 (along with additional emulators downloaded).
In my upcoming articles, I shall concentrate further on developing advanced Pocket PC applications. So, keep close tabs on this website :)
Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com
MS SQL Server 
ASP.NET Export to Excel and Word using VB.NET and C#
