Overview of the ADO.Net Entity Framework

ADO.Net Entity Framework

What is it?

Microsoft definition:

The ADO.NET Entity Framework is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. Entity Framework applications provide the following benefits:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
• Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.
• Multiple conceptual models can be mapped to a single storage schema.
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

So what does that mean?

A way to reference data as "plain old clr objects" (POCO), without regard to how the data is actual retrieved or persisted (unless you want to know).  You can query, create, update, and delete using .Net objects and either extension methods or Linq to Entities.

History

Data Readers - serial data access, no type safety, manual loading into objects.

DataSets - disconnected access, sorting, filtering, no type safety, manual loading or databinding.

Typed DataSets - disconnected, strongly typed, still datasets ("heavy", lots of dataset methods, easily abused)

Linq To Sql - connected, first attempt at using CLR code to access data.  Still one-to-one to database entities.  Lazy loading both good and bad.

Entity Framework - connected or disconnected, doesn't need to map directly to data model, lazy or eager loading, "mostly" POCO. 

So why would I want to use this?

Less code.  More time to focus on what matters, not data access logic.  Bindable, can be used directly to databind in Asp.Net or WPF.  Partial classes, allowing easy extension.

Examples

Acme Construction Consulting.  An application written in WPF using Sql Express 2008 for data storage and Entity Framework for data access.  Middle tier components accept scalar parameters and return Entity Framework objects to the front end.  DataContext is set in WPF UI directly to Entity Framework objects.  State management plays nicely with databinding, allowing changes to be tracked for persistence at a time of the developer's choosing. 

Very connected model.  Entire object graph can be retrieved with a single statement and bound to the page and elements on the page. 

Property Changed events allow "turbo-tax" style updates whenever appropriate data elements are changed. 

Large amount of data, can be slow to retrieve, but ok due to the nature of the application.

Partial classes extended to allow custom formulas to be exposed as properties.

public partial class SoftCost

{

    #region Calculated properties.

    public double? LumpSum

    {

        get

        {

            if (Scenario.TotalConstructionCost == null ||

                PercentofProject == null)

            {

                return null;

            }

            else

            {

                return

                    Scenario.TotalConstructionCostWithoutEscalation.Value *

                    PercentofProject.Value;

            }

        }

    }

    #endregion

}

Different types of ways to get at the data (extension methods, linq)

public IQueryable<DataObjects.RenovationType> GetRenovationTypes()

{

    return

        from renovationType in db.RenovationType

        select renovationType;

}

public DataObjects.RenovationType GetRenovationType(int id)

{

    return db.RenovationType.First(r => r.Id == id);

}

More complex queries possible

public IQueryable<DataObjects.DepartmentGroupType> GetDepartmentGroupTypes(int sectorId)

{

    return

        from departmentGroupType in db.DepartmentGroupType

        .Include("DepartmentType")

        where departmentGroupType.Sector.Id == sectorId

        || departmentGroupType.Sector.Id == 0 // Include "All" sectors

        orderby departmentGroupType.Description

        select departmentGroupType;

}

Lessons learned

Performance.  If you have an object that has nested related objects, you have a few choices as to how to retrieve that related information.  You can use the Include method to load all of the data on the database server, returning the full object graph.  Or, you can use the Load method to load individual objects after retrieving the initial object.  The problem with Include is that it creates a large UNION statement in the database to return one row for each of the lowest-level objects included.  This can be VERY slow if there are many child objects or data.  Include is certainly an easier approach to use if performance not an issue, but using the "Gatlin Gun" approach (use load many times instead of Include) is much faster.

Object context.  The object context is not always disconnected, even if Context is gone (for example, by using a Using statement).  The state tracker (ObjectStateManager) is still there and will complain if you try to "mix and match" objects that were retrieved from different contexts. 

Value Added Website.  An Asp.Net application using Entity Framework for database access. 

Different model than WPF application.  Everything is stateless.  Each page request is new connection to database, all entities are disconnected.  Change Tracking works differently. 

You can use ObjectDataSource with methods that accept or return Entity Framework objects, which can speed up your development time.

If you want to compile objects into one object graph that come from different business objects, you must be using the same context or you will get an exception.

"Unit of Work Scope" makes it easy to ensure that you are getting one instance.

protected DataObjects.Entities db

{

    get

    {

        if (UnitOfWorkScope.CurrentObjectContext != null)

        {

            return UnitOfWorkScope.CurrentObjectContext;

        }

        else

        {

            if (_db == null)

            {

                _db = new DataObjects.Entities();

            }

            return _db;

        }

    }

}

To use:

            using (new Service.UnitOfWorkScope(true))

            {

                                                ...

                                }

Criticisms

Performance.  Can be slower than raw data access code, unless you put in the extra time to optimize.  However, you can use stored procedures, can decide how things are loaded, can view actual queries to be run, can compile linq expression.

Not really POCO.  Still has a lot of extra methods.  Still has connected capabilities, even when you don't want them.

Context can be a pain, as can state management.  Mixing and matching, updating some entities while adding others and doing nothing to still others can be very complicated.  (MSDN August 2009 has a lot of good information on this).

No easy way to reattach complex object graphs, though there are patterns for doing it for the more simple entities.

Designer is sometimes "too" careful.  Removing entities from the EDM designer doesn't always get rid of all parts.  Sometimes you need to go into the Xml and manually get rid of elements.  Can be easier to remove all and regenerate in many cases. 

Future

POCO. 

Improved n-tier support.  Attach and change states.

T4 based code.  Use build in templates for common patterns or create your own.

Foreign Key properties.  Right now, can't set the foreign key (Id), can only set the actual object.  This also helps with n-tier and "mix and match" issues. 

Fun with System.Xml.Linq.XDocument

Overview

I've recently been using some new code to do some standard xml manipulation.  Although I am pretty comfortable working with an XmlDocument I was interfacing with some code from another team and they were using the newer XDocument.  I decided to give it a try and see how well it worked.  Needless to say, it's very different from an XmlDocument.  On the other hand, it provides a very intuitive interface and is very easy to use.

I've provided a few examples of adding and updating Elements (this APIs version of Nodes) and also of moving between the traditional XmlDocument and the newer XDocument.  I used the XDocument's Parse method to create a new object directly from a string and was off to the races.

Here is the code for the examples:

Program.cs

namespace BlogConsoleDemoApp

{

    class Program

    {

        static void Main()

        {

            XDocumentDemo.DemoXDocUsage();

        }

    }

}

XDocumentDemo.cs

using System.Xml;

using System.Xml.Linq;

using System.Xml.XPath;

namespace BlogConsoleDemoApp

{

    public class XDocumentDemo

    {

        public static void DemoXDocUsage()

        {

            XDocument xDocSource = XDocument.Parse("<?xml version=\"1.0\" encoding=\"utf-8\"?><albums><album id=\"1\"><name>Get your Ya-Yas Out</name><artist>The Rolling Stones</artist><rating>5</rating></album><album id=\"2\"><name>The White Album</name><artist>The Beatles</artist><rating>5</rating></album><album id=\"3\"><name>Baba O'Reilly</name><artist>The Who</artist><rating>5</rating></album><album id=\"4\"><name>Houses of the Holy</name><artist>Led Zepplin</artist><rating>5</rating></album><album id=\"5\"><name>All Along the Watchtower</name><artist>Jimi Hendrix</artist><rating>5</rating></album></albums>");

            XDocument xDoc = XDocument.Parse("<?xml version=\"1.0\" encoding=\"utf-8\"?><myalbums><album id=\"121\"><name>Toys in the Attic</name><artist>Aerosmith</artist><rating>4</rating></album><album id=\"34\"><name>Alive</name><artist>KISS</artist><rating>3</rating></album><album id=\"23\"><name>Goodbye Yellow Brick Road</name><artist>Elton John</artist><rating>4</rating></album><album id=\"229\"><name>Girlfriend</name><artist>Matthew Sweet</artist><rating>3</rating></album><album id=\"244\"><name>Live at the Sands</name><artist>Frank Sinatra</artist><rating>5</rating></album></myalbums>");

            Trace.WriteLine("Original Xml");

            Trace.WriteLine(xDoc.ToString());

            // create a new rating element and replace the existing element (retrieved using XPath) with the new element

            XElement oldXElement = xDoc.XPathSelectElement("myalbums/album[@id = '34']/rating");

            XElement newXElement = new XElement("rating", "5");

            if (oldXElement != null) oldXElement.ReplaceWith(newXElement);

            Trace.WriteLine("");

            Trace.WriteLine("******************************************************************************");

            Trace.WriteLine("Rating changed on the KISS album");

            Trace.WriteLine(xDoc.ToString());

            // Get the Matthew Sweet entry using XPath and replace it with Led Zepplin

            XElement oldXElementToBeReplaced = xDoc.XPathSelectElement("myalbums/album[@id = '229']");

            XElement newXElementToReplaceWith = xDocSource.XPathSelectElement("albums/album[@id = '4']");

            if (oldXElementToBeReplaced != null) oldXElementToBeReplaced.ReplaceWith(newXElementToReplaceWith);

            Trace.WriteLine("");

            Trace.WriteLine("******************************************************************************");

            Trace.WriteLine("Matthew Sweet replaced by Led Zepplin");

            Trace.WriteLine(xDoc.ToString());

            // create and add this new element

            //<album id=\"2007\"><name></name><artist>Foo Fighters</artist><rating>4</rating></album>

            XElement newXElementAdd = new XElement("album");

            newXElementAdd.SetAttributeValue("id", "2007");

            newXElementAdd.SetElementValue("name", "There Goes My Hero");

            newXElementAdd.SetElementValue("artist", "Foo Fighters");

            newXElementAdd.SetElementValue("rating", "4");

            if (xDoc.Root != null) xDoc.Root.Add(newXElementAdd);

            Trace.WriteLine("");

            Trace.WriteLine("******************************************************************************");

            Trace.WriteLine("Foo Fighters Created on the fly and added");

            Trace.WriteLine(xDoc.ToString());

            // Convert to XmlDocument

            XmlDocument xmlDoc = new XmlDocument();

            xmlDoc.LoadXml(xDoc.ToString());

            Trace.WriteLine("");

            Trace.WriteLine("******************************************************************************");

            Trace.WriteLine("Convert to XmlDocument for use with existing methods");

            Trace.WriteLine(xmlDoc.InnerXml);

            // Convert Back to XDocument

            XDocument newXDoc = XDocument.Parse(xmlDoc.InnerXml);

            Trace.WriteLine("");

            Trace.WriteLine("******************************************************************************");

            Trace.WriteLine("Convert back to XDocument when done");

            Trace.WriteLine(newXDoc.ToString());

       }

    }

}

I hope it helps - take care until next time.

How to be in two places at the same time - Asynchronous method calls

Overview:

Oftentimes developers are faced with time consuming tasks that appear to make the software being developed run slowly.  The majority of code developed is sequential and synchronous.  That is, step one is processed and when it is completed step two is processed.  There are, however times when a process that is not critical to the current output delays the application (for instance, after retrieving a large dataset from a database for a web application and rendering some portion of it to the page we might want to cache the dataset so as not to have to make another call to the database.)  Situations such as these are perfect opportunities to implement asynchronous logic and have the application be a bit faster.

In the following console application I implemented an artificial call that does nothing but wait a set amount of time before continuing with its execution.  This is intended to mimic the effort required to execute some piece of useful code within our application (like the caching example in the previous paragraph).  The output following the code is very clear as to the action and sequence being taken within the code.

To implement an asynchronous call one delegate is required.  It must have the same signature as the method that you will be calling, in this case the DoSomeUsefulWorkDelegate.  This delegate allows us to execute the method using the BeginInvoke method.  This method starts the function on the ThreadPool where it gets a new thread to perform its work.

If some clean-up or exception handling needs to be performed then another delegate is also required.  This second delegate is a System.AsyncCallback - the method takes one IAsyncResult parameter (in our console app we use the DoSomeUsefulWorkWrapUp method).  This callback is passed into the BeginInvoke method after the original signature values (in our console app, just the secondsToSleep integer value).  This method is called when the method has completed its work.  The BeginInvoke also takes a final parameter that holds an object that contains state information that needs to be passed around.

The AsyncCallback method passed into the BeginInvoke will be called receiving an IAsyncResult object as its parameter.  This object is the key to Asynchronous method working - it is of type System.Runtime.Remoting.Messaging.AsyncResult but it holds all of the information for the calling method, exception and other useful information in its _replyMsg [((System.Runtime.Remoting.Messaging.AsyncResult)(ar))._replyMsg].  It holds the state information passed around by the user in the AsyncState.

Console App:

using System.Diagnostics;

using System.Runtime.Remoting.Messaging;

using System.Threading;

using System;

namespace BlogConsoleDemoApp

{

    class Program

    {

        private static int _counter = 0;

        static void Main()

        {

            const int SLEEP_DURATION_IN_SECONDS = 2;

            AsynchronousCall(SLEEP_DURATION_IN_SECONDS);

            Trace.WriteLine("****");

            SynchronousCall(SLEEP_DURATION_IN_SECONDS);

            DoSomeUsefulWork(15);

        }

        private static void AsynchronousCall(int SLEEP_DURATION_IN_SECONDS)

        {

            PrintTimestamp("before asynchronous call to method");

            DoSomeUsefulWorkDelegate delDoSomeUsefulWorkDelegate = DoSomeUsefulWorkWithAnException;

            AsyncCallback ac = DoSomeUsefulWorkWrapUp;

            // Call method asynchronously 6 times.

            for (int i = 0; i < 6; i++)

            {

                delDoSomeUsefulWorkDelegate.BeginInvoke(SLEEP_DURATION_IN_SECONDS, ac, "passed around state");

            }

            PrintTimestamp("after asynchronous call to method");

        }

        private static void SynchronousCall(int secondsToSleep)

        {

            PrintTimestamp("before synchronous call to method");

            DoSomeUsefulWork(secondsToSleep);

            PrintTimestamp("after synchronous call to method");

        }

        private static void DoSomeUsefulWork(int secondsToSleep)

        {

            // sleep

            Thread.Sleep(secondsToSleep*1000);

        }

        private static void DoSomeUsefulWorkWithAnException(int secondsToSleep)

        {

            if ((++_counter % 3) != 0)

            {

                // sleep

                PrintTimestamp("before call in work with exception method");

                Thread.Sleep(secondsToSleep*1000);

                PrintTimestamp("after call in work with exception method");

            }

            else

            {

                int workerThreads, completionPortThreads;

                ThreadPool.GetAvailableThreads(out workerThreads,

                                               out completionPortThreads);

                // build a message to log

                string message =

                    String.Format(@"Is Thread Pool Thread: {0}, Thread Id: {1} Available Worker Threads {2}",

                                  Thread.CurrentThread.IsThreadPoolThread,

                                  Thread.CurrentThread.GetHashCode(),

                                  workerThreads);

                throw new Exception("Exception thrown: " + message);

            }

        }

        public delegate void DoSomeUsefulWorkDelegate(int secondsToSleep);

        private static void DoSomeUsefulWorkWrapUp(IAsyncResult ar)

        {

            DoSomeUsefulWorkDelegate doSomeUsefulWorkDelegate = (DoSomeUsefulWorkDelegate)((AsyncResult)ar).AsyncDelegate;

            try

            {

                doSomeUsefulWorkDelegate.EndInvoke(ar);

            }

            catch (Exception ex)

            {

                PrintTimestamp(ex.Message);

            }

        }

        private static void PrintTimestamp(string message)

        {

            Trace.WriteLine(string.Format("Timestamp {0}: {1}", message, DateTime.Now.ToLongTimeString()));

        }

    }

}

Output:

Timestamp before asynchronous call to method: 2:02:52 AM

Timestamp before call in work with exception method: 2:02:52 AM

Timestamp after asynchronous call to method: 2:02:52 AM

****

Timestamp before synchronous call to method: 2:02:52 AM

Timestamp before call in work with exception method: 2:02:53 AM

Timestamp after synchronous call to method: 2:02:54 AM

Timestamp after call in work with exception method: 2:02:54 AM

Timestamp before call in work with exception method: 2:02:54 AM

Timestamp Exception thrown: Is Thread Pool Thread: True, Thread Id: 13 Available Worker Threads 247: 2:02:54 AM

Timestamp before call in work with exception method: 2:02:54 AM

Timestamp after call in work with exception method: 2:02:55 AM

Timestamp Exception thrown: Is Thread Pool Thread: True, Thread Id: 14 Available Worker Threads 246: 2:02:55 AM

Timestamp after call in work with exception method: 2:02:56 AM

Timestamp after call in work with exception method: 2:02:56 AM

That's it for this time - hope it helps.

How to use the Client Script Manager

The ClientScriptManager class is used to manage client scripts and add them to Web applications. You can get a reference to the ClientScriptManager class from the ClientScript property of the Page object.

You can add a client script to a Web page declaratively by including the script in the HTML markup of the page.
This embedded script would look like this on the aspx page:
<script type="text/javascript">

            function changePostalCodeLabel(selectedValue)

            {

                        var lblZipPostalCode = document.getElementById("lblZipPostalCode");

                        if(selectedValue == 'US'){

                                    lblZipPostalCode.innerHTML = 'Zip Code'

                        } else {

                                    lblZipPostalCode.innerHTML = 'Postal Code'

                        }

            }

</script>

However, there are situations when adding client script dynamically is needed. To add a script dynamically, use the RegisterClientScriptBlock method (to inset a dynamically created block of script into the page), the RegisterClientScriptInclude method (to dynamically insert a reference to an external script file into the page), the RegisterStartupScript method (to dynamically insert a script that will execute during start-up), or the RegisterOnSubmitStatement method(to dynamically insert a script that will execute on form submission)

The ClientScriptManager class uniquely identifies scripts by a key String and a Type. Scripts with the same key and type are considered duplicates. Using the script type helps to avoid confusing similar scripts from different user controls that might be in use on the page.

One of the biggest advantages to using dynamic scripts is the ability to access the ClientID of various controls on the web page as the page is being loaded.  As the simple script listed at the top will work on a simple page when master pages are implemented .NET does some name mangling under the covers and the previous script would not work.  With master pages the correct script would be more along the lines of the following:
<script type="text/javascript">

            function changePostalCodeLabel(selectedValue)

            {

                        var lblZipPostalCode = document.getElementById("ctl00_MainContent_lblZipPostalCode");

                        if(selectedValue == 'US'){

                                    lblZipPostalCode.innerHTML = 'Zip Code';

                        } else {

                                    lblZipPostalCode.innerHTML = 'Postal Code';

                        }

            }

</script>

There is no way of knowing at design time, in a guaranteed fashion, what the control name will be.  By using the client script manager we can create the script on the fly and have the page provide us with the client id that it will be implementing.

The following example uses a simple drop-down containing three countries.  The idea behind the script is that if the selected country is the United States then the text box label will show Zip Code, for any other country it will display Postal Code. 

The code for the aspx page is:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ClientScriptManagerExample.aspx.cs" Inherits="ClientScriptManagerExample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Client Script Manager Example Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:DropDownList ID="ddlCountry" runat="server" Height="19px" Width="184px" onChange="changePostalCodeLabel(this.value)">

        </asp:DropDownList>

        <asp:Label ID="lblZipPostalCode" runat="server" Text="Zip/Postal Code"></asp:Label>

        <asp:TextBox ID="txtZipPostalCode" runat="server"></asp:TextBox>

    </div>

    </form>

</body>

</html>

The Code-Behind is this:
using System;

using System.Text;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class ClientScriptManagerExample : Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            // Add a few countries to the drop-down list

            ListItem liUS = new ListItem("United States", "US", true);

            ListItem liUK = new ListItem("United Kingdom", "UK", true);

            ListItem liCA = new ListItem("Canada", "CA", true);

            ddlCountry.Items.Add(liUS);

            ddlCountry.Items.Add(liUK);

            ddlCountry.Items.Add(liCA);

            // Create and register the script

            SetPostalCodeJavascript();

            // Set an initial value for the label

            if (ddlCountry.SelectedItem.Text.Equals("United States"))

            {

                lblZipPostalCode.Text = "Zip Code";

            }

            else

            {

                lblZipPostalCode.Text = "Postal Code";

            }

        }

    }

    private void SetPostalCodeJavascript()

    {

        // Define the name and type of the client scripts on the page.

        const string csname = "PostalCodeLabelScript";

        Type cstype = GetType();

        // Get a ClientScriptManager reference from the Page class.

        ClientScriptManager cs = Page.ClientScript;

        // Check to see if the client script is already registered.

        if (!cs.IsClientScriptBlockRegistered(cstype, csname))

        {

            StringBuilder cstext = new StringBuilder();

            cstext.AppendFormat("<script type=\"text/javascript\">{0}", Environment.NewLine);

            cstext.AppendFormat("\tfunction changePostalCodeLabel(selectedValue) {0}", Environment.NewLine);

            cstext.AppendFormat("\t{{{0}", Environment.NewLine);

            // lblZipPostalCode.ClientID is the important part here

            cstext.AppendFormat("\t\tvar lblZipPostalCode = document.getElementById(\"{0}\");{1}", lblZipPostalCode.ClientID, Environment.NewLine);

            cstext.AppendFormat("\t\tif(selectedValue == '{0}'){{{1}", "US", Environment.NewLine);

            cstext.AppendFormat("\t\t\tlblZipPostalCode.innerHTML = 'Zip Code' {0}", Environment.NewLine);

            cstext.AppendFormat("\t\t}} else {{ {0}", Environment.NewLine);

            cstext.AppendFormat("\t\t\tlblZipPostalCode.innerHTML = 'Postal Code' {0}", Environment.NewLine);

            cstext.AppendFormat("\t\t}} {0}", Environment.NewLine);

            cstext.AppendFormat("\t}}{0}", Environment.NewLine);

            cstext.Append(Environment.NewLine);

            cstext.AppendFormat("</script>{0}", Environment.NewLine);

            cs.RegisterClientScriptBlock(cstype, csname, cstext.ToString(), false);

        }

    }

}

By using the ClientScriptManager we can really leverage javascript on the client-side where appropriate, reducing round trips to the server, providing a better user experience as well as consuming less server time.  A win-win situation

That's it for this time - hope it helps.

XAMLFest 2008

After all of the success of the first round of Boston XAMLFest (12/11 and 12/12), Microsoft decided to host a second set of days this week (12/15 and 12/16).  XAMLFest was a quick but detailed look into WPF and SilverLight; with minimal marketing slides and a lot of hands-on coding and walk-throughs.  The time was split up between Visual Studio development (John P) for the coding aspects and Expression Blend 2 (John B) for the UI. 

While I have been doing a lot WPF work for a bit, this experience was awesome.  I learned several tips and tricks that should help for my current and future projects.  I also learned about some great tools that are must-haves for working with SilverLight or WPF.

One tool that is a must for understanding the details of what is happening within a running WPF application is Snoop.  This tool allows you to see the entire inheritance tree of all visual elements, with "actual" properties, values, and indicators so you know if the values are default, set, or inherited. 

I also learned a simple tip that helped solve a minor problem I was having.  I created a fancy title bar to replace the default title bar.  The problem was, one of my visual elements was over the buttons, causing only half of the button to respond to click events.  While there were likely many other ways to fix the problem, one simple property setting took care of the problem.  By setting IsHitTestVisible to False on the UI element, it no longer blocked the mouse down event from reaching the makeshift close and minimize buttons on my title bar.

Finally, there were a couple of differences between SilverLight and WPF that made a difference to me (though the full list is much bigger!)

• In SilverLight, you have a much more limited list of set Brushes to use when styling.  This was to keep the download package smaller.
• The default binding mode for SilverLight elements is OneWayToSource, while in WPF it is "default" (which varies by object type). 
• There is no native support for synchronized collection binding (IsSynchronizedWithCurrentItem or "/" notation to indicate "current item in collection".  This makes it more difficult to do "master-detail" binding, where you have a list view that automagically updates a detailed view.

I'm playing around with some list templates that should allow for some cool visual effects for both viewing and editing data.  I'll post them up when I get them done.

Joe

Enumerators Decorated with the Flag Attribute

An enumerator decorated with the [Flags] or [FlagAttribute] attribute (they are one in the same), is able to store a byte that represents the concatenation of all possible combinations of the enumeration.   Because of this concatenation it is required that the enumeration be numbered implementing a base 2 sequence (i.e. 0, 1, 2, 4).  As seen in the sample code this evaluates to the following base 2 numbers:

0 = 0000

1 = 0001

2 = 0010

4 = 0100

For the sake of simplicity we added another enum to represent when all of the flags have been selected All = 7

7 = 0111

The following code (a simple aspx page and its code-behind) will display a the uses of Flags as well as the differences from regular enums.  The code uses Bitwise OR to concatenate a flag (i.e. will toggle the bit on if either or both bits in the sequence are true (1)), as follows:

0010

0001

0011

The code also uses the the Bitwise Complement (~) operator as well as the Bitwise AND (&) operator to negate (remove) a specific flag (i.e. will toggle the bit off if both bits in the sequence are true (1)), as follows:

  0111

~0010

  0101

ASPX Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UsingEnums.aspx.cs" Inherits="UsingEnums" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Using Enumerators Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div style="text-align: center; width: 604px;">

        <asp:Label ID="lblProjectRequirements" runat="server" Text="Project Requirements (Pick up to two)"></asp:Label>

        <asp:CheckBox ID="chkAutoCorrect" runat="server" Text="Auto-correct" />

        <asp:CheckBoxList ID="cblProjectPreference" runat="server">

        </asp:CheckBoxList>

        <asp:Button ID="btnSelectRequirements" runat="server" Text="Select Requirements"

            OnClick="btnSelectRequirements_Click" Style="height: 26px" /><br />

        <asp:Label ID="lblResults" runat="server" Height="91px" Width="552px"></asp:Label><br />

        <asp:Button ID="btnCompare" runat="server" Text="Comapre Flags to Enums" OnClick="btnCompare_Click"

            Style="height: 26px" /><br />

        <asp:Label ID="lblFlagsDisplay" runat="server" Height="40px" Width="300px"></asp:Label>

        <asp:Label ID="lblEnumDisplay" runat="server" Height="40px" Width="300px"></asp:Label>

    </div>

    </form>

</body>

</html>

Code-Behind:

using System;

using System.Web.UI;

using System.Web.UI.WebControls;

[Flags]

public enum ProjectPreference

{

    /// <summary>

    /// No project requirements are selected

    /// </summary>

    None = 0,                   // (0000)

    /// <summary>

    /// The project must be done as inexpensively as possible

    /// </summary>

    Cheap = 1,                  // (0001)

    /// <summary>

    /// The project must be done as quickly as possible

    /// </summary>

    Fast = 2,                   // (0010)

    /// <summary>

    /// The project must be of the highest quality

    /// </summary>

    Good = 4,                   // (0100)

    /// <summary>

    /// This is an illegal value, since only two can be slected at one time

    /// </summary>

    All = Cheap | Fast | Good   // (0111)

}

// Based on Microsoft http://msdn.microsoft.com/en-us/library/system.flagsattribute.aspx

// Define an Enum without FlagsAttribute.

public enum SingleHue : short

{

    Black = 0,

    Red = 1,

    Yellow = 2,

    Blue = 4

}

// Define an Enum with FlagsAttribute.

[FlagsAttribute]

public enum MultiHue : short

{

    Black = 0,

    Red = 1,

    Yellow = 2,

    Blue = 4

}

public partial class UsingEnums : Page

{

    private const int SKIP_FOR_NONE_AND_ALL = 2;

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            // Get a string array of all the enum names

            string[] projectTypes = Enum.GetNames(typeof(ProjectPreference));

            // Don't display None or All enum values

            string[] displayProjectPreferences = new string[projectTypes.Length - SKIP_FOR_NONE_AND_ALL];

            int i = 0;

            foreach (string projectType in projectTypes)

            {

                if (

                        !projectType.Equals(ProjectPreference.None.ToString())

                    &&

                        !projectType.Equals(ProjectPreference.All.ToString())

                    )

                {

                    displayProjectPreferences[i++] = projectType;

                }

            }

            cblProjectPreference.DataSource = displayProjectPreferences;

            cblProjectPreference.DataBind();

        }

    }

    protected void btnSelectRequirements_Click(object sender, EventArgs e)

    {

        ProjectPreference selectedProjectPreferences = new ProjectPreference();

        try

        {

            // Gather all of the selected flags

            foreach (ListItem liProjectPreference in cblProjectPreference.Items)

            {

                if (liProjectPreference.Selected)

                {

                    // Concatenate the flag using the Enum.Parse method and the Bitwise OR (|) operator

                    selectedProjectPreferences |=

                        (ProjectPreference) Enum.Parse(typeof (ProjectPreference), liProjectPreference.Value, true);

                }

            }

            // if all have been selected then ...

            if (selectedProjectPreferences == ProjectPreference.All)

            {

                // if auto-correct is on remove the 'Cheap' flag

                if (chkAutoCorrect.Checked)

                {

                    // Use the Bitwise Complement (~) operator as well as the Bitwise AND (&)

                    // operator to negate (remove) a specific flag

                    selectedProjectPreferences &= ~ProjectPreference.Cheap;

                    foreach (ListItem liProjectPreference in cblProjectPreference.Items)

                    {

                        if (liProjectPreference.Value == ProjectPreference.Cheap.ToString())

                        {

                            liProjectPreference.Selected = false;

                        }

                    }

                }

                // otherwise throw an error

                else

                {

                    cblProjectPreference.SelectedIndex = -1;

                    throw new Exception("You can select no more than two project preferences.");

                }

            }

            lblResults.Text = selectedProjectPreferences.ToString();

        }

        catch (Exception ex)

        {

            // The flag enumeration evaluates to 'All' - no coding required

            string errMessage = string.Format("The error value of the flag enumeration is: {0}<br />",

                                              selectedProjectPreferences);

                    // Reset to 'None'

                    selectedProjectPreferences = ProjectPreference.None;

            errMessage += string.Format("{0}<br />The current value of the flag enumeration has been reset to: {1}",

                                            ex.Message, selectedProjectPreferences);

            lblResults.Text = errMessage;

        }

    }

    protected void btnCompare_Click(object sender, EventArgs e)

    {

        // Display all possible combinations of values.

        // Also display an invalid value.

        string flagDisplay = "<b>With</b> FlagsAttribute:<br />";

        for (int val = 0; val <= 8; val++)

        {

            flagDisplay += string.Format("{0,3} - {1}<br />", val, ((MultiHue)val));

        }

        lblFlagsDisplay.Text = flagDisplay;

        string enumDisplay = "<b>Without</b> FlagsAttribute:<br />";

        for (int val = 0; val <= 8; val++)

        {

            enumDisplay += string.Format("{0,3} - {1}<br />", val, ((SingleHue)val));

        }

        lblEnumDisplay.Text = enumDisplay;

    }

}

Using the [Flags] attribute allows you to let the code handle some of the display of the flags, as the ToString() method will return a comma seperated list of enums (e.g. "Cheap, Fast"),  it will also resolve the ToString() to an aggregated value (in this case to 'All' if all of the flags are selected) and finally in walking through code you will be able to hover and see the concatenated list of flags instead of a somewhat hard-to-decipher number.  That is, if you are walking through code after a breakpoint you can hover over a [Flags] enum and see "Cheap | Good" instead of "5", making debugging far easier.

That wraps it up for this time.  Hope it helps.

Filtering HTTP Responses

As part of a Microsoft Dynamics CRM implementation for a call center application, we were required to put a large number of edit controls on a single form, in order to satisfy the business requirement of having the fields available to the user during a call while keeping page navigation to a minimum.  While we were able to spread the controls across different tabs on the form, in order to improve navigation and user-friendliness, we ran into a browser performance issue in production whereby the page rendering slowed significantly due to the large number of controls on the form.  The problem was especially noticeable on machines with minimal RAM and slower CPUs.

We investigated several workarounds to this problem, but due to the requirement of CRM to only show one object per page, we were limited in the possible solutions we could implement.  One solution included breaking up the entity to several smaller entities, which would require changes to data structures and introduce extra navigation that would ultimately raise the average handling time (AHT) for a user processing a call.  Another solution involved using an IFrame in the form to host the data in our own page, which would give us the flexibility and performance we need, but at the price of a considerable development effort to implement the new page.

http://www.ondotnet.com/pub/a/dotnet/2003/10/20/httpfilter.html

JohnH

Four easy steps to implementing an ASP.NET AJAX Web Service

In this blog post I'm going to show you the four easy steps to implementing an ASP.NET AJAX Web Service:

• 1) Set-up a web service, this sample call invalidates any name beginning with the letter ‘a' (it is already taken), throws an exception for any name beginning with the letter ‘b' and validates all other names.

using System;

using System.Globalization;

using System.Web.Services;

namespace BlogDemo.Utility

{

    /// <summary>

    /// The BlogDemoService provides a sample web service to demonstrate AJAX client-side web service calls

    /// </summary>

    [WebService(Namespace = "http://syrinx.net/")]

    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

    Make sure to remove the following comment marks è // [System.Web.Script.Services.ScriptService]

    [System.Web.Script.Services.ScriptService]

    public class BlogDemoService : WebService

    {

        [WebMethod]

        public bool IsNameTaken(string checkName)

        {

            bool retVal = false;

            if (checkName.StartsWith("a", true, CultureInfo.CurrentCulture))

            {

                retVal = true;

            }

            else if (checkName.StartsWith("b", true, CultureInfo.CurrentCulture))

            {

                throw new Exception("Names cannot start with either the letter 'B' or 'b'");

            }

            return retVal;

        }

    }

}

N.B.: The asmx file must have the fully qualified name (including namespace) :

<%@ WebService Language="C#" CodeBehind="~/App_Code/BlogDemoService.cs" Class="BlogDemo.Utility.BlogDemoService" %>

• 2) Add the ScriptManager control to the page like so:

    <asp:ScriptManager ID="ScriptManager1" runat="server">

        <Services>

            <asp:ServiceReference Path="~/Services/BlogDemoService.asmx" />

        </Services>

    </asp:ScriptManager>

• 3) Add javascript to call the webservice - note that it requires a call to the fully qualified name

    <script type="text/javascript">

        function CheckNameAvailability(info)

        {

            var checkName = $get("txtUserName").value;

            BlogDemo.Utility.BlogDemoService.IsNameTaken(checkName, OnCheckNameSuccess, OnCheckNameFailure, info);

        }

        function OnCheckNameSuccess(results, userContext, methodName)

        {

            alert("Success using method: " + methodName);

            alert("This data was passed with the call: " + userContext);

            if(results)

            {

                alert("Name is taken");

            }

            else

            {

                alert("Name is available");

            }

        }

        function OnCheckNameFailure(exception, userContext, methodName)

        {

            alert("Failure using method: " + methodName);

            alert("This data was passed with the call: " + userContext);

            alert(exception._message);

        }

    </script>

The calls to and from the web service use the complete signature to the Web Service call.  The signature for the call into the Web Service is:
FullyQualifiedWebService. WebMethod(parameters, successfulCallback, failedCallback, userContext);

The successful callback signature is: function successfulCallback(result, userContext, methodName) {}

The failed callback signature is: function failedCallback(exception,userContext,methodName) {}.  The exception variable passed to the failedCallback is a JSON serialized Exception object, providing the description of the error and a full stack trace. This allows the developer to handle-and-re-throw exceptions on the server and still gracefully deal with them on the client.

The following web page shows how the information can be used - results are displayed using a simple alert as well as accessing a text box from the client side.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title>AJAX Web Service Page</title>

    <script type="text/javascript">

        var txtResultsID = '<% =txtResults.ClientID %>';

        function CheckNameAvailability(info)

        {

            var checkName = $get("txtUserName").value;

            BlogDemo.Utility.BlogDemoService.IsNameTaken(checkName, OnCheckNameSuccess, OnCheckNameFailure, info);

        }

        function OnCheckNameSuccess(results, userContext, methodName)

        {

            alert("Success using method: " + methodName);

            alert("This data was passed with the call: " + userContext);

            if(results)

            {

                $get(txtResultsID).value = "Name is taken";

            }

            else

            {

                $get(txtResultsID).value = "Name is available";

            }

        }

        function OnCheckNameFailure(exception, userContext, methodName)

        {

            alert("Failure using method: " + methodName);

            alert("This data was passed with the call: " + userContext);

            $get(txtResultsID).value = exception._message;

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <asp:ScriptManager ID="ScriptManager1" runat="server">

        <Services>

            <asp:ServiceReference Path="~/Services/BlogDemoService.asmx" />

        </Services>

    </asp:ScriptManager>

    <div>

        <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox><br />

        <asp:Button ID="btnSendBasicInfo" runat="server" Text="Send Basic" OnClientClick="CheckNameAvailability('Just the basics'); return false;" />

        <asp:Button ID="btnSendMoreInfo" runat="server" Text="Send More" OnClientClick="CheckNameAvailability('Very detailed information'); return false;" /><br />

        <asp:TextBox ID="txtResults" runat="server" Text="" Width="350px" ReadOnly="true"></asp:TextBox>

    </div>

    </form>

</body>

</html>

That's it for this time - hope you found it useful.

Converting Strongly Typed Collections to Generics: Part 3

In Part 1, the idea of converting your strongly typed collections to Generics was introduced.  Backward compatibility and existing client support was maintained along with compiler time type safety.  Part 2 demonstrated a reduction in code for new strongly typed collection classes utilizing lambda expressions and still maintaining compiler time type safety.  Here in part 3 Language Integrated Queries (Linq) will replace lambda expressions, compiler time type safety will of course be maintained.

Popular Projects

We occasionally see themes across our clients’ projects, here are two popular .NET development projects from 2008:

From Office to the Enterprise – Excel. Many companies develop financial models, marketing plans, and other crucial documents using Excel. Over time as more and more people want to use these documents (often at the same time), a pain point develops. People start e-mailing around the Excel file, “Do I have the right version?” is asked often, or file contention issues surface while trying to access the Excel file on a shared drive. Syrinx has helped clients move from linked spreadsheets to a .NET-based, enterprise-ready platform that allows scalability of both the number of simultaneous users and data storage/management.

From Office to the Enterprise – Access. Access is a powerful tool for developing and validating a home-grown database application, complete with data entry/edit/reporting. It is easy for business users to model their target process and build an application using Access. The limitations start to surface when people want to use mdb files simultaneously, or propagate application updates out to many users, or manage a distributed, standalone application as a single unit. Syrinx has helped clients “up-size” Access applications into ASP.NET and WPF applications, allowing users inside and outside the organization to access and manipulate data whenever they need to, while maintaining secure and centralized control over the application.

Team System Futures From Dev Connections

At the Dev Connections conference, Doug Seven gave a great presentation on Visual Studio Team System (VSTS) and Team Foundation Server (TFS). He started by discussing Agile development and the Agile Manifesto (see http://agilemanifesto.org/) and then highlighted how VSTS and TFS can be used to facilitate Agile development. What was nice to see was how different types of users, project planners, developers etc, can use different tools, Excel and VSTS, to view and edit the same data, allowing them to both work in a comfortable environment. He also showed how easily continuous integration and builds can be setup in a few mouse clicks.

Following on from this Doug gave us a glimpse of VSTS 2010 AKA Rosario. There are a couple of minor changes that facilitate Test Driven Development (TDD), For example in VS2008 you need to create a class and method before you can create a Unit Test - this goes against the TDD "rules" in that you are supposed to write the test first which makes you write the code. In VS2010 - you can now create the Unit Test first and then Right Click to create a class and method stub.

Another nice feature, so that we as developers no longer have to have the "token of shame" for breaking the build, is the notion of Gated Check in. With a gated check in policy, the developer checks in their code change and it is stored in a "Shelf Set". The shelf set is then merged locally and used to do a build; only if this build is completed is the code committed to the mainline code for use by other team members.

The builds in VS2010 will now be controlled using Windows Workflow, what was not clear to me is if the build workflow can then access the Windows SharePoint Services workflow environment to create or update WSS / SharePoint data. I can see this may be hard to do since they could potentially be running on different database servers.

Finally of note is the test case selection process, which can be used as part of the check in process. With VS2008 if you require unit tests to be run as part of a build after check in, this can take considerable time, with potentially the bulk of the tests not executing any of the new code. With VS2010, you can select to run a subset of the tests, with the subset being selected based on which tests will execute the newly checked in code. The test selection is performed using the test coverage information from the previous test runs. This should reduce the test case selection and time to execute making running unit tests as part of the check in very appealing.

Ian.

Converting Strongly Typed Collections to Generics: Part 2

In Part 1, the idea of converting your strongly typed collections to Generics was introduced.  Backward compatibility and existing client support was maintained.  However once a level of acceptance has been maintained for the new base class and the reduced amount of code in the collection classes, new development can utilize new constructs in c#3.0 which can reduce the code in your strongly typed collections even more, as well as eliminating the strongly typed collections all together.  See the code snippet below which shows the base generic class.

Converting Strongly Typed Collections to Generics: Part 1

Many enterprise applications make use of strongly typed collections in their solution.   Although they require more development time, the advantages they provide can outweigh the cost of the extra development time.  Some of the advantages include compile time and run time type safety, processor time savings from not having to downcast, the time to search and perform type checking, and sorting on specific properties.  .Net 1.1 contains base classes that provide all the typical methods and properties needed to build your own strongly typed collection class.  But as previously mentioned extra development time is needed to develop a collection class for each business class that can exist in multiple, simultaneous versions at run time.  This leads to higher development costs and higher maintenance costs.  Business requirements grow over time, and new properties can be added to business classes that require sorting at the application tier, thus needing development time to update the collection class to allow sorting on the new properties.  Also some properties can have their exposure increased and thus needing a new “FindBy…” method in the collection.
Much of this development time can be reduced using Generics, a new construct added to c# 2.0.  However when environments and projects must adhere to consistent standards, before new technologies make their way into new development cycles, sometimes legacy solutions will be converted to the new technology prior to new functionality using it.  Also if it is required to maintain either backward compatibility, or existing client code, removal of the existing strongly typed collections could be prohibited.  However reducing its code base and maintaining its existing signature is possible with Generics.

The topic of converting all your strongly typed collection classes will be discussed and broken up into 3 separate parts.  When large enterprise applications contain many strongly typed collection classes, converting them to Generics can contain large amounts of risk and could be separated into different steps that reduce risk, and keep a higher level of quality.

Here in Converting Strongly Typed Collections to Generics Part 1, the first step will be only to reduce the amount of code in each of the strongly typed collection classes by using Generics.  The basic structure of a non generic strongly typed collection class will be discussed with an example of all the usual parts.  Then the strongly typed collection class will have the majority of its code removed as well as changing its base class to use Generics, all the while maintaining backward compatibility.

Strongly Typed Collections

A strongly typed collection class has the typical methods and properties of any collection class but only accepts and processes 1 type.  For example, see the code snippet below that illustrates some typical methods.  Note this is only a snippet of the class; download the project to see the entire collection class as well as the complete example. The snippet below illustrates the typical properties and methods that a collection class and namely a strongly typed collection class will contain.

public class StudentCollection : BaseCollection
{
    public Student this[int index]
    {   //List handles throwing ArgumentOutOfRangeException
        get {return (Student)List[index];}
        set { List[index] = value; }
    }

    public int Add(Student student)
    {
        if (student == null)
        {
            throw new System.ArgumentNullException("student", "Cannot Add NULL to collection.");
        }
        // Is List a Unique list?
        if (IsUnique && List.Contains(student))
        {
            throw new System.ArgumentException("Cannot add duplicate entry to collection.");
        }
        return List.Add(student);
    }

    public StudentCollection FindByMajor(string major)
    {
        StudentCollection studentCollection = new StudentCollection();
        foreach (Student student in this.List)
        {
            if (student.Major == major)
            {
                studentCollection.Add(student);
            }
        }
        return studentCollection;
    }

Part 6 - Generating Some Classes (and an Interface)

In this installation of my blog series I'll cover creating integrated InterfaceObject, DataAccess and ApplicationObject (BusinessObject) classes based on the definition file that use these newly created stored procedures and provide an API to the users of our classes.

The Interface, Data Access and Application objects will be created using the field values from the CodeGeneratorPropertyList.  The interface will define the basic property signatures for each field.  The Data Access class will perform CRUD and list retrieval based on the stored procedures just created.  The Application Object will interact with the Data Access class using primitive values where possible and the generated interface when not.  So, when a new item is created the DataAccess would be expecting an instance of a class that implements the interface to be passed to it.  The Application Object will pass itself as it is just such a class.

The Application Object will also handle keeping track of whether or not it is dirty (a value has changed since instantiation) and whether or not it is new (the value does not yet exist in the database), s well as check some business rules.  It does this by inheriting from a base class that is standard within the code generator.  This will reduce unnecessary calls to persist unchanged items to the database or to try and delete items that do not yet exist.  The CheckRules method will initially just check that required fields are present and that fields with a given length (varchar, char, etc.) are not longer than allowed.

The Application Object and the Data Access classes will both use partial classes and partial methods to take advantage of the Code Generator's ability to generate new code while at the same time not losing any modified code.  The CheckRules method is a partial method.  The first time the file is generated the generator creates a method signature in the upper code generated class and then implements the actual method in the custom code section.  The generator does not do this if the file already exists so as not to lose any custom code.  Unfortunately, this means that any new rules for required fields or field length need to be implemented manually.

This will be changed in the future to make the CheckRules method a standard code method and add a call to a new partial method named CheckCustomRules.  This is a great example of one of the enormous advantages in creating and using a custom, code generator -the ability to incrementally improve it as time goes on.  This allows the developer to deliver more robust, re-usable code in less time.

The code to generate the interface is very straightforward.  It writes the using statements, the namespace and interface name using the CreateNameSpaceHeader method:

        private static string CreateNameSpaceHeader(string Namespace, string ClassName, IEnumerable<CodeGeneratorProperty> PropertyList)

        {

            StringBuilder retVal = new StringBuilder();

            retVal.Append(string.Format("using System;{0}", CodeGenerationHelper.GetEndOfLine()));

            foreach (CodeGeneratorProperty property in PropertyList)

            {

                if (property.DataType.EndsWith("List"))

                {

                    retVal.Append(string.Format("using System.Collections.Generic;{0}", CodeGenerationHelper.GetEndOfLine()));

                    break;

                }

            }

            retVal.Append(CodeGenerationHelper.GetEndOfLine());

            retVal.Append(string.Format("namespace {0}{1}", Namespace, CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(string.Format("{{{0}", CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}///<summary>{1}", CodeGenerationHelper.GetTabIndent(1),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}///The public interface for the {1} object{2}", CodeGenerationHelper.GetTabIndent(1),

                              ClassName, CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}///</summary>{1}", CodeGenerationHelper.GetTabIndent(1),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(string.Format("\tpublic interface I{0} " + CodeGenerationHelper.GetEndOfLine(), ClassName));

            retVal.Append(string.Format("\t{{{0}", CodeGenerationHelper.GetEndOfLine()));

            return retVal.ToString();

        }

 and then iterates through the CodeGeneratorPropertyList to create the property signatures using the CreateAccessorSignatures method:

        private static string CreateAccessorSignatures(IEnumerable<CodeGeneratorProperty> PropertyList)

        {

            StringBuilder retVal = new StringBuilder();

            retVal.Append(

                string.Format("{0}#region Property Signatures                 {1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            foreach (CodeGeneratorProperty property in PropertyList)

            {

                retVal.Append(

                    string.Format("{0}/// <summary>{1}",

                                  CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));

                retVal.Append(

                    string.Format("{0}/// {1}{2}",

                                  CodeGenerationHelper.GetTabIndent(2), property.Description, CodeGenerationHelper.GetEndOfLine()));

                retVal.Append(

                    string.Format("{0}/// </summary>{1}",

                                  CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));

                if (property.DataType.EndsWith("List"))

                {

                    retVal.Append(

                        string.Format("{0}List<{1}> {2}{3}",

                                      CodeGenerationHelper.GetTabIndent(2),

                                      property.DataType.Substring(0, property.DataType.LastIndexOf("List")),

                                      property.PropertyName,

                                      CodeGenerationHelper.GetEndOfLine()));

                }

                else

                {

                    retVal.Append(

                        string.Format("{0} {1} {2}{3}",

                                      CodeGenerationHelper.GetTabIndent(2), property.DataType, property.PropertyName,

                                      CodeGenerationHelper.GetEndOfLine()));

                }

                retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(2));

                if (property.IncludeGet)

                {

                    retVal.Append( string.Format("{0} get;{1}",

                                  CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));

                }

                if (property.IncludeSet)

                {

                    retVal.Append( string.Format("{0} set;{1}",

                                  CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));

                }

                retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(2));

                retVal.Append( CodeGenerationHelper.GetEndOfLine());

            }

            retVal.Append(

                string.Format("{0}#endregion Property Signatures{1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            return retVal.ToString();

        }

This method also handles the use of List classes that are typically foreign key based collections (e.g UserAddresses is a UserAdressList that has the UserId as the Foreign Key).

The Data Access generation is also very straightforward but with a few more moving parts.  It also creates the header in the same way but then generates the constants that reference the stored procedures created for the CRUD operations, it then uses the CodeGeneratorPropertyList to create Data Access methods (Find, Fetch, Add, Change and Remove) - these methods will be called by the Application Object methods as follows:

A good example of the code generator method for the Data Access class would be CreateAddMethod:

        private static string CreateAddMethod(string ClassName, IEnumerable<CodeGeneratorProperty> PropertyList)

        {

            StringBuilder retVal = new StringBuilder();

            retVal.Append(

                string.Format("{0}#region Add Data               {1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}// called to add new data into the database{1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}internal static bool Add(ref int insertedId, I{1} {1}Object){2}",

                              CodeGenerationHelper.GetTabIndent(2), ClassName, CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}using (SqlConnection cn = ConnectionManager.OpenConnection()){1}",

                              CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}try{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}using (SqlCommand cm = cn.CreateCommand()){1}", CodeGenerationHelper.GetTabIndent(5),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(5), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}cm.CommandType = CommandType.StoredProcedure;{1}",

                              CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}cm.CommandText = INSERT_SPROC;{1}", CodeGenerationHelper.GetTabIndent(6),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}cm.Parameters.Add(\"returnValue\", SqlDbType.Int);{1}",

                              CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format(

                    "{0}cm.Parameters[\"returnValue\"].Direction = ParameterDirection.ReturnValue;{1}",

                    CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(CreateParameterList(ClassName, PropertyList, 6));

            retVal.Append(

                string.Format("{0}cm.ExecuteNonQuery();{1}", CodeGenerationHelper.GetTabIndent(6),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}insertedId = SafeData.ConvertInt(cm.Parameters[\"@{1}{2}\"].Value);{3}",

                              CodeGenerationHelper.GetTabIndent(6),

                              CommonHelper.GetVariableAbbreviation(CommonHelper.GetPrimaryKey(PropertyList)),

                              CommonHelper.GetPrimaryKeyName(PropertyList), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}if (Convert.ToInt16(cm.Parameters[\"returnValue\"].Value) != 0){1}",

                              CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}return false;{1}", CodeGenerationHelper.GetTabIndent(7),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}else{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}return true;{1}", CodeGenerationHelper.GetTabIndent(7),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(5), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}catch (Exception ex){1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}SimpleLogger.LogError(ex.Message);{1}", CodeGenerationHelper.GetTabIndent(5),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}return false;{1}", CodeGenerationHelper.GetTabIndent(5),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}#endregion Add Data      {1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            return retVal.ToString();

        }

Each of the methods works in the same fashion, creating the method and where necessary calling a helper method named CreateParameterList to generate each Property as it is needed.  The List class has the same process applied to it but only for the SelectList and SelectListByForeignKey methods.  The SelectListByForeignKey are generated for each Foreign Key individually, a future enhancement might be to define multiple key foreign keys in the code generator and have those methods generated as well.  Currently this has to be done manually.

Finally the Application Object generator works in a fashion that combines both of the previous generators.  It creates The Namespace Header as before, it creates the Property accessors, much like the Interface generator, except this time it implements the actual accessor not just the signature and it implements the corresponding public methods listed in the table above.  It implements constructors which as well.  Additionally, the Application Object performs a rules check before allowing the object to be persisted.  The CheckRules method is generated as follows:

        private static string CreateCheckRules(string input, IEnumerable<CodeGeneratorProperty> PropertyList)

        {

            StringBuilder retVal = new StringBuilder();

            retVal.Append(

                string.Format("{0}/// <summary>{1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}/// All business rules for this class are checked in this method{1}",

                              CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}/// </summary>{1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append(

                string.Format("{0}public void CheckRules(){1}", CodeGenerationHelper.GetTabIndent(2),

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(2));

            // Walk through property list and create rules

            retVal.Append(CommonHelper.GetRulesForRequiredProperties(3, PropertyList));

            retVal.Append(CodeGenerationHelper.GetStandAloneClosingBracket(2));

            return (input + retVal);

        }

As one can see, the work for this method is mostly performed in the GetRuleForRequiredProperties helper method:

        public static string GetRulesForRequiredProperties(int TabIndent, IEnumerable<CodeGeneratorProperty> PropertyList)

        {

            string retVal = string.Empty;

            foreach (CodeGeneratorProperty property in PropertyList)

            {

                if (property.IsRequired)

                {

                    switch (property.DataType)

                    {

                        case "int":

                            retVal += GetRequiredInteger(TabIndent, property);

                            break;

                        case "Boolean":

                            retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";

                            break;

                        case "string":

                            retVal += GetRequiredString(TabIndent, property);

                            break;

                        case "byte[]":

                            retVal += GetRequiredByteArray(TabIndent, property);

                            break;

                        case "Guid":

                            retVal += GetRequiredGuid(TabIndent, property);

                            break;

                        case "DateTime":

                            retVal += GetRequiredDate(TabIndent, property);

                            break;

                        case "Double":

                            retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";

                            break;

                        case "Decimal":

                            retVal += GetRequiredDecimal(TabIndent, property);

                            break;

                        default:

                            retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";

                            break;

                    }

                }

            }

            return retVal;

        }

This in turn uses other helper methods, such as GetRequiredString:

        private static string GetRequiredString(int TabIndent, CodeGeneratorProperty property)

        {

            StringBuilder retVal = new StringBuilder();

            retVal.Append(

                string.Format("{0}if(String.IsNullOrEmpty(_{1})){2}", CodeGenerationHelper.GetTabIndent(TabIndent),

                              property.PropertyName,

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(TabIndent);

            retVal.Append(

                string.Format("{0}BrokenRules.Add(\"{1} is a required field and cannot be null or empty.\");{2}",

                              CodeGenerationHelper.GetTabIndent(TabIndent + 1), property.PropertyName,

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(TabIndent));

            // Greater than max length

            retVal.Append(

                string.Format("{0}if(_{1}.Length > {2}){3}", CodeGenerationHelper.GetTabIndent(TabIndent),

                              property.PropertyName, property.Size,

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(TabIndent));

            retVal.Append(

                string.Format("{0}BrokenRules.Add(\"{1} has exceeded its maximum length of {2} characters.\");{3}",

                              CodeGenerationHelper.GetTabIndent(TabIndent + 1), property.PropertyName, property.Size,

                              CodeGenerationHelper.GetEndOfLine()));

            retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(TabIndent));

            return retVal.ToString();

        }

As you can see not every DataType is handled, again, this being a personal code generator it can evolve as needed, instead of having to cover every scenario.

That wraps it up for this installation.  We now have the ability to generate a table, its related stored procedures, a related interface, a related data access class and an application object class that exposes an API for any user. In the next and final installation of this posting we'll look at pulling it all together and writing a little "calorie counter" application to see how it works.

Part 5 - Creating Stored Procedures using SQL Server Management Objects (SMO)

In this installation of my blog series I'll cover defining, creating and persisting SQL scripts for stored procedures based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO).  As I stated at the end of my last post, this takes an entirely different approach than table generation.  Tables are created and then the script is generated from the execution, stored procedures, on the other hand, require us to create the sql script and then execute it against the server.

The code generator will iterate the CodeGenratorPropertyList to generate the four basic CRUD stored procedures (Create, Retrieve, Update and Delete) as well as a generic Exists stored procedure and a generic SelectList stored procedure that returns all of the records and finally Foreign key based stored procedures.  That is, if a field is designated as a foreign key in a table then we will create a stored procedure that retrieves a list based on that Foreign key.  So, for example, the UserAddress table might have the UserId as a foreign key, in this case we would like to generate a SelectList_ByUserId stored procedure to use later on in our classes.

All of the stored procedure code will be generated using a standard layout and indenting to make it readable.  This includes the following:

• a standard DROP clause with a successful ‘dropped' print message

USE [NAMESPACE]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

/*

 * Drop Stored procedure if it exists

 */

IF EXISTS

       (

              SELECT

                     *

              FROM

                     sysobjects

              WHERE

                     id = object_id(N'[NAMESPACE_User_Select_ByUserId]')

              AND

                     OBJECTPROPERTY(id, N'IsProcedure') = 1

       )

       BEGIN

              DROP PROCEDURE [NAMESPACE_User_Select_ByUserId]

              IF @@ERROR = 0

                     BEGIN

                           PRINT '<<NAMESPACE_User_Select_ByUserId stored procedure was dropped successfully>>'

                     END

       END

GO

• A header

/*

************************************************************************************************************

*

* Name: NAMESPACE_User_Select_ByUserId

*

* Sample Call:

       NAMESPACE_User_Select_ByUserId '10228341-4204-4ddd-9f83-592e4c35cf2b'

*

* ----------------------------------------------------------------------------------------------------------

*

* This Procedure Called by .NET class methods: 

*             NAMESPACE.User.Fetch() 

*

* ----------------------------------------------------------------------------------------------------------

*

* Modification History:

*

* Date        Developer                Description

* ----------------------------------------------------------------------------------------------------------

* 2/12/2008          John P. Frampton               Created

*

************************************************************************************************************

*/

• the body of the procedure with basic error handling

CREATE PROCEDURE

       NAMESPACE_User_Select_ByUserId

              (

                     @uidUserId           uniqueidentifier

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       SELECT

               [UserId]

              ,[ParentUserId]

              ,[FirstName]

              ,[LastName]

              ,[PersonalUrl]

       FROM

              [dbo].[NAMESPACE_User]

                     WITH

                           (NOLOCK)

       WHERE

              [UserId] = @uidUserId

       /*

        * Check for errors and send return value

        */

       SELECT @v_intError = @@ERROR

       IF @v_intError <> 0 

              BEGIN

                     -- DEBUG 

                     --PRINT 'Returned -1 -- Undefined Error'

                     RETURN -1

              END

       ELSE

              BEGIN

                     -- DEBUG 

                     --PRINT 'Returned 0 -- Success'

                     RETURN 0

              END

END

GO

• a successful ‘generated' print message.

IF @@ERROR  = 0

       BEGIN

              PRINT '<<NAMESPACE_User_Select_ByUserId stored procedure was created successfully>>'

       END

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS OFF 

GO

GRANT EXECUTE ON [NAMESPACE_User_Select_ByUserId] TO [public]

GO

The generic select list is the easiest stored procedure to generate as it is a simple select with no parameters.  The body of that stored procedure would look like this:

CREATE PROCEDURE

       NAMESPACE_User_SelectList

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       SELECT

               [UserId]

              ,[UserId]

              ,[FirstName]

              ,[LastName]

              ,[PersonalUrl]

       FROM

              [dbo].[NAMESPACE_User]

                     WITH

                           (NOLOCK)

The Exists, Select and Delete stored procedures are also fairly straightforward as they each are passed the primary key as the lone parameter.  The Select statement is a detailed list of all the desired fields (as opposed to the ever-dangerous * method for returning everything).  The SELECT stored procedure  will look like this:

CREATE PROCEDURE

       NAMESPACE_User_Select

              (

                     @intUserId           int

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       SELECT

               [UserId]

              ,[ParentUserId]

              ,[FirstName]

              ,[LastName]

              ,[PersonalUrl]

       FROM

              [dbo].[NAMESPACE_User]

                     WITH

                           (NOLOCK)

       WHERE

              [UserId] = @intUserId

The Exists statement returns the count of records matching the passed in id (0 = false - does not exist, >=1 = true - does exist).  It will look like this:

CREATE PROCEDURE

       NAMESPACE_User_Exists

              (

                     @intUserId           int

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       SELECT

              COUNT(*) AS 'ExistsFlag'

       FROM

              [dbo].[NAMESPACE_User]

                     WITH

                           (NOLOCK)

       WHERE

              [UserId] = @intUserId

The Delete method deletes a record based on the Primary key parameter and it looks like this:

CREATE PROCEDURE

       NAMESPACE_User_Delete

              (

                     @intUserId           int

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       DELETE

              [dbo].[NAMESPACE_User]

       WHERE

              [UserId] = @intUserId

The insert stored procedure takes in every field in the table as a parameter, including the Primary key as an OUTPUT parameter that will be returned to the caller after it has been populated.  Each field that can be null will also have its default value set to null.  It will look like this:

CREATE PROCEDURE

       NAMESPACE_User_Insert

              (

                      @intUserId          int OUTPUT

                     ,@uidParentUserId    uniqueidentifier    

                     ,@strFirstName             nvarchar(50)         =      NULL

                     ,@strLastName        nvarchar(256)        =      NULL

                     ,@strPersonalUrl     nvarchar(256)        =      NULL

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       INSERT INTO [dbo].[NAMESPACE_User]

       (

               [ParentUserId]

              ,[FirstName]

              ,[LastName]

              ,[PersonalUrl]

       )

       VALUES

       (

               @uidParentUserId

              ,@strFirstName

              ,@strLastName

              ,@strPersonalUrl

       )

       SET @intUserId = SCOPE_IDENTITY()

The Update stored procedure will also take in all fields in the table in a likewise manner, except that the Primary key will not be an OUTPUT variable as it can't be changed by definition.  It looks like this:

CREATE PROCEDURE

       NAMESPACE_User_Update

              (

                      @intUserId          int

                     ,@uidParentUserId    uniqueidentifier

                     ,@strFirstName             nvarchar(50)         =      NULL

                     ,@strLastName        nvarchar(256)        =      NULL

                     ,@strPersonalUrl     nvarchar(256)        =      NULL

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       UPDATE

              [dbo].[NAMESPACE_User]

       SET

               [ParentUserId]      =      @uidParentUserId

              ,[FirstName]         =      @strFirstName

              ,[LastName]          =      @strLastName

              ,[PersonalUrl]              =      @strPersonalUrl

       WHERE

              [UserId] = @intUserId

Finally, the Foreign Key Select list stored procedures will follow this pattern:

CREATE PROCEDURE

       NAMESPACE_User_SelectList_ByParentUserId

              (

                     @uidParentUserId           int

              )

AS

SET NOCOUNT ON

BEGIN

       DECLARE @v_intError AS int

       SELECT

               [UserId]

              ,[ParentUserId]

              ,[FirstName]

              ,[LastName]

              ,[PersonalUrl]

       FROM

              [dbo].[NAMESPACE_User]

                     WITH

                           (NOLOCK)

       WHERE

              [ParentUserId] = @uidParentUserId

Now that stored procedures have been generated they can be executed against the server using the following code:

        private static void CreateProcedure(ProcedureType ProcType, string ClassName, string FolderName, string Prefix,

                            IEnumerable<CodeGeneratorProperty> PropertyList, string ForeignKeyName,

                            bool PersistToDatabase, Database SelectedDatabase)

        {

            // Get the name of the stored procedure and the file to which it iwll be persisted

            string sprocName = GetStoredProcedureName(ProcType, Prefix, ClassName, ForeignKeyName);

            string fileName = string.Format("{0}\\{1}{2}", FolderName, sprocName, SQL_FILE_EXTENSION);

            // Generate all of the different parts of the procedure

            string procedureContent = string.Empty;

            procedureContent += CreateDrop(sprocName, SelectedDatabase);

            procedureContent += CreateHeader(ProcType, Prefix, ClassName, ForeignKeyName);

            procedureContent += CreateBody(ProcType, Prefix, ClassName, PropertyList, ForeignKeyName);

            procedureContent += CreateClose(ProcType, Prefix, ClassName, ForeignKeyName);

            // Persist it to a physical file

            CodeGenerationHelper.WriteFile(fileName, procedureContent);

            // Execute it against the database if it is to be persisted there as well

            if (PersistToDatabase)

            {

                SelectedDatabase.ExecuteNonQuery(procedureContent);

            }

        }

That wraps it up for this installation, next we'll look at creating integrated InterfaceObject, DataAccess and ApplicationObject (BusinessObject) classes based on the definition file that use these newly created stored procedures and provide an API to the users of our classes.