如何为实体框架创建有效的连接字符串-基础提供程序在打开时失败? [英] How to create a valid connection string for Entity Framework - The underlying provider failed on Open?

问题描述

给出以下连接字符串:

<add name="PrimaryDBConnectionString" connectionString="metadata=res://*/;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=10.1.1.101;Initial Catalog=primary;Persist Security Info=True;User ID=myuserid;Password=mypassword;MultipleActiveResultSets=True;Application Name=EntityFramework&quot;" />

我尝试使用以下命令在DAL中打开连接:

I attempt to open a connection in my DAL with the following:

        using (PrimaryDBContext ctx = new PrimaryDBContext(ConfigurationManager.ConnectionStrings["PrimaryDBConnectionString"].ToString()))
        {
            try
            {
                ctx.Connection.Open();
                var result = ctx.sq_newsfeed_GetProfileByID(profileID);

我得到的错误是:

基础提供程序在打开时失败

The underlying provider failed on Open

我弄乱了EF连接字符串，并用"metadata = res://*/;"替换了所有提供程序前缀的东西.但还是不行.

I have messed around with the EF connection string and replaced all the provider prefix stuff with "metadata=res://*/;" but still no go.

请问有人对此有何启示?

Can anyone shed some light on this please?

谢谢.

-更新-

感谢您的回复...我最终只是从用户界面创建了一个新的数据库连接，然后修改了连接字符串以符合我的需求:

Thank you for the response... I ended up just creating a new db connection from the UI and modifying the connection string to match my needs:

<add name="PrimaryEntities" connectionString="metadata=res://*/PrimaryModel1.csdl|res://*/PrimaryModel1.ssdl|res://*/PrimaryModel1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=10.99.108.42;initial catalog=primary;user id=dbuserID;password=somepw;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

我保留了元数据部分.诀窍是确保您的.csdl，.ssdl和.msl文件名前缀与您的数据库上下文匹配.

I kept the metadata portion. The trick was to ensure that your .csdl, .ssdl and .msl file name prefix matches your db context.

推荐答案

您是否正在使用Code First/DbContext(您的问题在Entity-framework-4.1下提出)?如果是这样，那么您的连接字符串应该只是常规的连接字符串-像这样:

Are you using Code First/DbContext (your question is filed under entity-framework-4.1)? If so then your connection string should be just a regular connection string - something like this:

<add name="PrimaryDBConnectionString" providerName="System.Data.SqlClient"  connectionString="Data Source=(localdb)\v11.0;Initial Catalog=squirtprimary;Persist Security Info=True;Integrated Security=true;MultipleActiveResultSets=True;Application Name=EntityFramework" />    

(您也不必使用配置管理器做魔术-您可以像这样向ctor提供连接字符串的名称:

(You also don't have to do magic with configuration manager - you can just provide the name of the connection string to your ctor like this:

"name=PrimaryDBConnectionString"

它应该可以正常工作

另一方面，如果您不是使用DbContext而是使用ObjectContext(获得的异常将表明这一点-您没有得到异常，说明您的连接字符串错误或缺少providerName参数).然后，您需要检查是否能够在没有EF的情况下连接到数据库.一些提示:

On the other hand, if you are not using DbContext but ObjectContext (the exception you get would indicate this - you did not get an exception saying that your connection string is wrong or you are missing the providerName parameter). Then you would need to check if you are able to connect to the database without EF. A few hints:

• 您使用IP地址作为数据源-如果这是远程服务器，您确定已启用接受外部客户端吗?(默认情况下，这在Sql Server中是禁用的)
• 您正在使用user/pwd进行身份验证-您确定这些正确吗

检查以上内容的方法之一是在计算机上打开Sql Server Management Studio，并提供连接字符串中的数据.

One of the ways to check the above is to open Sql Server Management Studio on your machine and provide the data you have in your connection string.

您看到的异常并不表示元数据部分有任何问题.特别是关于无法打开与数据库的连接.

The exception you are seeing does not indicate any problems with metadata part. It is specifically about not being able to open the connection to the database.

这篇关于如何为实体框架创建有效的连接字符串-基础提供程序在打开时失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！