Migrating from SQL Server CE to SQL Server database

Question

Currently I am using SQL Server CE for persisting my data for which I am providing with a .sdf and connection string mentioned in app.config pointing to this .sdf file.

Now I want to provide user with the flexibility to have the data stored in their own SQL Server database if present at there disposal.

Now I am facing the problem of how to change the connection string at runtime if user chooses to uses its own database ?

Or if restrict them to use my predefined .mdf file how to attach that in their SQL Server ?

That'll be tough - since for SQL Server CE, you need to use stuff like SqlCeConnection and SqlCeCommand while for a "real" SQL Server, those are SqlConnection and SqlCommand. Just switching the connection string isn't enough .... — marc_s
– marc_s, Commented Feb 9, 2012 at 7:45

linkerro · Accepted Answer · 2012-02-09 07:57:08Z

1

My recommendation would be to have 2 connection strings in the configuration file (app or web). There is a special section for them intuitively called ConnectionStrings. You can then switch between them based on other settings.

answered Feb 9, 2012 at 7:57

linkerro

5,4783 gold badges28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

developer · Accepted Answer · 2012-02-09 07:27:53Z

Changing connection strings dynamically is actually pretty easy to do as long as you have a place to store the new settings (ie. web or app config files). If you provide a way for them to enter the server information you can use the ConfigurationManager class to update your app/web.config.

Ado.net typically has parameters on almost any db connection object that allows you to specify the connection string as an arguement. Additionally, there are helper classes that can be used to construct the connection string on the fly like the SqlConnectionStringBuilder or EntityConnectionStringBuilder. I personally love the Entity Framework as it allows you to create the database from the model itself if it does not already exist, provided you already have the connection string.

Collectives™ on Stack Overflow

Migrating from SQL Server CE to SQL Server database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related