Entity Framework Deploy - Connection Strings

Question

I have a little problem. I have application running on localhost and everything is OK, but when I upload it to production web server and I want to connect the database I get just "Sorry, an error occurred while processing your request.". When I try page without database interaction it works. I'm using Entity Framework, but I'm not sure if I have connection strings correct.

This is connection string for localhost:

  <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=localhost\MSSQL;Integrated Security=SSPI;Initial Catalog=KravmagaIS" />
    <add name="KravmagaISEntities" connectionString="metadata=res://*/Models.KravmagaModel.csdl|res://*/Models.KravmagaModel.ssdl|res://*/Models.KravmagaModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=ISKUDA-NTB\MSSQL;Initial Catalog=KravmagaIS;Integrated Security=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

and this one is for production server:

  <connectionStrings>
    <add name="ApplicationServices" connectionString="Data Source=192.168.1.5;User ID=db3542;Password=****;Initial Catalog=db3542" />
    <add name="KravmagaISEntities" connectionString="metadata=res://*/Models.KravmagaModel.csdl|res://*/Models.KravmagaModel.ssdl|res://*/Models.KravmagaModel.msl;provider=System.Data.SqlClient;provider connection string='Data Source=192.168.1.5;User ID=db3542;Password=*****;Initial Catalog=db3542'" providerName="System.Data.EntityClient" />
  </connectionStrings>

Is there any mistake? I'm sure that password is correct.

Thanks for helping.

EDIT:

Sorry my mistake. :-/ The connection is OK. But the relations in production server are the cause of the problem.

I have class Training and class Instructor. (Training has one instructor).

When I call in view:

@training.InstructorID

I get normaly the ID of training instructor, but when I call:

@training.Instructor.Fullname

I get the error message. On localhost is everything working.

Any ideas? Thanks.

Are you sure initial catalog is different between the two servers? Usually DB names are the same regardless of where the DB is deployed — BrokenGlass
– BrokenGlass, Commented Apr 18, 2011 at 12:47
The initial catalog is similar to database name, am I right? Because database name on localhost is KravmagaIS and on hosted server is db3542. — Iškuda
– Iškuda, Commented Apr 18, 2011 at 12:55
@Iškuda: Yes it's the DB name. Also make sure SQL on that server is accepting remote connections, TCP/IP is enabled in SQL, the port is open on the firewall - the usual candidates — BrokenGlass
– BrokenGlass, Commented Apr 18, 2011 at 12:58
@BrokenGlass It is a standard IIS hosting, where I'm trying to deploy my app, so problem should be somewhere else. :-( — Iškuda
– Iškuda, Commented Apr 18, 2011 at 13:13
Btw. second connection string doesn't have MARS enabled. Also what logging do you have in your application to get a real exception? — Ladislav Mrnka
– Ladislav Mrnka, Commented Apr 18, 2011 at 13:20

Ladislav Mrnka · Accepted Answer · 2011-04-18 18:47:13Z

I suppose that the same code runs in your development environment so it should not be a problem of your code. Also because you mentioned that Training is loaded it should not be a problem of connecting to the database server.

It looks like lazy loading is not working which turns back to my former note that MARS (MultipleActiveResultSets) is not enabled in the production connection string. MARS allows you to read results from one query in the loop and in the same time execute another query to get details. Typical example is:

// Executes something kile SELECT * FROM Trainings and has opened
// DataReated for the whole duration of the loop.
foreach(var training in context.Trainings) 
{
    // Executes something like SELECT * FROM Instructors WHERE Id = @Id
    // and opens its own DataReader to get the result.
    // If MARS is not enabled or not supported you will get an exception
    var fullName = training.Instructor.FullName;
}

Btw. the code is example of N+1 problem because it will exacute 1 outer query and for each result of the outer query it will execute 1 inner query. So for N results from the outer query you it will execute N subqueries => THIS IS BAD and should be avoided as much as possible by using another loading technique. For example:

// Loads both trainings ana related instructors in the same query.
foreach(var training in context.Trainings.Include("Instructor"))
{
    // No query is executed here because instructor is already loaded.
    var fullName = training.Instructor.FullName;
}

Collectives™ on Stack Overflow

Entity Framework Deploy - Connection Strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related