Chris Risner . Com

If you were paying attention, you know that part 6 in our Veritas Blog Engine series was supposed to be about Error Logging.  Well, we’re going to go a bit out of order and do Data Access first.  The reason for this is that we’re going to use a lot of our data access methods in the sections I originally thought we’d write first.  I ran into quite a bit of trouble the last time I played with Entity Framework when it was 3.5.  Thankfully, they made quite a few improvements with 4.0 so we’re going to give it another try.

First things first, if you haven’t already done so, add a Models folder to your DataLayer project.  All of our database objects and any extensions will sit in this folder and namespace.  After that, we’ll add a “ADO .Net Entity Data Model” which is the Entity Framework file that we generate all of our database classes and connections in.  Like every past version of Microsoft’s “This is the way to do database access”, we get to generate the majority of our code straight from the database.  Included in this is the same functionality we had with the Linq2Sql generator for pluralizing / singularizing object names (though now it’s optional) as well as including foreign key columns in the models (which will hopefully fix the FK problems that plagued 3.5 (seriously it was like they designed it to be hard to have foreign key constraints).  Once we’ve selected all of our tables and tell it to generate we’re given an EDMX file and presented with a fantastic database diagram view.  Now, technically, we’ve generated our database access.  We could call it a day.  However, since we’re using the repository pattern and we want all of our data access methods to be in one place, we’ll go ahead and create all of those methods in the repository class we made in the last entry.

Before we can add any data access methods we need to add a instance of our Entities object (EDMX) to our repository like so:

private VeritasBlogDBV3Entities db = new VeritasBlogDBV3Entities();

So inside of our repository class we’ll use db. to do all our database interaction.  The first method we’ll create is our Save method.  This method handles saving any inserts, updates, and deletes after they’re done:

   1:  /// <summary>

   2:  /// Saves all DB changes and then accepts the changes.

   3:  /// </summary>

   4:  public void Save()

   5:  {

   6:      db.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);

   7:  }

We’ll call this method after doing any DB changes (or after any group of DB changes if we’re not doing them individually).  After this, we’ll add entries (separated out into regions) for each of our objects for adding and deleting.  So for example, here are the methods for the BlogConfig object:

   1:  #region BlogConfig

   2:   

   3:  public void Add(BlogConfig blogConfig)

   4:  {

   5:      db.BlogConfigs.AddObject(blogConfig);

   6:  }

   7:   

   8:  public void Delete(BlogConfig blogConfig)

   9:  {

  10:      db.BlogConfigs.DeleteObject(blogConfig);

  11:  }

  12:   

  13:  #endregion

These are pretty simple methods and just handle telling our entity object that we want to insert or delete something.  The Save method still has to be called after each of these.  A quick note on the delete methods:  we may or may not end up ever using them.  Typically I don’t like deleting data as much as “marking it inactive” for historical purposes.  We’re going to write some unit tests in a second so we’re going to write a method to pull all our BlogConfigs from the DB.  We won’t end up using this anywhere but in our unit test.

   1:  public IEnumerable<BlogConfig> GetAllBlogConfigs()

   2:  {

   3:       return db.BlogConfigs;

   4:  }

Now, before we can actually write some unit tests, we need to implement the StartTransaction and RollbackTransaction methods we made in the last entry.  These methods will be called before and after any unit tests so we’re not actually putting anything in the database. 

   1:  public DbTransaction Transaction { get; set; }

   2:  /// <summary>

   3:  /// Will create a new transation.  

   4:  /// </summary>

   5:  public void StartTransaction()

   6:  {

   7:      db.Connection.Open();

   8:      DbTransaction trans = db.Connection.BeginTransaction();

   9:      this.Transaction = trans;

  10:  }

  11:   

  12:  /// <summary>

  13:  /// Rolls back a transation. 

  14:  /// </summary>

  15:  public void RollbackTransaction()

  16:  {

  17:      this.Transaction.Rollback();

  18:  }

Now that these methods are implemented, we just need to make a base test class to handle calling these.

   1:  [TestClass()]

   2:  public class TestBase

   3:  {

   4:      public VeritasRepository repo = VeritasRepository.GetInstance();

   5:   

   6:      //Use TestInitialize to run code before running each test

   7:      [TestInitialize()]

   8:      public void MyTestInitialize()

   9:      {

  10:          repo.StartTransaction();

  11:          repo.Save();

  12:      }

  13:   

  14:      //Use TestCleanup to run code after each test has run

  15:      [TestCleanup()]

  16:      public void MyTestCleanup()

  17:      {

  18:          repo.RollbackTransaction();

  19:      }

  20:  }

Now we have everything we need to actually write a unit test to test adding a new BlogConfig:

   1:  /// <summary>

   2:  ///A test for Add BlogConfig

   3:  ///</summary>

   4:  [TestMethod()]

   5:  public void AddBlogConfigTest()

   6:  {

   7:      BlogConfig blogConfig = new BlogConfig()

   8:      {

   9:          Host = "test.com",

  10:          LastUpdateDate = DateTime.Now,

  11:          CreateDate = DateTime.Now,

  12:          ConfigXml = "<BlogConfig></BlogConfig>"

  13:      };

  14:      repo.Add(blogConfig);

  15:      repo.Save();

  16:      //Check the db for changes

  17:      var configs = repo.GetAllBlogConfigs().ToArray();

  18:      var testConfig = configs.Where(p => p.Host == "test.com").SingleOrDefault();

  19:      Assert.IsNotNull(testConfig);            

  20:  }

Since this class implements our TestBase class, before this unit test is called, we’re getting a new instance of VeritasRepository and calling StartTransaction on it.  When the test is done, it will call the RollbackTransaction method on the repo.  If everything goes well, it will create a new config object, insert it into the database, save that change, pull it back, and make sure it comes back from the DB.  Since most of our tests (such as our DeleteConfig test) will rely on having a BlogConfig and a BlogUser (if not many more things) to already be in the DB, we’ll eventually insert all of these in the test initialize but for now, we can add all of our Add / Delete methods and create tests for them.  Since we need to be able to pull records back from the database to make sure our tests are working, we’ll need to add more methods like the GetAllBlogConfigs seen above.  There are a lot of methods that we’ll in our repository that we’re not going to list here so if you want to see them all, download the files and check out the repo.  As of now, we’ve got what should be all the data access methods we’ll need as well as the unit tests for all of them.  As always, you can download the latest here.

In the last entry in this series, we went over the “non-blog” tables in our database.  Now it’s time to cover the tables that store data specific to posts or entries.  We’re going to dive right into it and take a look at the most important table, BlogEntry:

This table will contain the details specific to an entry.  So Text will be the html or the text for the post.  Keywords will fill the meta keywords for the entry.  Short will be used if we only want to display a shortened version of the html (in order to get more click throughs on RSS feeds for example).  Our entry name will match our title with the exception that it will be HTML encoded (mostly).  Lastly, the BlogAuthorId has a Foreign Key (FK) constraint to the BlogUser table (as mentioned in the previous post).  In addition, BlogConfigId is a FK back to the BlogConfig table as per usual.  Finally, PostType is a value we might use down the road to distinguish between live posts, drafts, or anything else we might store in the table.  Moving along, we’ll look at the BlogViewEntryCount table:

For now the only real point of this table is the WebCount column.  This table just keeps track of the number of times this entry has been loaded via the web.  Down the road, we might add additional columns to see how many times the entry was loaded from an RSS feed or linked from Facebook, or anything else we might want to track.  Each entry will (hopefully) get some comments or feedback but before we can store the feedback, we need a table to store the author of each comment.  Since we’ll likely have repeat commentators we don’t want to have to store their info for every comment, so we’ll have a BlogFeedbackAuthor table:

This table will store the name, email, URL fields that we’ll ask for in every comment.  Then we’ll store a total number of feedbacks for the people that enter the same info repeatedly.  Simple enough.  Now we’ll go to the BlogFeedback table:

First we have FKs to the BlogEntry table and the BlogFeedbackAuthor table.  From there we are storing details on the actual feedback such as the body, the IP address of the user that entered it, the status it’s in (we’ll tie this to an enum in the code), the title of the feedback, and the user agent of the user.  The NotifyAuthorOnFeedback column will be used to figure out if we should email the feedback author when someone comments after them on the same entry.  Lastly we have Feedback Type which we might use down the road to distinguish between types of feedback.   Only 4 tables left (for now) so let’s go to the BlogCategory:

Nothing really special here.  The important things are the Title and the IsActive field.  Now we have our BlogEntryCategory table to tie entries to categories:

This table is straight FKs.  Now we’ll get to BlogPage:

This table will be used to store pages we might want to host on our site with any sort of static comment.  In the PageContent column we can put any html or javascript we want.  Late we’ll make a way to render this so the user can add any static pages they want without having to get into code or anything like that.  We’re also allowing the user to enter the Meta Keywords and Description.  Finally we’ve got the Page Title (what we’ll actually put in the Title tag) and the Encoded Title (what we’ll use for the URL).  Our last table (for now) will be the Blacklist table:

This table will just be used to store the addresses of anyone that wants to opt out of emails.  So if someone comments with a bad address and says they want to be emailed when someone comments after them, the original person has the ability to say “I don’t want these emails!”.  So we’re done (again for now) with our Database Schema.  WOOT!   Coming up we’ll go over the base project structure.  So that you don’t have to go and create all of these tables yourself, you can download a SQL script to create all of these tables here (the SQL file is in a ZIP).

Update 6-27-2010:
BlogCategory's IsActive field should have been a bit field but was incorrectly shown as a nvarchar.