Create SDF from DBML ???

[Jakob Lithner]

I am developing a client-server application using C# and LINQ-to-SQL.
During development I have been using a SQL 2008 database.

I need a local copy of the database on each client, and decided to go for an
SQL Server Compact 3.5 SDF file. I read MS articles on recommendations and
found this to be a good choice.

But how do I go about copying the final database structure into an SDF file
????

I expected this to be a piece of cake, but I just stumble into stupid
problems on all attempts!

1) I tried SQL Management - Didn't work!
2) I tried VS database generation - Didn't work!
3) I tried to use the dbml file and then use linq context CreateDataBase
method - Didn't work!

There must be other people doing this .....

I mean, who will develop a complex database in SDF with that limited GUI
support?
At least I want to have a clear overview by a database diagram to ensure all
my relations are correct.

 

[Lingzhi Sun [MSFT]]

Hi Jakob,

In your post, you have mentioned: "I read MS articles on recommendations
and found this to be a good choice." Could you please tell me where do
you find these recommendations?

In your scenario, you are working with SQL 2008 database via LINQ to SQL.
However, SQL Compact has many function limitations compared with SQL
Server, which makes LINQ to SQL on SQLCE is quite different with LINQ to
SQL to SQL Server. You can find more detailed information from:
http://blogs.msdn.com/matt/archive/2008/09/09/sql-ce-3-5-with-linq-to-sql.as
px
http://blogs.msdn.com/matt/archive/2008/09/26/sql-ce-3-5-with-linq-to-sql-re
visited.aspx
http://blogs.msdn.com/sqlservercompact/archive/2007/08/21/linq-with-sql-serv
er-compact-a-ka-dlinq-over-sql-ce.aspx

So we cannot say for sure that the SQLCE can meet all the request of your
original SQL Server 2008 database structures. Could you please use a SQL
Server Express instance database at each client side? I think the
DataContext.CreateDatabase is working fine in such scenario.
http://msdn.microsoft.com/en-us/library/bb399420.aspx.


If you need further assistance, please feel free to let me know.

Have a nice day!


Best Regards,
Lingzhi Sun
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jakob Lithner]

The mentioned article was found in this document
http://download.microsoft.com/downl...D-F02ADE638EBE/Compact_Express_Comparison.doc

It should not be that obsolete as it is still one of the top links on this
page:
http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx

For me easier deployment and "one file copy between clients" is a major
advantage. I don't expect to run into needs of more server features.

My impression is that SSCE 3.5 is intended to work and is used by some people.
How do they generate their databases?
Am I the first one wanting to copy an existing structure?


As to your examples they follow the same pattern as everywhere else.
The idea is generally that you have an SSCE database and want to create the
code.
The last example is the other way around but uses a very limited example
coded by hand.

My scenario is a dbml file with 7-8 tables with relations. Nothing fancy,
quite straightforward but I would not like to code it all over again by hand!

Every time I try to run the CreateDatabase method it complains on table name
being invalid and column name being invalid. It is nonsense as it actually
does not matter what names I use.

Third party tools making my world easier are welcome, if there is no
possible MS way to solve it.

 

[Lingzhi Sun [MSFT]]

Hi Jakob,

I think the reason why we cannot create the SDF file with the current .dbml
file is because of the non-support of data table schema in SQLCE.
By default, the data table in SQL Server is under schema dbo. We can see
an attribute on each data table data class like:
========================================================
[Table(Name="dbo.TableName")]
========================================================

Please try to remove the schema name in the dbml designer file and run some
code snippet as the following to create such a SDF file:
========================================================
string cs = @"Data Source='c:\data.sdf'";
using (SqlCeConnection conn = new SqlCeConnection(cs))
{
MyDataContext db = new MyDataContext (conn);
db.CreateDatabase();
}
========================================================

If the SDF is created successfully, we can use the modified .dbml file to
connect the SDF file, please remember to pass a SqlCeConnection to the
DataContext.

For how to synchronize the data from the client SQLCE to the remote SQL
Server, Remote Data Access (RDA) and Merge Replication features of SQLCE
can be really helpful. For detail, please see
http://msdn.microsoft.com/en-us/library/ms172917.aspx
http://msdn.microsoft.com/en-us/library/ms172367.aspx


Hope you have a nice day!


Best Regards,
Lingzhi Sun
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[Jakob Lithner]

Thanks.

1) Specifying the SqlCeConnection is probably a good idea.

2) I also found out that I have datatypes that are not compatible with SSCE:
varchar, ....

3) By the way I will consider the mdf one more time too ....