Results 1 to 6 of 6

Thread: Error trying to connect Entiry Framework in code using EntityConnectionStringBuilder

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    5

    Error trying to connect Entiry Framework in code using EntityConnectionStringBuilder

    Visual Studio 2017.

    A little bit of background to what I am trying to achieve..

    I've created a class library that produces a DLL that I am using within Access 2010. Methods that perform non database functions are called and return the values no problem however anything database related fails. I've tracked this down to an issue with the DLL not reading the config file when called from Access.

    To solve this, I am trying to create my entity framework connection within the class itself using the EntityConnectionStringBuilder() class.

    I can get it to perform a test connection but when I try and access my entity model it fails as the name of the entity doesn't appear anywhere in the connection string, I have tried to add it using entityBuilder.name but that produces an error about not being able to add name when other keywords are used

    I'm currently testing using a VB.NET windows form, trying to create the EF connection on the button click and run a EF linq query.

    I'm sure I am missing something simple here but i'm at a loss, can anyone point me in the right direction please:

    My code:

    Code:
    Public Function getConnectionString() As String
    
            ' Specify the provider name, server and database. 
            Dim providerName As String = "System.Data.SqlClient"
            Dim serverName As String = "myServer"
            Dim databaseName As String = "myDatabase"
    
            ' 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
    
            ' Build the SqlConnection connection string. 
            Dim providerString As String = sqlBuilder.ToString()
    
            ' Initialize the EntityConnectionStringBuilder. 
            Dim entityBuilder As New EntityConnectionStringBuilder()
    
            entityBuilder.Provider = providerName
            entityBuilder.ProviderConnectionString = providerString
    
            entityBuilder.Metadata = "res://*/myDatabase.csdl|res://*/myDatabase.ssdl|res://*/myDatabase.msl"
            Console.WriteLine(entityBuilder.ToString())
    
            Return entityBuilder.ToString()
    
        End Function
    I'm calling with :

    Code:
     Public Sub RunAQuery()
    
            Using conn As New EntityConnection(getConnectionString)
    
                Using myDB As New myDBEntities(conn)
    
                    Dim X = ("My Link Query").ToList
    
                End Using
    
            End Using
    
        End Sub
    It fails with {"No connection string named 'myDBEntities' could be found in the application config file."}

    Thanks
    Last edited by Ian_W82; Jul 15th, 2020 at 05:55 AM. Reason: Added some more information to make it clearer what I am doing

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil

    this doesn't really make sence "System.Data.SqlClient"
    Code:
    Public Function getConnectionString() As String
    
            ' Specify the provider name, server and database. 
            Dim providerName As String = "System.Data.SqlClient"
            Dim serverName As String = "myServer"
            Dim databaseName As String = "myDatabase"
    for Access it should be
    Code:
    OLEDB....
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    5

    Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil

    Quote Originally Posted by ChrisE View Post
    this doesn't really make sence "System.Data.SqlClient"
    Code:
    Public Function getConnectionString() As String
    
            ' Specify the provider name, server and database. 
            Dim providerName As String = "System.Data.SqlClient"
            Dim serverName As String = "myServer"
            Dim databaseName As String = "myDatabase"
    for Access it should be
    Code:
    OLEDB....
    Sorry, should have specified, I am connecting to an SQL server. The Access db is simply acting as the front end application. There are no Access tables involved.

    Thanks

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil

    Quote Originally Posted by Ian_W82 View Post
    Sorry, should have specified, I am connecting to an SQL server. The Access db is simply acting as the front end application. There are no Access tables involved.

    Thanks
    I think you are in the wrong Forum then, it should be VBA or Office-Development
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    5

    Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil

    Quote Originally Posted by ChrisE View Post
    I think you are in the wrong Forum then, it should be VBA or Office-Development
    Thanks but I will have to disagree, this is VB.NET code in Visual Studio 2017, I am currently testing using a Form within the project.

    The Access db accessing the DLL is the final goal but currently, my issue is based solely within the .NET development area.

  6. #6
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil

    The following focuses on getting a Entity Framework connection string from app.config, note you can't change it only get it while Entity Framework code first we have compete control over the connection string.

    Given the following in app.config

    Code:
    <connectionStrings>
      <add name="NorthWindAzure1Entities" connectionString="metadata=res://*/NorthWind.csdl|res://*/NorthWind.ssdl|res://*/NorthWind.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SQLEXPRESS;initial catalog=NorthWindAzure1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>
    Adding a reference for System.Configuration, on a form with one button.

    Code:
    Imports System.Configuration
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private Sub WorkingWithConnectionButton_Click(sender As Object, e As EventArgs) _
            Handles WorkingWithConnectionButton.Click
    
            Dim connectionString = ConfigurationManager.ConnectionStrings("NorthWindAzure1Entities").
                    ConnectionString
    
            Console.WriteLine(connectionString)
    
            Using context As New NorthWindAzure1Entities
                Dim builder = New SqlConnectionStringBuilder With
                        {
                            .ConnectionString = context.Database.Connection.ConnectionString
                        }
    
                Using cn As New SqlConnection With {.ConnectionString = builder.ConnectionString}
                    Try
                        cn.Open()
                        Console.WriteLine("Open")
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
                End Using
    
                Dim categories = context.Categories.AsNoTracking().ToList()
    
                For Each category As Category In categories
                    Console.WriteLine($"{category.CategoryID}, {category.CategoryName}")
                Next
    
            End Using
        End Sub
    End Class
    Results


    metadata=res://*/NorthWind.csdl|res://*/NorthWind.ssdl|res://*/NorthWind.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQLEXPRESS;initial catalog=NorthWindAzure1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
    Open
    1, Beverages
    2, Condiments
    3, Confections
    4, Dairy Products
    5, Grains/Cereals
    6, Meat/Poultry
    7, Produce
    8, Seafood

    Now with that said I don't recommend using the .edmx approach, better to use code first which there is much more control over just about everything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width