locked
Entity Framework connections strings messing up app.config RRS feed

  • Question

  •  

    I've just started to implement Entity Framework in an existing (very large) windows forms application. The projects consist of about 200 typed datasets all together. I'm doing an experiment trying to mix typed datasets and entity framework models in the projects.

     

    One of the first things that concerns me is the way entity framework handles connection strings. For every single model there is a matching connection string in the containing project's app.config file. Seriously! How can this be ment to work for large projects?!

     

    It seems that every connection string must be duplicated too, in the app.config files for each project where the models are used. For instance, I have a CustomerEntities model in a business logic project, which I try to add to a windows form in one of the GUI projects of the same solution. (I tried to do this the same way as with typed datasets, by adding an instance of the CustomerEntities model in the form's designer file.) Now when I try to launch the form designer, it complains about a missing connection string:

     

    "The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid."

     

    First of all, there must be some best practices on how to manage connection for entity framework at runtime. If the application is used against another database than it was compiled for, there must be a way to ensure that every entity model gets the correct connection when initialized.

     

    And second, there must be a way to avoid all those connection strings in the various app.config files at design time.

     

    Sunday, August 31, 2008 9:40 AM

Answers

  • 1. At runtime, you can pass in the connection string to the ObjectContext - take a look at the various ObjectContext constructors. The typical guidance is to copy the connection string information from the project where the model sits to the project where it is used - in other words, your winforms app needs the connection string in its app.config if you want to use the parameterless constructor pattern. Otherwise, you can manage your own connection strings any way you want by passing them into the constructor.

     

    2. At design-time, the designer relies on a connection string being available in the app or web.config. I do not see how having a lot of connection strings would be of any concern.

     

    Sunday, August 31, 2008 3:56 PM

All replies

  • 1. At runtime, you can pass in the connection string to the ObjectContext - take a look at the various ObjectContext constructors. The typical guidance is to copy the connection string information from the project where the model sits to the project where it is used - in other words, your winforms app needs the connection string in its app.config if you want to use the parameterless constructor pattern. Otherwise, you can manage your own connection strings any way you want by passing them into the constructor.

     

    2. At design-time, the designer relies on a connection string being available in the app or web.config. I do not see how having a lot of connection strings would be of any concern.

     

    Sunday, August 31, 2008 3:56 PM
  • 1. I've made a connection helper that manages what you describe.

     

    Code Snippet

    Public Class EntityConnectionHelper

        Public Shared Function CreateConnectionString(ByVal pType As Type) As String
            Return CreateConnectionString(pType.Name, pType.Assembly.FullName)
        End Function

     

        Public Shared Function CreateConnectionString(ByVal pModelName As String) As String
            Return CreateConnectionString(pModelName, Nothing)
        End Function

     

        Public Shared Function CreateConnectionString(ByVal pModelName As String, ByVal pAssemblyName As String) As String
            Dim vConnectionStringBuilder As New EntityConnectionStringBuilder()
            vConnectionStringBuilder.Provider = "System.Data.SqlClient"
            vConnectionStringBuilder.ProviderConnectionString = My.Settings.ProfVet_ClinicConnectionString
            vConnectionStringBuilder.Metadata = CreateMetadata(pModelName, pAssemblyName)
            Return vConnectionStringBuilder.ToString()
        End Function

     

        Private Shared Function CreateMetadata(ByVal pModelName As String, ByVal pAssemblyName As String) As String
            If String.IsNullOrEmpty(pAssemblyName) Then pAssemblyName = "*"
            Return String.Format("res://{1}/{0}.csdl|res://{1}/{0}.ssdl|res://{1}/{0}.msl", pModelName, pAssemblyName)
        End Function
    End Class

     

     

    So now I can pass the type of a model and get a connection string for that model, based on the ProviderConnectionString which is the real connection string.

    Code Snippet

    Me.mdlCustomer = New CustomerEntities(EntityConnectionHelper.CreateConnectionString(GetType(CustomerEntities)))

     

     

    2. In a solution with many projects, there must be a connection string for each model (about 200) in the app.config files of each GUI project (about 5). That means we must have about 1000 connection strings to manage in this single soultion. This feels kind of messy, compared to how it works with typed data sets, where each dataset know it's own connection string, which is loaded from a single connection string in each app.config file. It's not a critical issue though.
    Wednesday, September 3, 2008 9:19 AM
  • Can you explain why you have 200 models? How big is your database?

    Wednesday, September 3, 2008 6:47 PM
  • Well, the database consists of 204 tables and a few viewes. The reason why we have about 200 typed datasets, is because there is a problem having the same table represented in several typed datasets, and it is impossible to have one single dataset for all tables.

     

    If, for instance, the customer table is used in 10 different parts of the system, we can't have that table represented in 10 different typed datasets, because of two things:

    1. When there is a change in the database to the customer table, this change must be applied to 10 typed dataset (how many and which would be impossible to tell without some sort of map (documentation) that would cause a lot of administration to maintain, and which might be outdated and not reliable).
    2. When a customer instance must be sent as parameter from one part of the system to another, the tbl_Customer in one typed dataset would not be the same as tbl_Customer in another dataset, because they are different types/classes.

    Because of these two problems, we ended up with the one extreme of having (almost) one typed dataset for each table in the database. Of course there are a few duplicates some places, where that is considered reasonable. But we have a sort of knowledge on which tables are represented in which typed datasets, without having the need for documetation.

     

    The other extreme is to have one single typed dataset containing all the tables in the database. Actually we tried that first, but gave up after the first OutOfMemoryException from the dataset designer, which came after about 20 minutes of hard processing trying to generate the dataset.

     

    The golden mean, is in this case not very golden... Do you think we should consider the golden mean again before we convert all typed datasets to entity models? I know there is a better profit in relationships between entities in entity framework.

    Wednesday, September 3, 2008 7:17 PM
  • One thing that I noticed is that the connectionStrings get updated in the .vhost.exe and other various files in /bin/Debug and /bin/Release etc. You can just run a "clean" compile and it should just copy over the new App.config settings.

     

    You should also be able to update the connection string for each model through the .Designer.cs file through a custom code generator tool.

     

    Just change the following inside your .Designer.cs file:

     

    public partial class Entities : global:Tongue Tiedystem.Data.Objects.ObjectContext

    {

        public Entities() : base ("name=[ConnectionStringNameHere]", "[ConnectionStringNameHere]")

        {

            this.OnContextCreated();

     

    ....

     

    Then they all can share the same connection string in App.config, never delete that connection string and any time you regenerate your model you can just delete the excess App.config settings (kind of clugy I know, but it works).

     

    Wednesday, September 3, 2008 7:26 PM
  •  Jwendl wrote:

     

    Then they all can share the same connection string in App.config, never delete that connection string and any time you regenerate your model you can just delete the excess App.config settings (kind of clugy I know, but it works).

     

     

    Does this mean that the connection strings don't need to refer to the .csdl, .ssdl and .msl files which are specific for each model? If that is correct, there isn't much difference in EF connection strings and normal connection strings. I haven't tried to remove that part of the connection string, but only thought they had to be there. If this works, it is a great solution - until eventually the designer gets a property to do the same.

    Wednesday, September 3, 2008 7:40 PM
  •  

    Sorry, I should have been clearer, I think it gets it from the CSDL in the

     

    <EntityContainer Name="Entities">

     

    Tag, that maps directly to the .Designer.cs file function that reads in that connection string (if you check the box that says save this in App.config during the wizard).

    Wednesday, September 3, 2008 8:10 PM