Unable to access data in an ASP.NET MVC web application using Entity Framework and SQL Server database

In an ASP.NET MVC web application, I have created the following entity:

[Table("tblEmployee")]
public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
}

But, when I try to retrieve data from a database table called tblEmployee using Entity Framework, I get an error. What I have done until now is:

• Created a database MVCDemo with “.” as server name and using Windows authentication containing a table called tblEmployee
• Installed Entity Framework
• Added EmployeeContext.cs class file to Models folder

Code:

namespace MVCDemo.Models
{
    public class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }
    }
}

• Added a connection string to web.config file in the root directory

   <connectionSrtings>
       <add name="EmployeeContext" 
            connectionString="server=.; database=MVCDemo; integrated security=SSPI"
            providerName="System.Data.SqlClient;" />
   </connectionSrtings>

• Added Details actionResult to EmployeeController to show employee details:

    namespace MVCDemo.Controllers
    {
        public class EmployeeController : Controller
        {
            // GET: Employee
            public ActionResult Details(int id)
            {
                EmployeeContext employeeContext = new EmployeeContext();
                Employee employee = employeeContext.Employees.Single(e => e.EmployeeId == id);
                return View(employee);
            }
        }
    }

Finally, I added the following code to Global.asax to prevent initialization:

Database.SetInitializer<MVCDemo.Models.EmployeeContext>(null);

The problem is when I run the application I get this error:

HTTP Error 500.19 – Internal Server Error

The requested page cannot be accessed because the related configuration data for the page is invalid.

and when I comment connection strings out and try to reach

http://localhost:60613/Employee/Details/1

to show details of 1st employee, I get this error:

System.Data.Entity.Core.EntityException: ‘The underlying provider failed on Open.’SqlException: Cannot attach the file ‘C:UsersaryasourcereposMVCDemoMVCDemoApp_DataMVCDemo.Models.EmployeeContext.mdf’ as database ‘MVCDemo.Models.EmployeeContext’.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Check your tag name, it is incorrect. It will trigger the error definitely.

<connectionSrtings>

It should be:

<connectionStrings>

Update:
Since you have another issue, fix the last part of your connection string:

providerName="System.Data.SqlClient;

Remove the semi-colon at the end of SqlClient.

Method 2

This is because if you have the database named MVCDemo in the SQL Server then its fine, otherwise code first approach looking for MVCDemo database in SQL Server. If you dont have the database in SQL Server, then try this connection string to create the mdf file first.

connectionString="Data Source=(LocalDb)MSSQLLocalDB;AttachDbFilename=|DataDirectory|aspnet-MVCDemo-20200820010246.mdf;Initial Catalog=aspnet-MVCDemo-20200820010246;Integrated Security=True"

Method 3

First of all, I couldn’t see your database connection. Yes you have teached the table but you didn’t tell the program where to put those infos. Something like:

optionsBuilder.UseSqlServer("Data Source=databaseName")

Second possible reason is your program don’t know where to go at the beginning. I had those error before and solved it with using default map.

Third reason, this probably not true but, you wrote “connectionSrtings” wrong. As I say, it’s probably not that but I wanted to mention if it is.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0