January 2014
Volume 29 Number 1
The Working Programmer : Getting Started with Oak: Database Interaction
Ted Neward | January 2014
.jpg)
Welcome back. I’ve been walking through getting started with Oak, a dynamic approach to Web development that embodies ideas from the Ruby and Node.js worlds while still retaining the C# language, the ASP.NET MVC framework and the rest of the Microsoft .NET Framework you enjoy. The essence of Oak is “fast feedback, frictionless development and low ceremony,” in the words of project creator Amir Rajan.
When I left off, Oak was complaining it couldn’t connect to a database. In this column, I’ll show you how to do the following: wire up Oak to a SQL Server instance, add another related type to the system (comments on blog entries), build the database in Oak, relate the two types together using Oak, and see how Oak and its build system handle database interaction.
When We Left Our Hero ...
The last time Oak was running, it popped up an error message and helpful explanatory text, an excerpt of which is shown in Figure 1. (As a reminder, if you were following along in the last column—at msdn.microsoft.com/magazine/dn532208—you’ll need to kick off the server and sidekick runner.) Before I go much further, take note of how Oak isn’t just displaying an exception stack trace and leaving it to me to discover or research what could be causing the problem. It’s actually trying to diagnose the problem and suggesting potential solutions: in this case, “Update the web config with your server instance.”
.png "The Oak Project Help Window After an Error Is Thrown")
Figure 1 The Oak Project Help Window After an Error Is Thrown
Sure enough, a quick glance at the web.config file I picked up from the “warmup” gem shows that a simple placeholder is there, pointing to a “(local)” data source, which might work for a lot of configurations, depending on how the local SQL Server instance is configured. That said, it’s as trivial to drop in a LocalDB connection string as it is to drop in a remote SQL Server string (or a Windows Azure SQL Database, if it’s desirable to keep data in the cloud). I like using LocalDB, which is usually just a connection string of “Server=(localdb)\v11.0;Integrated Security=true” for explorations like this, because it’s small and cleans up fairly easily. Whichever SQL connection you use, just plug it into the <configuration>/<connectionStrings>/<add> element and refresh the page. In fact, Oak has a rake (the Ruby build tool) task to do this across the codebase for you: “rake update_db_server[(localdb)\\v11.0]” (the backslash needs to be escaped).
Uh ... Houston?
Unfortunately, when run again, Oak acts as if nothing has changed. Also unfortunately, that’s entirely true. Sidekick isn’t watching for changes to web.config, it seems, only source files. Considering how rarely you change the web.config file, this isn’t the end of the world, but it does put a small cramp in what’s otherwise a pretty effortless process. To get sidekick to recognize the project has changed, you just have to trigger a save of some important file, such as HomeController.cs or some other source file. You can do this in two ways: switch over to that file, pick a random place in the file, hit the space bar and delete that space (which convinces Visual Studio that the file is “dirty” and forces a save), or just manually kick off rake from a command prompt.
Once that’s done, a browser refresh brings up a different error (“Invalid object name ‘Blogs’”), and you’re squarely into the world of database migrations.
Data, Data, Data!
Like its conceptual predecessors, Oak wants to manage databases and database schema for you, so the database can remain more or less “hidden” from your view. In this case, Oak doesn’t want to just “automagically” craft a database out of thin air, because you’ll probably have some opinions about how the schema should look—and even if you don’t, the database admins often do. (Who wins that battle, or who should win that battle, is a discussion best had over beers—preferably long after the project has shipped.)
In Oak, seeding the database is done using a particular controller, the SeedController, found nested right next to the HomeController in SeedController.cs. That file contains a definition for the SeedController already, but—more importantly for your purposes—it also contains a Schema class, which will facilitate the steps necessary to build the schema and, optionally, put some sample data in place. Bear in mind, by the way, that the default convention of Oak is that objects are stored in a database table of pluralized name, so Blog objects will be stored in a table named Blogs. Figure 2 shows the Schema class.
Figure 2 The Schema Class
public class Schema
{
// This is the method you'll want to alter
public IEnumerable<Func<dynamic>> Scripts()
{
// Replace all content inside of the Scripts() method with this line
yield return CreateBlogsTable; // Return just the pointer to the function
}
public string CreateBlogsTable() // Here is the function definition
{
// This is an example, your table name may be different
// For more information on schema generation check out the Oak wiki
return Seed.CreateTable("Blogs",
Seed.Id(),
new { Name = "nvarchar(255)" },
new { Body = "nvarchar(max)" }
);
}
public void SampleEntries()
{
}
public Seed Seed { get; set; }
public Schema(Seed seed) { Seed = seed; }
}
Note the strange use of “yield return” in the Scripts method. For those of you who could never figure out what this did in C# 2.0, “yield return” creates an anonymous “stream” of objects and hands back an IEnumerable<T> pointing to that stream. In this particular case, it’s a stream of functions, and in this particular scenario, it’s a stream of one function (CreateBlogsTable). In essence, you’re setting up a stream of actions (Func<dynamic> instances) representing how to create the database, and Oak will take each action or function handed back from this stream and execute it. This way, when a new change to the schema comes into play, that change can be captured in a new function (“CreateCommentsTable,” if you will) and simply added to the list. If desired, you could “version” the database schema by calling the first function Version1, the second one Version2 and so on. The Oak wiki has more information on database migrations at bit.ly/1bgods5.
(For those who recall my “Multiparadigmatic .NET” series, which begins at msdn.microsoft.com/magazine/ff955611, yes, this is a pretty functional-oriented way of approaching this problem.)
The Seed.Id function creates the canonical primary key column: an auto-incrementing integer value marked as a primary key. The Oak wiki (bit.ly/1iKfcIb) contains a reference on creating a database using the Seed class to do so, but there’s always the ad hoc fallback of doing straight SQL if desired:
public IEnumerable<string> AdHocChange()
{
var reader = "select * from SampleTable".ExecuteReader();
while (reader.Read())
{
// Do stuff here like yield return strings
}
var name = "select top 1 name from sysobjects"
.ExecuteScalar() as string;
yield return "drop table SampleTable";
yield return "drop table AnotherSampleTable";
}
Add the AdHocChange method as another “yield return” method to call, and Oak will merrily carry out these commands as well. (Note that if you lose track of that wiki link, Oak includes it in the error/help message it displays.)
I Cannot Make Bricks Without Clay!
By the way, if the database needs some seed data, this is also part of the SeedController’s responsibility, and again it falls to the Schema class to do it, this time via the SampleEntries method. Because this system doesn’t have any real seed data that needs to be here, I’ll leave this alone.
Once the SeedController is compiled, sidekick will redeploy the project, but like most controllers it won’t get activated until hit with an HTTP request. If you didn’t glance over the SeedController, take a quick look. It exports four POST endpoints: PurgeDB, Exports, All and SampleEntries. While you could certainly hit the endpoints yourself, this is the kind of repetitive task that’s best left to automation—which in the case of Oak means rake. Sure enough, “rake reset” will drop all the tables and regenerate the schema (it does a POST to /seed/PurgeDB and then another POST to /seed/all), and “rake sample” will drop the tables, regenerate the schema and generate the sample data (POST /seed/SampleEntries). Just for completeness, by the way, a “rake export” (or a POST /seed/export) will return the SQL statements used to do those things.
(Curl or Ruby can make doing a POST from the command line a one-line exercise, by the way. The Rakefile.rb file has examples of how to do the POST using Net::HTTP::post_from that are pretty easy to cut and paste into another .rb file, if you don’t want it buried inside the Rakefile. Or you could use this as a gateway drug toward learning Ruby. Nobody’s judging.)
It’s Alive!
Assuming there are no typos, once rake reset is done, a refresh in the browser brings up a working Web page with a simple text field (for the blog title) and a “submit” button. Entering a new blog title will generate an error, however—although Blogs exist, there’s sort of an assumption (based on what’s in the Index.cshtml view) that there’s also some kind of a thing associated with Blogs called Comments.
In the simple relational model that dominates the blogging engine world, blogs have a one-to-many relationship to comments. I want to model that in this system as well, so first I need a data type for Comment objects, which is brain-dead simple, again. In fact, because Comments are (like Blog objects) essentially dynamic objects, with no interesting elements to them, they don’t even need a model class definition—the stock all-dynamic object (the Gemini type, for those who remember my August 2013 column, “Going Dynamic with the Gemini Library,” at msdn.microsoft.com/magazine/dn342877) is fine.
(And yes, if you haven’t had a “we’re a long way from Kansas, Toto” kind of moment before now, this is definitely time to stop and ponder: You’re working with a business object whose type you never bother to define.)
To describe the relationship of Comments to Blogs, however, you have to do two things. First, Comments needs a repository (just as Blogs did in my last column):
public class Comments : DynamicRepository
{
}
More significantly, the Blog class needs to be revamped slightly to capture the one-to-many relationship, both directly (Blog owning a collection of Comment objects, which in this case means having a Comments object as a field), and indirectly (so Oak knows that Blog and Comment objects are connected in the database, and how). This is done by introducing a new method, Associates, that describes the relationship, as shown in Figure 3.
Figure 3 Describing the One-to-Many Relationship Between Blogs and Comments
public class Blog : DynamicModel
{
Blogs blogs = new Blogs();
// Define comments
Comments comments = new Comments();
public Blog() { }
public Blog(object dto) : base(dto) { }
// Add an Associates method to add the Comments() method
IEnumerable<dynamic> Associates()
{
// And define the association
// For othere examples of associations check out the Oak wiki
yield return new HasMany(comments);
}
}
As you can see, it’s not really that far off from what I said you needed to do: The model matches the abstract concept of a blog pretty closely. This is where the beauty and power of using dynamic techniques can be seen. This one change (the Associates method doing a “yield return” of a HasMany) actually triggers three new methods being added to Blog—Comments, CommentIds and NewComment—to support the relationship of Comment objects to Blog objects, all of which are pure scaffolding and would normally require you to write those methods “the hard way” were you using normal, non-dynamic C#. Naturally, though, for all of this to work against the database, you need to back it up with a database description of the comment table, which means you’re back to SeedController (and a subsequent rake reset), as shown in Figure 4.
Figure 4 A Database Description of the Comments Table
public class Schema{
public IEnumerable<Func<dynamic>> Scripts()
{
yield return CreateBlogsTable;
yield return CreateCommentsTable;
}
public string CreateBlogsTable() // Here is the function definition
{
return Seed.CreateTable("Blogs",
Seed.Id(),
new { Name = "nvarchar(255)" },
new { Body = "nvarchar(max)" }
);
}
public string CreateCommentsTable() // Here is the function definition
{
return Seed.CreateTable("Comments",
Seed.Id(),
new { BlogId = "int", ForeignKey = "Blogs(Id)" },
new { Body = "nvarchar(max)" }
);
}
}
Because I’m here, by the way, I’ll add a couple of blog entries, just to show off how to use the SampleEntries method. It’s a lot easier than you might think, as shown in Figure 5.
Figure 5 Adding Blog Entries
public void SampleEntries(){
var blog1 = new // Store the ID
{
Name = "Hello, Oak Blog",
Body = "Lorem ipsum dolor sit amet, consectetur adipiscing elit."
}.InsertInto("Blogs");
new { Body = "great job!", BlogId = blog1 }.InsertInto("Comments");
new
{
Name = "Here are cat pictures!",
Body = "Meowem hisum collar sit amet, addipisces lick."
}.InsertInto("Blogs");
}
Once again, the use of dynamic objects and extension methods makes it almost not look like C# anymore. (In this case, you’re not creating a dynamic object so much as creating an instance of an anonymous object with the autogenerated properties Title and Body, and then using the extension method InsertInto to do the actual insertion.) And, by the way, the only reason to trap the object in the blog1 local variable is so it can be used as the blog ID value for the comment on it.
Go ahead. Refresh the database with a rake sample and then refresh the browser.
Next: User Input Validation
This is turning into some interesting stuff, if it wasn’t already. You have no model types defined, yet you have a working model and database storage for it. No SQL scripts were involved (though it’s fair to suggest that repeated use of ad hoc methods in the Schema class could easily turn into SQL scripts), and it’s important to point out that all of the code doing all of this is (still) tucked away in source form in the Oak folder in the scaffolded project, in case you need to debug or just feel like browsing.
There are still a few things yet to do, such as validating the user input to make sure it’s all good and correct before storing, but that’s a good subject for next time.
Happy coding!
Ted Neward is the principal of Neward & Associates LLC. He has written more than 100 articles and authored and coauthored a dozen books, including “Professional F# 2.0” (Wrox, 2010). He’s an F# MVP and speaks at conferences around the world. He consults and mentors regularly—reach him at ted@tedneward.com if you’re interested in having him come work with your team, or read his blog at blogs.tedneward.com.
Thanks to the following technical expert for reviewing this article: Amir Rajan (Oak project creator)
Amir Rajan is an active member of the development community with appearances on .Net Rocks, Herding Code and Hanselminutes. He has expertise in many .NET-based Web frameworks, REST architectures, Ruby, JavaScript (front-end and NodeJS), iOS and F#. He is always striving to better the industry through open source contributions, independent consulting and blogging (amirrajan.net) and can be reached at ar@amirrajan.net."