Creating new SQL Database using code first approach in entityframework

Posted by Maheshvishnu under ASP.NET on 9/5/2012 | Points: 10 | Views : 2067 | Status : [Member] | Replies : 2
How can i Create new SQL Database using code first approach in entityframework



Posted by: Muhsinathk on: 9/6/2012 [Member] Bronze | Points: 25

Here are the high level steps on how to setup EF Code First with SQL Express.

Step 1: Create you model classes. For example

public class Product
public int ProductId { get; set; }
public string Name { get; set; }
public string Description { get; set; }


Step 2: Create a class to handle the retrieval/persistence of model instances from a database. Lets call it ProductContext". It need to derive from the DbContext base class.

public class ProductContext : DbContext
public DbSet<Product> Products { get; set; }


Step 3: Modify the web.config to setup a connection-string to point to a SQL Express database. When you create a DbContext class with EF code-first,it needs a matching connection string . In this example, we named our context class "ProductContext", so the name of our connection string need to be "ProductContext" as well.

<add name="ProductContext" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=ProductDb;Integrated Security=SSPI;User Instance=true;MultipleActiveResultSets=True"/>

After you compile your application, your SQL Express database will be created by Entity Framework. If you try to connect to your new database through SQL Server Management Studio, you will be surprised not to find your database. So where is my new DB located? A quick search on my machine revealed that the SQL Server database file was created at the following location:

C:\Users\<UserName>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\ProductDb.mdb

So how do I view the data stored in this mdb file. The best solution I have found is to add a new data connection using Visual Studio "Server Explorer" window.

Select the local machine where your SQL Express is located and select the option of "Attach a database file" pointing to the location of your mdb file.

Now you will be able to see the database created by EF. If you make any changes to your underlying model and recompile your MVC app, you will get access violation error regarding mdb file. You have to make sure to close the active connection by selecting "Close Connection" in Server Explorer.

Once the connection is closed, you should be able to successfully compile the app.

Maheshvishnu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/14/2012 [Member] Bronze | Points: 25

Hi MaheshVishnu,
Please Mark as Answer if it helpful to you..That helps others who search the same...

Maheshvishnu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response