Creating your SQL Server Compact Edition database and schema in code


In most cases you’ll want to create your database and it’s schema as part of your applications first deployment.  Depending on the scenario, you may create it on the client, or create it on the server, do the initial sync, then stream the file down to the client pre-populated.


Why would you create the database on the fly, rather than ship the initial database with your app?


While you could create the empty shell, and deploy it, you’ll almost never want to start with a database that has data?  Why?  While in development, you’ll have some set of data.  Months later, when another user first installs your app that data may no longer be valid.  Since most sync systems don’t track deletes forever, you could wind up with data that is very stale, and now way to effectively update it. 


Data Files and Click Once


If you’re using ClickOnce, the other problem is how ClickOnce manages data files.  If you initially ship your app with a datafile that your customers edit you could loose those changes.  If the file timestamp on your data file changes, ClickOnce will try to “update it” and replace the data file in your ClickOnce data directory.  It does move the previous version to a .pre folder, but only once.  If you realized you made this mistake, and send another update to ‘fix it”, you could again move the data file to the .pre folder, and the original one that had the data you were trying to save will be permanently lost. 


For this post, I’m going to focus on how to create the database and schema.  You’ll see that with a combination of some SQLServerCe APIs, and the use of Resources, it’s not very difficult to create, test within SQL Server Management Studio and deploy these updates to your client.  This same procedure could be used for the initial deployment, as well as app updates where you may need to add new tables/columns, or add new relationships, constraints or indexes.


When an app starts up, I like to validate a few things like whether this is the first time the app was installed, if it’s the appropriate version, if all the necessary files still exist, as well as online/offline status.


Does the database exist?


I’ll first create a class called DatabaseHealthCheck()  I’ll first check if the database even exists.  Since with SQL Server Compact a database is a file, and a file is a database, it’s pretty straight forward.  The file either exists, or it doesn’t.  The connection string for a SQLce database contains a few name/value pairs depending on the options. 


Data Source =”|DataDirectory|\Another.sdf”; Password =”SomePassword”;


Rather than parse the string, or build it up manually, the SqlCeConnection object there’s a Database property that many don’t even think to look for.  Since the database is a file…, all you need to do is create a connection object, and ask for the Database property.  No need to open the connection, which wouldn’t work if the database/file didn’t exist.


Dim localConnection As New SqlCeConnection(My.Settings.LocalConnectionString)


If Not System.IO.File.Exists(localConnection.Database)


Nope, doesn’t exist, let’s create it


Next, if the database didn’t exist, we’ll need to create it.  This is where it gets interesting.  Since SQLce doesn’t run as a service, you can’t open a connection to the Master database and issue a Create Database command.  No problem, SQLce has just the API for you.  How about these two lines of code:

Dim sqlCeEngine As New SqlCeEngine(My.Settings.LocalConnectionString)

sqlCeEngine.CreateDatabase()


Pretty simple, huh.  For those of you that were wondering why you would use SQL Server Compact Edition as your local database, now you’re probably starting to see how SQLce can be used to reduce the complexity really needed in a local/embedded database.


Creating the initial schema


It would be nice if we had a simple CreateTable API on the SqlCeEngine object, but we’re not there yet.  But we can leverage T-SQL that we’d write in SQL Server Management Studio, at least the appropriate subset.  We’re all familiar, although it’s likely not our favorite, with the ability to create scripts, separated by the GO command.  Using this model we can test scripts, save them and re-execute them.


CREATE TABLE Suppliers(


      SupplierID nVarChar(10) NOT NULL CONSTRAINT Suppliers_PK PRIMARY KEY,


      CompanyName nvarchar(40) NOT NULL,


      Description nvarchar(100) NULL,


      SortOrder int NOT NULL DEFAULT(50),


      Active bit NOT NULL DEFAULT (1)


)


GO


CREATE TABLE OrderStatus(


      OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY,


      Status nvarchar(40) NOT NULL,


      Description nvarchar(100) NULL,


      SortOrder int NOT NULL DEFAULT(50),


      Active bit NOT NULL DEFAULT (1)


)


GO


The problem is how do we get this in our code?  We could embed the whole thing in quotes in our code:


Dim commandTest As String


commandTest = “CREATE TABLE OrderStatus(“ + _


    ” OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY,” + _


    Status nvarchar(40) NOT NULL,”


But we don’t have to get past line 2 before you realize, that sucks.  And, if you ever want to make a change, you’ll have to decrypt this to put it back inside a query window.  No problem, this is where we can leverage the handy dandy resource features.


Adding a SQL Script Resource


We’ll start by creating a script in SQL Server Management Studio.  You can use the above CREATE TABLE scripts above to get things started.  Within Management Studio, choose to save the script.  It doesn’t really matter where, because VS will pull a copy into your project in the next step.


Name the script something with a .sql extension.  If you do this, when you open the file within Visual Studio, you’ll get T-SQL syntax coloring. 


Within Visual Studio, open the Resource designer.  For VB, double click My Project from within your Solution Explorer.  For C#, double click Properties.  Click on the Resources tab on the left.  From the toolbar select Add Resource à Add Existing Resource.  Select the file you just created, and voila. 


Now you might ask why I didn’t just create the file here using the Add Resource à Add New Text File.  If I went that path, and I named the file Script.sql, Visual Studio would have added .txt to it so it would wind up as Script.sql.txt.  If I then renamed the file in Solution Explorer, the link in the resources would be broken, and I would have to delete the resource reference, and then drag the renamed file back to the resource designer.  Either works, this second way just highlights a less optimized scenarios.  But, I guess I just did that.  …either way…


Double click the script file you added, and voila, T-SQL syntax coloring.  You may get a warning dialog, but just ignore it.  The project template doesn’t understand this extension, yet Visual Studio does.  You could just uncheck the checkbox so you won’t get nagged anymore.


Now, it would be nice if you could just execute the script, but no, this isn’t enabled in non-database projects.


Executing the script


We now have a script that we can copy/paste back and forth between Visual Studio and SQL Server Management Studio.  But how do I execute this with my app?  This is where we start to see the sum of the parts come together.  We can use the My.Resources api, or in C#, the Resources.Default api to get a strongly typed experience to the resources in your project. We’ll then simply use the String.Split api to parse the commands on the word GO.


Dim commands() As String


commands = My.Resources.DatabaseCreation.Split( _


    New String() {“GO”}, _


    StringSplitOptions.RemoveEmptyEntries)


Putting it all together


That’s about it, we just need to put this all together now.


Public Class DatabasHealthCheck


  Public Sub VerifyDatabaseExists(ByVal connectionString As String)


    ‘ we’ll use the SqlServerCe connection object to get the database file path


    Using localConnection As New SqlCeConnection(My.Settings.LocalConnectionString)


      ‘ The SqlCeConnection.Database contains the file parth portion


      ‘ of the database from the full connectionstring


      If Not System.IO.File.Exists(localConnection.Database) Then


        ‘ No file, no database


        Using sqlCeEngine As New SqlCeEngine(connectionString)


          sqlCeEngine.CreateDatabase()


          CreateInitialDatabaseObjects(connectionString)


        End Using


      End If


    End Using


  End Sub


  ”’ <summary>


  ”’ When the database is created, we need to initialize it with new tables, relations and possibly data


  ”’ </summary>


  Public Sub CreateInitialDatabaseObjects(ByVal connectionString As String)


    ‘ Grab a reference to the connection on the client provider


    Using connection As New SqlCeConnection(connectionString)


      ‘ Using the SQL Management Studio convention of using GO to identify individual commands


      ‘ Create a list of commands to execute


      Dim commands() As String


      ‘ To simplify editing and testing TSQL statements,


      ‘ the commands are placed in a managed resource of the dll. 


      commands = My.Resources.DatabaseCreation.Split( _


          New String() {“GO”}, _


          StringSplitOptions.RemoveEmptyEntries)


 


      Dim cmd As New SqlCeCommand()


      ‘ make sure we put the connection back to its previous state


      cmd.Connection = connection


      connection.Open()


      For Each command As String In commands


        cmd.CommandText = command


        cmd.ExecuteNonQuery()


      Next


    End Using


  End Sub


End Class


In your application startup, simply execute the following code:


Dim databaseHealthCheck As New DatabasHealthCheck()


databaseHealthCheck.VerifyDatabaseExists(My.Settings.LocalConnectionString)


So, now you’ve got an easy way to create your initial database, or alter an existing database as part of your app.  As always, I love to get your feedback and thoughts,


Steve


Comments (66)

  1. Aaron says:

    yeah, but does that really differ then the tack you would take for deploying any other sql flavor?

  2. Steve Lasker says:

    Hi Aaron, I was just focusing on the simplicity of checking and creating the datafile.  You’re correct.  Once the database is verified and created, there isn’t much difference.  

    Steve

  3. I was just reading a post by Steve Lasker in which he describes how you can create a SQL Server Compact

  4. Ed says:

    Steve,

    I went through the same issue prior to our mobile app being released late last year and pretty much did the same thing (although we relied on updating clients from a .net DataTable schema pushed down via web services).

    Anyway just a short tip for everyone that may help. Note that data types aren’t identical for SQL Server and Compact, (i.e. DECIMAL is NUMERIC in Compact), so if the sql script works perfectly for all other editions of SQL, and mysteriously doesn’t for Compact, it’s likely a data type issue.

    Hope this helps…

    Ed

  5. Steve Lasker says:

    Hi Ed,

    Yes, there are a subset of datatypes on Compact.  XML maps to nText, VarChar maps to nVarChar, etc.  Eventually, I’d really like raise the bar all together and just work with String, Int, Bool, but that’s a bit out.  

    Steve

  6. Cameron says:

    Hi Steve,

    Thanks for the post it works beautifully. I work in c# so I had to port it over, but other than that it gives me peace of mind and I don’t have to worry about clickonce data files now that the db is a simple resource of my .dll

    Thanks again,

    -Cameron

  7. jjcorra says:

    I tried to follow your example, but at the engine.CreateDatabase() I get the error:

    "The file name is not valid. Check the file name for the database. [ File name = |DataDirectory|MyDbFile.sdf ]

    The connection string in my app.config is

    <connectionStrings>

    <add connectionString="Data Source = ‘|DataDirectory|MyDbFile.sdf’" name="Default" />

    </connectionStrings>

    It doesn’t look like |DataDirectory| is resolving correctly in the constructor for the SqlCeEngine class. But using the same connection string with an SqlCeConnection, the Database property of the SqlCeConnection returns ".MyDbFile.sdf"

  8. Brian Squibb says:

    Steve,

    I think more work is needed on the SUB CreateInitialDatabaseObjects as splitting on “GO” will create problems. Perhaps ” GO ” would be better.

    Brian

  9. Tejana says:

    Steve,

    I did not understand the line:

    a()      ‘ make sure we put the connection back to its previous state

    Thanks

  10. Steve Lasker says:

    Hi Joel,

    If you’re not resolving the |DataDirectory| it’s either because you’re running on devices, where |DataDirectory| isn’t supported, or you don’t actually have SQLce 3.1 installed.  Even after installing VS 2005 SP1, you don’t actually have SQLce 3.1 installed, you simply have the entry name updated from SQL Mobile to SQL Server Compact Edition.  To install SQLce 3.1, please visit:

    http://www.microsoft.com/sql/editions/compact/downloads.mspx

    Then hit the link for:

    Download the SQL Server 2005 Compact Edition Runtime Engine

    http://go.microsoft.com/fwlink/?LinkID=79611

    This gives you the <2mb engine.

    To get the updated tools, look for:

    SQL Server 2005 Compact Edition Tools for Visual Studio 2005 Service Pack 1

    http://www.microsoft.com/downloads/details.aspx?FamilyId=877C0ADC-0347-4A47-B842-58FB71D159AC&displaylang=en

    That should take care of the updates

    Steve

  11. Steve Lasker says:

    Hi Brian,

    Yes, you’re point about parsing just GO wouldn’t be robust enough.  Good catch.  It should take into consideration NewLineGoEndOfLine so words like “ongoing” don’t get split.

    Another reason why you shouldn’t take anything for granted, even info in blogs <g>

    Steve

  12. Steve Lasker says:

    Hi Tejena,

    Yeah, I’m not sure what a() originally was.  Something got eaten in the paste from code.  In more robust code, I usually do a try/finally block where I save the original state of the connection, then put it back.  For instance some apps may have a common connection around but keeps it closed.  For each operation it needs to be opened, so it must be closed again.  However, for a loop of continual operations, it may already be open, so in that case, it should be left open.

    Steve

  13. Neil Lamka says:

    I am using SqlCE and have created a DB that I’d like to “script” (use as a template) so that the DB can be created/recreated at run time if necessary.

    With MS SQL Server Management Studio Express when working with the SqlCE DB I don’t find any way to create a script.  Am I missing something or do I need to create the DB in SQLExpress or something, script that and then use that in my SqlCE application?

    Thanks

    Neil

  14. 黎波 says:

    在智能设备(Smart Device)应用程序和智能客户端(Smart Client)应用程序的部署阶段,我们需要对离线数据进行初始化,即将后台数据库服务器中的一些数据,导入到离线数据库中。本文介绍了一种将各种数据库中的数据到如到 SQL Server Compact Edition 数据库中的方法。

  15. 黎波 says:

    在智能设备(Smart Device)应用程序和智能客户端(Smart Client)应用程序的部署阶段,我们需要对离线数据进行初始化,即将后台数据库服务器中的一些数据,导入到离线数据库中。本文介绍了一种将各种数据库中的数据导入到 SQL Server Compact Edition 数据库中的方法。

  16. 黎波 says:

    在智能设备(Smart Device)应用程序和智能客户端(Smart Client)应用程序的部署阶段,我们需要对离线数据进行初始化,即将后台数据库服务器中的一些数据,导入到离线数据库中。本文介绍了一种将各种数据库中的数据导入到 SQL Server Compact Edition 数据库中的方法。

  17. 勤勤同学 says:

    如何将数据导入到 SQL Server Compact Edition 数据库中

  18. jfj says:

    Is it possible to create a stored procedure in SQL CE?

  19. Hanze says:

    1,000,000 Thank you!!!!!

    You saved my life. Your article is very useful for smart client developer.

  20. Troy says:

    This is a very good article, however, I think it would make more sense to have the scripts run as part of the installation rather than part of the app. Once the user’s database is up-to-date (when installed or upgraded), why would I want to check for the existence of the database and rerun the alter script every time the application is run? It seems to me that a  separate executable that runs your create/update code should be executed only during an installation or upgrade. Is this possible and does this make sense to you?

  21. Steve Lasker says:

    Hi Troy,

    Yes, the same concepts could be run at install as well.  The challenges are sometimes end users delete files.  So, this is sort of a health check scenario.  

    Steve

  22. Dan Miser says:

    I agree with Neil above. We need a way to script out the schema for an existing SDF file. It would make the most sense to have it in MSSQL Management Studio, but those options don’t exist when connecting to an SDF file.

    Any ETA on when that feature will be available?

  23. Steve Lasker says:

    We’re working on a migration tool that will enable scripting.  We want to get this functionality into VS as well as Management Studio, but we just couldn’t get it done for the ’08 release.  It might be something we could do as a VS Extension, but the current migration tool will be stand alone for now.

    Steve

  24. zmark says:

    Is there any way to use SQL Server Compact Edition 3.5 in VB6? I tried with ADO and connection string had from MS site .. but I keep getting Runtime 3706 – Provider not found.

  25. Steve Lasker says:

    We really don’t have a full OleDB implementation that most expect on desktop.  Our OleDB implementation was done for devices which is a fairly small subset.  For instance, there’s no RecordSet. So, you can use it with limited usage scenarios, but it’s unfortunately not something we’re looking to expand upon at this time.

    Sitaram posted this info a while back that explains what we do have available: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/windows-2000-support.aspx

    Steve

  26. John Hamilton says:

    Regarding scripting.  Design the DB in SQL Express then Generate the Script and run it against SQLCE and place it in your application for use during a create/upgrade.

  27. derryzhang says:

    如何将数据导入到 SQL Server Compact Edition 数据库中

  28. Raj says:

    Hi,

    I have problem while data insertion in sql ce via vs2005 for mobile sdk 6.

    No error code do well but no effect noi data in database.

    If you can please send me at sandlohana@hotmail.com

    CODE HERE

    cnSQL = New SqlCeConnection(ConnectionString)

    cnSQL.Open()

    Dim cmd As SqlCeCommand = cnSQL.CreateCommand()

    cmd.CommandText = "INSERT INTO dP(Date1) Values(100)"

    ra = cmd.ExecuteNonQuery()

    Other way

    Dim myCommand As SqlCeCommand

    myCommand = New SqlCeCommand("INSERT INTO dP(Date1) Values(200)")

    myCommand.Connection = cnSQL

    ra = myCommand.ExecuteNonQuery()

  29. Carlos says:

    I was able to see among the data sources within Visual Studio 2005 the SQL server compact edition provider. I did install, and the data source disappeared. Then, I removed v3.5, and attempted to install the version that updates VS2005 SP1, and it did not let me. Now, I am able to connect thru SQL server anagement studio to that provider, but not thru VS 2005.

    Can you please help?

    Thanks,

              Carlos.

  30. Steve Lasker says:

    Hi Carlos, please see these steps for configuring SQLce 3.1 with VS 2005.  

    http://blogs.msdn.com/stevelasker/archive/2007/08/07/configuring-visual-studio-2005-for-use-wtih-sql-server-2005-compact-edition.aspx

    For SQLce 3.5, you can use it with code, but the designers in VS 2005 will not work with SQLce 3.5.  Likewise, the designers in VS 2008 will not work with SQLce 3.1.

    Steve

  31. Carlos says:

    Steve, thank you for posting your reply so

    fast. In fact what I did find is that by just

    re-installing the sql server mobile from the

    VS2005 installation disk (i.e. residing under the wcusqlce directory), and then

    re-installing the sql server compact edition (v3.1), the VS2005 environment was able to recover the SQL server compact among the list of

    data providers.

  32. 婕舞飞扬 says:

    黎波MobileBusinessSolution

    一、场景描述在智能设备(SmartDevice)应用程序和智能客户端(SmartClient)应用程序的部署阶段,我们需要对离线数据进行初…

  33. 如何将数据导入到 SQL Server Compact Edition 数据库中(一)

  34. 懒羊 says:

    懒羊语:近来一直在研究学习wm5,主要原因公司近期可能与此相关有一个项目要进行,从了解最基本的通过donet还是evc,到后来的界面编程,再到后来的数据库,而这篇算是让我两脚的前一半脚趾进入数据库的开始,强烈的推荐这篇文章,转载这篇文章一是给需要的朋友看看,更是给自己留下一个看的地方。

  35. Koh says:

    How to programmatically convert non-encrypted database to encrypted and add password to it, also how to change password after that?

    thanks.

  36. Koh says:

    i have found the answer, use System.Data.SqlServerCe.SqlCeEngine Compact method.

  37. Simon Hart says:

    Great article, the only thing to watch out for is if there are any columns, tables etc named GO or has ‘GO’ in them, you’ll have problems. Best to read into a TextReader then check each line for the word "GO".

  38. Steve Lasker says:

    Good catch Simon,

    I’ve updated the sample.  There are several updates, including checking for CR + LF.  Which still isn’t perfect as you could have GO without the comma after it, but it’s what I’ve got so far…

    I’ve also updated the scripts to use transactions.  I’ll write an article explaining this, but in the meantime: http://steve.lasker.members.winisp.net//Demos//SQLce35//CreatingDatabaseSchemaInCode.zip

    Steve

  39. BillH says:

    I am developing a standalone product that ships with a database that has a lot of reference data in it. The product may or may not ever connect to a server.  The product was started when SQL Mobile was current.

    1) How can I convert my SQL Mobile database to SQL Compact Edition 3.1?

    2) What can I use to edit the structure of the database?  In Visual Studio 2005 I can make some changes but not change column names, etc.

    3) Currently the systems are created by simply including the base database file in the appropriate folder.  It sounds like this might have issues, but so far we haven’t seen any.

  40. Steve Lasker says:

    Hi Bill,

    1 Converting from SQL Mobile to Compact 3.1 – Actually, there’s nothing to do to the database as 3.1 is really a “service pack” for 3.0 so no file format changes were needed.  To reflect the expanded usage on desktops, we changed the name, since we couldn’t really have a newly named product have the same and version, or somehow call it a service pack.  It’s all very confusing we realize, but it is what it is…

    To move from 3.0 to 3.1, simply reference the Compact 3.1 dlls.  They’ll say System.Data.SqlServerCe, as opposed to SQL Mobile, or something similar.

    When/if you move from 3.0/3.1 to 3.5, we do have a file format change, but we have a simple API, SqlCeEngine.Upgrade() to do the conversion directly on the users machine.

    2 Schema Changes – yeah, unfortunately you can’t actually change the name of a column.  We know this is a pain point, and something we’ll be fixing in our next release.  We hoped to get it done in this release, but it simply fell off the radar- lame I know. You can of course add a new column with the right name, do an update to the same table to move the data over, and then delete the old column.

    3 If the data is only read-only, and can never be changed by the user, it’s not so bad.  The problem is when you want to update the app and the database has user data, you can’t just replace the database.  If your “updates” have minimal changes to the local database, it may be more efficient to simply add a table, row, column through script changes, as opposed to replacing the entire database.

    Hope that helps,

    Steve

  41. Jonny Walker says:

    So, how many of those 3 database connections – the initial for testing, the second for populating and the implied third for using – do you *have* to create?  I can imagine that the first one may be invalid if the file is not present at that point, but I would have thought for the sake of efficiency (and sanity) it would be better for the procedure creating the DB to instead be a function returning the connection.

  42. Steve Lasker says:

    I’m working on a newer sample I’ll be showing at Tech Ed that does better encapsulation, and includes encryption, versioning and transaction support.  An early view of an updated sample is here:

    Steve

  43. Jonny Walker says:

    Other than some minor concerns about the efficiency and possibly having your schema documented in SQL in your delivered code, I like this approach.

    SQL schema versioning is generally a complete pain, and extending this technique you can add version n – n+1 update scripts to your health check.  Another thing you can do here that you can’t do with other techniques that I’ve seen is have a local password for your DB file – i.e. the application generates the password, which is different per installation.  If you deploy an SDF there’s no easy way (that I’ve found) of re-passwording it to a local password.  (And, as before, there’s also no way to ‘deploy’ schema updates like this.)

  44. Steve Lasker says:

    Hi Jonny,

    I’m not sure about your “efficiency” concern.  Are you worried about developer efficiency?  Or runtime efficiency?  Form a developer efficiency, I agree we need better tools here, and something we’re looking to do in our next release, 4.0.  For runtime efficiency, this tends to be the best model, particularly when your users have already changed data in the local file.  It’s actually more efficient to issue an “ALTER TABLE Customer ADD COLUMN AddressLine3…” than to ship a new database, and have to do a local data migration from one file to another.

    For the n+1 versioning, yup, that’s the idea.  By “versioning” your local database schema, developers can chose how many previous versions they wish to support.  You could even skip versions rather than forcing someone who’s current on V1 and needs to get to V3 from having to upgrade to V2 first, than upgrade to V3 again.

    For the local passwords, yup, that’s another topic of great discussion.  In the sample here, I’ve used Guid.NewGuid() as a simple way to get unique passwords per user install.  Of course this could be more elaborate, but you likely don’t want to use the same password on each users machine or cracking one machine will allow you to just walk up to another machine.  However, this methodology would also break the “doc centric” format where you want to ship the “document” from one user to another.  If each user has their own password, the recipient won’t be able to open the database.  

    For repasswording an sdf, you can use the SqlCeEngine.Compact() api.  We essentially create a new database.  If you specify a different set of connection string name/value pairs, including the password, you can change the password.

    Hope that answers your questions,

    Steve

  45. Will says:

    Thanks for the example on how to check the database is created or not. However, I wonder is there a way to go further in and check whether the tables are created if the database exists?

  46. rajiv says:

    hi

    Its a very helpful article.

    Since I read that SMO and SqlCe dont go together, I am not sure on how to proceed on my problem.

    I have a data table from a different source(may be text file). Everytime the schema is going to be different for data table. Since I cannot use SMO to copy the schema into SqlCe , how do I do it??

  47. Raviraj says:

    Hi,

    I am trying to create a table with following command in SQL CE 3.5 –

    CREATE TABLE dbo.AuthInspectionFacility_InspectionRegimeCode(

    InspectionRegimeCode char(1) NOT NULL,

    InspectionRegimeShortDescription varchar(30) NULL,

    InspectionRegimeLongDescription varchar(50) NULL,

    SortKey tinyint NULL,

    CONSTRAINT PK_AuthInspectionRegimeCode PRIMARY KEY CLUSTERED

    (

    InspectionRegimeCode ASC

    )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON PRIMARY

    ) ON PRIMARY

    GO

    I get error as –

    A first chance exception of type ‘System.Data.SqlServerCe.SqlCeException’ occurred in System.Data.SqlServerCe.dll

    Additional information: The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 18,Table name = AuthInspectionFacility_InspectionRegimeCode ]

    How to fix this error?

    Ravi

  48. Steve Lasker says:

    Hi Will,

    Sure, you can simply query some of the system views:

    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘Employees’

    There are additional tables for columns, indexes, etc.  

    Steve

  49. Steve Lasker says:

    Hi Raviv

    I’m not sure I understand your question.  Are you looking for a method for creating the schema of a table?  SMO provides this functionality for SQL Server, but you could also achieve the same results with CREATE TABLE …

    Or, are you saying you have a System.Data.DataTable (DataSet) that you want to infer the schema from to create the equivalent Compact table?

    Steve

  50. Josh says:

    Yes, this is what I need to find out:

    "…are you saying you have a System.Data.DataTable (DataSet) that you want to infer the schema from to create the equivalent Compact table?"

    I have some DataTables or DataSets that I need to somehow create individual SQLCE Tables at runtime, since I do not know in advance how many columns or datatypes.

  51. socrboy says:

    I don’t know if you received the last post, as I was not signed in; but this is what I wanted to know if you could help me: "…are you saying you have a System.Data.DataTable (DataSet) that you want to infer the schema from to create the equivalent Compact table?"

    Yes, I have various DataTables/DataSets that I would like to import into SQLCE as tables; also, I do not know what information the tables would have as far as number of columns or datatypes, since they will be generated at runtime.

    Any help in the right direction will be appreciated,

    Josh

  52. Raviraj says:

    Hi Steve,

    Thank you for your quick reply. I figured out that SQL CE 3.5 does not support schema and my SQL text had schema in it.

    I am trying to migrate SQL Express database to SQL CE 3.5. So application is trying to create database from a script file (containing SQL). Though i have resolved some of the issues and managed to create SQL CE 3.5 database from this SQL script file i have some questions –

    1. I have a serious issue with "nocheck" construct. In SQL CE I find that this option is no more supported. Is there any way to disbale  this option (e.g. for bulk inserts)?

    2. How to create database script from SQL CE 3.5 database?

    Thanks and regards,

    Ravi

  53. Steve Lasker says:

    Hi Josh,

    We used similar concepts in Sync Services to gather the schema information from the DataSet based on the Max Length for variable length columns, CLR data types and nullability.  That said, we don’t have an API that can directly take a DataTable schema and do this ad-hoc.  It would be an interesting community sample …

    Steve

  54. Paul C. says:

    Hello Steve.  Thanks for the write-up.  I am having an issue where my application ran just fine (in debug mode within VS2K5) and then stopped working with the dreaded "The file name is not valid. Check the file name for the database.  [ File name = |DataDirectory|\myDatabase.sdf ]"

    Odd thing is that the .sdf file is indeed in the location (bin/debug in this case) that’s also the same as the Application.StartupPath.

    I’m stumped as to how this could literally be working upon one running of the app from within VS2K5 and not working during the next run of the app.

    I made no changes to any of the code that has to do with the data access logic.

    Have you seen/heard of this issue?

    Thanks in advance.

    -Paul

  55. 双霜之君 says:

    一、场景描述

    在智能设备(SmartDevice)应用程序和智能客户端(SmartClient)应用程序的部署阶段,我们需要对离线数据进行初始化,即将后台数据库服务器中的一些数据,导入到离线数据…

  56. SUNIL MEHTA says:

    very useful article. is there a way we can create the script for the file..

    Sunil Mehta

  57. Nige says:

    Great stuff! I was struggling (like a fool) to create the bd without the db file being there so cheers for that.

    Its worth noting you can use VS 2005 by making a reference to C:Program FilesMicrosoft SQL Server Compact Editionv3.5DesktopSystem.Data.SqlServerCe.dll. This way you can use .Net2.0 instead of having your clients install .Net3.5 which is a bad point of sale in my mind.

  58. Steve Lasker says:

    Hi Nige,

    Happy to hear this helped.

    For the FX dependencies, you’re correct, Compact doesn’t require .NET FX 3.0 or 3.5.  The engine is compiled against FX 2.0, so any FX version, 2.0, 3.0, 3.5, 3.5 (SP1), will work.  If you’re using the ADO.NET Entity Framework, you’ll need FX 3.5 SP1 as this is where the base Entity Framework first ships.

    Steve

  59. Nige says:

    Hi, not using ADO.NET. I’ve added all the dlls to my deployment. Odd thing is I can’t get compact to appear as a prerequisite on Click Once in VS2005 – maybe it dosnt do the  bootstrap for 2005. Shame cos its adding over 2 Meg to every update I do and I’m on 5.5 Meg as it is! Still I like onlt having .Net2.0 as a prereq as it causes a lot less bother when it comes to installation for the first time.

    Cheers again for getting me out of my little hole I was digging for myself, the compact sql commands are easy and simple – means more beer time ‘pour moi’ 🙂

  60. Steve Lasker says:

    Hi Nige,

    Here’s some info on how to install Compact with VS 2005: http://blogs.msdn.com/stevelasker/archive/2007/08/07/configuring-visual-studio-2005-for-use-wtih-sql-server-2005-compact-edition.aspx

    Although, you’re saying you’re not using ADO.NET, but you mention .NET FX 2.0, so I’m a bit confused.  

    If you’re programming in .NET, you’re using ADO.NET.  Unless, you’re trying to use OleDB, which wouldn’t work ’cause Compact doesn’t have a full OleDB provider.  It was designed as a minimal api for devices.

    Anyway, the VS2005 link should help if you want the bootstrapper, but remember, it will require admin rights for your end users.

    Steve

  61. Steve,

    I see several other folks had issues with generating the script itself since Management Studio will not export it from an existing SQL Compact database.

    One solution to this is to generate the script from the Entity model.  The EDMX file itself doesn’t have all the information you need (no hints as to what to index etc.) but it can do a reasonable job at generating the tables, primary keys and foreign keys.

    You can find a T4 script to do this on my blog.  Adding this script to a project will autogenerate a .sql file that can then be embedded as a resource.

  62. Matthew Cole says:

    Hi there Steve!

    I wonder if you would help? I’m creating my sdf and populating it from web services (c#). I am getting the sdf created now but when I go to populate it I get:

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

    My createSdf (creates file and tables+indexes) and insertData (a looped adapter.insert) are in two separate web service operations.

    I am using a SQL INSERT statement in a table adapter whose connection string I am setting before the adapter.insert loop.

    I have had this working on my workstation but it has now stopped with the error above and I can’t see what to do. My web research has led me on a merry journey over several days and I’m now quite desperate! Am am fairly new to .Net and CF development (1 year).

    I hope you can help!

    Cheers, in advance.

    Matthew

  63. Jane says:

    Hi Steve,

    Do we need to install the SQL server compact 3.5 in order to use your code example to create our SQL server ocmpact edition databse on the production pc?

    thanks,

    Jane

  64. George Joseph says:

    Thanks for This Excellent and Useful Code

  65. Chris Wells says:

    Using it in ASP.NET v4 with SQL CE 4 I find that "|DataDirectory|Data.sdf" is resolved correctly by the SqlCeEngine.CreateDatabase() method (i.e. it's created in the App_Data directory), but not by the System.IO.File.Exists method (therefore it tries to create the database twice). So I replace "|DataDirectory|" before passing it to FileExists using C# code like …

               string filename = m_connection.Database;

               string slugDataDirectory = @"|DataDirectory|";

               if (filename.StartsWith(slugDataDirectory))

               {

                   string dataDirectory = (string)AppDomain.CurrentDomain.GetData("DataDirectory");

                   filename = filename.Substring(slugDataDirectory.Length);

                   filename = Path.Combine(dataDirectory, filename);

               }

               if (!File.Exists(filename))

                   createDatabase();