-
Jul 15th, 2020, 05:24 AM
#1
Thread Starter
New Member
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
-
Jul 15th, 2020, 05:40 AM
#2
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
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.
-
Jul 15th, 2020, 05:42 AM
#3
Thread Starter
New Member
Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil
Originally Posted by ChrisE
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
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
-
Jul 15th, 2020, 05:50 AM
#4
Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil
Originally Posted by Ian_W82
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.
-
Jul 15th, 2020, 05:53 AM
#5
Thread Starter
New Member
Re: Error trying to connect Entiry Framework in code using EntityConnectionStringBuil
Originally Posted by ChrisE
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.
-
Jul 15th, 2020, 07:42 AM
#6
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="data source=.\SQLEXPRESS;initial catalog=NorthWindAzure1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|