This is the fourth part in my Building a photoblog with Nancy and Simple.Data series:
- Setting up the project
- Defining the routes
- Rendering some views
- Adding the database
- Updating Simple.Data
- Adding comments
- The archives
In the previous post we made a few adjustments to the routes and modules we created in the second post. We added our first view and instructed Nancy to return it when calling a certain route. We also enabled the usage of CSS and Javascript files. We added a Views and Content directory. In this post we will add a SQL Server Compact 4.0 database to the project, setup Simple.Data and make our view a bit more dynamic.
Enabling SQL Server Compact 4.0
Maybe you don’t have the required components to use SQL Server Compact 4.0 databases. To verify this, download the Microsoft Web Platform Installer and run it. Select Products in the top part, select Database in the left and check the list on the right for Microsoft SQL Server Compact 4.0 and Microsoft SQL Server Compact 4.0 Tools. If they are marked as Installed, you’re good to go. If not, click Add next to both of them and finally click Install in the bottom of the application.
Adding the database
Right click your project in the Solution Explorer, choose Add, Add ASP.NET Folder and App_Data. This adds the App_Data directory to our project. Right click the App_Data directory and choose Add and New Item…. In the Add New Item dialog, select SQL Server Compact 4.0 Local Database and enter MyPhotoBlog.sdf as the name.
Creating the tables
Doubleclick the MyPhotoBlog.sdf database in the Solution Explorer. This opens up the Server Explorer you see on the right here. Let’s add a table for our photo’s. To do that, right click the Tables node and choose Create Table. That will open a new window where we can choose a name for the table and add the desired columns.
Enter Photos for the table name and add these columns:
- Id (int, Primary Key, Identity)
- Name (nvarchar(100))
- Slug (nvarchar(100))
- Filename (nvarchar(100))
- Camera (nvarchar(100))
- Lens (nvarchar(100))
- Aperture (nvarchar(100))
- Exposure (nvarchar(100))
- ISO (nvarchar(100))
- DatePosted (datetime)
- Published (bit)
- DatePublished (datetime)
Click OK to save the table and add another one. Call it Comments and add these columns:
- Id (int, Primary Key, Identity)
- PhotoId (int, Allow Nulls = No)
- Name (nvarchar(100))
- Email (nvarchar(100))
- Website (nvarchar(100))
- Message (nvarchar(4000))
- Approved (bit)
Click OK to save it again.
To add a relation between the two tables, you can right click the Comments table and pick Table Properties. In the Table Properties window, select Add Relations on the left. Enter these details:
- Relation Name: PhotoComments
- Update Rule: NO ACTION
- Delete Rule: CASCADE
- Primary Key Table: Photos
- Foreign Key Table: Comments
- Primary Key Table Column: Id
- Foreign Key Table Column: PhotoId
Click Add Columns and then Add Relation to save the relation.
Adding some test data
To test Simple.Data, we will start with trying to fetch data from the database. Before we can do that, we need to add some data to the database so we will actually have something to fetch.
Right click the MyPhotoBlog.sdf database in the Server Explorer and choose New Query. An Add Table window will popup asking us to add tables. We’ll just ignore this and click Close. We see the query view is divided in four parts. We will only need the third one that shows “SELECT FROM” by default. Replace the “SELECT FROM” with this code:
insert into Photos (Name, Slug, Filename, Camera, Lens, Aperture, Exposure, ISO, DatePosted, Published, DatePublished) values ('First post', 'first-post', 'firstpost.jpg', 'Sony A200', 'Sony 50mm F1.8', 'f/2.0', '1/800', '100', '2011/01/01 12:00:00', 1, '2011/01/01 12:00:00');
insert into Photos (Name, Slug, Filename, Camera, Lens, Aperture, Exposure, ISO, DatePosted, Published, DatePublished) values ('Second post', 'second-post', 'secondpost.jpg', 'Sony A200', 'Sony 50mm F1.8', 'f/4.0', '1/250', '200', '2011/02/01 12:00:00', 1, '2011/02/01 12:00:00');
insert into Photos (Name, Slug, Filename, Camera, Lens, Aperture, Exposure, ISO, DatePosted, Published, DatePublished) values ('Third post', 'third-post', 'thirdpost.jpg', 'Sony A200', 'Tamron 90mm Macro', 'f/8.0', '1/125', '200', '2011/03/01 12:00:00', 1, '2011/03/01 12:00:00');
insert into Photos (Name, Slug, Filename, Camera, Lens, Aperture, Exposure, ISO, DatePosted, Published, DatePublished) values ('Fourth post', 'fourth-post', 'fourthpost.jpg', 'Sony A200', 'Sony 50mm F1.8', 'f/1.8', '1/3200', '100', '2011/04/01 12:00:00', 1, '2011/04/01 12:00:00');
insert into Photos (Name, Slug, Filename, Camera, Lens, Aperture, Exposure, ISO, DatePosted, Published, DatePublished) values ('Fifth post', 'fifth-post', 'fifthpost.jpg', 'Sony A200', 'Sony 50mm F1.8', 'f/16', '8"', '100', '2011/04/09 12:00:00', 1, '2011/04/09 12:00:00');
Now we can press CTRL+R or click the red exclamation mark in the toolbar to execute the query. We get a warning telling us that the query definitions differ and that the query cannot be represented graphically in the Diagram and Criteria Pane. That’s normal. It’s because there is no way to graphically represent insert statements. We’ll Just click Continue. We now get notified that five rows were affected by the last query.
To verify this, right click the MyPhotoBlog.sdf database again and choose Show Table Data. Our five test records are present in the table!
In addition to some test records in the database, we will also need a photo for each record. We will keep photo’s of our photoblog in a separate directory. Let’s add it to our project. Create a directory called Data in the root of your project and copy the Web.Config file from the Content directory to the new Data directory so Nancy won’t interfere when trying to serve static files from that directory. Now we can put five test photo’s in the Data directory. You can download the samples I use, or you can put some of your own there. Just make sure the names are correct (“firstpost.jpg”, “secondpost.jpg”, …, “fifthpost.jpg”). My photo’s are all 960 pixels wide to fit in the “design” of the photoblog.
Adding Simple.Data to our Modules
The NerdBeers application provided me with some inspiration on how to add Simple.Data to our Modules.
Creating the DBFactory
Add a new directory called Services to your project and add an interface called IDBFactory to it. This is a very simple interface because this is all the code it needs:
public interface IDBFactory
{
dynamic DB();
}
Just an interface won’t do it. We need something that implements it. Add a new class called DBFactory to the Services directory and add the following code:
public class DBFactory : IDBFactory
{
protected dynamic _db = null;
public DBFactory()
{
}
public dynamic DB()
{
if (_db == null)
{
var c = System.Web.HttpContext.Current;
var s = ConfigurationManager.ConnectionStrings["MyPhotoBlog"];
if (s == null || String.IsNullOrWhiteSpace(s.ConnectionString))
{
_db = Simple.Data.Database.OpenFile(c.Server.MapPath("~/App_Data/MyPhotoBlog.sdf"));
}
else
{
_db = Simple.Data.Database.OpenConnection(s.ConnectionString);
}
}
return _db;
}
}
The DBFactory class has one method: DB(). This method checks if there is a connection string listening to the name “MyPhotoBlog” present in the Web.Config file. If it is, Simple.Data uses that connection string to establish a connection to the database and if it isn’t, we tell Simple.Data to fall back to our MyPhotoBlog.sdf in the App_Data directory.
“Wait a minute,” you say. “What’s this? The DB() method just returns a dynamic. We need something like a DbContext, no?” No we don’t. Simple.Data is built using the awesomeness of dynamics and we’ll get to that in just a minute!
Adding the DBFactory to our Modules
Remember that we created a base class called PhotoblogModule in part 2 of this blog? Well, there was a good reason for that. We’ll use it to take care of calling DBFactory.DB() for all other modules.
Open the PhotoblogModule.cs file (in the Modules directory) and add a dynamic object called DB:
public abstract class PhotoblogModule : NancyModule
{
protected dynamic DB;
public PhotoblogModule() : base()
{
}
public PhotoblogModule(string modulePath) : base(modulePath)
{
}
}
We now have an object that can store what the DBFactory.DB() will return. We can add this.DB = new DBFactory().DB(); right? Well, yes, we could do that, but there is a better solution. We can make use of something called Dependency Injection. Dependency Injection (or DI) is a form of Inversion of Control (or IoC). What that means is that it inverses the flow of control by injecting dependencies in order to reduce the coupling between components. If you’re not familiar with DI or IoC you’ll probably be a bit confused after reading that. I know I was when I first heard about it.
Let’s say we just use this.DB = new DBFactory().DB(); in the constructor of our PhotoblogModule class. When we do that, we create a tight coupling between the PhotoblogModule class and the DBFactory class. If we ever want to replace the DBFactory class with something else, we would probably have to change our PhotoblogModule as well because they are tightly coupled. Or if we want to Unit Test our PhotoblogModule we would be forced to have a database ready when doing the tests because the PhotoblogModule relies on the DBFactory class.
Dependency Injection solves this for us. In this case we will give the constructors of the PhotoblogModule class a parameter of the type IDBFactory like this:
public abstract class PhotoblogModule : NancyModule
{
protected dynamic DB;
public PhotoblogModule(IDBFactory dbFactory) : base()
{
DB = dbFactory.DB();
}
public PhotoblogModule(IDBFactory dbFactory, string modulePath) : base(modulePath)
{
DB = dbFactory.DB();
}
}
Now, the PhotoblogModule class knows “Ok, I will be provided with something that implements the IDBFactory interface. I don’t know what concrete object I will receive or where it comes from, and frankly, I don’t even care. I only know that whatever is passed to me will at least have a DB() methods which returns a dynamic.”
What is responsible for passing in concrete object implement the IDBFactory interface? In our case, the built in IoC container in Nancy called TinyIoC will take care of everything for us. When creating modules, it will notice that the PhotoblogModule has a dependency on the IDBFactory interface. It will then look for a class implementing that interface and it will inject an instance of that class into the module. Pretty neat, huh?
When we try to build our project, we will get a few errors at this point. That’s because the modules inheriting from our PhotoblogModule will call incorrect constructors of their base class. To fix this, add IDBFactory dbFactory as the first argument of each constructor and pass it to the base() statement like this:
public ArchivesModule(IDBFactory dbFactory) : base(dbFactory, "/archives")
{
...
}
Now all our modules have access to a dynamic DB object created by Simple.Data. However, when we build and run the project (CTRL+F5), we get an error! It took me a while to figure this one out, be it appears that Simple.Data.SqlCe40 also needs Simple.Data.Ado to function. Adding it is easy thanks to NuGet. Just enter “Install-Package Simple.Data.Ado” in the Package Manager Console and we’re set!
Reading from the database with Simple.Data
Open the RootModule class. We’ll see how we can get the latest published photo. All we have is the dynamic DB object from our PhotoblogModule base class. Because it is dynamic, we can type DB. followed by whatever we want. Simple.Data will figure out during runtime what to do with whatever you typed.
When we are going to query the database, we will need something to represent the data that is returned. So before we can actually fetch some data, we will need add a Photo class to our project. Create a new directory called Models and in it, create a new class called Photo:
public class Photo
{
public int Id { get; set; }
public string Name { get; set; }
public string Slug { get; set; }
public string Filename { get; set; }
public string Camera { get; set; }
public string Lens { get; set; }
public string Aperture { get; set; }
public string Exposure { get; set; }
public string ISO { get; set; }
public DateTime DatePosted { get; set; }
public bool Published { get; set; }
public DateTime? DatePublished { get; set; }
}
As you can see, this looks very much like what our Photos table in the database looks like.
Add another class to the Models directory and call it PhotoDetail. Add the following code:
public class PhotoDetail
{
public Photo Photo { get; set; }
public string NextSlug { get; set; }
public bool HasNext { get { return !String.IsNullOrEmpty(this.NextSlug); } }
public string PreviousSlug { get; set; }
public bool HasPrevious { get { return !String.IsNullOrEmpty(this.PreviousSlug); } }
}
This will serve as the model to pass to our view. It has a way of letting the view know if there is a previous and a next photo and telling it what the slugs are.
Now we can finally try to get something out of our database. On the homepage we need the most recent published photo. That’s the photo where Published is True with the highest DatePublished. Add this code to the Get["/"] route in the RootModule class:
Get["/"] = parameters =>
{
// Get all photo's that are published
var photos = DB.Photos.FindAllByPublished(true);
List<Models.Photo> photoList = photos.ToList<Models.Photo>();
// Order them so the newest come first
photoList = photoList.OrderByDescending(p => p.DatePublished).ToList();
// Get most recent photo
var latestPhoto = photoList.FirstOrDefault();
if (latestPhoto != null)
{
var model = new Models.PhotoDetail();
model.Photo = latestPhoto;
model.NextSlug = String.Empty;
if (photoList.Count > 1) model.PreviousSlug = photoList[1].Slug;
else model.PreviousSlug = String.Empty;
return View["photodetail", model];
}
else
{
return View["nophoto"];
}
};
On line 4, we are telling Simple.Data that we want a list of photo’s where the Published field is true. Then we ask for the results as a List<Models.Photo> on line 5.
Of the next part, I’m not really sure if I’m doing this the right way. This is the first time I’m working with Simple.Data and the available documentation is a bit sparse at the moment, but I can’t find a way to pass in an order by clause to the database. That’s why we are doing the sorting of the photo’s on their publication date in the code on line 8. Update: Mark Rendle, the author of Simple.Data just told me that sorting and paging is scheduled for version 0.6.
After the list is sorted, we can get the photo with the highest publication data on line 11. It is of course possible that there is no photo in the database, so we check for that on line 13.
If there is a photo, we put it in an instance of PhotoDetail (line 16). Since this is the latest photo, NextSlug is always empty (line 17). On lines 19 and 20 we check if there is a previous photo. If there is, we set the slug. Finally, we pass our model to the view on line 22.
If no photo was found, we’ll return a view called “nophoto”.
Updating our views
Now that we are sending data back to our view, we need to update our view so it display the incoming data. Change photodetail.cshtml so it looks like this:
<!doctype html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>My First View</title>
<link rel="Stylesheet" type="text/css" href="/Content/Css/reset.css" />
<link rel="Stylesheet" type="text/css" href="/Content/Css/photoblog.css" />
<script type="text/javascript" src="/Content/Scripts/modernizr-1.7.min.js"></script>
</head>
<body>
<header>MyPhotoBlog</header>
<div id="main">
<div id="name">@Model.Photo.Name</div>
<div id="photo">
<img src="/Data/@Model.Photo.Filename" />
</div>
<div id="exif">
<span class="type">Camera:</span> <span id="camera" class="value camera">@Model.Photo.Camera</span>
<span class="type">Lens:</span> <span id="lens" class="value">@Model.Photo.Lens</span>
<span class="type">Aperture:</span> <span id="aperture" class="value">@Model.Photo.Aperture</span>
<span class="type">Exposure:</span> <span id="exposure" class="value">@Model.Photo.Exposure</span>
<span class="type">ISO:</span> <span id="iso" class="value">@Model.Photo.ISO</span>
</div>
<nav>
@if (Model.HasPrevious)
{
<a id="previous" href="/photos/@Model.PreviousSlug">« Previous</a>
}
else
{
<span id="previous">« Previous</span>
}
<a href="/archives" id="archives">Archives</a>
@if (Model.HasNext)
{
<a id="next" href="/photos/@Model.NextSlug">Next »</a>
}
else
{
<span id="next">Next »</span>
}
</nav>
</div>
<footer>MyPhotoBlog is built with <a href="https://github.com/NancyFX/Nancy">Nancy</a> and <a href="https://github.com/markrendle/Simple.Data">Simple.Data</a>.</footer>
<script type="text/javascript" src="/Content/Scripts/jquery-1.5.2.min.js"></script>
</body>
</html>
Testing everything
Ok, this is exciting! Press CTRL+F5 to see if everything works. Isn’t it amazing?
The “nophoto” view
Since we return a “nophoto” view when there are no photo’s in the database, we need to add that view to our Views directory. Add a new HTM File and call it nophoto.cshtml and add the following code:
<!doctype html>
<html>
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>Photo's gone missing</title>
<link rel="Stylesheet" type="text/css" href="/Content/Css/reset.css" />
<link rel="Stylesheet" type="text/css" href="/Content/Css/photoblog.css" />
<script type="text/javascript" src="/Content/Scripts/modernizr-1.7.min.js"></script>
</head>
<body>
<header>MyPhotoBlog</header>
<div id="main">
<div id="photo">
<div>Oops. All our photo's have gone missing. If you find one, please let us know!</div>
</div>
</div>
<footer>MyPhotoBlog is built with <a href="https://github.com/NancyFX/Nancy">Nancy</a> and <a href="https://github.com/markrendle/Simple.Data">Simple.Data</a>.</footer>
<script type="text/javascript" src="/Content/Scripts/jquery-1.5.2.min.js"></script>
</body>
</html>
Wrapping it up
That’s it for today. We’ve done quite a lot actually. We have installed SQL Server Compact 4.0, added a database to our project, created tables and added test data. Next we have created an IDBFactory interface and a DBFactory class and we have injected it in our modules. Finally, we have made changes to our homepage so that it always show the most recently published photo. On the right you can see the project stucture at this point (click it for a bigger image).
As usual, the changes to the source code can found on Github.
Next time we’ll take care of the rest of the front end views: the PhotoModule and the ArchivesModule.
), that could mean that we are asking the database to return a million records at once. That’s bad.
In this post we have made a few adjustments to the routes and modules we created in the previous post. We have added our first view and instructed Nancy to return it when calling a certain route. We’ve also enabled the usage of CSS and Javascript files. We have added a Views and Content directory. Our project structure now looks like the one on the right.
Now that we have our five modules in the Modules directory, we can delete the MainModule.cs we created in the first post since we won’t be needing it anymore. All modules and routing information are nicely grouped in the Modules directory. Our project now looks like the one on the right.