locked
Entity Framework code first database is not created RRS feed

  • Question

  • User2008942235 posted

    Hello,

    I am currently working on this tutorial to learn ASP.net https://docs.microsoft.com/en-us/aspnet/web-forms/overview/getting-started/getting-started-with-aspnet-45-web-forms/create_the_data_access_layer but the stuff with my database is not working. I.e. when I run the website the database is not created automatically. Instead of creating a default database as described in the tutorial I would like to use my local SQL Server 2017. So what I did to do this is use another connection string and refer to it from the context class:

    public class ProductContext : DbContext
        {
            public ProductContext() : base("name=WingtipToys")
            {
            }
            public DbSet<Category> Categories { get; set; }
            public DbSet<Product> Products { get; set; }
        }

    <connectionStrings>
        <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WingtipToys-20171201071611.mdf;Initial Catalog=aspnet-WingtipToys-20171201071611;Integrated Security=True"
          providerName="System.Data.SqlClient" />
        <add name="WingtipToys"
          connectionString="Data Source=(local);Initial Catalog=wingtiptoys;Integrated Security=sspi"
          providerName="System.Data.SqlClient" />

    </connectionStrings>

    But for some reason this is not working. I also tried if I can connect programatically and it worked fine.

    SqlConnection sqlConnection = new SqlConnection();
    sqlConnection.ConnectionString = "Data Source=(local);Initial Catalog=wingtiptoys;Integrated Security=sspi";
    sqlConnection.Open();

    I am using sspi as I want to do Windows Authentification.

    So I don't understand why the Entity Framework isn't doing what it is supposed to do. Unfortunately I also don't see any information in the output window of Visual Studio so I have no idea what's going on. When I look into the profile window on my SQL Server Management Studio I see that nothing is done.

    Has anyone an idea what else I can try?

    Best regards

    Thomas

    Saturday, December 2, 2017 12:33 PM

Answers

  • User-707554951 posted

    Hi ThomasKe,

    For your problem, you should have same thing as below:

    Category and  Product class as you provided:

    ProductContext.cs:

     public class ProductContext : DbContext
        {
            public ProductContext() : base("name=WingtipToys")
            {
             
            }
            public DbSet<Category> Categories { get; set; }
            public DbSet<Product> Products { get; set; }
        }

    ProductDatabaseInitializer.cs:

     public class ProductDatabaseInitializer : DropCreateDatabaseIfModelChanges<ProductContext>
        {
            protected override void Seed(ProductContext context)
            {
                GetCategories().ForEach(c => context.Categories.Add(c));
                context.SaveChanges();
                GetProducts().ForEach(p => context.Products.Add(p));
                context.SaveChanges();
                base.Seed(context);
            }
    
            private static List<Category> GetCategories()
            {
                var categories = new List<Category> {
                    new Category
                    {
                        CategoryID = 1,
                        CategoryName = "Cars"
                    },
                    new Category
                    {
                        CategoryID = 2,
                        CategoryName = "Planes"
                    },
                    new Category
                    {
                        CategoryID = 3,
                        CategoryName = "Trucks"
                    },
                    new Category
                    {
                        CategoryID = 4,
                        CategoryName = "Boats"
                    },
                    new Category
                    {
                        CategoryID = 5,
                        CategoryName = "Rockets"
                    },
                };
    
                return categories;
            }
    
            private static List<Product> GetProducts()
            {
                var products = new List<Product> {
                    new Product
                    {
                        ProductID = 1,
                        ProductName = "Convertible Car",
                        Description = "This convertible car is fast! The engine is powered by a neutrino based battery (not included)." +
                                      "Power it up and let it go!",
                        ImagePath="carconvert.png",
                        UnitPrice = 22.50,
                        CategoryID = 1
                   },
                    new Product
                    {
                        ProductID = 2,
                        ProductName = "Old-time Car",
                        Description = "There's nothing old about this toy car, except it's looks. Compatible with other old toy cars.",
                        ImagePath="carearly.png",
                        UnitPrice = 15.95,
                         CategoryID = 1
                   },
                    new Product
                    {
                        ProductID = 3,
                        ProductName = "Fast Car",
                        Description = "Yes this car is fast, but it also floats in water.",
                        ImagePath="carfast.png",
                        UnitPrice = 32.99,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 4,
                        ProductName = "Super Fast Car",
                        Description = "Use this super fast car to entertain guests. Lights and doors work!",
                        ImagePath="carfaster.png",
                        UnitPrice = 8.95,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 5,
                        ProductName = "Old Style Racer",
                        Description = "This old style racer can fly (with user assistance). Gravity controls flight duration." +
                                      "No batteries required.",
                        ImagePath="carracer.png",
                        UnitPrice = 34.95,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 6,
                        ProductName = "Ace Plane",
                        Description = "Authentic airplane toy. Features realistic color and details.",
                        ImagePath="planeace.png",
                        UnitPrice = 95.00,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 7,
                        ProductName = "Glider",
                        Description = "This fun glider is made from real balsa wood. Some assembly required.",
                        ImagePath="planeglider.png",
                        UnitPrice = 4.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 8,
                        ProductName = "Paper Plane",
                        Description = "This paper plane is like no other paper plane. Some folding required.",
                        ImagePath="planepaper.png",
                        UnitPrice = 2.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 9,
                        ProductName = "Propeller Plane",
                        Description = "Rubber band powered plane features two wheels.",
                        ImagePath="planeprop.png",
                        UnitPrice = 32.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 10,
                        ProductName = "Early Truck",
                        Description = "This toy truck has a real gas powered engine. Requires regular tune ups.",
                        ImagePath="truckearly.png",
                        UnitPrice = 15.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 11,
                        ProductName = "Fire Truck",
                        Description = "You will have endless fun with this one quarter sized fire truck.",
                        ImagePath="truckfire.png",
                        UnitPrice = 26.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 12,
                        ProductName = "Big Truck",
                        Description = "This fun toy truck can be used to tow other trucks that are not as big.",
                        ImagePath="truckbig.png",
                        UnitPrice = 29.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 13,
                        ProductName = "Big Ship",
                        Description = "Is it a boat or a ship. Let this floating vehicle decide by using its " +
                                      "artifically intelligent computer brain!",
                        ImagePath="boatbig.png",
                        UnitPrice = 95.00,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 14,
                        ProductName = "Paper Boat",
                        Description = "Floating fun for all! This toy boat can be assembled in seconds. Floats for minutes!" +
                                      "Some folding required.",
                        ImagePath="boatpaper.png",
                        UnitPrice = 4.95,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 15,
                        ProductName = "Sail Boat",
                        Description = "Put this fun toy sail boat in the water and let it go!",
                        ImagePath="boatsail.png",
                        UnitPrice = 42.95,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 16,
                        ProductName = "Rocket",
                        Description = "This fun rocket will travel up to a height of 200 feet.",
                        ImagePath="rocket.png",
                        UnitPrice = 122.95,
                        CategoryID = 5
                    }
                };
    
                return products;
            }

    Global.cs:

     public class Global : HttpApplication
        {
            void Application_Start(object sender, EventArgs e)
            {
                // Code that runs on application startup
                RouteConfig.RegisterRoutes(RouteTable.Routes);
                BundleConfig.RegisterBundles(BundleTable.Bundles);
    
                // Initialize the product database.
                 Database.SetInitializer(new ProductDatabaseInitializer());
    
                ProductContext con = new ProductContext();
                con.Database.Initialize(true);
                con.Database.CreateIfNotExists();
          
            }
        }

    Connection string:

    <add name="WingtipToys" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WingtipToys.mdf;Initial Catalog=WingtipToys;Integrated Security=True"
         providerName="System.Data.SqlClient" />

    After run application, it will generate WingtipToys.mdf  in App-Data folder as below:

    Data in database:


    Best regards 

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 9:13 AM
  • User-707554951 posted

    Hi ThomasKe,

    The way i provide will create the tables directly on an already existing database,

    You just need to provide  correct  connection string to your  existing database,

    Datatable1 be included by an  existing database

    Best regards 

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 9:49 AM

All replies

  • User475983607 posted

    It's

    public ProductContext() : base("WingtipToys")

    Not

    public ProductContext() : base("name=WingtipToys")

    Saturday, December 2, 2017 5:01 PM
  • User2008942235 posted

    Hello,

    I tried it but doesn't work. Actually I guess that "name=WingtipToys" is correct. See http://www.entityframeworktutorial.net/code-first/database-initialization-in-code-first.aspx.

    I want that my connection string named "WingtipToys" contained in webconfig is used.

    Best regards,

    Thomas

    Saturday, December 2, 2017 6:35 PM
  • User-707554951 posted

    Hi ThomasKe,

    For your problem, you should have same thing as below:

    Category and  Product class as you provided:

    ProductContext.cs:

     public class ProductContext : DbContext
        {
            public ProductContext() : base("name=WingtipToys")
            {
             
            }
            public DbSet<Category> Categories { get; set; }
            public DbSet<Product> Products { get; set; }
        }

    ProductDatabaseInitializer.cs:

     public class ProductDatabaseInitializer : DropCreateDatabaseIfModelChanges<ProductContext>
        {
            protected override void Seed(ProductContext context)
            {
                GetCategories().ForEach(c => context.Categories.Add(c));
                context.SaveChanges();
                GetProducts().ForEach(p => context.Products.Add(p));
                context.SaveChanges();
                base.Seed(context);
            }
    
            private static List<Category> GetCategories()
            {
                var categories = new List<Category> {
                    new Category
                    {
                        CategoryID = 1,
                        CategoryName = "Cars"
                    },
                    new Category
                    {
                        CategoryID = 2,
                        CategoryName = "Planes"
                    },
                    new Category
                    {
                        CategoryID = 3,
                        CategoryName = "Trucks"
                    },
                    new Category
                    {
                        CategoryID = 4,
                        CategoryName = "Boats"
                    },
                    new Category
                    {
                        CategoryID = 5,
                        CategoryName = "Rockets"
                    },
                };
    
                return categories;
            }
    
            private static List<Product> GetProducts()
            {
                var products = new List<Product> {
                    new Product
                    {
                        ProductID = 1,
                        ProductName = "Convertible Car",
                        Description = "This convertible car is fast! The engine is powered by a neutrino based battery (not included)." +
                                      "Power it up and let it go!",
                        ImagePath="carconvert.png",
                        UnitPrice = 22.50,
                        CategoryID = 1
                   },
                    new Product
                    {
                        ProductID = 2,
                        ProductName = "Old-time Car",
                        Description = "There's nothing old about this toy car, except it's looks. Compatible with other old toy cars.",
                        ImagePath="carearly.png",
                        UnitPrice = 15.95,
                         CategoryID = 1
                   },
                    new Product
                    {
                        ProductID = 3,
                        ProductName = "Fast Car",
                        Description = "Yes this car is fast, but it also floats in water.",
                        ImagePath="carfast.png",
                        UnitPrice = 32.99,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 4,
                        ProductName = "Super Fast Car",
                        Description = "Use this super fast car to entertain guests. Lights and doors work!",
                        ImagePath="carfaster.png",
                        UnitPrice = 8.95,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 5,
                        ProductName = "Old Style Racer",
                        Description = "This old style racer can fly (with user assistance). Gravity controls flight duration." +
                                      "No batteries required.",
                        ImagePath="carracer.png",
                        UnitPrice = 34.95,
                        CategoryID = 1
                    },
                    new Product
                    {
                        ProductID = 6,
                        ProductName = "Ace Plane",
                        Description = "Authentic airplane toy. Features realistic color and details.",
                        ImagePath="planeace.png",
                        UnitPrice = 95.00,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 7,
                        ProductName = "Glider",
                        Description = "This fun glider is made from real balsa wood. Some assembly required.",
                        ImagePath="planeglider.png",
                        UnitPrice = 4.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 8,
                        ProductName = "Paper Plane",
                        Description = "This paper plane is like no other paper plane. Some folding required.",
                        ImagePath="planepaper.png",
                        UnitPrice = 2.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 9,
                        ProductName = "Propeller Plane",
                        Description = "Rubber band powered plane features two wheels.",
                        ImagePath="planeprop.png",
                        UnitPrice = 32.95,
                        CategoryID = 2
                    },
                    new Product
                    {
                        ProductID = 10,
                        ProductName = "Early Truck",
                        Description = "This toy truck has a real gas powered engine. Requires regular tune ups.",
                        ImagePath="truckearly.png",
                        UnitPrice = 15.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 11,
                        ProductName = "Fire Truck",
                        Description = "You will have endless fun with this one quarter sized fire truck.",
                        ImagePath="truckfire.png",
                        UnitPrice = 26.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 12,
                        ProductName = "Big Truck",
                        Description = "This fun toy truck can be used to tow other trucks that are not as big.",
                        ImagePath="truckbig.png",
                        UnitPrice = 29.00,
                        CategoryID = 3
                    },
                    new Product
                    {
                        ProductID = 13,
                        ProductName = "Big Ship",
                        Description = "Is it a boat or a ship. Let this floating vehicle decide by using its " +
                                      "artifically intelligent computer brain!",
                        ImagePath="boatbig.png",
                        UnitPrice = 95.00,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 14,
                        ProductName = "Paper Boat",
                        Description = "Floating fun for all! This toy boat can be assembled in seconds. Floats for minutes!" +
                                      "Some folding required.",
                        ImagePath="boatpaper.png",
                        UnitPrice = 4.95,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 15,
                        ProductName = "Sail Boat",
                        Description = "Put this fun toy sail boat in the water and let it go!",
                        ImagePath="boatsail.png",
                        UnitPrice = 42.95,
                        CategoryID = 4
                    },
                    new Product
                    {
                        ProductID = 16,
                        ProductName = "Rocket",
                        Description = "This fun rocket will travel up to a height of 200 feet.",
                        ImagePath="rocket.png",
                        UnitPrice = 122.95,
                        CategoryID = 5
                    }
                };
    
                return products;
            }

    Global.cs:

     public class Global : HttpApplication
        {
            void Application_Start(object sender, EventArgs e)
            {
                // Code that runs on application startup
                RouteConfig.RegisterRoutes(RouteTable.Routes);
                BundleConfig.RegisterBundles(BundleTable.Bundles);
    
                // Initialize the product database.
                 Database.SetInitializer(new ProductDatabaseInitializer());
    
                ProductContext con = new ProductContext();
                con.Database.Initialize(true);
                con.Database.CreateIfNotExists();
          
            }
        }

    Connection string:

    <add name="WingtipToys" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\WingtipToys.mdf;Initial Catalog=WingtipToys;Integrated Security=True"
         providerName="System.Data.SqlClient" />

    After run application, it will generate WingtipToys.mdf  in App-Data folder as below:

    Data in database:


    Best regards 

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 9:13 AM
  • User2008942235 posted
    Hello,

    thanks for your answer. But I would like to use my Sql Server instead of creating a mdf File somewhere on Disk. By doing it in your way I would need to copy the database to my Server. Isn't there a possibility to let EF create the tables direktly on an already existing database on a Sql server 2017?

    Best regards
    Thomas
    Monday, December 4, 2017 9:32 AM
  • User-707554951 posted

    Hi ThomasKe,

    The way i provide will create the tables directly on an already existing database,

    You just need to provide  correct  connection string to your  existing database,

    Datatable1 be included by an  existing database

    Best regards 

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 4, 2017 9:49 AM
  • User2008942235 posted

    Hello Cathy,

    thank you very much for your support. Inserting the underlined code solved my problem. So I guess this code is missing in the microsoft's tutorial. As I expected the tutorial to be correct I only focused on my database connection and was getting crazy as it wasn't working.

    For all others which are facing the same problem this is my connection string:

    <add name="WingtipToys"
          connectionString="Data Source=(local);Initial Catalog=wingtiptoys;Integrated Security=sspi"
          providerName="System.Data.SqlClient" />

    I use sspi as I use windows authentification. If you are using user authentificationyou need to set Integrated Security=True.

    Best regards,

    Thomas

    Monday, December 4, 2017 6:11 PM