SQL Server CE Programming with Pocket PC and Visual Studio.NET 2003: Databases and Tables - Creating SQL Server CE databases and tables on Pocket PC programmatically: explanation
(Page 4 of 4 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
