Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLCE Workshop IV - Integration with Visual Studio 2005

By Jacob Sebastian,

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.

Resources:

test.sql
Total article views: 3111 | Views in the last 30 days: 21
 
Related Articles
FORUM

Insert query

Bulk Insert query

FORUM

Different RESULT mssql_query vs. SSMS?????

Insert Fails in mssql_query, same query string succeeds in MS SQL Server Management Studio

FORUM

Cannot execute queries from Management Studio

Cannot execute queries from SQL Server 2008 Management Studio

FORUM

Unable to create a database using SQL Server Management Studio or from external program

Unable to create a database using SQL Server Management Studio or from external program

ARTICLE

Creating Views - SQL School Video

This video covers the basics of creating a view in SQL Server. Views allow you to treat a query as a...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones