none
LocalDb and DataDirectory in MVC4 connection string RRS feed

  • Question

  • I'm playing around with MVC4 and EF5 in VS2012 and I'm having a lot of issues. Sometimes it works and sometimes it doesn't. My first issue was that VS couldn't create my Controller and views using Entity Framework. After a weekend of jacking around with it I finally changed the connection string in my web.config from "AttachDBFilename=|DataDirectory|\ProjectDb.mdf" to "AttachDBFilename=C:\SourceControlWorkspace\US09TFS02\Macleanfogg\user\ProjectManagement\ProjectManagement\ProjectManagement\App_Data\ProjectDb.mdf" and now it can build the Controller and views using Entity Framework. However, now when I run "Update-Database" through the package manager it runs once with no errors and any consecutive runs it says "the database is in use" while running the Seed method. I have to kill sqlserver.exe to release it's kung-fu grip from my database. And even then, the first run doesn't populate the data in the database. Before this "database in use" message I would sometimes have to run the "Update-Database" three or four times before the data was in there. 

    While writing this I was doublechecking my steps as I wrote them. Now I get a completey different error when running "Update-Database"; Cannot attach the file 'C:\SourceControlWorkspace\US09TFS02\Macleanfogg\user\ProjectManagement\ProjectManagement\ProjectManagement\App_Data\ProjectDB.mdf' as database 'ProjectDB'. Which I can understand because the mdf is not there to attach to. I would think that it would be creating it.

        public class DbProject : DbContext
        {
            public DbProject() : base("name=DefaultConnection")
            {
    
            }
    
            public DbSet<Department> Departments { get; set; }
            public DbSet<TeamMembers> TeamMembers { get; set; }
            public DbSet<ProjectRequest> ProjectRequests { get; set; }
            public DbSet<ProjectAnalyzation> ProjectAnalyzations { get; set; }
            public DbSet<Project> Projects { get; set; }
            public DbSet<ProjectPriority> ProjectPriorities { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                Database.SetInitializer(new DropCreateDatabaseAlways<DbProject>());
                
                // Create needed navigation chain
                modelBuilder.Entity<Project>().HasRequired(p => p.ProjectAnalyzation);
                modelBuilder.Entity<Project>().HasRequired(par => par.Lead);
                modelBuilder.Entity<ProjectAnalyzation>().HasRequired(pa => pa.ProjectRequest);
                modelBuilder.Entity<ProjectAnalyzation>().HasRequired(pr => pr.AnalyzedBy);
                
            }

    My Configuration class:

        public class Configuration : DbMigrationsConfiguration<DbProject>
        {
            public Configuration()
            {
                AutomaticMigrationsEnabled = true;
            }
    
            protected override void Seed(DbProject context)
            {
                
                Department d1 = new Department { DepartmentCode = "BS", DepartmentName = "BusinessSystems" };
                TeamMembers t1 = new TeamMembers { Department = d1, FirstName = "Chris", LastName = "Test" };
    
                ProjectRequest p1 = new ProjectRequest
                {
                    Division = "MFCS",
                    RequestDate = DateTime.Now,
                    RequestedCompletionDate = DateTime.Now.AddDays(30),
                    Description = "This is the project description",
                    CurrentState = "We can't currently count pieces until they are weighed.",
                    ProposedFutureState = "We use a new device and count the world!",
                    Objectives = "",
                    TimeSavings = 20,
                    TimeSavingsFrequency = 2,
                    TimeSavingType = "Weekly",
                    CostSavings = 5000,
                    ReoccuringSavings = 200,
                    CostSavingType = "??",
                    SuccessCriteria = "some criteria",
                    Sponsor = "Randy Tester",
                    RequestApproved = true
                };
    
                context.Departments.AddOrUpdate(d1);
                context.TeamMembers.AddOrUpdate(t1);
    
                context.ProjectRequests.AddOrUpdate(p1);
    
                ProjectAnalyzation pa1 = new ProjectAnalyzation
                {
                    AnalyzedBy = t1,
                    ScopeOfWork = "We have to make everything run on the new Device.",
                    EstimatedDuration = 60,
                    EstimatedCost = 1000,
                    Comments = "We love the new Device.",
                    ProjectSize = "Medium",
                    ProjectRequest = p1
                };
    
                context.ProjectAnalyzations.AddOrUpdate(pa1);
    
                context.Projects.AddOrUpdate(new Project
                {
                    FinalProjectNumber = "",
                    ProjectName = "",
                    Lead = t1,
                    Priority = new ProjectPriority { Priority = "", Description = "" },
                    Requester = "",
                    ProjectAnalyzation = pa1,
                    DueDate = DateTime.Now.AddDays(pa1.EstimatedDuration),
                    Status = "",
                    Comments = ""
                });
            }
        }

    And Connection string:

      <connectionStrings>
        <add name="DefaultConnection" 
             connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=ProjectDB;Integrated Security=SSPI;AttachDBFilename=C:\SourceControlWorkspace\US09TFS02\Macleanfogg\user\ProjectManagement\ProjectManagement\ProjectManagement\App_Data\ProjectDB.mdf" 
             providerName="System.Data.SqlClient" />
      </connectionStrings>

    I really dislike LocalDb at this point. Which I know is due to my own ignorance.

    Thanks for any help in advance.


    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 4:54 PM

Answers

  • Ignorance is not bliss. 

    So some of these errors are due to my own failures of understanding how the database is created and updated and what might be using a kung-fu grip on the database. Some of this was due to starting out with a default LocalDb instance that the solution created on its own. Since I switched form (LocalDb)\Projects to (LocalDb)\v11.0 it must have caused some sort of error based upon switching from one to another. My "database in use" error was because IIS Express was still running after debugging to see if the data was in there. Shame on me. I was getting the "cannot attach to file" because I was dropping the database in my OnModelCreating method and then it no longer existed. The mdf was also gone after a reboot. I had to run my MVC project to create the database and THEN run "Update-Database".

    Package Manager now runs my "Update-Database" message just fine and runs the Seed Method with no errors. However, the data is still not making its way into the database.

    Any help?


    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 5:39 PM
  • Update:

    I checked this

     

    and this

    and the data is there.

    However, once I start debugging the data is gone. I realized that it must be dropping the database so it must be calling OnModelCreating because that is the only place I have stated that it should drop the database. I misunderstood when this was called. For some reason I was thinking that this was called when running the Seed method. Not so. See OnModelCreating. What a jackwagon....

    So I commented out the code:

    Database.SetInitializer(new DropCreateDatabaseAlways<DbProject>());

    Runs fine. Everything is fine. Thanks for listening! =P


    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 6:01 PM

All replies

  • Ignorance is not bliss. 

    So some of these errors are due to my own failures of understanding how the database is created and updated and what might be using a kung-fu grip on the database. Some of this was due to starting out with a default LocalDb instance that the solution created on its own. Since I switched form (LocalDb)\Projects to (LocalDb)\v11.0 it must have caused some sort of error based upon switching from one to another. My "database in use" error was because IIS Express was still running after debugging to see if the data was in there. Shame on me. I was getting the "cannot attach to file" because I was dropping the database in my OnModelCreating method and then it no longer existed. The mdf was also gone after a reboot. I had to run my MVC project to create the database and THEN run "Update-Database".

    Package Manager now runs my "Update-Database" message just fine and runs the Seed Method with no errors. However, the data is still not making its way into the database.

    Any help?


    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 5:39 PM
  • Update:

    I checked this

     

    and this

    and the data is there.

    However, once I start debugging the data is gone. I realized that it must be dropping the database so it must be calling OnModelCreating because that is the only place I have stated that it should drop the database. I misunderstood when this was called. For some reason I was thinking that this was called when running the Seed method. Not so. See OnModelCreating. What a jackwagon....

    So I commented out the code:

    Database.SetInitializer(new DropCreateDatabaseAlways<DbProject>());

    Runs fine. Everything is fine. Thanks for listening! =P


    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 6:01 PM
  • I need to add that the DataDirectory must have also been due to starting with one instance and moving to v11.0 because I changed the web.config back and it also works correctly now.

    Chris "Is there anything besides programming?"

    Monday, December 3, 2012 6:22 PM