Let's say we want to display data in a table like this:
id | username |
1 | Jeremy |
2 | Natalie |
3 | Winston |
One way to accomplish this is just to build an HTML table:
<table>
<tr>
<td>ID: </td>
<td>Username</td>
</tr>
<tr>
<td> 1 </td>
<td> Jeremy </td>
</tr>
<tr>
<td> 2 </td>
<td> Natalie </td>
</tr>
<tr>
<td> 3 </td>
<td> Winston </td>
</tr>
</table>
A better way to accomplish this is to get the data from a SQL database. Explaining the many nuiances of SQL are outside the scope of this tutorial. This tutorial assumes some familiarity with SQL.
Install EntityFramework
Connection String
<add name="SQLdatabasename"
connectionString="Data Source=domain\server; database=databasename; User ID=username; password=password"
providerName="System.Data.SqlClient"
/>
Here is an example of a real connection string:
<add name="SQL_freekb_db"
connectionString="Data Source=freekb\Eng1; database=freekb; User ID=bthomas; password=bn56Hjn4!s"
providerName="System.Data.SqlClient"
/>
To verify the connection is successful:
Next we will create the Model View Controller (MVC).
Model
Note: This class can contains a single database. However, the database may have numerous tables. It is good to name the class the same as the database name.
using System.Data.Entity;
namespace example.Models
{
public class table1
{
public int id { get; set; }
public string column1{ get; set; }
}
public class table2
{
public int id { get; set; }
public string column1 { get; set; }
}
public class db1_connect : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
modelBuilder.Entity<table1>().ToTable("table1");
modelBuilder.Entity<table2>().ToTable("table2");
}
public DbSet<table1> App { get; set; }
public DbSet<table2> Account { get; set; }
}
}
Controller
Both the Controller and View are automatically built, and the application can be published. Because we named the model "Users" in this example, we can view the records in the SQL table by going to http://www.example.com/Users.
Following is a section of the AddController.cs file.
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using example.com.Models;
namespace example.com.Controllers
{
public class AppController : Controller
{
private example_db db = new example_db();
// GET: App
public ActionResult Index()
{
return View(db.Example.ToList());
}
. . .
View
Following is the minimum markup needed in the View.
@model IEnumerable<example.com.Models.Example>
@foreach (var item in Model)
{
@Html.DisplayFor(modelItem => item.column1)
}
This minimum markup would produce the following: