What's wrong with my connection string?

Question

I'm trying to create an ASP.NET MVC application, but have been struggling all day to even get a model from the database set up. The frustrating part is that the connection string was generated when I created the ADO.NET model from the database, and it doesn't like it.

The connection strings found in web.config:

<add name="DefaultConnection" 
     connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True" 
     providerName="System.Data.SqlClient"
     />
<add name="DatabaseEntities" 
   connectionString="metadata=res://*/Models.DatabaseModels.csdl|res://*/Models.DatabaseModels.ssdl|res://*/Models.DatabaseModels.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|\Database.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
   providerName="System.Data.EntityClient"
   />

Exception triggered at statement:

using (SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["DatabaseEntities"].ConnectionString))
{..}

'ArgumentException' Keyword not supported: 'metadata'.

SOLUTION: As LTMOD answered, I needed two connections in web.config, the initial SqlClient and the generated EntityClient connection. I needed to use the SqlClient connection string in the 'using' statement, as well as calling con.Open() in the block.

Alex T · Accepted Answer · 2015-01-30 23:17:46Z

1

The connection string for entities and ADO is somewhat different. In my projects, I typically have both. Something like this:

<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=SERVER;Initial Catalog=DBNAME;User ID=USER;Password=PWD;" providerName="System.Data.SqlClient" />
<add name="Entities" connectionString="metadata=res://*/Models.PTModel.csdl|res://*/Models.PTModel.ssdl|res://*/Models.PTModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SERVER;initial catalog=DBNAME;user id=USER;password=PWD;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

DefaultConnection is used by ADO, while Entity access automatically uses the Entities connection.

There are a number of reason for this. For one thing, sometimes it's easier to construct an SQL query to perform a task rather than jumping through hoops with LINQ/Lambda. There are plenty of cases where all of the work needs to take place in the DB, and having both connections lets you easily do this.

answered Jan 30, 2015 at 23:17

Alex T

7751 gold badge8 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grhm Over a year ago

I was being a bit dense earlier and hadn't changed the SqlConnection argument to the default connection's string. There is now a new exception ServerVersion = 'con.ServerVersion' threw an exception of type 'System.InvalidOperationException', as I think the default connection string is wrong.

meda · Accepted Answer · 2015-01-31 16:18:38Z

0

You used DatabaseEntities for your ADO.net access layer, that connection is meant for Entity framework.
No you don't need 2 connection strings for ADO.net, you will need them only for EF when using EDMX files because they need DefaultConnection in addition to the model connection string (DatabaseEntities).
Use DefaultConnection and if you still get errors, it means your connection string is not correct, use visual studio to connect to your database and copy the connections string to your web.config file.

var connectionString = 
         ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (SqlConnection con = new SqlConnection(connectionString){...}

answered Jan 31, 2015 at 16:18

meda

45.5k14 gold badges96 silver badges123 bronze badges

Collectives™ on Stack Overflow

What's wrong with my connection string?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related