After the first video for MVC here, I have decided to make a next part, in which I save the entries to a database and display them:
Sounds like a tiny update. However, it is not that tiny. 🙂 I have added 2 more projects to the solution:
- PartyDB
- PartyLibrary
The idea of those was to facilitate the database connection and display. The PartyDB consists of one sql file, however, it is added to the server through VisualStudio and its publishing feature, thus it is quite valuable:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE [dbo].[PartyPeople] ( [Id] INT NOT NULL IDENTITY(1, 1), [NickName] NVARCHAR(50) NULL, [FancyMail] NVARCHAR(50) NULL, [FavouriteAnimal] NVARCHAR(50) NULL, [AnimalName] NVARCHAR(50) NULL, CONSTRAINT [PK_PartyPeople] PRIMARY KEY ([Id]) ) |
The PartyLibrary is a bit more tricky. It has 3 classes:
- DataAccess.cs – pretty straight forward, returning saving the data with Dapper and providing access to the connection string
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public class DataAccess { public static string GetConnectionString() { return @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=PartyDB2;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; } public static int SaveData(string sql, T data) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { return conn.Execute(sql, data); } } } |
- GuestModel.cs – used for mapping the guestModel to the database. It is a bit different from the GuestResponse.cs class, because the GuestResponse.cs takes the response data from the web site and the GuestModel takes this data and writes it to the Database. E.g., the GuestModel.cs has public int Id { get; set; } and public string AnimalName { get; set; } , which are generated from the code and not from the data entry:
|
1 2 3 4 5 6 7 8 |
public class GuestModel { public int Id { get; set; } public string NickName { get; set; } public string FancyMail { get; set; } public string FavouriteAnimal { get; set; } public string AnimalName { get; set; } } |
- GuestProcessor.cs – it takes the data from the data entry and adds it to the database. Posses one function only:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public class GuestProcessor { public static int CreateGuest(string nickName, string fancyMail, string favouriteAnimal, bool? willAttend) { GuestModel data = new GuestModel { NickName = nickName, FancyMail = fancyMail, FavouriteAnimal = favouriteAnimal, AnimalName = nickName[0] + " the wild " + favouriteAnimal }; string sql = @"INSERT INTO dbo.PartyPeople (NickName, FancyMail, FavouriteAnimal, AnimalName) VALUES (@NickName, @FancyMail, @FavouriteAnimal, @AnimalName);"; return DataAccess.SaveData(sql, data); } } |
To the controllers class, a new controller is added. It takes the data from the database and displays it in a view:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
public ViewResult ListAllResponses() { string connectionString = DataAccess.GetConnectionString(); List guestList = new List(); using(SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); string sql = "SELECT NickName, FancyMail, AnimalName FROM PartyPeople"; SqlCommand cmd = new SqlCommand(sql, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { GuestModel guest = new GuestModel(); guest.NickName = reader["NickName"].ToString(); guest.FancyMail = reader["FancyMail"].ToString(); guest.AnimalName = reader["AnimalName"].ToString(); guestList.Add(guest); } } return View(guestList); } |
The view for the database, using the guestList looks like this:
|
1 2 3 4 5 6 |
@model List @{ Layout = null; } <!--<span class="hiddenSpellError" pre="" data-mce-bogus="1"-->DOCTYPE html> |
List of party people:
@foreach (PartyLibrary2.GuestModel g in Model)
{
}
| NickName | FancyMail | FavouriteAnimal |
|---|---|---|
| @g.NickName | @g.FancyMail | @g.AnimalName |
|
1 |
Pretty much this is how it looks on a video:
The code is available in GitHub.
Cheers!