VSTO & .NET & Excel

August 30, 2010

Using SQL Server Compact Edition Database with Excel

Filed under: .NET & Excel, SQL Server — Dennis M Wallentin @ 1:19 am

Introduction
Often we see questions about acquiring data from various data sources like SQL Server Databases, MDBs, Excel and text/xml-files in webbased Q&A forums and in blogs. But what I see very rarely is any discussions on how to retrieve data from Microsoft SQL Server Compact Edition (SSCE) databases and to populate Excel worksheets with the acquired data. This article will introduce how to use SCEE databases as data sources and dump the retrieved data into a pre-defined range in a worksheet.

SSCE is a lightweight version of the SQL Server database engine. It requires a minimal installation and configuration to use. Unlike its bigger “brother” it’s a server less database engine.

For an introduction to SSCE databases please see the following sites:

This article describes how to proceed to get data into an Excel solution from a SSCE database by using VBA. But before diving into it let us first take a look on how to create a SSCE database and its limitations.

Creating SSCE Databases
As with many other targets to achieve; creating SSCE databases can be done in some ways. Here I will show how simple it is by using the SQL Server Manager Studio which I regular use (as opposite to my use of the standalone SSCE database engine which I never use.)

Actually, it’s very simple to create a SSCE database from scratch:

  • Open the SQL Server Manger Studio and in the Connect to Server Dialog we first select the Server type SQL Server Compact Edition and  select <New Database…> in the Database File’s field as the following screen shot shows:

  • In the next step we actually create the SSCE database as the following screen shot also shows:

When the database has been created we need to add the wanted tables and their contents inside the SQL Server Manager Studio.

But what if we have a SQL Server database and would like to convert it to a SSCE database? The easiest way I have found out is simple to use a third-party tool to do it for us. In my opinion the best tool for the job is the inexpensive Data Port Wizard tool from Primeworks. In addition to convert SQL Server databases it can also convert in a smooth way MS Access databases to SSCE databases. In other words, it shouldn’t be difficult to port these database types to SSCE database type.

Limitations of SSCE Databases
SSCE is on its face very attractive but before we get carried away we need to take a closer look into its limitations.

  • The most important limitation, in my opinion, is that the SSCE does not support the use of views, stored procedures or functions. In other words, its main job is to store larger sets of data and make the data available for mobiles and desktop applications and nothing else.
  • The maximum database size is 4 GB which is in my world is a rather large database size. But by default the data size is only 256 MB and to change the size change we  need to add the parameter Max Database Size=xxxx MB to the connection string.
  • The maximum rowsize is 8060 bytes but since this exclude text and blob fields there exist rarely a practical limitation, at least not in my experience.
  • It does not support ASP/ASP.NET which does not affect me at all.
  • Edit: SSCE targets individuals only, i.e. it does not allow multiple users to access a database simultaneously which SQL Server and the Express Edition allow (see Mathias comment below).

Populate a Worksheet by using VBA, ADO and a SSCE Database
Before we start to take a closer look on the code solution let me first mention that if You want more knowledge about ADO than You can download MDAC 2.8 which includes a superb help file (ado28.hlp) on the subject: MDAC 2.8 SDK

The next thing I feel I must point out is the driver to use with SSCE databases and the required system files for running them. When distributing solutions that involve SSCE databases we must also distribute theses files. Actually, in my experience this is a must in all cases. To do so we first need to get the redistributing MSI file which we do by downloading the package Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop. When running the EXE file we get the required MSI file for x86 as well as the MSI file for the x64 platform. Actually included in the package is also the MSI file that includes both these two files.

What is also notable is that although we know it’s available the driver does not show up in the Data Link Wizard or in the .NET Wizard. Because of its anonymous character we can only get the correct connection string by trying. But a good start for the efforts is SQL Server Compact Edition Connection Strings Samples.

After installing the required files we can execute, for instance, the following code in order to populate a worksheet with data from a SSCE database:

Option Explicit
'Make sure You have a reference set to Microsoft
'ActiveX Data Objects 2.5 Library or later.

Sub Retrieve_Data_From_SSCE_In_VBA()

'Name of the SSCE database.
Const C_stDBName = "XLDennis.sdf"

'The SQL Expression to be used.
Const C_stSQL = "SELECT CompanyName AS Company, City FROM Customers;"

Dim stPath As String
Dim stConnection As String

Dim ADODBcnt As ADODB.Connection
Dim ADODBrst As ADODB.Recordset
Dim ADODBfld As ADODB.Field

Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnstart As Range

Dim lnFieldCounter As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)
Set rnstart = wsSheet.Range("A2")

'Path to the SSCE database.
stPath = "C:\Users\Dennis Wallentin\SSCE\"

'Connection string.
stConnection = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" & _
 "Data Source=" & stPath & C_stDBName & ";"

'Instantiate the ADODB objects.
Set ADODBcnt = New ADODB.Connection
Set ADODBrst = New ADODB.Recordset

'Open the connection to the databas.
ADODBcnt.Open stConnection

'Create a disconnected recordset.
With ADODBrst
 .CursorLocation = CursorLocationEnum.adUseClient
 .Open C_stSQL, _
 ADODBcnt, _
 ADODB.CursorTypeEnum.adOpenForwardOnly, _
 ADODB.LockTypeEnum.adLockReadOnly, _
 ADODB.CommandTypeEnum.adCmdText
 .ActiveConnection = Nothing
End With

'Close down the connection.
ADODBcnt.Close

Application.ScreenUpdating = False

'Populate the worksheet with the field names.
For Each ADODBfld In ADODBrst.Fields
 rnstart.Offset(-1, lnFieldCounter).Value = _
 ADODBrst.Fields(lnFieldCounter).Name
 lnFieldCounter = lnFieldCounter + 1
Next ADODBfld

'Dump the data into the worksheet.
rnstart.CopyFromRecordset ADODBrst

'Close the recordset.
ADODBrst.Close

'Release objects from memory.
Set ADODBrst = Nothing
Set ADODBcnt = Nothing

End Sub

Because of the limited differences between VBA and VB.NET I decided to exclude the sample when using VB.NET. I believe it should not be a problem for You.

Kind regards,
Dennis

I have also published a post about how to create a SSCE database with VBA.

About these ads

6 Comments »

  1. Is there a difference between SQL Server Compact Edition and SQL Server Express? If yes, how to choose between the two?

    Comment by rvyafin — August 30, 2010 @ 8:38 am

    • Yes, it is as SQL Server Express can use stored procedures, views and functions which SSCE cannot. SQL Server Express requires a server installation before we can run any databases which SSCE does not request.

      It’s difficult to outline a general guide about which to choose but in my experience I used SSCE when I needed to distribute data to end users’ desktop. Whenever we have access/are allowed to access servers it’s the preferred way.

      Hope the above may clear it at least a little bit.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — August 30, 2010 @ 12:36 pm

      • Of course: now that you have written it in plain language (the stored procedures, views, function and server installation) I bang my head because I should have known it.

        Thanks for the clarification.

        I guess that the SQL CE could in many case replace Access as a free data store (i.e where you do not use specific access features like Forms, etc.)

        Good article

        Comment by rvyafin — August 31, 2010 @ 9:06 am

      • Thanks for Your kind words. Although I haven’t made any science comparison between SSCE and MS Acces it seems that SSCE is faster, especially when dealing with large data sets. Yes, MS Access is not always available so SSCE can be a better option than it.

        By the way, I will post another article about SSCE and it will cover how to create SSCE databases with VBA and with VB.NET.

        Kind regards,
        Dennis

        Comment by Dennis Wallentin — August 31, 2010 @ 1:13 pm

  2. Good post! Regarding Ryvafin’s question, one way to think about it is that AFAIK, SQL Server Express is a less powerful version of SQL Server, whereas SQL Server CE allows you to deal with your local files as if they were in a SQL server. I believe (not totally sure) CE doesn’t allow multiple users to access it simultaneously – the idea is that rather than having to save your file in xml or text, you can save it and work with it as if it was a database, using SQL tools.
    One aspect which is nice, but I don’t think you can use it from VBA, is that you can leverage tools like Linq to SQL or the Entity Framework to play with your data, and bridge the gap between the world of data, and the .NET world of objects.
    Mathias

    Comment by Mathias — September 4, 2010 @ 2:51 am

    • Mathias,
      Good catch and I have added it to the list of limitations that SSCE has.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — September 4, 2010 @ 12:56 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers

%d bloggers like this: