Changing data sources (and offline views)

Apr 30, 2013 at 1:34 AM
I have 2 computers I use when developing apps. Each has a differently named local SQL server. And when others in the company download the code to their computer, they have yet another local SQL server instance.

Right now, we just have the various machine specific settings in the App.Config file and comment out the others and uncomment the one we need for that computer.

But I was thinking that there must be a better way.

We also need to support offline views for our customers, so they will probably have a similar issue when the app can't connect to the live db and reverts to the local db.

Does the framework have any built in support for easily switching to a different db server (for development or if the user goes offline) and/or overriding the app.config settings? I don't mind writing code to handle this, but thought I would at least check (you have so many other cool things in there.)

Thanks?
Coordinator
Apr 30, 2013 at 11:24 PM
No, not currently. People like to use things like Entity Framework (EF) or nHibernate and we don't want to reinvent that wheel.

As I just wrote in a response to Fletcher though, we used to have a pretty cool ADO.NET based data access framework which we have not as of yet brought forward into CODE Framework. It was still pretty cool though, and it wouldn't hurt to have it. So we will give that a bit of a facelift and bring it into CODE Framework for those who want to use it (it will always be optional).

With that said: This will not fix all the issues you raise above. The offline stuff is usually not a slam dunk. It usually requires custom coding.


Markus
May 1, 2013 at 5:16 AM
Markus,

Actually, both were to me... :) But, my situation is using the EF. EF assumes that the connection string is set in the app.config file. My eventual plan is to just update it from the users local settings. For now, I just have a number of connection strings and use the one that is appropriate based on the computer name.

For anyone else following along, here are the key commands to do something similar:
// Get all the current connection strings
System.Configuration.ConnectionStringSettingsCollection title =
        System.Configuration.ConfigurationManager.ConnectionStrings;
// Get the connections object reference
System.Configuration.Configuration appConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
// Update the connection string for the database with the 3rd connection string in the app.config file (hard coded just for the example.)
appConfig.ConnectionStrings.ConnectionStrings["yourDatabaseNameHere"].ConnectionString = title[2].ConnectionString;
// Now save it 
appConfig.Save(ConfigurationSaveMode.Modified, true);
// And tell C# to refresh all the values now.
ConfigurationManager.RefreshSection("connectionStrings");
As indicated, I hard coded the example to use the 3rd connection string (from title) in the collection of connection strings. In reality, I have a routine that figures out which one to use and uses that one. In my actual app, I will most likely just build the connection string from scratch, based on the users settings, and ignore whatever is in the app.config file. This can be done with an instance of EntityConnectionStringBuilder.

Since I work on multiple computers at the same time, I can copy/check out the code to either computer and it will run against that computers local db without me having to remember to edit the app.config file anymore...

For offline views, I am planning to create a standalone host that can be part of the applications and can be called on when the real db is not available. That one will do essentially the same thing by looking at a local replication of the data from the main system.
Coordinator
May 1, 2013 at 8:49 AM
Oops. Sorry about that. Must have gotten confused with an email I was answering about at the same time. I probably called someone else Fletcher then :-)

Yeah, for EF, you are pretty much stuck with what EF does for you. We are not messing with any of that.


Markus
May 1, 2013 at 5:14 PM
Markus,

On the FE, if I want to add code that intercepts the error when the connection can not be made (so I can either prompt the user to enter valid ports/URLs/etc. or if they want to switch to a local db), where would be the best place to put the code?

And, if I put the code in that location to get the info from the user, what would I need to do (at that specific point) to refresh the settings enough so that I could retry the connection. For the refresh, I would assume it would be essentially the same approach as above - but changing the appSettings key values instead.

Thanks,

Fletcher
Coordinator
May 2, 2013 at 9:35 PM
To be honest, I am not the best person to ask about EF stuff like this. I'll see if one of my guys knows the answer to this, since a lot of them know EF very well.

I think your options are somewhat limited though, because EF doesn't allow nearly the degree of freedom in configuration, which is one of the reasons I often prefer different solutions when I need flexibility over other aspects.


Markus
Developer
May 2, 2013 at 10:10 PM
With respect to different development SQL server names you could just use the term “localhost” instead of a named server. Assuming the database name and structure are common (shouldn’t be too hard to control) this would alleviate the issue of having multiple connection strings and commenting out stuff, at least during development.

The current question, trying to intercept the connection and where to put it – would require some testing/research. But my guess would be to put that code in the constructor of the Model.Designer.cs file. That (as far as I can tell) is the first place the connection is attempted. The down side here is that this is an auto generated file, so changes will be overwritten.

Once upon a time I read some articles about trying to dynamically generate the connection string, I'll have to see if I can find these again....

Jeff
Developer
May 2, 2013 at 10:30 PM
Something to add:

you can obtain a list of local instances (or even remote instances if permitted) and have them be displayed in a dropdownlist, or have the intelligence in the software to select the default/only item and connect to that. Then pump that connection/SQL instance down the chain.

There are a couple of ways. One is using the ADO.NET approach to enumerate through the instances:

http://msdn.microsoft.com/en-us/library/a6t1z9x2.aspx

The other is to use the SMO approach:

Add a reference to the assembly "Microsoft.SqlServer.Smo.dll" which is generally installed in the Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll folder

Once added, code this:

add a using statement:
using Microsoft.SqlServer.Management.Smo;

// Retrieve local instances (bool is set to true for this)
DataTable localInstances = SmoApplication.EnumAvailableSqlServers(true);


you could also then go an extra step further and query the databases for the selected instance:

Server server = new Server(selectedServerInstance);
DatabasesCollection dbsOnServer = server.Databases;


I hope this helps you in some way,

Ahmed Ilyas
May 3, 2013 at 3:12 AM
Jeff,
I have 2 computers, a laptop and a desktop. On the laptop, I simply use a direct reference to the data table, so my connection looks like:
<add name="ServicesExerciseEntities" connectionString= "metadata=res:///ProjectModel.csdl|res:///ProjectModel.ssdl|res://*/ProjectModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Apps\MSSQL\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ServicesExercise.mdf;Integrated Security=True;Connect Timeout=30"" providerName="System.Data.EntityClient" />

So I have no SQL server running, etc. I just open the table directly. This seems to work quite well and I can easily use this approach for offline views.

However, the main DB will be running on an SQL server and have a connection similar to:
<add name="ServicesExerciseEntities" connectionString="metadata=res:///ProjectModel.csdl|res:///ProjectModel.ssdl|res://*/ProjectModel.msl;provider=System.Data.SqlClient;provider connection string="data source=SQLSERVER\SQLLiveDB;initial catalog=ServicesExercise;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />

Localhost will work in the second case ONLY if the SQL server is on my computer.

So the connection strings are fairly different. If I copy the code from my desktop to my laptop so I can go down and work at the local Starbucks, I have to remember to edit the connection strings or I crash. I now have code that will check the machine name and pick the correct one - so the problem is fixed for me. But I will have the same problem when this is deployed - i.e. I will need to have a connection string for the main SQL server and 1 for the local table (if the computer has support for offline views turned on.)

So using the machine name is not something that will work outside of my development environment.

But it is very easy to build a connection string on the fly using the EntityConnectionStringBuilder in VS provided I have the information someplace (we already have that issue solved and know where to get the info.)

The reason for starting the thread is that it has been implied that EPS uses EF and that multiple people work on various projects all of which are checked into source control. So unless everyone uses the exact same SQL server, I thought that you might already have something in place to make it easy for someone to get the current version of an application and run it on their computer without having to edit the app.config file (which would then break other peoples implementations when checked back in.) Of course, you may be excluding the app.config from being checked in, but then that leads to another set of problems.... :)
Developer
May 3, 2013 at 3:43 AM
Edited May 3, 2013 at 3:43 AM
Hey FletcherJ.

Sometimes we do use the one database. Othertimes our own local database and with that the chances of us all having the same local default instances are very true. Other times, like me, don't so usually what I do is I create a local app.config and don't check that in/exclude it from being checked in and overwritten and just check for any app setting changes (Which are rare in comparison to code in general) and update my local copy instead.

if you use SVN - the equivilent would be to ignore the file in question from being checked in (exclude file).