Tags

, , , , ,

After trying numerous suggestions posted on Stack Overflow without luck, I eventually came across a much easier way of getting an ASP.NET application to display, modify and remove SQL database records, and very little actual coding was involved. The following builds on the project created in my previous post, but it can be done with a blank MVC template also.

Create a Database
Because I totally screwed my local installation of SQL Server, the database in this example is instead hosted on Azure. The important thing is we have a working SQL database that an application can connect to, and a note is made of the server address, user name and password.

For the next stage we need the Microsoft SQL Server Management Studio, to set up the database table. An initial connection attempt to an Azure-hosted SQL Server results in the following error message:

MVC-SQL-Manager-Connect-Error

This is normal, and is resolved by adding a firewall entry for the local machine (Client IP address) in the Azure portal.

MVC-Azure-Firewall

In the database, a table is created (in this case using SQL Server Management Studio) with three columns: userID, userTitle and userData. For Visual Studio to build an Entity Framework model from this, one of the columns must be set as the primary key. Here it’s userID, and in the Column Properties I set the Identity Specification to ‘Yes’.

MVC-Database-Table

Creating the Application
As I’ve pointed out, this could be done either with an existing MVC project, or with a Visual Studio template. Normally when connecting an application to a database, we import a driver, add a connection string then pass queries within our code. It works a little differently with MVC.
The MVC project requires a ‘Model’, which is added by right-clicking on the Model folder and ading an ADO.NET Entity Data Model. In the Entity Data Model Wizard, I chose to use ‘EF Designer from database’. The details entered into the Connection Properties will determine the connection string for the project. Here it doesn’t really matter whether sensitive information is included in the connection string.

MVC-Connection-Properties

With a bit of luck Visual Studio will fetch the information required to build an Entity Framework model for the database.

MVC-EntityFramework-Diagram

Now the project has a connection string and EF model, it needs to be rebuilt so the model can be referenced by the source files to be added.

Controller and Views
The application requires a Controller to fetch records from the database, display them in whichever CSHTML page and perform whatever actions when the user interacts with the application.
Right-click the Controllers directory, and select the option to add a Controller. It’s possible to code this from scratch, but here I’ve added ‘MVC 5 Controller with views, using Entity Framework’.

MVC-Add-EF-Controller

The important options in the next window are for ‘Model Class’ and ‘Generate views’. From the Model Class drop-down, select the database table/model. With the ‘Generate views’ option ticked, Visual Studio will set up the new MVC page to enable users to interact with the database.

MVC-Add-Controller-Options

At this point there should be a sub-directory and a set of CSHTML files within ‘Views’ for the model. Right-click on its index.cshtml entry, and select ‘View in Browser’. The result should be a working application that can query, modify and delete records are stored in the Azure database.

MVC-Application-with-DB

And finally add the following line to _Layout.cshtml to add the table page to the navigation menu:
@Html.ActionLink("Database","../exampleTables/index", "Home")

Advertisements