1

I am beginner in ASP.NET MVC and trying to Database access with Entity Framework Code-First but I am getting an error. I saw many questions on stackoverflow but they are not relevant to my situation. My error is.

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code

Additional information: Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName.

in LensStoreController.cs at var brands = lensStoreDB.Brands.ToList(); line

LensStoreController.cs 

public ActionResult Index()
{
    var brands = lensStoreDB.Brands.ToList();
    return View(brands);
}

Brands.cs, Lenses.cs and Manufacturer.cs are my Model classes

Brand.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace EyeContactLens.Models
{
    public class Brands
    {
        [Key]
        public int BrandId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public List<Lenses> Lenses { get; set; }

    }

}

Lenses.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace EyeContactLens.Models
{
    public class Lenses
    {
        [Key]
        public int LensesId { get; set; }
        public int BrandId { get; set; }
        public int ManufacturerId { get; set; }
        public string Title { get; set; }
        public decimal Price { get; set; }
        public string LensManuUrl { get; set; }
        public Brands Brand { get; set; }
        public Manufacturer Manufacturer { get; set; }
    }
}

Manufacturer.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace EyeContactLens.Models
{
    public class Manufacturer
    {
        public int ManufacturerId { get; set; }
        public string Name { get; set; }
    }
}

I have one more class SampleData.cs, whose data I want to show on browser.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace EyeContactLens.Models
{
    public class SampleData : DropCreateDatabaseIfModelChanges<EyeContactLensEntities>
    {
        protected override void Seed(EyeContactLensEntities context)
        {
            var brands = new List<Brands>
            {
                new Brands { Name = "Cooper Vision"},
                new Brands { Name = "Fresh Kon"},
                new Brands { Name = "Flexcon"},
                new Brands { Name = "Avaira"},
            };

            var manufacturer = new List<Manufacturer>
            {
                 new Manufacturer { Name = "Oculus"},
                 new Manufacturer { Name = "Alcon (CIBA Vision)"}
            };

            new List<Lenses>
            {
                new Lenses { Title = "Biofinity Contact Lens", Brand = brands.Single(b => b.Name == "Cooper Vision"), Price = 8.99M, Manufacturer = manufacturer.Single(a => a.Name == "Alcon (CIBA Vision)"), LensManuUrl = "/Content/Images/placeholder.gif" },
                new Lenses { Title = "FreshKon A55", Brand = brands.Single(b => b.Name == "Fresh Kon"), Price = 18.99M, Manufacturer = manufacturer.Single(a => a.Name == "Oculus"), LensManuUrl = "/Content/Images/placeholder.gif" },
                new Lenses { Title = "Flexcon Blue Tint UV Prolong wear (BUPW) (45%)", Brand = brands.Single(b => b.Name == "Flexcon"), Price = 81.99M, Manufacturer = manufacturer.Single(a => a.Name == "Oculus"), LensManuUrl = "/Content/Images/placeholder.gif" },
                new Lenses { Title = "Frequency 55 Toric Contact Lens", Brand = brands.Single(b => b.Name == "Cooper Vision"), Price = 10.99M, Manufacturer = manufacturer.Single(a => a.Name == "Alcon (CIBA Vision)"), LensManuUrl = "/Content/Images/placeholder.gif" },
                new Lenses { Title = "Freshkon N-Hance Toric", Brand = brands.Single(b => b.Name == "Fresh Kon"), Price = 11.99M, Manufacturer = manufacturer.Single(a => a.Name == "Oculus"), LensManuUrl = "/Content/Images/placeholder.gif" }
            }.ForEach(a => context.Lenses.Add(a));
        }
    }
}

I am also using EyeContactLensEntities.cs as a DbContext:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace EyeContactLens.Models
{
    public class EyeContactLensEntities : DbContext
    {
        public DbSet<Lenses> Lenses { get; set; }
        public DbSet<Brands> Brands { get; set; }
    }
}

web.config:

<connectionStrings>
    <add name="EyeContactLensEntities" connectionString="Data Source=|DataDirectory|EyeContactLens.sdf" providerName="System.Data.SqlClient"/>
</connectionStrings>

Global.asax.cs

protected void Application_Start()
{
    System.Data.Entity.Database.SetInitializer(new Models.SampleData());

    AreaRegistration.RegisterAllAreas();
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nabia saroosh
  • 399
  • 1
  • 14
  • 36
  • Why are you using an Access database? Why not put SQL Server Developer Edition on your computer? – mason Apr 13 '17 at 13:09
  • Because I want to show data on browser by SampleData which is mentioned in my question. – nabia saroosh Apr 13 '17 at 13:11
  • That does not answer my question. – mason Apr 13 '17 at 13:13
  • Your mean, I must use SQL server for this purpose? – nabia saroosh Apr 13 '17 at 13:15
  • I am not sure about using Entity Framework with Access. Never tried it. But Access is a poor choice to use as the database for a website. Access isn't meant to be used from multiple contexts at the same time. You'd be better off using SQL Server, or migrating to some free equivalent such as SQLite, MySQL, Postgres etc. You're welcome to try it though. The error is indicating it doesn't know what database to use. You need to specify and initial catalog or AttachDBFileName in your connection string. – mason Apr 13 '17 at 13:19
  • I agree with @mason. Ditch Access and use something better, I like MySQL for use with EF as a free alternative. Install WAMP to use MySQL easily on your local machine. – Luke Apr 13 '17 at 13:21
  • You don't need a full database server instance simply for development. Visual Studio has a database server built-in (basically SQL Server Express). You're free to use that. EF just doesn't work with an Access database. – Chris Pratt Apr 13 '17 at 14:25

2 Answers2

0

Your connection string is wrong. The things you need to provide in the config string are the Data Source (e.g. a SQL Server instance) and an initial catalog (e.g. a Database on that server).

An .sdf can be opened with Sql Server Management Studio and from there you should be able to see what exactly you need to connect to.

EDIT: A link on how to do that with SQL Server Management Studio: How do you open an SDF file (SQL Server Compact Edition)?

Community
  • 1
  • 1
Mario Mucalo
  • 597
  • 5
  • 16
0

You need to change the providerName in your connection string:

<connectionStrings>
    <add name="EyeContactLensEntities" 
         connectionString="Data Source=|DataDirectory|EyeContactLens.sdf" 
         providerName="System.Data.SqlSqlServerCe.4.0"/>
</connectionStrings>

And you need to install the SQL Server Compact EF Nuget package:

Install-Package EntityFramework.SqlServerCompact
ErikEJ
  • 40,951
  • 5
  • 75
  • 115