I absolutely love Asp.net MVC.
At my employer we use SQL Server databases and use Stored Procedures (SPROCS) for all database interaction. For safety measures we have denied all select, insert, update, and delete actions on the anonymous user on all tables if it does not come in through a stored procedure.
When I first started to learn MVC I found that Entity framework was limited at best when working with SPROCS. It allowed for selecting lists but not much for selecting a single record. I also found that entity sometimes dropped sprocs. Since I use a database first approach it is just easier for me to create my own classes and the methods of them, and not to use Entity Framework at all.
To that end let me create a class that will be for boardgames. We will have a list of the games and the information on a single game in this class.
First let's create the database table
CREATE TABLE [dbo].[Games](
[GameId] [nvarchar](128) NOT NULL,
[GameName] [nvarchar](200) NOT NULL,
[RecommendedAges] [varchar](25) NOT NULL,
[Synopsis] [nvarchar](max) NULL,
[Link] [varchar](100) NULL,
[CheckedOut] [bit] NOT NULL,
[MinPlayers] [int] NOT NULL,
[MaxPlayers] [int] NOT NULL,
[Playtime] [nvarchar](20) NULL,
[MinPlaytime] [int] NULL,
PRIMARY KEY CLUSTERED
(
[GameId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Now let's create the SPROCS that are used
CREATE PROCEDURE [dbo].[GetGameInfo]
@GameId nvarchar(128)
AS
SET NOCOUNT ON
SELECT
GameName,
RecommendedAges,
Synopsis,
Link,
CheckedOut,
MinPlayers,
MaxPlayers,
Playtime,
MinPlaytime
FROM
dbo.Games
WHERE
GameId = @GameId
GO
CREATE PROCEDURE [dbo].[ListGames]
AS
SET NOCOUNT ON
SELECT
GameId,
GameName,
RecommendedAges,
Link,
CheckedOut,
MinPlayers,
MaxPlayers,
Playtime,
MinPlaytime,
Synopsis
FROM
dbo.Games
ORDER BY
GameName
GO
This is the C# class (Model) with the two methods
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
namespace MyLibrary.Models
{
public class Game
{
public string GameId { get; set; }
[Required, Display(Name = "Game Name"), StringLength(200)]
public string GameName { get; set; }
[Required, Display(Name = "Recommended Age"), StringLength(25)]
public string RecommendedAges { get; set; }
[Required]
public string Synopsis { get; set; }
[StringLength(100)]
public string Link { get; set; }
[Required, Display(Name = "Time to Play"), StringLength(20)]
public string Playtime { get; set; }
public bool CheckedOut { get; set; }
[Required, Display(Name = "Minimum Players")]
public int MinPlayers { get; set; }
[Required, Display(Name = "Maximum Players")]
public int MaxPlayers { get; set; }
[Required, Display(Name = "Minimum time to play")]
public int MinPlaytime { get; set; }
public List<Game> ListGames()
{
using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (SqlCommand oCmd = new SqlCommand())
{
try
{
oCmd.CommandText = "ListGames";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Connection = oConn;
oConn.Open();
using (SqlDataReader reader = oCmd.ExecuteReader())
{
List<Game> myList = new List<Game>();
while (reader.Read())
{
Game game = new Game
{
GameId = reader[0].ToString(),
GameName = reader[1].ToString(),
RecommendedAges = reader[2].ToString(),
Link = reader[3].ToString(),
CheckedOut = (bool)reader[4],
MinPlayers = (int)reader[5],
MaxPlayers = (int)reader[6],
Playtime = reader[7].ToString(),
MinPlaytime = (int)reader[8],
Synopsis = reader[9].ToString()
};
myList.Add(game);
}
return myList;
}
}
catch (Exception ex)
{
Errors.ErrorOccured(ex);
return null;
}
finally
{
oCmd.Dispose();
oConn.Close();
}
}
}
}
public Game GetGame(string GameId)
{
using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
using (SqlCommand oCmd = new SqlCommand())
{
try
{
oCmd.CommandText = "GetGameInfo";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
oCmd.Connection = oConn;
oConn.Open();
using (SqlDataReader reader = oCmd.ExecuteReader())
{
Game game = new Game();
while (reader.Read())
{
game.GameId = GameId;
game.GameName = reader[0].ToString();
game.RecommendedAges = reader[1].ToString();
game.Synopsis = reader[2].ToString();
game.Link = reader[3].ToString();
game.CheckedOut = (bool)reader[4];
game.MinPlayers = (int)reader[5];
game.MaxPlayers = (int)reader[6];
game.Playtime = reader[7].ToString();
game.MinPlaytime = (int)reader[8];
}
return game;
}
}
catch (Exception ex)
{
Errors.ErrorOccured(ex,"GameId = " + GameId);
return null;
}
finally
{
oCmd.Dispose();
oConn.Close();
}
}
}
}
}
}
And now for the Controller
using System;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using MyLibrary.Models;
namespace MyLibrary.Controllers
{
public ActionResult Index()
{
try
{
Game game = new Game();
var model = game.ListGames();
return View(model);
}
catch(Exception ex)
{
Errors.ErrorOccured(ex);
}
return RedirectToAction("Index","Error");
}
public ActionResult Details(string id)
{
if(id == null)
{
return RedirectToAction("MissingInfo","Error");
}
try
{
Game game = new Game();
var model = game.GetGame(id);
if(model == null)
{
return HttpNotFound();
}
return View(model);
}
catch(Exception ex)
{
Errors.ErrorOccured(ex);
}
return RedirectToAction("Index","Error");
}
}
And now the views Index
@model IEnumerable<MyLibrary.Models.Game>
@{
ViewBag.Title = "Games";
}
@section styles{
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs/dt-1.10.18/datatables.min.css"/>
}
<h2>Games</h2>
<p>
@Html.ActionLink("Create New", "Create") | @Html.ActionLink("Checked Out Games", "GamesOut")
</p>
<table id="myTable" class="table display">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.GameName)
</th>
<th>
Ages
</th>
<th>
Out
</th>
<th>
Min Players
</th>
<th>
Max Players
</th>
<th>
@Html.DisplayNameFor(model => model.Playtime)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.GameName)
</td>
<td>
@Html.DisplayFor(modelItem => item.RecommendedAges)
</td>
<td>
@if(item.CheckedOut)
{ <span>True</span> }
else
{ <span></span> }
</td>
<td>
@Html.DisplayFor(modelItem => item.MinPlayers)
</td>
<td>
@Html.DisplayFor(modelItem => item.MaxPlayers)
</td>
<td>
@Html.DisplayFor(modelItem => item.Playtime)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.GameId }) |
@Html.ActionLink("Details", "Details", new { id=item.GameId }) |
@if (!item.CheckedOut)
{
@Html.ActionLink("Check Out", "Checkout", new { id = item.GameId }, null)
}
</td>
</tr>
}
</tbody>
</table>
@section scripts{
<script type="text/javascript" src="https://cdn.datatables.net/v/bs/dt-1.10.18/datatables.min.js"></script>
<script>
$(document).ready(function () {
$('#myTable').DataTable();
});
</script>
}
and now the details view
@model MyLibrary.Models.Game
@{
ViewBag.Title = "Game Details";
}
<h2>@ViewBag.Title</h2>
<div>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.GameName)
</dt>
<dd>
@Html.DisplayFor(model => model.GameName)
</dd>
<dt>
@Html.DisplayNameFor(model => model.RecommendedAges)
</dt>
<dd>
@Html.DisplayFor(model => model.RecommendedAges)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Synopsis)
</dt>
<dd>
@Html.DisplayFor(model => model.Synopsis)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Link)
</dt>
<dd>
@Html.DisplayFor(model => model.Link)
</dd>
<dt>
@Html.DisplayNameFor(model => model.CheckedOut)
</dt>
<dd>
@Html.DisplayFor(model => model.CheckedOut)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MinPlayers)
</dt>
<dd>
@Html.DisplayFor(model => model.MinPlayers)
</dd>
<dt>
@Html.DisplayNameFor(model => model.MaxPlayers)
</dt>
<dd>
@Html.DisplayFor(model => model.MaxPlayers)
</dd>
<dt>
@Html.DisplayNameFor(model => model.Playtime)
</dt>
<dd>
@Html.DisplayFor(model => model.Playtime)
</dd>
</dl>
</div>
You probably noticed that I have an error handling method in there. What this method does is email me anytime an error is thrown. I will go over that in another post. I will also cover what to do if for some reason there is a null value in one or more of the fields in your table.
Hopefully, you find this helpful when you want to use stored procedures to interact with your database in your asp.net MVC project.