Create Websites using SQL Express with ASP.NET MVC

I was trying my hand on learning ASP.NET MVC but one thing that create most problem for me was that most of the tutorials used SQL Compact (here after reffered as SQL CE). But I wanted to use SQL Express as my database server. I had to struggle a lot to find a way to make website using SQL Express and ASP.NET MVC. So I thought if I put it here it might help :)

To start with this, we first need to create a solution in VWD 2010 with MVC.

1. Create a project, choose a name for the project,  for convenience I have named it "MvcTest". Click "Ok" button.

2. Choose Internet Application and Razor as your view engine and click "Ok".

3. You will see that VWD creates different files for you automatically.

4. Now in the Model folder create a Model. You can name it Test.cs

5. In the class that is created add the columns you want to specify in the table in the database, for example here I have created “{get; set;}” property for four items, ID, Name, RollNumber and Fee. When database will be created by Entity framework of ASP.NET MVC these will represent the columns in the table that is created. The definition for table however is not included in this picture (below), that will be given in further steps.

6. Now create a context class. Name the same as TestContext. This context class will basically define the database and table for your application. For adding context class you need to refer to System.Data.Entity. After adding the context your class will basically look like below, here Tests represents your database and Test (given in DbSet<Test>) represents your table.

7. Now coming to the point where you add connection string to the webconfig file.

The connection string to use SQL CE is:

<add connectionstring="Data Source=|DataDirectory|Test.sdf" name="TestContext" providername="System.Data.SqlServerCe.4.0"/>

Courtesy : http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

We add this connection string to web.config file on the root of the solution.

To change the connection string to use SQLExpress we can use the following string:

<add name="TestContext" connectionString="Server=.\SQLExpress;Database=Test;Integrated Security=SSPI;User Instance=true" providerName="System.Data.SqlClient" />


Courtesy : Lot of google searches and some personal tweaks

Now once the connection string is defined in web.config file, we need to build the solution before we proceed.

8. Once you have built the solution in the controller folder try to add Test controller with the following parameters

a. Template: Controller with read/write actions and views, using Entity Framework

b. Model class: Test(MvcTest.Models)

c. Data context class: TestContext(MvcTest.Models)

d. Views: Razor (CSHTML)

9. Sometimes you will get an error saying “Unable to create dbo.Test”, if this happens I suggest adding Tests.mdf database manually to App_Data folder, you can do that by right clicking on the App_Data folder Add=>New Item=> SQL Server Database as given below:

10. Click on the add button. Now you will see magic happen. VWD will automatically create Index.cshtml, Details.cshtml, Create.cshtml, Edit.cshtml, Delete.cshtml files for you in your view directory.

11. Now you are free to use and tweak the application to your liking. As of now with this much code only you already have working application. Screen Shots for Index page, Create page, Edit page and Delete page is given below. To get to the Index page run the application from VWD using Ctrl+F5. In the address bar of Internet Explorer append “Test” to the address. eg. http://localhost:xxxx/Test

a. Index

b. Create (Click on Create New Hyperlink to get to this page)

c. Changed Index page. Once the data is created you are redirected back to Index.

d. Click on Edit, Details and Delete for viewing respective pages.

Hope this post helps you to use SQLExpress with ASP.NET MVC :)

PS: I have just started learning ASP.NET MVC, so I might have made some mistakes (which doesn’t look likely because the application is working but still) please help me to correct them.

Disclaimer: I am not the creator of ASP.NET, MVC, Razor or Entity, all I am trying to do is put up a simple tutorial in place using SQL Express and ASP.NET MVC and all my learning is from the resources collected from the internet.