Saturday, January 05, 2008 #

In Search of the Ultimate Data Access Layer - Summary

This post will serve as a summary of my posts related to my not very scientific analysis of data access layers.  These posts won't be tutorials but more pointers to things I liked/didn't like about each method as well as the main bits of interesting code. 

For more detail grab the code from: http://code.google.com/p/browniesbikes/.  Along with the code is also my NUnit tests.

DataReaders and Stored Procs:

  • Listing Data
  • Retrieving a single item - implemented post coming soon
  • Inserting Data - implemented post coming soon
  • Updating Data - implemented post coming soon

TableAdapters:

  • Listing Data
  • Retrieving a single item - implemented post coming soon
  • Inserting Data - implemented post coming soon
  • Updating Data - implemented post coming soon

NHibernate:

  • Implemented - posts coming soon

LinqToSQL

  • Implemented - posts coming soon

SubSonic

  • partially implemented

Data Access Application Block (Enterprise Library)

  • meaning to implement it someday.

posted @ Saturday, January 05, 2008 8:43 PM | Feedback (0)

TableAdapters - The List

Most of my post on retrieving my Product list focused on the Stored Procedure.  When it came time to implement the same list with a TableAdapter all that work was already done for me.  My steps became:

  1. New dataset.
  2. Drag my stored procedure from server explorer.
  3. Rename the table and tableadapter
  4. And there we have it:

image

I then double clicked on the designer and edited the codebehind file to allow my DataRows to implement my IProduct interface:

    1 using System;

    2 using BrowniesBikes.DTO;

    3 

    4 namespace BrowniesBikes.Data.TableAdapter {

    5 

    6 

    7     partial class ProductDS

    8     {

    9         partial class ProductListRow : IProduct

   10         {

   11             #region IProduct Members

   12 

   13             public int? PhotoID

   14             {

   15                 get { throw new System.NotImplementedException(); }

   16                 set { throw new System.NotImplementedException(); }

   17             }

   18 

   19             ...etc...

   20             #endregion

   21         }

   22     }

   23 }

My DAL class ended up with very little code:

   18         public IEnumerable<IProduct> GetProductList(

   19             string searchString,

   20             string sortExpression,

   21             int startRow,

   22             int maxRows)

   23         {

   24             int endRow = startRow + maxRows;

   25             ProductListTableAdapter productListTableAdapter =

   26                 new ProductListTableAdapter();

   27 

   28             int? totalProducts = 0;

   29 

   30             ProductDS.ProductListDataTable listDataTable =

   31                 productListTableAdapter.GetData(

   32                 searchString,

   33                 startRow,

   34                 endRow,

   35                 sortExpression,

   36                 ref totalProducts);

   37 

   38             foreach (ProductDS.ProductListRow row in

   39                 listDataTable.Rows)

   40             {

   41                 yield return row;

   42             }

   43         }

Deceptively easy. Keep an eye out for my post on inserting and updating as well as TableAdapter maintenance. There are some major pain points coming your way.

posted @ Saturday, January 05, 2008 8:19 PM | Feedback (0)

Getting formatted code onto the web

This post is mostly for me as I recently switched machines and forgot the steps and software needed to get nicely formatted code into my blog:

  1. Download CopySourceAsHtml (as recommended by Hanselman)
  2. Extract the files from the MSI using Less MSIérables as the installer doesn't work with VS 2008.
  3. Copy the files to the VS 2008 plugin directory as per these instructions.
  4. Open Visual Studio Enable the plugin from Tools > Add-in Manager
  5. Select text and choose "Copy AS HTML" from the context menu.
  6. Right click in windows live writer and select "Paste Special". Choose "Keep Formatting" from the dialog.
  7. 1 Console.WriteLine("Beautifully formatted Code!");

    I almost didn't bother trying this tool as I didn't like the way Visual Studio put boxes around blocks of SQL code.  I forgot of course that these boxes couldn't be carried over the HTML even if I'd wanted them too.  The SQL looks just as nice as it does in management studio:

        1 -- Comment: Comment goes here

        2 SELECT * from Products

    The only problem I am finding is that code quite quickly gets too wide and wraps messing up the line numbers. That is probably more to do with my blog template.

    Update: I have just come across a neat way of solving the wrapping problem. 

    1. Uncheck wrap words in the copy as html dialog.
    2. Wrap my code in overflow divs:
    <DIV style="OVERFLOW: auto">

    I can now have nice scrollbars: 

       11 [Test]
       12         public void ShouldRetrieveSpeakerByEmail()
       13         {
       14             Conference anConference = new Conference("tea party", "");
       15             using (ISession session = getSession())
       16             {
       17                 session.SaveOrUpdate(anConference);
       18                 session.Flush();
       19             }
       20             string email = "brownie@brownie.com.au";
       21             Speaker speaker =
       22                 new Speaker("Andrew", "Browne", "http://blog.brownie.com.au", "the comment", anConference,
       23                             email, "http://blog.brownie.com.au/avatar.jpg", "Info about how important I am to go here.","password", "salt");
       24 
       25             ISpeakerRepository repository = new SpeakerRepository(_sessionBuilder);
       26             repository.Save(speaker);
       27 
       28             Speaker rehydratedSpeaker = null;
       29             //get Attendee back from database to ensure it was saved correctly
       30             using (ISession session = getSession())
       31             {
       32                 rehydratedSpeaker = repository.GetSpeakerByEmail(email);
       33 
       34                 Assert.That(rehydratedSpeaker != null);
       35                 Assert.That(rehydratedSpeaker.Contact.FirstName, Is.EqualTo("Andrew"));
       36                 Assert.That(rehydratedSpeaker.Website, Is.EqualTo("http://blog.brownie.com.au"));
       37                 Assert.That(rehydratedSpeaker.Comment, Is.EqualTo("the comment"));
       38                 Assert.That(rehydratedSpeaker.Conference, Is.EqualTo(anConference));
       39             }
       40         }

    posted @ Saturday, January 05, 2008 7:54 PM | Feedback (0)