Creating your SQL Server Compact Edition database and schema in code

re: Creating your SQL Server Compact Edition database and schema in code

Aaron — Sat, 31 Mar 2007 07:38:08 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sat, 31 Mar 2007 09:28:24 GMT

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

Now Why Didn't I think of That

Nick Randolph's .NET Travels — Mon, 02 Apr 2007 01:54:33 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Ed — Mon, 02 Apr 2007 09:35:44 GMT

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...

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Tue, 03 Apr 2007 07:33:54 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Cameron — Thu, 05 Apr 2007 01:53:34 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

jjcorra — Mon, 30 Apr 2007 05:55:04 GMT

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>

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"

re: Creating your SQL Server Compact Edition database and schema in code

Brian Squibb — Mon, 30 Apr 2007 09:38:04 GMT

Steve,

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

Brian

re: Creating your SQL Server Compact Edition database and schema in code

Tejana — Mon, 07 May 2007 19:37:30 GMT

Steve,

I did not understand the line:

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

Thanks

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sun, 13 May 2007 20:22:13 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sun, 13 May 2007 20:24:26 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sun, 13 May 2007 20:27:31 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Neil Lamka — Thu, 21 Jun 2007 23:09:06 GMT

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

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

黎波 — Sun, 29 Jul 2007 11:14:08 GMT

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

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

黎波 — Sun, 29 Jul 2007 11:19:36 GMT

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

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

黎波 — Sun, 29 Jul 2007 11:32:04 GMT

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

勤勤同学 — Sun, 29 Jul 2007 16:18:29 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

jfj — Mon, 13 Aug 2007 21:47:54 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Hanze — Fri, 31 Aug 2007 19:21:57 GMT

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

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

re: Creating your SQL Server Compact Edition database and schema in code

Troy — Mon, 08 Oct 2007 21:11:43 GMT

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?

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Tue, 16 Oct 2007 07:19:57 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Dan Miser — Wed, 17 Oct 2007 19:01:55 GMT

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?

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Tue, 30 Oct 2007 06:40:30 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

zmark — Wed, 12 Dec 2007 10:19:27 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sat, 15 Dec 2007 05:08:12 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

John Hamilton — Sat, 29 Dec 2007 08:52:46 GMT

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.

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

derryzhang — Wed, 09 Jan 2008 09:38:08 GMT

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

DML Problem

Raj — Thu, 17 Jan 2008 16:01:58 GMT

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()

re: Creating your SQL Server Compact Edition database and schema in code

Carlos — Sun, 03 Feb 2008 01:54:28 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sun, 03 Feb 2008 06:01:51 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Carlos — Mon, 04 Feb 2008 00:22:31 GMT

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 wcu\sqlce 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.

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

婕舞飞扬 — Mon, 11 Feb 2008 17:23:28 GMT

黎波MobileBusinessSolution

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

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

xiangxiangouhongyuan — Thu, 14 Feb 2008 14:51:37 GMT

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

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

懒羊 — Mon, 03 Mar 2008 10:25:41 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Koh — Sat, 19 Apr 2008 16:48:37 GMT

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

thanks.

re: Creating your SQL Server Compact Edition database and schema in code

Koh — Sat, 19 Apr 2008 17:13:24 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Simon Hart — Fri, 25 Apr 2008 17:56:31 GMT

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".

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Wed, 30 Apr 2008 06:05:41 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

BillH — Tue, 06 May 2008 22:03:15 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Sun, 11 May 2008 19:48:34 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Jonny Walker — Tue, 13 May 2008 18:06:36 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Tue, 13 May 2008 21:30:27 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Jonny Walker — Wed, 14 May 2008 11:22:36 GMT

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.)

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Wed, 14 May 2008 20:34:26 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Will — Tue, 20 May 2008 20:37:31 GMT

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?

re: Creating your SQL Server Compact Edition database and schema in code

rajiv — Tue, 08 Jul 2008 17:32:32 GMT

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??

re: Creating your SQL Server Compact Edition database and schema in code

Raviraj — Wed, 09 Jul 2008 16:19:54 GMT

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

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Wed, 09 Jul 2008 23:50:05 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Wed, 09 Jul 2008 23:54:36 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Josh — Fri, 11 Jul 2008 20:10:19 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

socrboy — Fri, 11 Jul 2008 21:09:34 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Raviraj — Mon, 14 Jul 2008 18:41:14 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Tue, 15 Jul 2008 04:02:50 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Paul C. — Wed, 03 Sep 2008 17:41:57 GMT

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

[zt]如何将数据导入到 SQL Server Compact Edition 数据库中（一）

双霜之君 — Wed, 15 Oct 2008 05:00:52 GMT

一、场景描述

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

re: Creating your SQL Server Compact Edition database and schema in code

SUNIL MEHTA — Thu, 16 Oct 2008 12:40:27 GMT

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

Sunil Mehta

re: Creating your SQL Server Compact Edition database and schema in code

Nige — Mon, 03 Nov 2008 11:15:36 GMT

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 Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.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.

re: Creating your SQL Server Compact Edition database and schema in code

Nige — Mon, 03 Nov 2008 12:06:06 GMT

Just to add: http://msdn.microsoft.com/en-us/library/aa983326.aspx if trouble deploying :)

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Mon, 03 Nov 2008 18:19:29 GMT

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

re: Creating your SQL Server Compact Edition database and schema in code

Nige — Mon, 03 Nov 2008 19:57:08 GMT

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' :)

re: Creating your SQL Server Compact Edition database and schema in code

Steve.Lasker — Thu, 20 Nov 2008 06:56:49 GMT

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

Great post, and here's T4 code to generate the script from an EDMX

Hightechrider — Mon, 18 May 2009 20:13:35 GMT

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.

re: Creating your SQL Server Compact Edition database and schema in code

Matthew Cole — Wed, 20 May 2009 18:29:12 GMT

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