none
Entity Framework Fails When I Set The Connection String in Code RRS feed

  • Question


  • I want to develop with the framework pointing to a DEV server and want to use it when it's pointing to a production server where users will need a userID and password to get in.

    Using Northwind and Integrated Security for DEV.

    Tried to use the same server and settings, only with a connection string that I built up.

    Any clue on what I'm doing wrong?

    Thanks

    Here's the App.config:


    <connectionStrings>

     <add name="NWModelConnectionSettings" connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MAGICMICRO\SQLEXPRESS;initial catalog=Northwind;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

    </connectionStrings>

    <entityFramework>

    'Here is the code. I commented out what used to work before trying to set the string in code.

    Imports System

    Imports System.Collections.Generic

    Imports System.Collections

    Imports System.Data.Common

    Imports System.Data

    Imports System.IO

    Imports System.Data.SqlClient

    Imports System.Data.EntityClient

    Imports System.Data.Metadata.Edm

    Module

    Module1


    'CONSOLE APPLICATION, Using Visual Studio 2012, Net Framework 4.0

    'This worked when I used NWModelConnectionSettings, not when I use EntityConnection

    'Private northwindContext As NWModelConnectionSettings

    Private northWindContext As EntityConnection

    Sub Main()

    'Trying to set connection string in code.

    ' Specify the provider name, server and database.

    Dim providerName As String = "System.Data.SqlClient"

    Dim serverName As String = "MAGICMICRO\SQLEXPRESS"

    Dim databaseName As String = "Northwind"

    ' Initialize the connection string builder for the underlying provider.

    Dim sqlBuilder As New SqlConnectionStringBuilder()

    ' Set the properties for the data source.

    sqlBuilder.DataSource = serverName

    sqlBuilder.InitialCatalog = databaseName

    sqlBuilder.IntegratedSecurity = True

    sqlBuilder.MultipleActiveResultSets = True


    ' Build the SqlConnection connection string.

    Dim providerString As String = sqlBuilder.ToString()


    ' Initialize the EntityConnectionStringBuilder.

    Dim entityBuilder As New EntityConnectionStringBuilder()


    'Set the provider name.

    entityBuilder.Provider = providerName


    ' Set the provider-specific connection string.

    entityBuilder.ProviderConnectionString = providerString


    ' Set the Metadata location.

    entityBuilder.Metadata = "metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl"

    'This FAILS to connect

    Using conn As New EntityConnection(entityBuilder.ToString())

    conn.Open()

    Console.WriteLine("Just testing the connection.")

    conn.Close()

    End Using


    Try

    'This all worked when I used NWModelConnectionSettings. Does not work using EntityConnection. Probably because it failed to connect. Will it work when we get it co connects?


    ' northwindContext = New NWModelConnectionSettings

    northWindContext = New EntityConnection(entityBuilder.ToString())

    Catch ex As SqlClient.SqlException


    Catch ex As Exception

    End Try


    'This used to work.


    Dim queryByPrice = (From prod In northWindContext.Products Where prod.UnitPrice > 10)

    For Each Product In queryByPrice

    Console.WriteLine(Product.ProductName)

    Next

    Console.ReadKey()


    End Sub

    End


    Module


    Harlan Black

    Thursday, June 20, 2013 4:27 PM

Answers

  • Dev vs Prod doesn't matter provided the schemas are the same.  The only thing that would change in your config file is "data source" key, possibly the "initial catalog" key and optionally the security-related keys.  The rest of the string would remain unchanged.  Using a config transform your Dev config file would use the Dev server and your Prod config would use the Prod server.  If you follow the VS approach then you'd end up having a separate project config for each one but I don't recommend that.  Instead I recommend that you switch to environmental configs that are transformed during a build into their final version.  You can then swap out the appropriate config based upon the environment.  For debugging purposes the base config is generally set up for the dev server.  I'm actually blogging about that topic right now if you'd like to see how we solve it where I work.

    The net result is the same though, your config file contains the correct connection string rather than hard coding anything in code.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Proposed as answer by Mike FengModerator Monday, June 24, 2013 3:10 AM
    • Marked as answer by Harlan B Monday, June 24, 2013 1:29 PM
    Thursday, June 20, 2013 5:56 PM
    Moderator

All replies

  • You do not want to do this in code.  It entirely defeats the purpose of using config files.  You should pull your connstring from the config file.  The dev version of your config will be different than the prod version.  That is one of the reasons why config transforms were created.  Just by changing your config file you can get the same code to work against different servers.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Thursday, June 20, 2013 4:43 PM
    Moderator
  • Thanks for your response.

    How would this work in a client-server environment?

    Does one need to make two edmx files, one for DEV and one for production?

    If the databases on both machines have the same structure, why couldn't I re-use the DEV edmx and just tell the production application to use a different server?


    Harlan Black

    Thursday, June 20, 2013 5:45 PM
  • Dev vs Prod doesn't matter provided the schemas are the same.  The only thing that would change in your config file is "data source" key, possibly the "initial catalog" key and optionally the security-related keys.  The rest of the string would remain unchanged.  Using a config transform your Dev config file would use the Dev server and your Prod config would use the Prod server.  If you follow the VS approach then you'd end up having a separate project config for each one but I don't recommend that.  Instead I recommend that you switch to environmental configs that are transformed during a build into their final version.  You can then swap out the appropriate config based upon the environment.  For debugging purposes the base config is generally set up for the dev server.  I'm actually blogging about that topic right now if you'd like to see how we solve it where I work.

    The net result is the same though, your config file contains the correct connection string rather than hard coding anything in code.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    • Proposed as answer by Mike FengModerator Monday, June 24, 2013 3:10 AM
    • Marked as answer by Harlan B Monday, June 24, 2013 1:29 PM
    Thursday, June 20, 2013 5:56 PM
    Moderator