Miranda's Musings

computer articles, politics, and more

Showing multiple models on one page

There are times when you will need to show multiple models on a single MVC view. In this post I will show how to accomplish that.   

I will be using my Library solution for this tutorial.  In this case I will be using the Models related to Movies. 

Those models are Movie, Actor, Genre, and Format.  These are all tables in the database as well.  However, there are also some foreign key tables to handle the one to many relationships that are possible with Movie to Actor, Movie to Genre, Movie to Format and.  

The view will be displaying information on the selected movie from my database, including the different actors, the formats I have the movie in, and the different genres the movie fits in. 

example

Title
A Million Ways To Die In The West
Rating
NR
Stars
4.00
Actors
Seth  MacFarlane
Neil Patrick  Harris
Liam  Neeson
Sarah  Silverman
Charlize  Theron
Formats
Digital
Genres
Comedy
Western

 

The tables are set up as such 

USE [Library]
GO
/****** Object:  Table [dbo].[ActorMovies]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ActorMovies](
	[MovieID] [int] NOT NULL,
	[ActorID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Actors]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Actors](
	[ActorID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](30) NOT NULL,
	[LastName] [nvarchar](40) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ActorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Formats]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Formats](
	[FormatID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Book] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[FormatID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Genres]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Genres](
	[Genre] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Genre] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MovieFormat]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MovieFormat](
	[MovieID] [int] NOT NULL,
	[FormatID] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[MovieGenres]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MovieGenres](
	[MovieID] [int] NOT NULL,
	[Genre] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Movies]    Script Date: 11/21/2023 3:51:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Movies](
	[MovieID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](400) NOT NULL,
	[Rating] [nvarchar](5) NULL,
	[Stars] [decimal](18, 0) NULL,
PRIMARY KEY CLUSTERED 
(
	[MovieID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ActorMovies]  WITH CHECK ADD  CONSTRAINT [FK_ActorMovie] FOREIGN KEY([MovieID])
REFERENCES [dbo].[Movies] ([MovieID])
GO
ALTER TABLE [dbo].[ActorMovies] CHECK CONSTRAINT [FK_ActorMovie]
GO
ALTER TABLE [dbo].[ActorMovies]  WITH CHECK ADD  CONSTRAINT [FK_MovieActor] FOREIGN KEY([ActorID])
REFERENCES [dbo].[Actors] ([ActorID])
GO
ALTER TABLE [dbo].[ActorMovies] CHECK CONSTRAINT [FK_MovieActor]
GO
ALTER TABLE [dbo].[MovieFormat]  WITH CHECK ADD  CONSTRAINT [FK_FormatMovie] FOREIGN KEY([FormatID])
REFERENCES [dbo].[Formats] ([FormatID])
GO
ALTER TABLE [dbo].[MovieFormat] CHECK CONSTRAINT [FK_FormatMovie]
GO
ALTER TABLE [dbo].[MovieFormat]  WITH CHECK ADD  CONSTRAINT [FK_MovieFormat] FOREIGN KEY([MovieID])
REFERENCES [dbo].[Movies] ([MovieID])
GO
ALTER TABLE [dbo].[MovieFormat] CHECK CONSTRAINT [FK_MovieFormat]
GO
ALTER TABLE [dbo].[MovieGenres]  WITH CHECK ADD  CONSTRAINT [FK_GenreMovie] FOREIGN KEY([MovieID])
REFERENCES [dbo].[Movies] ([MovieID])
GO
ALTER TABLE [dbo].[MovieGenres] CHECK CONSTRAINT [FK_GenreMovie]
GO
ALTER TABLE [dbo].[MovieGenres]  WITH CHECK ADD  CONSTRAINT [FK_MovieGenre] FOREIGN KEY([Genre])
REFERENCES [dbo].[Genres] ([Genre])
GO
ALTER TABLE [dbo].[MovieGenres] CHECK CONSTRAINT [FK_MovieGenre]
GO

and the Models are set up as such

public class Actor
{ 
    public int ActorId { get; set; }

    [Required, Display(Name ="First Name"), MaxLength(30)]
    public string FirstName { get; set; }

    [Required, Display(Name ="Last Name"), MaxLength(40)]
    public string LastName { get; set; }

    public string Name { get; set; }
}

public class Format
{
    public int FormatID { get; set; }

   	[Required, MaxLength(50)]
    public string Name { get; set; }

   	[Required]
    public bool Book { get; set; }
}

public class Genre
{
    [Display(Name ="Genre")]
    public int GenreId { get; set; }

    [Required, MaxLength(100)]
    public string Name { get; set; }
}

public class Movie
{
    public int MovieId { get; set; }
	
    [Required,MaxLength(400)]
    public string Title { get; set; }

    [MaxLength(5)]
    public string Rating { get; set; }

    public decimal? Stars { get; set; }

    public int FormatId { get; set; }

    [Display(Name ="Format")]
    public string FormatName { get; set; }
}

 

The first thing we have to do is add some code to the movie model.  We need to let the model know that Actor, Genre and Format are collections.   This will allow for us to list the information for these on our view.

so we add this to the Movie model

 

        public virtual ICollection<Actor> Actor { get; set; }
        public virtual ICollection<Genre> Genre { get; set; }
        public virtual ICollection<Format> Format { get; set; }

        public Movie()
        {
            Actor = new HashSet<Actor>();
            Genre = new HashSet<Genre>();
            Format = new HashSet<Format>();
        }

 Now the class has the collections for the information so all we need to do is add the method to fill in the list.   

        public Movie GetMovie(int MovieId)
        {
            List<Actor> actorList = null;
            List<Format> formatList = null;
            List<Genre> genreList = null;
            using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                using (SqlCommand oCmd = new SqlCommand())
                {
                    try
                    {
                        oCmd.CommandText = "GetMovie";
                        oCmd.CommandType = CommandType.StoredProcedure;
                        oCmd.Parameters.Add("@MovieId",SqlDbType.Int).Value = MovieId;
                        oCmd.Connection = oConn;
                        oConn.Open();
                        using (SqlDataReader reader = oCmd.ExecuteReader())
                        {

                            Movie movie = new Movie();
                            while (reader.Read())
                            {
                                movie.MovieId = MovieId;
                                movie.Title = reader[0].ToString();
                                movie.Rating = reader[1].ToString();
                                movie.Stars = (decimal)reader[2];
                            }   
                            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                            {
                                using (SqlCommand cmd = new SqlCommand())
                                {
                                    try
                                    {
                                        cmd.CommandText = "ListFormatsByMovie";
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.Add("@MovieId", SqlDbType.Int).Value = MovieId;
                                        cmd.Connection = conn;
                                        conn.Open();
                                        using (SqlDataReader rdr = cmd.ExecuteReader())
                                        {
                                            formatList = new List<Format>();
                                            while (rdr.Read())
                                            {
                                                Format format = new Format
                                                {
                                                    Name = rdr[0].ToString()
                                                };
                                                formatList.Add(format);
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        Errors.ErrorOccured(ex, "MovieId = " + MovieId);
                                        return null;
                                    }
                                    finally
                                    {
                                        conn.Close();
                                        cmd.Dispose();
                                    }
                                }
                            }
                            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                            {
                                using (SqlCommand cmd = new SqlCommand())
                                {
                                    try
                                    {
                                        cmd.CommandText = "ListActorsByMovie";
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.Add("@MovieId", SqlDbType.Int).Value = MovieId;
                                        cmd.Connection = conn;
                                        conn.Open();
                                        using (SqlDataReader rdr = cmd.ExecuteReader())
                                        {
                                            actorList = new List<Actor>();
                                            while (rdr.Read())
                                            {
                                                Actor actor = new Actor
                                                {
                                                    FirstName = rdr[0].ToString(),
                                                    LastName = rdr[1].ToString()
                                                };
                                                actorList.Add(actor);
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        Errors.ErrorOccured(ex, "MovieId = " + MovieId);
                                        return null;
                                    }
                                    finally
                                    {
                                        conn.Close();
                                        cmd.Dispose();
                                    }
                                }
                            }
                            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                            {
                                using (SqlCommand cmd = new SqlCommand())
                                {
                                    try
                                    {
                                        cmd.CommandText = "ListGenresByMovie";
                                        cmd.CommandType = CommandType.StoredProcedure;
                                        cmd.Parameters.Add("@MovieId", SqlDbType.Int).Value = MovieId;
                                        cmd.Connection = conn;
                                        conn.Open();
                                        using (SqlDataReader rdr = cmd.ExecuteReader())
                                        {
                                            genreList = new List<Genre>();
                                            while (rdr.Read())
                                            {
                                                Genre genre = new Genre
                                                {
                                                    Name = rdr[0].ToString()
                                                };
                                                genreList.Add(genre);
                                            }
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        Errors.ErrorOccured(ex, "MovieId = " + MovieId);
                                        return null;
                                    }
                                    finally
                                    {
                                        conn.Close();
                                        cmd.Dispose();
                                    }
                                }
                            }
                            movie.Format = formatList;
                            movie.Actor = actorList;
                            movie.Genre = genreList;
                            return movie;
                        }                  
                    }
                    catch (Exception ex)
                    {
                        Errors.ErrorOccured(ex);
                        return null;
                    }
                    finally
                    {
                        oConn.Close();
                        oCmd.Dispose();
                    }
                }
            }
        }

Then it is just call the method from the controller

        public ActionResult Details(int?id)
        {
            try
            {
                if(id == null)
                {
                    return RedirectToAction("Index");
                }
                Movie movie = new Movie();
                var model = movie.GetMovie((int)id);
                return View(model);
            }
            catch (Exception ex)
            {
                Errors.ErrorOccured(ex);
            }
            return RedirectToAction("InternalServerError", "Error");
        }

And display in the view

@model Library.Models.Movie

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>Movie</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Title)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Title)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Rating)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Rating)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Stars)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Stars)
        </dd>

        <dt>
            Actors
        </dt>

        <dd>
            @foreach (var actor in Model.Actor)
	    {
                @actor.FirstName <span> </span>@actor.LastName <br />
	    }
        </dd>

        <dt>
            Formats
        </dt>

        <dd>
            @foreach (var frmt in Model.Format)
	    {
                @frmt.Name <br />
	     }
        </dd>

        <dt>
            Genres
        </dt>

        <dd>
            @foreach (var genre in Model.Genre)
	    {
                @genre.Name <br />
	    }
        </dd>

    </dl>
</div>

 

 OK  now you can see how to display multiple models in a single view.   I hope this helps.

Handling Null values passed from your stored procedure to your model

Sometimes there is a null value in a column for a database table.   When this is not a string it will cause a null exception error in the model if you do not account for that null.   Even when the data type is nullable. 

The cure for that is to alter the assigment to the variable.   like so

GenreId = (int?)(rdr.IsDBNull(0) ? null : rdr[0])

The above code says if the very first field returned is null, then to pass the value null into the assignment, otherwise to assign the value being returned.

Here is an example where I  display two more optional items  for the game when viewing the information of the game.  The first is all Genres the game may belong to.  The next one is photos that are listed for the game. Both of these are optional items meaning they may not exist so we need to account for that possibility in our method.  

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 Game()
        {
            GamePhoto = new HashSet<GamePhoto>();
            GameGenre = new HashSet<Genre>();
        }

        public string Email { get; set; }
        public DateTime DateChecked { get; set; }

        public virtual ICollection<GamePhoto> GamePhoto { get; set; }
        public virtual ICollection<Genre> GameGenre { get; set; }

		public Game GetGame(string GameId)
		{
			List<GamePhoto> myList = null;
			List<Genre> gList = null;
			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];
							}

							using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
							{
								using (SqlCommand Cmd = new SqlCommand())
								{
									try
									{
										Cmd.CommandText = "ListGamePhotos";
										Cmd.CommandType = CommandType.StoredProcedure;
										Cmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
										Cmd.Connection = Conn;
										Conn.Open();
										using (SqlDataReader rdr = Cmd.ExecuteReader())
										{
											myList = new List<GamePhoto>();
											while (rdr.Read())
											{
												GamePhoto photo = new GamePhoto
												{
													Id = (int?)(rdr.IsDBNull(0) ? null : rdr[0]),
													PhotoName = rdr[1].ToString()
												};
												myList.Add(photo);
											}
										}
									}
									catch (Exception ex)
									{
										Errors.ErrorOccured(ex, "Inner Query - GameId= " + GameId.ToString());
										return null;
									}
									finally
									{
										Conn.Close();
										Cmd.Dispose();
									}
								}
							}
							using (SqlConnection aConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
							{
								using (SqlCommand aCmd = new SqlCommand())
								{
									try
									{
										aCmd.CommandText = "ListGameGenres";
										aCmd.CommandType = CommandType.StoredProcedure;
										aCmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
										aCmd.Connection = aConn;
										aConn.Open();
										using (SqlDataReader rdr = aCmd.ExecuteReader())
										{
											gList = new List<Genre>();
											while (rdr.Read())
											{
												Genre g = new Genre
												{
													GenreId = (int?)(rdr.IsDBNull(0) ? null : rdr[0]),
													GenreName = rdr[1].ToString()
												};
												gList.Add(g);
											}
										}
									}
									catch (Exception ex)
									{
										Errors.ErrorOccured(ex, "Inner Query - GameId= " + GameId.ToString());
										return null;
									}
									finally
									{
										aConn.Close();
										aCmd.Dispose();
									}
								}
							}
							game.GamePhoto = myList;
							game.GameGenre = gList;
							return game;
						}
					}
					catch (Exception ex)
					{
						Errors.ErrorOccured(ex,"GameId = " + GameId);
						return null;
					}
					finally
					{
						oCmd.Dispose();
						oConn.Close();
					}
				}
			}
		}
	}
}

To wrap it up, when your class allows for a null value, then you need to account for that in your asignment of values coming in from your SQLDataReader.   

Error Notification system

As i mentioned in my previous post, I have a solution for sending me an email whenever an error occurs in the model or on the controller.   This way if something is omitted or invalid data is entered I am notified.  To get this to work I created the following code

The Model will have one method with an overload.  I use the overload to pass the parameters in the sprocs so that way I know if it is a SQL server error what caused it.

using System;
using System.Diagnostics;
using System.Net.Mail;
using System.Web;

namespace MyLibrary.Models
{
    public class Errors
    {
        public static void ErrorOccured(Exception ex)
        {
            var st = new StackTrace(ex, true);
            // Get the top stack frame
            var frame = st.GetFrame(0);
            // Get the line number from the stack frame
            var line = frame.GetFileLineNumber();
            string page = frame.GetFileName();
            var col = frame.GetFileColumnNumber();

            string domain = HttpContext.Current.Request.Url.Host;
            string MailBody = ex.Message.ToString() + "\r\n<br />File: " + page + "\r\n<br />Line number: " + line.ToString() + " column " + col.ToString() + "\r\n<br />";
            string MessageToSend = ex.StackTrace.ToString();
            string MailSender = "the email address used to send the notification";
            string MailRecipient = "The email address you want to send the notification to";
            string MailSubject = "Error on " + domain;
            MailMessage msg = new MailMessage();

            msg.ReplyToList.Add(MailSender);
            msg.To.Add(MailRecipient);
            msg.Subject = MailSubject;
            msg.Body = MailBody + "\r\n<br /><br />" + MessageToSend;
            msg.BodyEncoding = System.Text.UTF8Encoding.UTF8;
            msg.SubjectEncoding = System.Text.Encoding.Default;
            msg.IsBodyHtml = true;
            using (var smtp = new SmtpClient())
            {
                smtp.Send(msg);
            }
        }

        public static void ErrorOccured(Exception ex, string parameters)
        {
            var st = new StackTrace(ex, true);
            // Get the top stack frame
            var frame = st.GetFrame(0);
            // Get the line number from the stack frame
            var line = frame.GetFileLineNumber();
            string page = frame.GetFileName();
            var col = frame.GetFileColumnNumber();

            string domain = HttpContext.Current.Request.Url.Host;
            string MailBody = ex.Message.ToString() + "\r\n<br />File: " + page + "\r\n<br />Line number: " + line.ToString() + " column " + col.ToString() + "\r\n<br />" + parameters + "\r\n<br />";

            string MessageToSend = ex.StackTrace.ToString();
            string MailSender = "the email address used to send the notification";
            string MailRecipient = "The email address you want to send the notification to";
            string MailSubject = "Error on " + domain;
            MailMessage msg = new MailMessage();

            msg.ReplyToList.Add(MailSender);
            msg.To.Add(MailRecipient);
            msg.Subject = MailSubject;
            msg.Body = MailBody + "\r\n<br /><br />" + MessageToSend;
            msg.BodyEncoding = System.Text.UTF8Encoding.UTF8;
            msg.SubjectEncoding = System.Text.Encoding.Default;
            msg.IsBodyHtml = true;
            using (var smtp = new SmtpClient())
            {
                smtp.Send(msg);
            }
        }
    }
}

The above assumes that you have the mailSettings set up in your Web.config file as in below.

<system.net>
	<mailSettings>
		<smtp from="your email address here">
			<network host="your smtp mail host" 
				port="the port you will use  generally 25 or 587" 
				userName="the username typically the email address" 
				password="the password for that email address." 
				enableSsl="true or false, depending on if you use ssl for email" />
		</smtp>
    </mailSettings>
</system.net>

 

Next is the ErrorController 

using System;
using System.Web.Mvc;

namespace MyLibrary.Controllers
{
    public class ErrorController : Controller
    {
         
        public ActionResult Index()
        {
            ViewBag.ErrorType = TempData["ErrorType"];
            ViewBag.Error = TempData["Error"];
            return View();
        }

        public ActionResult BadRequest()
        {
            return View();
        }

        public ActionResult NotAllowed()
        {
            return View();
        }

        public ActionResult PageNotFound(string message, string aspxerrorpath)
        {
            ViewBag.Errorpath = aspxerrorpath;
            ViewBag.Message = message;
            return View();
        }
    }
}

 

Then there is a View called Index which is a custom error page.  It can have anything you want on there.  I give a message telling the user that the programmers have been notified of the error, and also include a graphic of some sort.

Now to use this all you need to do is include a try/catch block of code and in the catch pass the information to the ErrorCcontroller.  On the controller that is done like so

public ActionResult Index(Games games)   
{        
           try
           {
               //some code here
               return View();
            }
            catch (Exception ex)
            {
                Errors.ErrorOccured(ex);
            }
            return RedirectToAction("Index", "Error", null);
}

And in a model the code looks something like this.  As you can see I pass the value of each parameter along with the name of that parameter

public string Add(string GameId, string GameName, string RecommendedAges, string Synopsis, string Link, int MinPlayers, int MaxPlayers, string Playtime, int MinPlaytime)
{
	using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
	{
		using (SqlCommand oCmd = new SqlCommand())
		{
			try
			{
				oCmd.CommandText = "AddGame";
				oCmd.CommandType = CommandType.StoredProcedure;
				oCmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
				oCmd.Parameters.Add("@GameName", SqlDbType.NVarChar).Value = GameName;
				oCmd.Parameters.Add("@RecommendedAges", SqlDbType.VarChar).Value = RecommendedAges;
				oCmd.Parameters.Add("@Synopsis", SqlDbType.NVarChar).Value = Synopsis;
				oCmd.Parameters.Add("@Link", SqlDbType.VarChar).Value = Link;
				oCmd.Parameters.Add("@MinPlayers", SqlDbType.Int).Value = MinPlayers;
				oCmd.Parameters.Add("@MaxPlayers", SqlDbType.Int).Value = MaxPlayers;
				oCmd.Parameters.Add("@Playtime", SqlDbType.NVarChar).Value = Playtime;
				oCmd.Parameters.Add("@MinPlaytime", SqlDbType.Int).Value = MinPlaytime;
				oCmd.Connection = oConn;
				oConn.Open();
				string gid = oCmd.ExecuteScalar().ToString();
				return gid;
			}
			catch (Exception ex)
			{
				Errors.ErrorOccured(ex, "GameId = " + GameId + " GameName = " + GameName + " RecommendedAges = " + RecommendedAges +
					" Synopsis = " + Synopsis + " Link = " + Link + " MinPlayers = " + MinPlayers + " MaxPlayers = " + MaxPlayers +
					" Playtime = " + Playtime + " MinPlaytime = " + MinPlaytime);
				return null;
			}
			finally
			{
				oCmd.Dispose();
				oConn.Close();
			}
		}
	}
}
 

This may not be the most elegant solution, but it works for me.  I am notified of all errors that occur in the Controller or in the Model.   The one caveat is if it is an error that occurs on the View, I am not notified.   I had a method that also sent me an email for errors from the view but that used the Global.asax file and would also send me 404 errors.   The reason I don't like that is a lot of bots hit our sites and some of those are looking for exploits and they can hit a page many times in the course of a second before moving on and I am then sent an email for each attempt.  

 



 

 

 

Using Stored Procedures in Asp.Net MVC

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.