Bracora

Moved

2010-08-06T14:40:00.001+10:00

In the process of moving my content to blogger..coming soon.

Mastering iTunes using Linq To Xml

2008-08-19T16:34:00.000+10:00

Last updated : 19th August 2008

Background

Lets us assume for this scenario that we are adding a new feature to our web dating web site. Yes, let's presume that we already match people based on their interests etc but what if we could match them based on the music they like. In fact, what if we could scan their iTunes library and see what kind of music they listen to most often and match this against other daters listening habits? Wouldn't that be a good proposition?

Just about everyone I know has an apple music product of some description which inevitably means they will have iTunes installed to manage this. But how do we get to this information? Fortunately, all this music data is stored within an XML document maintained by iTunes. The format of this data is not ideal though. The music data is stored within a <dict> node that contains all of its data in a pair of nodes that are similar in structure to a key value pair. I have shown this below.

Strategy

To enable this matching we need to provide a facility to upload a daters iTunes Music Library information which includes all the music they have along with all the usage data as well. This will allow us to search through this information and use it on our web site. All this information is stored in an XML file called, funny enough, iTunes Music Library. Now, to help us extrapolate this data we will use a nice new feature of the .Net Framework 3.5 called Language Integrated Query (LINQ). More specifically we'll use LINQ to XML, as this provides a simple way to interrogate XML data using simple queries which look a lot like standard SQL.

Implementation

Before we start, let's have a look at how the final web site will look. In the figure below you will see that users are provided with a simple interface to upload their iTunes XML file. When they click on the Find Me My Match button, the system will :

Upload this file
Parse the file for the current users favourite tracks
Match this against a simulated database of users
The system will then make a decision about who would be the best match

In Visual Studio we need to create a new web application. We need to make sure that we select .Net framework 3.5 and we will use VB.NET for this demo.

To save a load of time on the user interface I downloaded a free web site template from AnVisionWebTemplates.com. This template gives us the html blueprint for our design and we only need to add the upload functionality into the overall website application. You would normally spend a bit of time to take out the common html elements and place them into a master page or pages. I have just copied this into my directory structure and modified the aspx page that I'll be using. You can download the files in this article from the link provided above.

The Project structure should resemble the figure below:

I have created a simple XML file called userdb.xml and this is simulating a database of users that we may have on our dating website. The structure of this file is shown below:

Next we want to create a class to do all the work. In the App_Code folder I created a new class called Bracora.Matchmarker that will be implementing the couple of static methods we need.

       
                
    ''' <summary>
    ''' General class for demo
    ''' </summary>
    ''' <remarks>Note: you would want to seperate the logic out into seperate classes (tiers)
    ''' in a real world application
    ''' </remarks>
    Public NotInheritable Class MatchMaker
        ''' <summary>
        ''' </summary>
        ''' <remarks>Don't instantiate</remarks>
        Private Sub New()

        End Sub

Now let's start using some of this lovely Linq functionality. The first method I want to implement is the one to upload all the user information in the usersdb XML file and use this as the basis for matching later on. For this simple demo, to match music I am merely looping through this information and performing a basic string comparison so I am returning a general IEnumerable interface for my simulated database.

As you can see from the code below, I simply load the data into a XDocument object that is part of the Linq namespace. This allows me to run queries against this data as if it were held in a relational database. The query syntax is similar to SQL but slightly more intuitive. Another nice feature of .net 3.5 is the ability to return anonymous types. In the query below I am returning a strongly typed collection of objects containing two properties, userId and track. The compiler does all the hard work and saves me having to define this class just to return these values.

       
        
        ''' <summary>
        ''' Get all the data we have in our simulated database. We would normally keep all this
        ''' information in a database. But this is fine for the demo.
        ''' </summary>
        ''' <returns>A list of tracks an associated user ID's</returns>
        ''' <remarks>Just hard coding values for the demo</remarks>
        Public Shared Function GetAllMusic() As IEnumerable

            Dim databasePath = HttpContext.Current.Server.MapPath("App_Data") + "/userdb.xml"

            If System.IO.File.Exists(databasePath) Then

                Dim userdb = XDocument.Load(databasePath)

                ' Just return a list of our simulated users
                Dim dbSongs = From s In userdb...<song> _
                              Order By s.<UserID>.Value _
                              Select _
                                s.<UserID>.Value, _
                                s.<track>.Value

                Return dbSongs
            Else
                Return Nothing
            End If
        End Function

The next thing I need to do is create a method that will read through an uploaded iTunes library document and grab the top 25 songs most often played. Again, using Linq to XML this is an easy task.

       
        
        ''' <summary>
        ''' Process the uploaded file and get the top tracks baced on how often they
        ''' are played. In a real world we perform this processing offline.
        ''' </summary>
        ''' <param name="iTunesFile">The file location of the uploaded iTunes file</param>
        ''' <returns>A list of top songs for that user</returns>
        ''' <remarks>We are only taking the bare minimun information for this example.
        ''' Note how LINQ feturns a generic list of anonymous types without us having to
        ''' create a class definition for this ;-)
        ''' </remarks>
        Public Shared Function GetUsersTopTracks(ByVal iTunesFile As String) As IEnumerable

            If System.IO.File.Exists(iTunesFile) Then
                Dim iTunes = XDocument.Load(iTunesFile)

                ' Drill into the iTunes XML structure to get the music information
                ' Transform it into a more meaningful XML structure
                Dim tracks = From t In iTunes.<plist>.<dict>.<dict>.<dict> _
                             Select New XElement("song", _
                                 From key In t.<key> _
                                 Select New XElement(key.Value.ToString.Replace(" ", ""), _
                                 CType(key.NextNode, XElement).Value.ToString))

                ' Get the top 25 most played songs
                Dim topTracks = From song In tracks _
                                Order By song.<PlayCount>.Value Descending _
                                Take (25) _
                             Select _
                                Artist = song.<Artist>.Value, _
                                TrackName = song.<Name>.Value

                Return topTracks
            Else
                Return Nothing
            End If

        End Function

There are a couple of things to note here. You will notice that I am drilling into the iTunes XML structure using what is known as Child Axis properties. VB.NET provides me with this syntax. The line of code (iTunes.<plist>.<dict>.<dict>.<dict>) is basically me drilling into where the music information is stored within the file hierarchy. I don't like the way iTunes manages this data so I am taking this raw information and transforming it into an XML structure that works for me. After I do this I am then sorting on descending Playcount value and then taking the top 25 songs. Again, I am simply returning a strongly typed collection of objects that contain 2 properties, Artist and Trackname.

And now for the matching routine....

To hold the matching data I have created a simple class.

       
Namespace Bracora.Demo
    Public Class MatchedEntry
        Public Sub New()
        End Sub

        Private _userID As String
        Public Property UserId() As String
            Get
                Return _userID
            End Get
            Set(ByVal value As String)
                _userID = value
            End Set
        End Property

        Private _trackName As String
        Public Property TrackName() As String
            Get
                Return _trackName
            End Get
            Set(ByVal value As String)
                _trackName = value
            End Set
        End Property

    End Class
End Namespace

All I want to do now is pass in two lists to a routine, one with the top 25 music tracks for the current user and another list that contains our simulated database with all of our stored users favourite selections. We then loop through the data and store all matches in a generic list of MatchedEntry objects. From here we run a Linq query to group and sort the matches based on the most 'hits' and then we select the first or top match.

If we have a valid match at this stage, we then execute another Linq query that gathers all the music tracks that were common to both users. So we now have a userId and an array of tracks that were matched (Note the .ToArray() method on the Linq object). Now thank you .net 3.5, we now just create an anonymous type with this information and return it back :-)

       
                                            
        ''' <summary>
        ''' Attempts to find the most matched user based on checking their music information
        ''' against the music information we have in our simulated database
        ''' </summary>
        ''' <param name="userTracks">A list of user tracks to match</param>
        ''' <param name="dbTracks">A list of music tracks against users we have in our 'database'</param>
        ''' <returns>The best matched userID or nothing for no matches</returns>
        ''' <remarks>Don't try this at home. Honestly there are much better ways to do this!</remarks>
        Public Shared Function GetTopMatchedUser(ByVal userTracks As IEnumerable, _
                                                 ByVal dbTracks As IEnumerable) As Object

            ' contains a list of non-unique userIDs when songs are matched
            Dim MusicMatches As New Generic.List(Of MatchedEntry)
            Dim Match As MatchedEntry = Nothing

            ' loop round each of the users favourite tracks and try to match this
            ' against the list of tracks we have in our  simulated database. If
            ' a match occurs the add the userId. The more instances of that userId
            ' then the more music they have in common.
            For Each track In userTracks
                For Each song In dbTracks
                    If track.TrackName = song.track Then
                        Match = New MatchedEntry()
                        Match.UserId = song.UserID
                        Match.TrackName = song.track
                        MusicMatches.Add(Match)
                    End If
                Next
            Next

            ' Group by the userID and then work out the count for each one
            Dim top = From u In MusicMatches _
                        Group By u.UserId Into g = Group _
                        Order By g.Count() Descending _
                      Select _
                            UserId, _
                            NumberOfMatches = g.Count()

            If top.Count() > 0 Then

                Dim topMatchedUser = top.First()

                ' Get the matched songs based on the userId
                Dim matchedSongs = From u In MusicMatches _
                                    Where u.UserId = topMatchedUser.UserId _
                                   Select u.TrackName Distinct

                Dim MatchedUser = New With {.User = topMatchedUser.UserId, .Songs = matchedSongs.ToArray}

                Return MatchedUser

            End If

            Return Nothing
        End Function

To finish up with this class we need to create a controller routine that ties all these methods together.......

       
       
        ''' <summary>
        ''' Main entry point for the demo and controller 
        ''' </summary>
        ''' <remarks></remarks>
        Public Shared Function FindMeMyMatch(ByVal fileName As String) As Object

            Dim userSongs As IEnumerable = GetUsersTopTracks(fileName)
            Dim allUsersSongs As IEnumerable = GetAllMusic()

            Return GetTopMatchedUser(userSongs, allUsersSongs)

        End Function

Now, all we need to do now is implement the user interface. Therefore we need a FileUpload control, a few labels, an image control to show matched users and a button to trigger the whole thing off. The full details are contained in the download and I would encourage you to get it and run the application as there are no database requirements. Below is the main routine that uploads the iTunes file and displays the outcomes, either matched or unmatched.

One point to note is that ASP.NET constrains the upload size by default. As iTunes library files can be quite large, you'll need to modify the web.config file to increase the allowed upload size.

       

        <!-- need to increase the size of the permitted upload size -->
        <httpRuntime maxRequestLength="20480" />

Here is the code behind for the web page. It's pretty straight forward.

We check that it's a proper XML file
We generate a new unique filename and save the uploaded file
We then pass the filename to the matching routine and await our fate!

I have coded some simple display logic for the demo. But we either match or we don't and display accordingly.

       
    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim savePath As String = HttpContext.Current.Server.MapPath("uploads") + "/"

        If (FileUpload1.HasFile) Then
            'make sure it's an XML file
            If (FileUpload1.PostedFile.ContentType.ToLower <> "text/xml") Then
                message.Text = "This is not a valid iTunes file. Please make sure you upload the proper file."
                Exit Sub
            End If

            ' create a unique name for the uploaded file.
            Dim fileName As String = Guid.NewGuid().ToString + ".xml"

            ' Append the name of the file to upload to the path.
            savePath += fileName

            FileUpload1.SaveAs(savePath)

            Dim MyMatch = Bracora.Demo.MatchMaker.FindMeMyMatch(savePath)
            Dim HeaderText As String = String.Empty
            Dim ImageOfMatchedUserUrl As String = String.Empty
            Dim DisplaySongs As String = String.Empty
            If MyMatch IsNot Nothing Then
                Select Case MyMatch.User
                    Case "1"
                        ImageOfMatchedUserUrl = "App_Themes/Standard/images/user1.jpg"
                    Case "2"
                        ImageOfMatchedUserUrl = "App_Themes/Standard/images/user2.jpg"
                    Case "3"
                        ImageOfMatchedUserUrl = "App_Themes/Standard/images/user3.jpg"
                    Case Else
                        ImageOfMatchedUserUrl = "App_Themes/Standard/images/user4.jpg"
                End Select

                For i As Int32 = 0 To MyMatch.Songs.Length() - 1
                    DisplaySongs += String.Format("<li>{0}</li>", MyMatch.Songs(i))
                Next
                HeaderText = String.Format("<h3>{0}</h3>", "Success, we found a match!")
            Else
                ' No matches show the user who has been waiting longest :-)
                ImageOfMatchedUserUrl = "App_Themes/Standard/images/user4.jpg"
                DisplaySongs = String.Format("<li>{0}</li>", "You have no songs in common, here is your closest match")
                HeaderText = String.Format("<h3>{0}</h3>", "No luck I'm afraid!")
            End If

            Me.headerMatch.Text = HeaderText
            Me.songList.Text = DisplaySongs
            imageOfMatchedUser.ImageUrl = ImageOfMatchedUserUrl
            imageOfMatchedUser.Visible = True
        Else
            ' Error message for no file
            message.Text = "<p class=""error"">You did not specify an iTunes file to upload.</p>"
        End If
    End Sub

And that's all there is to it. Linq to Xml is a wonderfully powerful addition to the .net framework.

Summary

Hopefuly this tutorial has shown how you might use Linq in your own applications. Linq to XML is really nice feature and makes working with XML documents really easy. The leaning curve is very smooth and you can quickly pick up the core concepts without too much effort and grow into the more complex aspects of Linq namespace later on.

The code download also contains a few sample iTunes in the upload directory.

Note: iPod® and iTunes® are registered trademarks of Apple Inc.

Generic GridView Delete Strategy

2007-11-05T16:47:00.000+10:00

Last updated : 5th November 2007

Background

There are a number of ways to implement a JavaScript delete confirmation in a GridView. Most of these involve changing the command columns on the GridView into template columns. From here you can either add the onClientClick code to the template directly or search for the control on the row data bound event of the gridview.

Below is a way to get this done in a re-usable way without having to change each pages GridView command columns.

Strategy

We want to create a shared (VB) or static (C#) method that can be used by all pages within the web application. We will still utilise the RowDataBound event but we will encapsulate this functionality and call the method with a single line of code.

We will be using a ObjectDataSource connected to a Gridview to demonstrate this.

Implementation

I have extracted a slimmed version of the Categories table in Northwind and saved it as XML for this article. I have provided the source code so I'll not be covering the simple Data object class.

Firstly we want to create a re-usable routine that can be called from any page. Each page may vary the command column location on the GridView. Some users like this to be the first column on the left, others prefer the 'Edit', 'Delete' commands to be on the right. Therefore we need to keep this flexible to prevent excessive control look-ups on the RowDataBound events. The completed code is shown below:


Public NotInheritable Class GridViewHelper

    Private Sub New()
    End Sub

    ''' <summary>
    ''' Adds a javascript pop-up delete confirmation message to ANY LinkButton control 
    ''' found in the passed column index that has a text value of Delete
    ''' </summary>
    ''' <param name="deleteColumnIndex">the column you wish to 
    ''' restrict your search for a Delete LinkButton control</param>
    ''' <param name="e">The GridView row agruments when the GridView is in RowDataBound</param>
    ''' <remarks></remarks>
    Public Shared Sub AddDeleteConfirmation(ByVal deleteColumnIndex As Integer, ByVal e As GridViewRowEventArgs)
        '' Check you are only working with Data Rows, not headers/footers
        If e.Row.RowType = DataControlRowType.DataRow Then
            '' Loop through each control in the GridView command column
            For Each ctl As Control In e.Row.Cells(deleteColumnIndex).Controls
                Try
                    '' If it is a LinkButton with a text value of Delete then add the confirmation message.
                    If TypeOf (ctl) Is LinkButton Then
                        Dim DeleteLink As LinkButton = CType(ctl, LinkButton)
                        If DeleteLink.Text.ToLower = "delete" Then
                            DeleteLink.Attributes.Add("onclick", _
                                                   "return confirm(""Are you sure you wish to delete this record?"")")
                        End If
                    End If
                Catch ex As Exception
                    ' just ignore for deployment. May want to re-throw for development.
                End Try
            Next
        End If
    End Sub

End Class

Now, we want to hook this up to our GridViews. We will use the RowDataBound event to loop through the LinkButton controls on the command column of the GridView. If it finds one that has a text value of 'delete' it will add the necessary JavaScript to provide users with the delete confirmation message box. The code is shown below:

Protected Sub GridView1_RowDataBound(ByVal sender As Object, _
                                    ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        '' The command buttons are in the first column (zero index)
        GridViewHelper.AddDeleteConfirmation(0, e)
End Sub

The finished article will produce the following results:

Summary

As you can see this provides the basis of a generic delete pop-up confirmation routine that you can use without having to convert your command column to template fields. You can further extend this by passing 'unique' row information to provide a more detailed message to the client.

Create A Data Access Layer (DAL) without writing any code

2007-10-11T13:46:00.000+10:00

Last updated : 11th October 2007

Background

You spend a lot of your time writing very repetitive code just to grind out standard objects that mirror your database tables and all of the CRUD operations that goes along with this. If only there was a way to take care of this and allow us to dive straight into the business logic and UI functionality. Well this article will present one solution.

Strategy

We will be using the Repository Factory Package from the Microsoft Patterns & Practices Team to create our data access layer. The Repository Factory is a CodePlex project that integrates with Visual Studio and it can be used to generate all our business objects, stored procedures and data access code.

Implementation

To implement the features in this article you require the following.

Pre-requisites:
Visual Studio with SP1 (Need to support web applications projects)
A build of Repository Factory Package
Guidance Automation Tools for Visual Studio (GAT and GAX July 2007 CTP)

Note: - This article cannot be used with any of the Visual Studio Express Versions.

In this example we will create a simple web application that can be used to monitor the power capacity of computer racks based in data centres. The database schema is very straightforward and concurrency is not an issue.

Each Rack can accommodate a number of servers to a maximum power capacity. Each Rack is housed in a Cabinet that can contain up to 20 Racks. Each Cabinet is located on a Floor and in a particular Location. Each Rack can be assigned to a customer and may have one or more power sources.

At this stage we have the database designed. So now we want to generate all the grunt code so we can go straight into coding the business rules. So how doe we do it?

Step 1 - Download and Install the Repository Factory Package

Go to http://www.codeplex.com/RepositoryFactory

Click on the releases Tab and download the latest MSI release.

Close any Visual Studio windows and run the MSI. This will Install the Repository Package on your machine.

Step 2 - Install Guidance Automation Extensions and Guidance Automation Toolkit

Download and install both of these Microsoft products to let you run guidance packages with Visual Studio.
GAT and GAX July 2007 CTP Download.

Step 3 - Create the Visual Studio Project Structure

The Repository Package generates all the user code in C#. However if you are a VB.Net developer then don't panic you can still use the classes without having to write a line of C# code.

VB.Net developers: You need to create 2 web applications. 1 C# (Host project for Repository Factory) and 1 VB.
After you have completed all your code generation tasks all you need from the C# project is the web.config file. Copy this to the VB web application and then you can delete the C# web application. Not ideal, but workable.

Create a visual studio solution with the following 3 projects.

User Interface Web Project (C# and VB) - Start-up Project
Data Access Project (Must be C#)
Business Logic Layer Project (C# or VB)

Figure 2.

Step 4 - Enable the Guidance Package for the solution

Within Visual Studio, select Tools and then click on the Guidance Package Manager.

The Following screen will pop up. Click on the Enable/Disable Packages button.

From here you will be presented with packages that are installed on your machine. There may be more or less displayed depending on which packages you have downloaded. Choose the Repository Factory package.

Step 5 - How to use the Repository Factory

Now that you have enabled the package there are a number of 'Recipes' to choose from. I tend to think of Recipes as glorified macros that execute and perform a series of tasks for you. The Repository package contains the following

Our workflow with this package will be:

Specify Project Responsibilities
Add a database connection
Create CRUD stored procedures
Create Business Entities
Create Data Repository Classes

Step 6 - Specify Project Responsibilities

For the package to generate our data access code and classes it needs to know into which projects will it place the final code. If you think again of a macro scenario then you are simply supplying the parameters. The package need to know :

The Host Project - This will be our web application

The Data Access Project - This will be our PowerCapacityDal class project

The Business Entities Project - This will also be our PowerCapacityDal class project

*Note: You could create a new project for your business entities but I prefer just to put them in the DAL

Right click the PowerCapacityUI project. You will see that a new 'Repository Factory' menu option has appeared. Select the specify project responsibility option.

From here, select Host Project and click the Finish button.

Next right click the PowerCapacityDal project. Again, select Repository Factory and then specify the project responsibility option. This time choose both Business Entities Project AND Data Access Project. This tells the package that you want to store all the generated code into this project.

Step 7 - Add a database connection

We need to tell the package which database we would like to connect to. This will be stored in the web.config file of the web application. So, right click the PowerCapacityUI project and select Add database connection.

Type in PowerCapacityConnection in the Connection Name field. And click on the builder button (...) to the right side of the Connection String field.

Step through the wizard to create the connection string to the database.

Step 8 - Create CRUD stored procedures

The package will create a sql file that contains scripts to generate all the necessary stored procedures. This file can be run against the database.

Right click on the PowerCapacityDal project and select create CRUD stored procedures.

This will open a wizard. Select the connection name from the drop down list.

Select all the tables that you wish to generate stored procedures for.

The package will generate all the necessary stored procedures. Simply click next to select the default names and CRUD operations.

Specify a file to save the script produced.

We now have a file in the PowerCapacityDal that can be executed against our database. Use SQL Query Analyser or equivalent to execute this script. (see below for SQL 2000 users)

Note for SQL 2000 Users

The SQL generated is in SQL Server 2005 syntax. If your stuck on SQL 2000 don't panic, you can edit this file to get it to run. Remove the RethrowError SP at the top and just find and replace 2005 specific syntax.

Find: sys.objects
Replace: sysobjects

Find: BEGIN TRY
Replace:

Find: END TRY
Replace:

Find: BEGIN CATCH
Replace:

Find: END CATCH
Replace:

Find: EXEC RethrowError;
Replace:

At this stage you should have all your select, update, insert and delete stored procedures created on the database just by going though a few wizards. Easy or what!

Step 9 - Create Business Entities

The next stage is to get the Repository Factory to generate our Business Entities for us. We can then use these objects within our web application to hold and manipulate the data.

Right click the PowerCapacityDal project and select the create business entities from database recipe.

Again, choose the connection name from the drop down list.

From here you can customise which elements are needed within the application. I have just accepted the default to include all fields in the database. Each field is mapped to a property on the Business Entity with the relevant get and set statements created for you.

The final stage is to customise the objects themselves. You may want to change the names and determine if the properties should only have a get method for the given attribute (read only). I have just accepted the default of all.

Click on the Finish button and the Repository Factory will generate all your business entity classes.

You can have a look the class view to see the properties that have been exposed.

Step 10 - Create Data Repository Classes

By this stage we have our Business Entities and all of our database stored procedures. The next stage is to create classes that we can use to execute commands against our database.

Right click the PowerCapacityDal project and select the Create data repository classes from business entities.

This again starts a wizard to walk you through the process. First step is to choose your connection name from the dropdown list. You should be getting used to this by now ;-)

Then, just confirm where the wizard can find the business entities. We chose to put them in the PowerCapacityDal project but as mentioned before you can create a separate project for these objects.

Next, click on the objects that you want to generate repository classes for. I have clicked on them all.

Next you need to tell the Factory which operations you are interested in. By default there is a GetAll, Insert, Update and Delete.

I also need a GetOne for each object e.g. Bring me back a Cabinet object based on a Cabinet Id that I pass. So click on the Add... button and select Get One.

Next, specify which stored procedure to use to get the one that you want. For me I want to use GetCabinetByCabinetId.

Next, you need to confirm the mapping between output columns and the business object properties. This has been done for me as the field names in the database are the same as my Cabinet business entity.

Click Finish.

You need to do this for each object that you require a GetOne operation. Perhaps in future releases this behaviour will be defaulted. Now you see all the operations that will be generated. Notice the GetCabinetByCabinetId operation below.

When you click Finish this time the Repository Factory will go and create all the classes needed for your data access layer.

You will notice that the Repository Factory has referenced all the necessary dll's that are required. It also uses the Enterprise Library for that actual database calls so this is copied in for you. Each Business Entity get's its own folder with the necessary classes for each CRUD operation. I won't go into the detail here of the programming patterns used as you can just think of it as a black box for now.

So there you have it. A complete data access layer created without you having to write a single line of code. The only thing left is to use it.

Step 11 - Consuming the Data Access Layer

First build the solution to make sure you don't have any errors.

Next, right click your PowerCapacityBll project add a reference to the PowerCapacityDal project.

Next, Add a reference to the Repository dll. This should be in the following folder:

C:\Program Files\Microsoft Patterns & Practices\Data Access Guidance Package Setup\

Next, in your PowerCapacityBll project add a new class called CabinetManager

Add the following code to get a list of cabinet objects from the database and save a cabinet object back to the database.

using System;
using System.Collections.Generic;
using System.Text;
using PowerCapacityDal;
using Microsoft.Practices.Repository;

namespace PowerCapacityBll
{
    public static class CabinetManager
    { 
        /// <summary>
        /// Get all cabinet objects
        /// </summary>
        /// <returns>A generic list of cabinet objects</returns>
        public static List<Cabinet> GetAll()
        {
            // The Repository creates the proper object based on the interface provided
            // This mapping is held in the web.config file
            ICabinetRepository cabinetRepository = RepositoryFactory.Create<ICabinetRepository>("PowerCapacityConnection");
            // In real life you may want to check the identity of the caller or perform
            // other operations prior to returning the list
            return cabinetRepository.GetAllFromCabinet();
        }

        /// <summary>
        /// Save a cabinet object to the database
        /// </summary>
        /// <param name="cabinet">the cabinet object to save</param>
         public static void Save(Cabinet cabinet)
        {
            ICabinetRepository cabinetRepository = RepositoryFactory.Create<ICabinetRepository>("PowerCapacityConnection");
            cabinetRepository.Save(cabinet);
        }
    } 
}

The Repository Factory 'works out' which class to create based on mapping information held in the web.config. You don't have to worry about this as the Repository Factory deals with it. I show this below for information only.

<repositoryFactory>
    <repositories>
      <add interfaceType="PowerCapacityDal.ICabinetRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.CabinetRepositoryArtifacts.CabinetRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.ICustomerRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.CustomerRepositoryArtifacts.CustomerRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IFloorRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.FloorRepositoryArtifacts.FloorRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.ILocationRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.LocationRepositoryArtifacts.LocationRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IPowerSourceRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.PowerSourceRepositoryArtifacts.PowerSourceRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IRackRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.RackRepositoryArtifacts.RackRepository, PowerCapacityDal" />
    </repositories>
  </repositoryFactory>

This is how you can construct your Business Layer. Now that you see how easy it is to call your Data Access methods all we need to do is link up the user interface.

Step 12 - Implement the User Interface

By this stage we have a fully functioning data access layer created entirely without writing a single line of code. We have added a business logic layer that wraps over this allowing you to implement any business rules that are required.

Next, we will create a web page with a GridView control that will bind to a method in the PowerCapacityBll that will in turn call a data access layer method in our PowerCapacityDal project. A nice n-tier design.

Right click the PowerCapacityUI web project and add a reference to the PowerCapacityBll project so we can call its methods.

In the code behind page of the default.aspx page add the following code to the page load event.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = PowerCapacityBll.CabinetManager.GetAll();
        GridView1.DataBind();
    }
}

And thats it. What's more, it's up to you to decide how much you want from the Repository Factory package.

Summary

Hopefully this article will encourage you to download and use the Repository Factory package. This package is still work in progress so I'm sure the team would welcome any feedback you have on it but already it provides many benefits.

As you have seen, it's possible to have Visual Studio generate all of your data access code using this package. Think how much time you can save and how little 'grunt' coding you are required to write. At a bare minimum you can use this to generate all of your database stored procedures. You can make the choice whether you want to implement the business entity objects and data access code yourself or extend and modify the code that is generated.

Query Active Directory using Atlas (AJAX)

2006-10-12T12:55:00.000+10:00

Last updated : 12th October 2006

Background

There is a lot of buzz around about AJAX and Microsoft's 'wrapper' technology referred to as Atlas. This is still in development but it's stable enough to use, certainly in my environment. This tutorial uses the CTP edition of the Ajax Library.

We will build a tool that allows users to find individuals on an Active Directory(AD) and then retrieve additional information from their AD Entry

Strategy

To make this as slick as possible we will Atlas enable the solution. What we want is for the user to start typing the individuals name and the application should then retrieve/filter and suggest valid entries as they type. When they locate the correct entry they will then click a button to retrieve the required information about the user back from Active Directory. This should update a section of the page without a full postback.

The first thing you need is to have the Atlas libraries installed. You can download the installation files from http://atlas.asp.net. After installation you will have a new project template as shown below. This adds the Microsoft.Web.Atlas.dll and relevant web.config entries that are necessary to 'bridge' out to the web service.

I've had a few emails requesting C# instead of VB.Net, so this tutorial is in C# but you can download a VB version as well. In order to achieve this AJAX functionality we will use 2 server controls from the Atlas library - UpdatePanel and the AutoCompleteExtender. See below.

Atlas Toolbar in VS 2005

Implementation

Lets have a look at what the final result will look like. I have kept the styling and error handling to a minimum for clarity.

To achieve this we require a single web page and a web service with a single method call.

Web Service

<%@ WebService Language="C#" Class="ADValidate" %>

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.DirectoryServices;
using System.Configuration;
using System.Collections.Generic;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class ADValidate  : System.Web.Services.WebService {

    [WebMethod]
    public string[] GetUserNames(string prefixText, int count)
    {
        // Entry to LDAP objects e.g. LDAP://name.server.com/OU=UsersOU=Managed Objects,DC=server,DC=com
        DirectoryEntry de = new DirectoryEntry(ConfigurationManager.AppSettings.Get("ADPath"));

        de.Username = ConfigurationManager.AppSettings.Get("ADServiceAccount");
        de.Password = ConfigurationManager.AppSettings.Get("ADServiceAccountPassword");
        de.AuthenticationType = AuthenticationTypes.FastBind;

        DirectorySearcher ds = new DirectorySearcher(de);
        ds.ClientTimeout = TimeSpan.FromSeconds(30);
        ds.PropertiesToLoad.Add("displayName");

        ds.SizeLimit = 100;
        SortOption srt;
        srt = new SortOption("displayName", SortDirection.Ascending);
        ds.Sort = srt;
        ds.Filter = ("(displayName=" + prefixText.Trim() + "*)");

        SearchResultCollection src;
        src = ds.FindAll();

        List<String> CandidateList = new List<string>();
        foreach (SearchResult sr in src)
        {
            foreach (string Candidate in sr.Properties["displayName"])
            {
                CandidateList.Add(Candidate);
            }
        }
        return CandidateList.ToArray();
    }
    
}

The web service accepts 2 parameters (prefixText and count). The web service is responsible for returning an Array of strings back to the JavaScript calling function and this is used to populate the filtered list of matching users. Although the calling mechanism is JavaScript there is no client side coding required as the Atlas server controls take care of the necessary JavaScript that is emitted based on the web client (IE, Firefox etc).

Inside the web service we basically create a searcher object that will query the Active Directory using the System.DirectoryServices namespace (Remember to add it into your solution as a reference). Note the asterisk (*) at the end of the filter, this acts like a wild card. We also make sure that the list comes back sorted. We are only interested in returning the 'displayName' entry in the Active Directory structure so this is the only property we load prior to execting the FindAll() call.

After the call I am simply looping through the results and adding them to a generic collection of strings or simply returning an empty string array if no entries match.

UI - Web Page

<%@ Page Language="C#" %>

<%@ Import Namespace="System.DirectoryServices" %>
<%@ Import Namespace="System.Configuration" %>
<%@ Import Namespace="System.Data" %>

<!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></title>
    <style type="text/css">
     body { 
        font-family: Arial, Verdana;
        font-size: 11pt;
        font-color: #000000;
        margin: 0;
        padding: 0 10px 0 10px;
        text-align: left 
        }
   </style>
    <atlas:ScriptManager ID="AtlasScriptCore" runat="server" EnablePartialRendering="true" />
</head>
<body>
    <form id="Form1" runat="server">
        <div id="content">
            <h3>
                Active Directory Searcher</h3>
            <div id="searchbox">
                Type Users Surname<br />
                <asp:TextBox ID="txtFullName" runat="server">
                </asp:TextBox>
                <asp:Button ID="btnDetails" runat="server" Text="Get AD Details" OnClick="GetADDetails" />
                <atlas:AutoCompleteExtender runat="server" ID="autoComplete1">
                    <atlas:AutoCompleteProperties 
                            TargetControlID="txtFullName" 
                            Enabled="True" 
                            ServicePath="ADValidate.asmx"
                            ServiceMethod="GetUserNames" 
                            MinimumPrefixLength="1" />
                </atlas:AutoCompleteExtender>
            </div><br />
            <div id="Results">
                <atlas:UpdatePanel ID="UpdatePanel1" runat="server" Mode="Conditional">
                    <ContentTemplate>
                        <asp:GridView ID="ADUserProperties" runat="server">
                        </asp:GridView>
                        <asp:Literal runat="server" ID="SysMessage" EnableViewState="false" ></asp:Literal>
                    </ContentTemplate>
                    <Triggers>
                        <atlas:ControlEventTrigger ControlID="btnDetails" EventName="Click" />
                    </Triggers>
                </atlas:UpdatePanel>
            </div>
        </div>
    </form>
</body>
</html>

<script runat="server">
    protected void GetADDetails(Object sender , EventArgs e )
    {
        // Take the value from the input box and pull back a few AD details
        DataTable UserProperties  = null;
        UserProperties = GetUserByDisplayName(txtFullName.Text);
        // Display data if we have any or show warning
        if (UserProperties != null)
        {
            ADUserProperties.DataSource = UserProperties;
            ADUserProperties.DataBind();
        }
        else
        {
            // Show no records
           SysMessage.Text = "<p style=\"color: red;\">Could not find any details for this user. <br />" +
                                  "Please check that the users name is correct.</p>";
        }
    }

    protected DataTable GetUserByDisplayName(String fullUserName)
    {
        DirectoryEntry de = new DirectoryEntry(ConfigurationManager.AppSettings.Get("ADPath"));
        // Authentication details
        de.Username = ConfigurationManager.AppSettings.Get("ADServiceAccount"); //DOMAIN\User
        de.Password = ConfigurationManager.AppSettings.Get("ADServiceAccountPassword");
        de.AuthenticationType = AuthenticationTypes.FastBind;

        DirectorySearcher DirectorySearcher = new DirectorySearcher(de);
        DirectorySearcher.ClientTimeout = TimeSpan.FromSeconds(30);
        // load the properties we are interested in
        DirectorySearcher.PropertiesToLoad.Add("cn");
        DirectorySearcher.PropertiesToLoad.Add("sAMAccountName");
        DirectorySearcher.PropertiesToLoad.Add("mail");
        DirectorySearcher.PropertiesToLoad.Add("displayName");
        DirectorySearcher.PropertiesToLoad.Add("mDBStorageQuota");
        DirectorySearcher.PropertiesToLoad.Add("title");
        DirectorySearcher.PropertiesToLoad.Add("physicalDeliveryOfficeName");
        DirectorySearcher.PropertiesToLoad.Add("telephoneNumber");
        // filter it on exact entry - NOTE no wild card
        DirectorySearcher.Filter = "(displayName=" + fullUserName.Trim() + ")";

        SearchResult result;
        // There should only be one entry
        result = DirectorySearcher.FindOne();
        if (result != null)
        {
            // Create a table an populate it with properties to bind to gridview
            DataTable myTable = new DataTable("ActiveDir");
            myTable.Columns.Add(new DataColumn("Key", System.Type.GetType("System.String")));
            myTable.Columns.Add(new DataColumn("Value", System.Type.GetType("System.String")));
            DataRow myRow;
            foreach (string propname in result.Properties.PropertyNames)
            {
                foreach (Object objValue in result.Properties[propname])
                {
                    myRow = myTable.NewRow();
                    myRow[0] = propname;
                    myRow[1] = objValue.ToString();
                    myTable.Rows.Add(myRow);
                }
            }
            return myTable;
        }
        else
        {
            return null;
        }
    }
</script>

Note the entries for the Atlas controls. I'm only setting properties, there is no need to write any Javascript as the Atlas framework is responsible for this. The UpdatePanel is updated when we click on the 'Get AD Details' button. You can see that I have set this in the <Triggers> section of the Update Panel. Also note that I set a property on the script manger to enable partial page rendering - EnablePartialRendering="true"

For the AutoCompleteExtender, I tell the control where to go for the data (web service and method name), how many characters need to be typed before making the web service call and which control on the page is looking for the data. This is all done in a declarative fashion.

When the user selects a entry form the dropdown list and clicks on the 'Get AD Details' button we then execute another search on the Active directory but this time we are searching for only one entry. We use the .FindOne() method to bring back the reequired information, hence the additional PropertiesToLoad entries. Once we get these back I am simply creating a DataTable to return back for binding to a GridView in the standard fashion. See below.

Summary

Hopefully this tutorial has shown how you might query an Active Directory structure and how easy it then is to add AJAX functionality with Atlas Server controls.

If you download the samples remember to change the AppSettings section in the web.config file to match entries in your environment.

SQL Transactions

2006-09-13T15:32:00.000+10:00

Last updated : 13th September 2006

Background

In many applications there is a need to be able to insert data across multiple tables as an atomic unit of work. In this example I will cover a simple page in a shopping cart application that requires users to provide both a billing and a shipping address. This information is stored across two separate tables (User and Address) as a parent-child relationship. There would be little point in creating a new user in our database if no address details were added. The Billing dept would not know where to send the invoice and the Shipping department would not know where to deliver the goods.

Therefore we require that the new customer page will only succeed if:

User details are inserted into the User table
Billing address is inserted into the Address table with the new customer ID
Shipping address is inserted into the Address table with the new customer ID

To achieve this we need to wrap all this work together as a transaction. If all parts succeed we then commit this to the database otherwise everything needs to be rolled back so that the database is in the same state that it was in prior to the insert.

Strategy

To implement this functionality as an ATOMIC transaction we will use the SQLTransaction class of the System.Data.SQLClient namespace. Just so you are aware, there are other ways to implement transactions, notably TSQL in stored procedures and the System.Transactions namespace that is in the new version of the DOT.NET framework.

I like System.Transactions coding but this does not work well with the SQL 2000 databases that I use. This is because it enlists the support of the MSDTC and this adds to the overhead. However it was designed to work well with SQL Server 2005 using promotable transactions.

Implementation

We will assume that a user has filled their shopping basket and the next stage for new users is to register themselves and their billing and shipping information. Again this is an all or nothing insert.

Step 1 - Architecture

We will create a User and Address class to hold the information that we collect from the web form. As we will be holding multiple addresses. We will use a Generic List collection to hold these.

User Class

Namespace Bracora.Articles.TransactionExample
    Public Class User
        Private _forename As String
        Private _surname As String
        Private _email As String

        ''' <summary>
        ''' Initializes a new instance of the User class.
        ''' </summary>
        ''' <param name="forename"></param>
        ''' <param name="surname"></param>
        ''' <param name="email"></param>
        Public Sub New(ByVal forename As String, ByVal surname As String, ByVal email As String)
            _forename = forename
            _surname = surname
            _email = email
        End Sub
        ''' <summary>
        ''' Initializes a new instance of the User class.
        ''' </summary>
        Public Sub New()
        End Sub
        Public Property Forename() As String
            Get
                Return _forename
            End Get
            Set(ByVal value As String)
                If _forename = value Then
                    Return
                End If
                _forename = value
            End Set
        End Property
        Public Property Surname() As String
            Get
                Return _surname
            End Get
            Set(ByVal value As String)
                If _surname = value Then
                    Return
                End If
                _surname = value
            End Set
        End Property
        Public Property Email() As String
            Get
                Return _email
            End Get
            Set(ByVal value As String)
                If _email = value Then
                    Return
                End If
                _email = value
            End Set
        End Property

    End Class
End Namespace

Address Class

Namespace Bracora.Articles.TransactionExample

    Public Class Address
        Public Const AT_BILL As String = "BILLADDRESS"
        Public Const AT_SHIP As String = "SHIPADDRESS"
        Private _userID As Integer
        Private _address1 As String
        Private _address2 As String
        Private _address3 As String
        Private _city As String
        Private _postZipCode As String
        Private _addressType As String


        ''' <summary>
        ''' Initializes a new instance of the Address class.
        ''' </summary>
        ''' <param name="userID"></param>
        ''' <param name="address1"></param>
        ''' <param name="address2"></param>
        ''' <param name="address3"></param>
        ''' <param name="city"></param>
        ''' <param name="postZipCode"></param>
        ''' <param name="addressType"></param>
        Public Sub New(ByVal userID As Integer, ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String, ByVal addressType As String)
            _userID = userID
            _address1 = address1
            _address2 = address2
            _address3 = address3
            _city = city
            _postZipCode = postZipCode
            _addressType = AddressType
        End Sub

        ''' <summary>
        ''' Initializes a new instance of the Address class.
        ''' </summary>
        ''' <param name="address1"></param>
        ''' <param name="address2"></param>
        ''' <param name="address3"></param>
        ''' <param name="city"></param>
        ''' <param name="postZipCode"></param>
        Public Sub New(ByVal address1 As String, ByVal address2 As String, ByVal address3 As String, ByVal city As String, ByVal postZipCode As String)
            _address1 = address1
            _address2 = address2
            _address3 = address3
            _city = city
            _postZipCode = postZipCode
        End Sub

        ''' <summary>
        ''' Initializes a new instance of the Address class.
        ''' </summary>
        Public Sub New()
        End Sub
        Public Property userID() As Integer
            Get
                Return _userID
            End Get
            Set(ByVal value As Integer)
                If _userID = Value Then
                    Return
                End If
                _userID = Value
            End Set
        End Property

        Public Property Address1() As String
            Get
                Return _address1
            End Get
            Set(ByVal value As String)
                If _address1 = value Then
                    Return
                End If
                _address1 = value
            End Set
        End Property
        Public Property Address2() As String
            Get
                Return _address2
            End Get
            Set(ByVal value As String)
                If _address2 = value Then
                    Return
                End If
                _address2 = value
            End Set
        End Property
        Public Property Address3() As String
            Get
                Return _address3
            End Get
            Set(ByVal value As String)
                If _address3 = value Then
                    Return
                End If
                _address3 = value
            End Set
        End Property
        Public Property City() As String
            Get
                Return _city
            End Get
            Set(ByVal value As String)
                If _city = value Then
                    Return
                End If
                _city = value
            End Set
        End Property
        Public Property PostZipCode() As String
            Get
                Return _postZipCode
            End Get
            Set(ByVal value As String)
                If _postZipCode = value Then
                    Return
                End If
                _postZipCode = value
            End Set
        End Property
        Public Property AddressType() As String
            Get
                Return _addressType
            End Get
            Set(ByVal value As String)
                If _addressType = value Then
                    Return
                End If
                _addressType = value
            End Set
        End Property
    End Class
End Namespace

Step 2 - Data Access Code

There is a static class UserManager that we will use to perform the necessary database inserts. The methods in this class will work on the User and Address objects mentioned above.

In the UserManager class we have the shared methods to handle the database insert logic and the transactional part. We have a method called InsertUserAndAddresses that controls the SQL Transaction. This method takes a populated User object and a Generic List of Address objects.

For the InsertUser and InsertAddress functions to participate in our transaction we need to pass a reference to the SQLConnection and the SQLTransaction that we created in our controlling function InsertUserAndAddresses. These are then attached to the actual SQLCommand objects that perform the inserts.

We wrap this functionality in a try/catch block so that we can easily rollback the transaction if any errors occur. If all the inserts are successful we then commit the transaction and it is saved in the database.

Static Data Access Class

Imports Bracora.Articles
Imports System.Collections.Generic
Imports System.Data.SqlClient

Namespace Bracora.Articles.TransactionExample
    Public NotInheritable Class UserManager
        'static class
        'not to be instantiated
        Private Sub New()
        End Sub

        Public Shared Function NewConn() As SqlConnection
            Return New SqlConnection(ConfigurationManager.ConnectionStrings("Articles").ConnectionString)
        End Function

        Public Shared Sub InsertUserAndAddresses(ByVal newUser As TransactionExample.User, _
                        ByVal newAddress As List(Of TransactionExample.Address))

            Dim userID As Int32 = 0
            Dim Tran As SqlTransaction

            Using conn As SqlConnection = NewConn()
                conn.Open()
                Tran = conn.BeginTransaction
                Try
                    ' Try to add the user 
                    userID = InsertUser(newUser, Tran, conn)

                    Dim AddressCount As Int32 = 0
                    For i As Int32 = 0 To newAddress.Count - 1
                        newAddress(i).userID = userID
                        InsertAddress(newAddress(i), Tran, conn)
                    Next
                    Tran.Commit()
                Catch ex As Exception
                    Tran.Rollback()
                    ' re-throw back to the ui
                    Throw ex
                End Try
            End Using
           
        End Sub

        Public Shared Function InsertUser(ByVal newUser As TransactionExample.User, _
                                ByVal sqlTran As SqlTransaction, _
                                ByVal sqlConn As SqlConnection) As Int32

            Dim UserID As Object = Nothing
            Dim Sql As String
            Sql = "INSERT INTO [User] (" & _
                "[Forename],[Surname],[Email]) " & _
                "VALUES(@Forename,@Surname,@Email);SELECT SCOPE_IDENTITY();"

            Using cmd As New SqlCommand
                cmd.Connection = sqlConn
                cmd.Transaction = sqlTran
                cmd.CommandType = Data.CommandType.Text
                cmd.CommandText = Sql
                cmd.Parameters.Add(New SqlParameter("Forename", newUser.Forename))
                cmd.Parameters.Add(New SqlParameter("Surname", newUser.Surname))
                cmd.Parameters.Add(New SqlParameter("Email", newUser.Email))
                userID = cmd.ExecuteScalar

                If userID IsNot Nothing Then
                    Return Convert.ToInt32(userID)
                End If
            End Using
        End Function

        Public Shared Sub InsertAddress(ByVal newAddress As TransactionExample.Address, _
                                ByVal sqlTran As SqlTransaction, _
                                ByVal sqlConn As SqlConnection)

            Dim Sql As String
            Sql = "INSERT INTO [Address] (" & _
                "[UserID],[Address1],[Address2],[Address3],[City],[PostZipCode],[AddressType]) " & _
                "VALUES(@UserID,@Address1,@Address2,@Address3,@City,@PostZipCode,@AddressType)"

            Using cmd As New SqlCommand
                cmd.Connection = sqlConn
                cmd.Transaction = sqlTran
                cmd.CommandType = Data.CommandType.Text
                cmd.CommandText = Sql
                cmd.Parameters.Add(New SqlParameter("UserID", newAddress.userID))
                cmd.Parameters.Add(New SqlParameter("Address1", newAddress.Address1))
                cmd.Parameters.Add(New SqlParameter("Address2", newAddress.Address2))
                cmd.Parameters.Add(New SqlParameter("Address3", newAddress.Address3))
                cmd.Parameters.Add(New SqlParameter("City", newAddress.City))
                cmd.Parameters.Add(New SqlParameter("PostZipCode", newAddress.PostZipCode))
                cmd.Parameters.Add(New SqlParameter("AddressType", newAddress.AddressType))
                cmd.ExecuteNonQuery()
            End Using
        End Sub
    End Class

End Namespace

Step 3 - User Interface

When the user populates the form fields and hits submit we create a User object and a List of Address Objects. We then call the transactional method InsertuserAndAddresses. If successful, we would move onto the next stage in the checkout process. Otherwise we display an error message back to the user.

Submit Code

Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        ' you would want to validate prior to inserting
        ' we assume all necessary fields are complete
        Dim NewUser As New TransactionExample.User

        NewUser.Forename = Me.txtForename.Text.Trim
        NewUser.surname = Me.txtSurname.Text.Trim
        NewUser.Email = Me.txtEmail.Text.Trim

        Dim NewAddress As New Generic.List(Of TransactionExample.Address)
        Dim tmpAddress As TransactionExample.Address
        tmpAddress = New TransactionExample.Address
        tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
        tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
        tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
        tmpAddress.City = Me.txtBillCity.Text.Trim
        tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
        tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_BILL
        NewAddress.Add(tmpAddress)

        ' check to see if we need to use the same address for shipping
        If Me.chkUseBilling.Checked Then
            tmpAddress = New TransactionExample.Address
            tmpAddress.Address1 = Me.txtBillAddress1.Text.Trim
            tmpAddress.Address2 = Me.txtBillAddress2.Text.Trim
            tmpAddress.Address3 = Me.txtBillAddress3.Text.Trim
            tmpAddress.City = Me.txtBillCity.Text.Trim
            tmpAddress.PostZipCode = Me.txtBillZip.Text.Trim
            tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
            NewAddress.Add(tmpAddress)
        Else
            tmpAddress = New TransactionExample.Address
            tmpAddress.Address1 = Me.txtShipAddress1.Text.Trim
            tmpAddress.Address2 = Me.txtShipAddress2.Text.Trim
            tmpAddress.Address3 = Me.txtShipAddress3.Text.Trim
            tmpAddress.City = Me.txtShipCity.Text.Trim
            tmpAddress.PostZipCode = Me.txtShipZip.Text.Trim
            tmpAddress.AddressType = Bracora.Articles.TransactionExample.Address.AT_SHIP
            NewAddress.Add(tmpAddress)
        End If
        Try
            TransactionExample.UserManager.InsertUserAndAddresses(NewUser, NewAddress)
            ClearFields(Page.Form)
        Catch ex As Exception
            ' display errr in UI
            Me.litError.Text = "<div class=""error"">There was a problem with the update. " _
                        & "If this persists then please contact the administrator.<br />" _
                        & ex.Message & "</div>"

        End Try

End Sub

If you wish to test the rollback of the transaction simply change the SQL statement within the InsertAddress function. This will then fail and the successful User Insert will be rolled back.

Summary

There are many occasions where you will need to handle workflow as a transactional piece of work against a database. Hopefully this article has given you a feel for how you can perform such a task using the SQLTransaction object.

Friendly Form Field Highlighting

2006-09-11T16:55:00.000+10:00

Last updated : 11th September 2006

Background

You have a form based input screen that users must navigate. The problem is that there are a number fields on display and the user is getting lost when they move there eyes away from the screen. Especially true if users are not good typists.

Strategy

We will change the background colour of the currently selected form field. This will stand out from the other fields and make it easy for our user to find it on the screen.

We will handle the focus and blur events using client side JavaScript to dynamically change the CSS class of the control and thus change the background colour.

Implementation

First, let's see how the final result should look. Notice how the currently selected field is differently coloured to the rest of the form fields.

Step 1 - Add the CSS

We will start of by adding two simple CSS classes. One that can be applied to the form field when the user enters it and another to reset it back to its default display when the user exits the field.

.normalfld 
{
    background-color: #FFFFFF;
}
.focusfld 
{
    background-color: #FFFFCC;
}

Step 2 - Add the JavaScript

We need to use client side code to change the CSS class assigned to the form field. There are a number of ways to add this in ASP.NET projects. One practice, which I'm not a great fan of, is to use the ClientScriptManager classes. For this example I will create a simple JavaScript file and attach it to the page using standard HTML <script> notation.

function DoBlur(fld) {
    fld.className='normalfld';
}

function DoFocus(fld) {
    fld.className = 'focusfld';
}

Step 3 - Plumbing the JavaScript

We will add the plumbing to the ASP.NET form controls in the Page_Load event of the page. We loop through all the controls on the form and add the necessary events to our target fields. In this case we are searching for only textboxes.

   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            SetHighlightFields(Page.Form)
            Me.txtForename.Focus()
        End If
    End Sub

    Sub SetHighlightFields(ByVal frm As Control)
        Dim TempCheckbox As CheckBox = Nothing
        Dim TempTextBox As TextBox = Nothing
        For Each tmpctl As Control In frm.Controls
            If TypeOf tmpctl Is TextBox Then
                TempTextBox = CType(tmpctl, TextBox)
                TempTextBox.Attributes.Add("onFocus", "DoFocus(this);")
                TempTextBox.Attributes.Add("onBlur", "DoBlur(this);")
            End If
        Next
    End Sub

    Sub ClearFields(ByVal frm As Control)
        For Each tmpctl As Control In frm.Controls
            If TypeOf tmpctl Is TextBox Then
                CType(tmpctl, TextBox).Text = ""
            End If
        Next
    End Sub

That's all there is to it. Now when the user tabs or clicks through in your form each field will be highlighted when it receives the focus.

Summary

This has hopefully given you a few ideas of your own on how to provide some 'value add' features to your web applications. As you can see from the code, or lack of it, it's easy to implement this functionality. When developing, try to imagine your application from the users perspective who is going to be using it 8 hours a day!

Friendly GridView Deletion Messages

2006-09-02T16:42:00.000+10:00

Last updated : 2nd September 2006

Background

You know the scenario. You present a whole load of tabular data to users in a GridView. You may have reduced the font-size so you can cram in an extra couple of rows from the database. You have an Edit and Delete LinkButtons there. So you think well I'll put in a piece of client script to make sure my users get a confirmation pop-up when they are about to delete a record. I'll code the nice OnClientClick event on the LinkButton and put in a 'Are you sure you wish to delete the record' message.

But wait a minute - my users are not that accurate with the mouse - I'm not that accurate with the mouse. And that font is quite small. How do I make 100% sure that they know exactly what record will be deleted?

Well I'll show you how to include some personal information in your pop-up message so there's no mistakes.

Strategy

We need to add a Delete LinkButton into a template field in the GridView. When the GridViews RowDataBound event fires for each row we want to set the onClientClick property of the LinkButton with a personalised message

Implementation

We have a users table that is bound to an ObjectDataSource which in turn is bound onto a GridView control. The GridView code is shown below.

<asp:GridView ID="GvUsers" runat="server" DataKeyNames="ID" AutoGenerateColumns="False"
     AutoGenerateEditButton="true" DataSourceID="ObjectDataSource1">
  <Columns>
    <asp:TemplateField ShowHeader="False">
    <ItemTemplate>
       <asp:LinkButton ID="DeleteUser" runat="server" CausesValidation="False" CommandName="Delete"
           Text="Delete"></asp:LinkButton>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="ID" Visible="False" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
    <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
    <asp:BoundField DataField="Telephone" HeaderText="Telephone" SortExpression="Telephone" />
    <asp:BoundField DataField="Hiredate" HeaderText="Hiredate" SortExpression="Hiredate" />
 </Columns>
</asp:GridView>

If you look at the GridView properties you will see that we have set AutoGenerateEditButton to true but have not auto generated the delete button. We have created a template field and added a LinkButton to it with ID="DeleteUser". Note the CommandName="Delete" which auto-wires the delete button to the GridView.

Next step is to code up the GridViews RowDataBound event. As each row is bound to the GridView we search for the Delete LinkButton and customise the OnClientClick property. We use a simple JavaScript confirm function that returns true if the user clicks OK, otherwise false.

Protected Sub GvUsers_RowDataBound(ByVal sender As Object, _
              ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) _
              Handles GvUsers.RowDataBound
        ' add a confirm button to the delete button
        ' We are only interested in datarows
        If e.Row.RowType = DataControlRowType.DataRow Then
            ' make sure we can find the control first
            If e.Row.FindControl("DeleteUser") IsNot Nothing Then
                ' cast it to a LinkButton and set the client script
                CType(e.Row.FindControl("DeleteUser"), LinkButton).OnClientClick = _
                            "return confirm('Are you sure you want to delete " & _
                            e.Row.Cells(3).Text.Replace("'", "\'") & _
                            " from the database?');"
            End If
        End If
End Sub

You can see we are concatenating a display message with a value from the Name field on the GridView. Single quotes cause issues with the JavaScript so we need to escape them by prefixing them with a backslash e.Row.Cells(3).Text.Replace("'", "\'"). This takes care of names like O'Malley etc.

Now when your users hit delete they will receive a much more detailed message like the one below.

Summary

As you can see this is a very simple technique to add into your web applications. I think this approach is much better than having users on the phone asking for a database restore as they have mistakenly deleted your top customer!

Optimistic Concurrency With SQL Server Rowversion Data Type

2006-09-02T16:03:00.000+10:00

Last updated : 2nd September 2006

Background

You are developing a multi-user web based system that involves users updating shared data. Therefore you need to make sure that users do not overwrite each others updates. For deletes, you want to make sure that the record to be deleted has not been modified by another user between the time you retrieved it from the database and the time you eventually delete it.

SQL Server has column type rowversion (synonym timestamp) which automatically changes its value whenever a row is modified in the database. This makes it an ideal candidate to enforce an optimistic concurrency solution. The rowversion field contains a binary value of 8 bytes.

Strategy

We will use a GridView bound to an ObjectDataSource to present the data to our users. We then retrieve the rowversion binary value from SQL Server as part of the data. As we need this value to validate our updates and deletes we will convert it to a string representation and hold it in the Gridview.

On updates and deletes we will convert the rowversion string representation back into a binary value and use this to qualify the SQL update and delete statements.

I will split this tutorial in to four main sections.

Table Design
Stored Procedures
Data Access Layer
User Interface

Step 1 - Table Design

We will create a simple users table to demonstrate this technique. We will create a unique index on the email field to prevent duplicate email addresses in the system. This should prevent records being created twice by different users.

CREATE TABLE [dbo].[Users](
    [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [Name] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](50) NOT NULL,
    [Telephone] [nvarchar](50) NULL,
    [Hiredate] [datetime] NULL,
    [Dataversion] [rowversion] NOT NULL
    )
 
CREATE UNIQUE NONCLUSTERED INDEX [IX_UsersUniqueEmail] ON [dbo].[Users] 
(
    [Email] ASC
) ON [PRIMARY]

Step 2 - Stored Procedures

Create stored procedures to perform CRUD activities against the database

Select all users
Insert a new user
Update a users details
Delete a user

The select stored procedure just grabs all users from the database. The Insert is straight forward, any duplicates entries will fail because of the unique index we have on the email address.

On the Update stored procedure note the WHERE clause. We are updating the record based on the rows unique identifier AND the rowversion that we previously retreived from the database. Remember, if there any data modifications on that row SQL Server will change the rowversion value automatically. So if the row was changed the update will affect zero rows.

Again for the Delete stored procedure note the WHERE clause. we qualify both the unique identifier AND the rowversion value.

-- =============================================
-- Author: Martin Millar
-- Create date: Sat 12th Aug 2006
-- Description:    Retreive all users in the database
--              Large tables may require paging 
-- Version: 1.0
-- =============================================
CREATE PROCEDURE [dbo].[proc_SelectUsers] 
    AS
BEGIN
SET NOCOUNT ON;
SELECT [id]
      ,[Name]
      ,[Email]
      ,[Telephone]
      ,[HireDate]
      ,[Dataversion]
  FROM [Users]
END

-- =============================================
-- Author: Martin Millar
-- Create date: Sat 12th Aug 2006
-- Description:    Insert new user in database
-- Version: 1.0
-- =============================================
CREATE PROCEDURE [dbo].[proc_UserInsert] 
    @Name nvarchar(50),
    @Email nvarchar(50),
    @Hiredate datetime,
    @Telephone nvarchar(50)
AS
BEGIN
    INSERT INTO [Articles].[dbo].[Users]
          ([Name]
          ,[Email]
          ,[Telephone]
          ,[Hiredate])
    VALUES
          (@Name
          ,@Email
          ,@Telephone
          ,@Hiredate)
END

-- =============================================
-- Author: Martin Millar
-- Create date: Sat 12th Aug 2006
-- Description:    Update user details
-- Version: 1.0
-- =============================================
CREATE PROCEDURE [dbo].[proc_UpdateUser]
    @ID int, 
    @Name nvarchar(50),
    @Email nvarchar(50),
    @Telephone nvarchar(50),
    @Hiredate datetime,
    @Dataversion rowversion

AS

BEGIN
    UPDATE [Users]
    SET [Name] = @Name
        ,[Email] = @Email
        ,[Telephone] = @Telephone
        ,[Hiredate] = @Hiredate
    WHERE (ID=@ID) AND (Dataversion = @Dataversion)
END

-- =============================================
-- Author: Martin Millar
-- Create date: Sat 12th Aug 2006
-- Description:    Deletes user from the database
-- Version: 1.0
-- =============================================
CREATE PROCEDURE [dbo].[proc_DeleteUser] 
    @ID int,
    @Dataversion rowversion
AS

BEGIN
    DELETE FROM [Users]
    WHERE (ID = @ID) AND (Dataversion = @Dataversion)
END

Step 3 - Data Access Layer

For the data access layer I have implemented a static class with with a private constructor so that it can't be instantiated. I have 'garnished' the class definition with the <ComponentModel.DataObject()> attribute. Basically this allows the ObjectDataSource that we will use later on to correctly identify that this is a data component. I will also set method attributes on the Select, Insert, Update and Delete routines. Again this allows the ObjectDataSourceControl to easily identify which methods are used for CRUD operations.

I will be using the SQLClient classes of the System.Data namespace. The download comes with a SQL Express database. However I have included a sql script to add the necessary objects to a SQL 2000/2005 database.

Select Method

I will be returning a DataTable back to the UI layer containing all rows in the user table.

<ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Select, True)> _
Public Shared Function FillUsers() As DataTable
    Using connection As New SqlConnection( _
        ConfigurationManager.ConnectionStrings("MySQLDatabase").ConnectionString)
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "proc_SelectUsers"
            Using adapter As New SqlDataAdapter(command)
                Dim table As DataTable = New DataTable
                adapter.Fill(table)
                Return table
            End Using
        End Using
    End Using
End Function

Insert Method

Before I go through this I'll quickly touch on nullable types. The Telephone and HireDate fields are nullable in our database. This is not a problem for string values as they are reference types and intrinsically have the concept of null or nothing values. However HireDate is a datetime value which is a value type.

Value types are stored on the stack and have no concept of nulls. Hence we need to add this functionality by wrapping it in a nullable structure. When we do this we can check the .HasValue property to check for nulls.

The insert method checks the RowsAffected property of the ExecuteNonQuery method and throws an exception if the insert has failed. Remember though we have a unqiue index on email so duplicate records will throw a SQLException prior to this.

<ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Insert, True)> _
Public Shared Sub InsertUser(ByVal name As String _
                            , ByVal email As String _
                            , ByVal telephone As String _
                            , ByVal hireDate As Nullable(Of Date))

    Dim RecsAffected As Int32 = 0
    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("MySQLDatabase").ConnectionString)
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "proc_UserInsert"

            ' Add the parameters
            command.Parameters.Add(New SqlParameter("Name", name))
            command.Parameters.Add(New SqlParameter("Email", email))
            command.Parameters.Add(New SqlParameter("Telephone", IIf(telephone IsNot Nothing, telephone, System.DBNull.Value)))
            ' Hiredate in SQL Table is nullable so we need to wrap the value type
            ' in the nullable wrapper and check for the existance of a value
            command.Parameters.Add(New SqlParameter("HireDate", IIf(hireDate.HasValue, hireDate, System.DBNull.Value)))

            ' open, execute and return
            connection.Open()
            RecsAffected = command.ExecuteNonQuery
            If RecsAffected <> 1 Then
                Throw New ApplicationException("Failed to insert new user into the database.")
            End If
        End Using
    End Using
End Sub

Update Method

Note the signature on the update method. When coding data components it's worth remembering the idiosyncrasies of the controls that you will be using.

For example, I'm using original_id name as input parameter to the update method. Why? - because by default the ObjectDataSource will hold this key value in the a collection prefixed with original_.

This can be changed by modifying the attribute in the Object DataSource.
OldValuesParameterFormatString="original_{0}"

Also note that I'm passing the rowversion in as a byte array. As you will see later, you could just as easily pass this in as a string and perform a coversion back to a byte array value in method itself.

<ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Update, True)> _
Public Shared Sub UpdateUser(ByVal original_id As Int32 _
                            , ByVal name As String _
                            , ByVal email As String _
                            , ByVal telephone As String _
                            , ByVal hireDate As Nullable(Of Date) _
                            , ByVal dataVersion As Byte())
 

    Dim RecsAffected As Int32 = 0
 
    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("MySQLDatabase").ConnectionString)
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "proc_UpdateUser"

            ' Add the parameters
            command.Parameters.Add(New SqlParameter("ID", original_id))
            command.Parameters.Add(New SqlParameter("Name", name))
            command.Parameters.Add(New SqlParameter("Email", email))
            command.Parameters.Add(New SqlParameter("Telephone", IIf(telephone IsNot Nothing, telephone, System.DBNull.Value)))

            ' Hiredate in SQL Table is nullable so we need to wrap the value type
            ' in the nullable wrapper and check for the existance of a value
            command.Parameters.Add(New SqlParameter("HireDate", IIf(hireDate.HasValue, hireDate, System.DBNull.Value)))

            ' Now pass in the DataRowVersion that was retrieved from the database
            ' We compare this with the value currently in the database. If these
            ' values differ we do NOT want to overwrite with our changes
            command.Parameters.Add(New SqlParameter("DataVersion", dataVersion))

            ' open, execute and return
            connection.Open()
            RecsAffected = command.ExecuteNonQuery
            If RecsAffected <> 1 Then
                Throw New ApplicationException("Another user has changed this record. " & _
                                            "Your update was cancelled.")
            End If
        End Using
    End Using
End Sub

Delete Method

For the delete method we will need both the ID and the Dataversion of the row to delete. Again I'm sticking to the format original_ prefix.

<ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Delete, True)> _
   Public Shared Sub DeleteUser(ByVal original_id As Int32 _
                               , ByVal original_dataVersion As Byte())

    ' We only want to delete if the user has the latest version of the record
    Dim RecsAffected As Int32 = 0

    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("MySQLDatabase").ConnectionString)
        Using command As New SqlCommand()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "proc_DeleteUser"

            ' Add the parameters
            command.Parameters.Add(New SqlParameter("ID", original_id))

            ' Now pass in the DataRowVersion that was retrieved from the database
            ' We compare this with the value currently in the database. If these
            ' values differ we do NOT want to delete it
            command.Parameters.Add(New SqlParameter("DataVersion", original_dataVersion))

            ' open, execute and return
            connection.Open()
            RecsAffected = command.ExecuteNonQuery
            If RecsAffected <> 1 Then
                Throw New ApplicationException("Another user has changed this record. " & _
                                            "User has NOT been deleted.")
            End If
        End Using
    End Using
End Sub

Thats the data access sorted so we'll moved to the user interface.

Step 4 - User Interface

For the user interface we will use a GridView control that is wired to an ObjectDataSource. For inserts I have just created some textboxes but you could easily use the FormView or DetailsView controls. The finished page is shown below.

So first drag an ObjectDataSource onto the web form and set the Business Object to the data access class. Because we set the proper method attributes on the business object all the methods will be pre-selected. I have included screenshots for reference.

In the source code of the page you should now see the following mark-up.

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteUser"
        InsertMethod="InsertUser" OldValuesParameterFormatString="original_{0}" 
        SelectMethod="FillUsers"
        TypeName="Bracora.Articles.Users" UpdateMethod="UpdateUser">
    <DeleteParameters>
         <asp:Parameter Name="original_id" Type="Int32" />
         <asp:Parameter Name="original_dataVersion" Type="Object" />
    </DeleteParameters>
    <UpdateParameters>
         <asp:Parameter Name="original_id" Type="Int32" />
         <asp:Parameter Name="name" Type="String" />
         <asp:Parameter Name="email" Type="String" />
         <asp:Parameter Name="telephone" Type="String" />
         <asp:Parameter Name="hireDate" Type="DateTime" />
         <asp:Parameter Name="dataVersion" Type="Object" />
    </UpdateParameters>
    <InsertParameters>
         <asp:Parameter Name="name" Type="String" />
         <asp:Parameter Name="email" Type="String" />
         <asp:Parameter Name="telephone" Type="String" />
         <asp:Parameter Name="hireDate" Type="DateTime" />
    </InsertParameters>
</asp:ObjectDataSource>

Next, drag on a GridView Control and set its datasource to the ObjectDataSource. I like to manually specify the fields so I set the AutoGenerateColumns to false. I have also created two template columns.

One for the Delete button as we want to specify a custom JavaScript confirmation to fire. I don't like the standard 'Are you sure you wish to delete this?' type message. For more information on this see my article here
One is for a hidden label to hold the rowversion data which is converted into a string representation as it binds to the grid. Look for the code below in the DataRowVersion template field :
System.Convert.ToBase64String(Eval("DataVersion"))
This gives us access to this value during updates and deletes.

The final GridView markup is shown below.

<asp:GridView ID="GvUsers" runat="server" DataKeyNames="ID" AutoGenerateColumns="False"
   AutoGenerateEditButton="true" DataSourceID="ObjectDataSource1">
      <Columns>
         <asp:TemplateField ShowHeader="False">
             <ItemTemplate>
                  <asp:LinkButton ID="DeleteUser" runat="server" CausesValidation="False"
                   CommandName="Delete" Text="Delete"></asp:LinkButton>
             </ItemTemplate>
             </asp:TemplateField>
             <asp:BoundField DataField="ID" Visible="False" HeaderText="ID" ReadOnly="True" SortExpression="ID" />
             <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
             <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
             <asp:BoundField DataField="Telephone" HeaderText="Telephone" SortExpression="Telephone" />
             <asp:BoundField DataField="Hiredate" HeaderText="Hiredate" SortExpression="Hiredate" />
             <asp:TemplateField Visible="False" HeaderText="DataRowVersion">
                 <ItemTemplate>
                    <asp:Label runat="server" ID="DataVersion" 
                   Text='<%# System.Convert.ToBase64String(Eval("DataVersion")) %>'>
                    </asp:Label>
                 </ItemTemplate>
           </asp:TemplateField>
       </Columns>
</asp:GridView>

All we need to do is plug in the code to handle CRUD operations in the user interface.

Both the GridView and the ObjectDataSource have complimentary events that fire during CRUD changes. I refer to these as ing and ed events e.g.

RowUpdating and RowUpdated
RowDeleting and RowDeleted
Inserting and Inserted
.......

The ing events allow you to perform processing before your database operation such as validation, business rules checks....etc

The ed events allow you to perform processing after the database operation such as exception handling, clean-up code...etc

I'll take each operations in turn

GridView - Update

For updates, we want to make sure that if we submit a change to the database that we do not overwrite any changes that another user may have made while we were in the process of editing the record. Remember multiple users will have access to the same common data.

We only update the row if the rowversion we fetched from the database is the same as the one that is currently stamped on that row in SQL Server.

As mentioned before we do this by qualifying this in the WHERE clause of the Update statement.

To handle Updates we will use the GridView_RowUpdating and ObjectDataSource_Updated events.

GridView_RowUpdating - We have access to the NewValues dictionary at this point which is passed to ObjectDataSource for the update operation. The non key parameters are already populated. However we need to set the value of the original_rowversion parameter from the hidden Label control in the Template field of the GridView and convert it from its string representation back into a byte array.

ObjectDataSource_Updated - We just check to see if there were any exceptions and display them in the UI. Make sure you set the e.ExceptionHandled = True otherwise the exception will continue to bubble up.

Protected Sub GvUsers_RowUpdating(ByVal sender As Object, _
                        ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) _
                        Handles GvUsers.RowUpdating
        e.NewValues("DataVersion") = _
            Convert.FromBase64String(CType(Me.GvUsers.Rows(e.RowIndex).FindControl("DataVersion"), Label).Text)
End Sub


Protected Sub ObjectDataSource1_Updated(ByVal sender As Object, _
                            ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) _
                            Handles ObjectDataSource1.Updated
        ' handle any Update exceptions here
        If e.Exception IsNot Nothing Then
            DisplayUIError(IIf(e.Exception.InnerException Is Nothing, e.Exception.Message, e.Exception.InnerException.Message))
            e.ExceptionHandled = True
        End If
End Sub

GridView - Delete

When deleting records from the GridView we don't want users to delete rows that may have been modified by another user after the data was retrieved.

To handle Deletes we will use the GridView_RowDeleting, ObjectDataSource_Deleting and ObjectDataSource_Deleted events.

GridView_RowDeleting - We store a string representation of the Dataversion field for the deleted row in a page level variable called DeletedRowVersion. This is taken from the hidden Label control in the Template field of the GridView.

ObjectDataSource_Deleting - We have access to the InputParameters dictionary at this point which is used for the delete operation. The original_ID is already populated because it is the DataKeyField of the GridView. Therefore we just need to set the value of the original_rowversion parameter. We get this from the Page variable DeletedRowVersion which we set when the GridView entered edit mode. We convert this from its string representation back into a byte array.

ObjectDataSource_Deleted - We just check for exceptions and display them in the UI.

Protected DeletedRowVersion As String

Protected Sub GvUsers_RowDeleting(ByVal sender As Object, _
              ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) _
              Handles GvUsers.RowDeleting

              DeletedRowVersion = CType(Me.GvUsers.Rows(e.RowIndex).FindControl("DataVersion"), Label).Text
End Sub


Protected Sub ObjectDataSource1_Deleting(ByVal sender As Object, _
              ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs) _
              Handles ObjectDataSource1.Deleting

              Dim DeleteParams As IDictionary = e.InputParameters
              DeleteParams("original_dataVersion") = Convert.FromBase64String(DeletedRowVersion)
End Sub


Protected Sub ObjectDataSource1_Deleted(ByVal sender As Object, _
              ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) _
              Handles ObjectDataSource1.Deleted

              ' handle any delete exceptions here
              If e.Exception IsNot Nothing Then
                    DisplayUIError(IIf(e.Exception.InnerException Is Nothing, e.Exception.Message, e.Exception.InnerException.Message))
                    e.ExceptionHandled = True
              End If
End Sub

GridView - Inserts

For the inserts we will just create an input form above the GridView. The use of a FormView or DetailsView control is perfectly valid as well. I have included the code for reference.

<fieldset>
   <legend>New User Details</legend>
       <table>
          <tr style="font-weight: bold;">
            <td>
             Name<br />
             <asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
            <td>
             Email<br />
             <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
           </td>
           <td>
             Telephone<br />
              <asp:TextBox ID="txtTelephone" runat="server"></asp:TextBox>
            </td>
            <td>
              Hire Date<br />
              <asp:TextBox ID="txtHireDate" runat="server"></asp:TextBox>
            </td>
             <td>
               <br />
                <asp:Button ID="btnAddUser" runat="server" Text="Add New User" />
            </td> 
              </tr>
        </table>
</fieldset>

Concurrency is not so much of an issue on the insert. However we do want to prevent two users keying in duplicate data. For example, Operator A keys in 50 new clients from a list of paper forms. Operator B comes in on the next shift, sees the paper forms and keys them in again.

To prevent this we have set up a unique index on email address. An SQLException will be thrown if any attempt is made to insert duplicate email addresses.

Here is the Insert code

Protected Sub btnAddUser_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddUser.Click
        Try
            Dim tmpHireDate As DateTime
            If txtHireDate.Text.Trim <> "" Then
                ' must be value, so check it 
                DateTime.TryParse(txtHireDate.Text.Trim, tmpHireDate)
                If tmpHireDate = DateTime.MinValue Then
                    DisplayUIError("The Hire date is not recognised as a valid date. Please correct.")
                    Exit Try
                End If
            End If

            Bracora.Articles.Users.InsertUser(txtName.Text.Trim _
                                            , txtEmail.Text.Trim _
                                            , txtTelephone.Text.Trim _
                                            , IIf(tmpHireDate = DateTime.MinValue, Nothing, tmpHireDate))

            Me.GvUsers.DataBind()

        Catch ex As Exception
            DisplayUIError(ex.Message)
        End Try
End Sub

Summary

In this article I have presented you with one technique to control modifications to shared data in multi-user environments. The rowversion column type is an ideal candidate for handling concurrency with SQL Server databases. Just to reiterate there are other methods to handle this situation but I think this is particularly 'clean'. Hopefully you will take this example and enhance it to suit your own situation. You may want to automatically re-load the data back into the UI if changes have occurred. It's up to you (or your customer) to decide the best route for each application.

Bracora

Moved

Mastering iTunes using Linq To Xml

Background

Strategy

Implementation

Summary

Generic GridView Delete Strategy

Background

Strategy

Implementation

Summary

Create A Data Access Layer (DAL) without writing any code

Background

Strategy

Implementation

Step 1 - Download and Install the Repository Factory Package

Step 2 - Install Guidance Automation Extensions and Guidance Automation Toolkit

Step 3 - Create the Visual Studio Project Structure

Step 4 - Enable the Guidance Package for the solution

Step 5 - How to use the Repository Factory

Step 6 - Specify Project Responsibilities

Step 7 - Add a database connection

Step 8 - Create CRUD stored procedures

Step 9 - Create Business Entities

Step 10 - Create Data Repository Classes

Step 11 - Consuming the Data Access Layer

Step 12 - Implement the User Interface

Summary

Further Reading

Query Active Directory using Atlas (AJAX)

Background

Strategy

Implementation

Web Service

UI - Web Page

Summary

SQL Transactions

Background

Strategy

Implementation

Step 1 - Architecture

User Class

Address Class

Step 2 - Data Access Code

Static Data Access Class

Step 3 - User Interface

Submit Code

Summary

Friendly Form Field Highlighting

Background

Strategy

Implementation

Step 1 - Add the CSS

Step 2 - Add the JavaScript

Step 3 - Plumbing the JavaScript

Summary

Friendly GridView Deletion Messages

Background

Strategy

Implementation

Summary

Optimistic Concurrency With SQL Server Rowversion Data Type

Background

Strategy

Step 1 - Table Design

Step 2 - Stored Procedures

Step 3 - Data Access Layer

Select Method

Insert Method

Update Method

Delete Method

Step 4 - User Interface

GridView - Update

GridView - Delete

GridView - Inserts

Summary