Introduction
In the previous sessions of SQLCE workshop we have seen some simple examples which explained some basic database operations like creating a database, table and inserting data to the tables. In this session we will see a few examples which are little more detailed.
I work most of the day with SQL Server Management Studio. Most of my work involves writing TSQL Code. My development environment is SQL Server Management
Studio. Hence I write more of TSQL code and very little of DOT NET
code. I guess most SQL Server developers must be spending most of their programming hours on Query Analyzer or SQL Server Management Studio. While working with SQLCE, we need to compromise a little. There is not much that we could do from SQL Server Management Studio. The only activity that we might perform could be creating a table or querying some data from the tables. SQLCE
does not support stored procedures. Neither VIEWS nor Functions are supported.
The most powerful weapons of a TSQL developer are taken away. However, it makes sense given the fact that it is an embedded database engine which loads into the application's memory space.
So it is apparent that we will have to do most of our programming outside SQL Server
Management Studio if we are working on an SQLCE project, or a project that uses SQLCE seriously. In this session, we will not use SQL
Server Management Studio at all. SQLCE is tightly integrated with
Visual Studio 2005 and we really do not need Management studio
to create an SQLCE application.
Creating the DB and Tables for the Sample Application
Let us create a new SQLCE database and create a couple of tables for our sample application. Today, we will not use SQL Server Management Studio.
Instead, we will do it from with in Visual Studio 2005. Open Visual Studio
2005 and create a new project. Once the project is created, click on the "Server Explorer" and right click on "Data Connections" to create a new connection.
In the "Add Connection" dialog box, make sure that you have data source "Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server Everywhere)" selected. If it is not the default value, click on the Change button and select Microsoft SQL Server Mobile Edition (.NET Framework Data Provider for SQL Server Everywhere) as the data source.
Click on the Create button to create a new database.
Enter the name of the database file that you wish to create. You can enter a password if you would like to have it protected. If you prefer, you can select the Encrypt check box for greater protection. After the database is created, click on the Test Connection button to make sure that the database is created correctly and a connection to the database succeeds. If you click on the Advanced button, it will show you the connection string at the bottom of the dialog. We will use this connection string to connect to the SQLCE database.
Click on the OK button and close the dialogs. At this stage, a new Data Connection will appear in the Server Explorer. If you do not see it yet, click on the Refresh button.
Let us create a new table now. Expand the Data Connection tree root and right click on the Tables node. Select Create Table menu option. This will open the Table Designer. It is very close to the table designer window that we find in SQL Server Management Studio.
Let us create a new table named Departments. Make the first column, DepartmentID an IDENTITY column. When you set the identity column you need to make sure that the Allow Nulls setting is changed to False. By default this will be True and if you do not change it, you will get an error. Note that SQLCE does not have VARCHAR data type. Let us use NVARCHAR for storing the DepartmentName.
If you are more comfortable writing DDL queries, you should select New Query instead of Create Table. This will open the query editor and you can write your TSQL statements here. Let us create the next table by running a DDL query.
Write your query and click on the Run button (RED Exclamation Mark) on the toolbar to run the query. If you like keyboard shortcuts, click CTRL+R to run the query. NOTE that, unlike SQL Server Management Studio, F5 will not execute your TSQL statements. You should use CTRL+R to execute your queries. If you press F5, it will run your application.
Resource Files To Store Queries
I hate to hard-code TSQL statements with in the source code. I guess most of you would not prefer to write your queries inside the source code. But having said that
SQLCE does not support stored procedures, we are left with no other option than to write all our queries inside the application. Well, if you really hate doing that, you can probably use a Resource File to store your queries and then execute those queries from your application.
Let us write two queries. The first query will insert a record to the Departments table and the next will insert a record to the
Employees table. We will store those two queries into a Resource file. We will load the queries from the resource file at run time and will do a dynamic substitution of parameter values. Let us see how it works. Here are the two queries that we need.
1 -- Query to insert into "Departments" table
2 INSERT INTO Departments (DepartmentName)
3 VALUES ('{0}')
4
5 -- Query to insert into "Employees" table
6 INSERT INTO Employees (EmployeeName, Department ID)
7 VALUES ( '{0}', {1} )
Do not be surprised to see values like "{0}" inside our query. These are not valid TSQL values. Instead, these are place holders for specific values which we will insert into the given location before running the query. We will use String.Format() method to insert parameter values into the TSQL query.
Next, let us add a Resource File to the project. Right click on the project and select "Add" and then select "Add new item".
Double click on the resource file name in the project explorer and the resource editor will open. Add the two queries we created previously into the resource file.
Now let us write the code which inserts records to both the tables.
1 'Import "SqlServerCe" Namespace
2 Imports System.Data.SqlServerCe
3 Module Module1
4
5 Sub Main()
6 'Create and open the connection
7 Dim con As SqlCeConnection
8 Dim str As String = "Data Source =""c:\employee.sdf"";"
9 con = New SqlCeConnection(str)
10 con.Open()
11
12 'Load the queries
13 Dim depQry As String
14 Dim empQry As String
15 depQry = My.Resources.Resource1.InsertDepartment.ToString()
16 empQry = My.Resources.Resource1.InsertEmployee.ToString()
17
18 'Insert a new department
19 Dim Qry As String
20 Qry = String.Format(depQry, "Information Technology")
21 Dim cmd As SqlCeCommand = New SqlCeCommand(Qry, con)
22 cmd.ExecuteNonQuery()
23
24 'Retrieve the DepartmentID
25 cmd.CommandText = "SELECT @@IDENTITY"
26 Dim deptID As String
27 deptID = cmd.ExecuteScalar().ToString()
28
29 'Insert a new Employee
30 Qry = String.Format(empQry, "Jacob", deptID)
31 cmd.CommandText = Qry
32 cmd.ExecuteNonQuery()
33
34 'Clean up
35 con.Close()
36 con.Dispose()
37 cmd.Dispose()
38 End Sub
39
40 End Module
I think the code does not need any explanation except for line 24. After we insert a new department, we need to retrieve the IDENTITY value of the new record. This value should go to the Employees table as a foreign key. With SQLCE,
you can run only one statement at a time. Hence, you need to run a second query to retrieve the IDENTITY value of the previous insert query.
Conclusions
In this session, we examined SQLCE's integration with Visual Studio 2005. We created a database, added tables and inserted records, all from Visual Studio. We saw how to use a Resource file to store queries. This will be helpful if you do not want to hard-code TSQL code inside your application. At last, we saw how to retrieve the IDENTITY value of a new record inserted to one of the tables.
