Ajax comes to EmigratingToOz.com

The goal of the EmigratingToOz.com website is to be the oracle of information about Australia for us UK poms and to help them in the emigrating process. To that end, one of the things we needed to do was also supply real-time information about Australia from the point of view of weather, money and time.

Initially, the website was set up to actively get this information from a variety of sources and simply display on the page via the master page. However, the weather information in particular, comes from a slowish website that was killing the inital load time of the site.

The solution was simply to implement Ajax calls to get the information required in an asynchronous manner. Using the generic library that we created to demostrate Ajax calls on our main website, we were able to implement the calls for all of the data in one quick afternoon. The result is a quicker and more pleasing experience...

Showing a Windows form full screen

Ok....quite simple you might think! And yes it is, but only if you know how!

For a small project that we are working on at the moment we needed to show a Windows Form in full screen mode without the taskbar etc... It is quite simple in the end but needs a couple of Win32Api calls, but that is simple enough. The one thing that is easy to forget is that you need to set the FormBorderStyle property for the form to None, otherwise you will still have the menu bar showing....

To acheive the full screen mode create a simple class with the code below in it

[DllImport("user32.dll", EntryPoint = "GetSystemMetrics")]
public static extern int GetSystemMetrics(int which);

[DllImport("user32.dll")]
public static extern void SetWindowPos(IntPtr hwnd, IntPtr hwndInsertAfter, int X, int Y, int width, int height, uint flags);

private const int SM_CXSCREEN = 0;
private const int SM_CYSCREEN = 1;
private static IntPtr HWND_TOP = IntPtr.Zero;
private const int SWP_SHOWWINDOW = 64;

public static int ScreenX
{
get { return GetSystemMetrics(SM_CXSCREEN); }
}

public static int ScreenY
{
get { return GetSystemMetrics(SM_CYSCREEN); }
}

public static void SetWindowFullScreen(IntPtr hwnd)
{
SetWindowPos(hwnd, HWND_TOP, 0, 0, ScreenX, ScreenY, SWP_SHOWWINDOW);
}

Then in your application simply create a new instance of the form and call the Show method on the form.

Form form1 = new Form();
form1 .Show(this);

Then finally create an OnLoad handler for the form and call the SetWindowFullScreen method on our helper class and that is it....

WinApi.SetWindowFullScreen(Handle);

Reverse iterating through a List collection

Reversing through the object in a List is an operation that isn't so uncommon, and there is a simple way of doing this using a for loop with a decrement on an index value. But it isn't a particularly elegant way of acheiving this and the re-use of the for loop isn't the most efficient in terms of code re-use. Thankfully with the use of the IEnumerable interface we can create a simple, clean and elegant solution to the problem.

By deriving from the IEnumerable interface we can create a new GetEnumerator method and do the meat of the work here. Below is a class that implements this interface to create a reversable enumerator dependant on a simple local property. The property is from a simple enumeration that is defined as Forwards or Reverse.

public sealed class Counts : List<long>, IEnumerable<long>
{
///

/// The direction to iterate through our collection
///
private IteratorDirection iteratorDirection = IteratorDirection.Forwards;

///

/// Returns an enumerator that iterates through the collection.
///
///
/// A
/// that can be used to iterate through the collection.
///
public new IEnumerator<long> GetEnumerator()
{
if(iteratorDirection == IteratorDirection.Forwards)
{
for (int i = 0; i < class="kwrd">yield return this[i];
}
}
else
{
for (int i = Count - 1; i >= 0; i--)
{
yield return this[i];
}
}
}

///

/// Sets the iterator direction.
///
/// The iterator direction.
public IteratorDirection IteratorDirection
{
set { iteratorDirection = value; }
}
}

To use the class is simple... First set the direction that you want to iterate through the collection, then iterate through the collection using the ubiquitous foreach loop

OurCounts.IteratorDirection = IteratorDirection.Forwards;
foreach (long count in OurCounts)
{
}

OurCounts.IteratorDirection = IteratorDirection.Reverse;
foreach (long count in OurCounts)
{
}

Getting the application path using reflection and C#

Just recently I had a problem whereby there was a case which resulted in the current working directory changing before some of my code had run. As I hadn't catered for this case it came up with a crash which was easily fixed by getting the current application path and then combining this with the filename which I wished to access. Heres the code that I used to acheive this in a nice clean fashion and create a path to a configuration file (configurationFilename):

Uri uri = new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
string fullConfigurationFilename = Path.Combine(Path.GetDirectoryName(uri.AbsolutePath), configurationFilename);
fullConfigurationFilename = HttpUtility.UrlDecode(fullConfigurationFilename);

By using reflection to get the location of the currently executing assembly I am not relying on the actual .exe location (or website location in fact), but instead using the actual dll's location from which the current code is running. You might wish to use the actual application's path or the website root path, but in the scenario that I am using this in, the assembly's path is exactly what I wanted.

Scripting SQL Inserts from existing table data

In the past I usually hand generated a quick bit of SQL to generate my INSERT statements from existing table data like so:

select
'insert into dbo.Links values (' +
cast(exceedence_set_id as varchar(100)) + ', ' +
cast(seq as varchar(100)) + ', ' +
cast(link_id as varchar(100)) + ', ' +
cast(link_offset as varchar(100)) + ', ' +
'false,' +
cast(band_id as varchar(100)) +
')'
from dbo.Links

The only problem with this was that I had to hand add in the column name, handle null data etc... So, I thought I'd have a look to see if someone with loads more SQL experience than me had had a go at handling this all automatically. After a bit of searching on Google I found a handy procedure from Narayana Vyas Kondreddi. Put simply it can automatically generate your Insert statements for any table and also comes with a host of configurable parameters to tailor the output to your own needs. For example to generate the statements for table Links but ommit the column names because the data will be inserted into an identical table on another SQL Server instance, and to also ommit the identity column, call the procedure like so:

EXEC sp_generate_inserts 'Links', @include_column_list = 0, @ommit_identity = 1

It's that simple....great piece of work by Vyas. Also check out the rest of his site as there's plenty more SQL stuff available...

Auto-generating a sitemap conforming to Sitemap Protocol 0.9

Having recently installed BlogEngine.NET, I was interested to see the auto-generated sitemap that Mads had created. Basically, he implemented a custom HttpHandler that when requested would respone with XML that conformed to the Sitemap Protocol 0.9 which is dictated by sitemaps.org. This is an XML schema that all of the major search engines have signed up to which include Google, Yahoo! and Microsoft.

What I wanted to do is to implement this for my own site, but the custom handlers of Mads was tailored to BlogEngine.NET, so it required a bit of tweaking. Now, I have a Web.sitemap for my website, so in the schema of wanting to do things from a generic point of view, I decided to implement the HttpHandler to utilise the Web.sitemap file to generate the sitemap response. To do this, first we have to load up the sitemap file...

XmlSiteMapProvider xmlSiteMap = new XmlSiteMapProvider();
NameValueCollection myCollection = new NameValueCollection(1);
myCollection.Add("siteMapFile", "Web.sitemap");
xmlSiteMap.Initialize("provider", myCollection);
xmlSiteMap.BuildSiteMap();


Then we have to navigate through the tree structure identifying the nodes that we are interested in...

private static void ProcessNode(XmlWriter writer, SiteMapNode node, string attribute)
{
foreach (SiteMapNode siteMapNode in node.ChildNodes)
{
string actualUrl;
if (siteMapNode.HasChildNodes)
{
ProcessNode(writer, siteMapNode, attribute);
}
actualUrl = siteMapNode[attribute];
if (string.IsNullOrEmpty(actualUrl))
{
actualUrl = siteMapNode.Url;
if (string.IsNullOrEmpty(actualUrl))
{
continue;
}
}
WriteUrl(actualUrl, writer);
}
}


And lastly building up the XML response...

if(Uri.IsWellFormedUriString(actualUrl, UriKind.Relative))
{
FileInfo fileInfo = new FileInfo(HostingEnvironment.MapPath(actualUrl));
writer.WriteStartElement("url");
writer.WriteElementString("loc",
HttpContext.Current.Request.Url.AbsoluteUri.Replace(
HttpContext.Current.Request.Url.AbsolutePath, "") + actualUrl);
writer.WriteElementString("lastmod", fileInfo.LastWriteTime.ToString("yyyy-MM-dd"));
writer.WriteElementString("changefreq", "monthly");
writer.WriteEndElement();
}


I have implemented this HttpHandler in a seperate component, simply to allow me to reference this utility from other websites that I have written, but you may decide to simply plug in the HttpHandler to your main project....the decision is yours.

Now, hopefully you've also realised that you'll need to reference the HttpHandler in the website's web.config......like so...

<httpHandlers>
<add verb="*" path="sitemap.axd" type="CodeConsults.HttpHandlers.Generic.Sitemap" validate="false"/>
</httpHandlers>

Now you can run your website, point your favoured browser to http://website/sitemap.axd and voila, your sitemap is available for all to see. But the last stage should be to update your robots.txt file to tell the search engines that you have a nice sitemap for them to use. To do this simply open your robots.txt file and enter the following...

sitemap: http://www.codeconsults.com/sitemap.axd

And that's it! Here's a link to the source code for the class... I'll leave the rest to you

Alternative to Server.MapPath...use HostingEnvironment.MapPath

A call to the following context sensitive MapPath function does exactly what the documentation says it does.

System.Web.HttpContext.Current.Server.MapPath("\filename.txt");

It returns the physical file path that corresponds to the specified virtual path on the Web server. But this isn't always what you want as it works on the current context which may be a web form running in a sub-directory of the main website.

In this case the solution is simple. Use the following code to map the path to a physical path on the server

System.Web.Hosting.HostingEnvironment.MapPath("\filename.txt");

How to get the size of blob data

I currently get blob data from a SQL database but a collegue pointed out that I wasn't making it easy for him to see how much data is returned from the query. After a bit of head-scratching he found the solution which as usual is not in the MSDN documentation. Here's an example of how to do it...

using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
reader.Read();

// Get length of data by pass null as the byte array parameter
long dataLength = reader.GetBytes(0, 0, null, 0, 0);

Simple really....if only Microsoft would tell us this in the first place...!

Shrinking a SQL Server log file (.ldf)

I've been working quite a bit recently with a database that I have been constantly deleting from and re-adding data for testing an uploader. The uploader carries out hundreds of SQL statements and as such it has resulted in a rather large log file, which over time would get to the unmanageable size.

So I had a quick look for ways to reduce the log file size when I want to... In the end it's simple Smile

DBCC SHRINKDATABASE()

Simply put in the name of the database and execute it. Here's a link for more information on the SHRINKDATABASE command

Re-seeding an identity column in SQL using DBCC CHECKIDENT

Here's a handy bit of SQL that i've been using to reset the seed of an identity column for a table. I have often needed to do this when i've cleaned out a table, but have needed to start the identity entry back at 0 rather than it's current seed value

DBCC CHECKIDENT (<table name>, RESEED, -1)
GO

Simply fill in the name of the table and execute it. This will reseed the identity to -1 meaning that when you next enter a new row it will start at 0

Getting the Calling method through Reflection

Having recently created a simple framework for other developers to use, I needed to create a custom Exception class to use when throwing exceptions.

When throwing an exception in a WinForms application I always generate my own Exception class, which adds some other useful information that I can package along with the actual exception that was originally thrown. Although the Call Stack is included in the actual exception, one thing that I like to do is tell the developer the method in which the exception was originally thrown. This removes the need for the developer to navigate through the exception as all they are initially after is where the Exception occured.

In the past I would have normally simply passed in the method name like so:

throw new Exception(connectionStringSettings, MethodBase.GetCurrentMethod().Name, "Argument is Null", ex);


But I decided to spend some time making this transparent. In the end I was able to put this in the actual Exception class using the following method which is called as a part of the constructor of the Exception class:

private void SetCallingMethod()
{
method = new StackFrame(2, false).GetMethod().Name;
}


In this we open up a new stack trace and navigate to the second frame in the trace (remembering that frame 1 of the stack will result in the constructor method). Now the code to create the new exception is simpler:

throw new Exception(connectionStringSettings, "Argument is Null", ex);

C# Syntax Highlighting for Blogs

Back in November of last year I blogged about an excellent C# tool for quickly running and testing C# code using the Snippet Compiler.

Having just installed BlogEngine.NET, I decided to look more into properly formatting C# code for including in blogs. After a bit of looking I discovered that the solution to my problem was right under my nose. The Snippet Compiler has the HTML File... or HTML to Clipboard options under the File -> Export menu



Simple export the HTML to Clipboard for example and paste into the HTML section of the blog like so:

      /// <summary>
    /// Executes the current command using the ExecuteReader
    /// method and returns each DbDataRecord through
    /// the yield return iterator
    /// </summary>
    /// <returns></returns>
      public IEnumerable<DbDataRecord> GetDbDataRecords()
    {
        using(DbDataReader reader = currentCommand.ExecuteReader())
        {
            foreach (DbDataRecord dbDataRecord in reader)
            {
                yield return dbDataRecord;
            }
        }
    }

You need to slightly edit the pasted HTML as it comes complete with head, title and body tags, but apart from that it just works perfectly

New CodeConsults Ltd blog using BlogEngine.NET

For the last year I've been using Google's Blogger for the company's blog software. Now whilst this is a good piece of blogging software, it is annoying how inflexible it is when you want to can the way it looks etc... My biggest gripe is the screen width of the blog. Firstly you're left with a pitiful amount of width in which to write your blog and secondly it doesn't wrap the text very well at all. Once I had the company's dedicated server in place I decided that it was high time to look at upgrading the blog to something better.......

I've been a keen viewer of Mads Kristensen for quite a while now and have used a couple of his HttpModules in previous jobs. For some time now he's been using his own BlogEngine.NET which he has kindly opened up to the wider world......and here it is!!!

For the time being though I'll be maintaining 2 separate blogs as the Google one does seem to get indexed really well......not surprisingly by Google themselves!

A neater way of getting data out of a DbDataReader

Previously I have always done the following to iterate through a DbDataReader:

..while(reader.Read())
..{
....int index = reader.GetOrdinal("User_Description");
....list.Add(reader[index] as string);
..}


But today I stumbled upon the following which is so much neater:

..foreach (DbDataRecord dbDataRecord in reader)
..{
....int index = dbDataRecord.GetOrdinal("User_Description");
....list.Add(dbDataRecord[index] as string);
..}

I just think it's a lot neater and clearer as to what's going on....

This application has failed to start because the application configuration is incorrect

C++ installations have never been easy and probably never will be. Having not done any C++ release work for a number of years I ran into a problem when releasing a C++ application that I built using Visual Studio 2005.

In the old days you had a nightmare working out which version of the C++ libraries to distribute and where to put them. Unless of course you statically link to the libraries which resolves the problem but makes the files rather larger.

But now with building the application using VS 2005, there is this new problem that arises which results in the obsure message

This application has failed to start because the application configuration is incorrect

After a bit of digging into all of this I found that there is a redistributable package from Microsoft that installs the necessary libraries for C++ that were used by VS2005 to build the application in the first place. Where it puts the libraries I don't know, but to be honest I don't really care as the installation was very simple and worked first time....so i'm happy for now...

Heres a link to the redistributable package from Microsoft..... This is the SP1 version of the package which is required if your VS2005 installation is all up-to-date. If it isn't there is an older version of the package available from Microsoft, or your could do the right thing and get VS2005 up-to-date instead....

Reading other app.config files

For a recent project that had plugins I needed to be able to specify seperate plugin configuration files for each plugin. But out of the box .Net doesn't allow this through the usual ConfigurationManager class without a bit of work first.

In the end the solution is pretty simple, but as with most things, its difficult to find out how to do it if you haven't already... Below is an example of how to load a seperate configuration file and return it's associated Configuration class...

ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap();
fileMap.ExeConfigFilename = "ClassLibrary.dll.config";
return ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);

Enjoy...

Simple YUI Data Table example

There's now a simple working example of YUI's Data Table alongside a normal ASP.NET data grid. For this example i've demonstrated the tables with paging so you can see the impact that the AJAX enabled YUI version has on the browser, compared to the ASP.NET version.

AJAX working example

After finally updating my website at the weekend, I managed to begin what I had always intended to do with the website......play about a bit and put up some useful stuff.

To this end, I have started to put up some working examples of AJAX. Initally only a working example of an AJAX Data grid is up and running, but hopefully this will be expanded to include many different aspects of website design that can be improved through the use of AJAX.

What i've tried to do with the first example is show a standard ASP.NET GridView control with an identically formatted AJAX Data Grid. The primary reason for doing this is to enable the user to really see what AJAX can give you in terms of slick design and feedback. Given a bit more time, this example will be improved to include paging and possibly some other useful features...

New look website and more services

After putting up with the company website for so long I finally decided to change it and give it a bit of a re-vamp. Now that we have dedicated servers running ASP.NET 2.0 and SQL 2000, we've been able to simply and quickly re-design the site around master pages and CSS.

We've now expanded our services also to provide web-hosting from as little as £5 for an ASP.NET 2.0 hosted account, whilst SQL 2000 support can be added for another £5

LRSChauffeurs.co.uk - new CodeConsults website

We've just finished our first customer website LRSChauffeurs

The website was created in ASP.NET 2.0 and used master pages which has made the initial creation of the website so much easier than normal. The site is also hosted on one of our dedicated servers.

As everything was handle by CodeConsults, this allowed us to provide the customer with an extremely cheap and quick service for creating and hosting their website. All in the domain registration, email, hosting and web design was completed within 2 weeks. How's that for service....

C++ C# DateTime Interoperability using DDX DDV

Having recently started a new contract, I was tasked to handle data between a C++ plug-in container and a C# managed dialog view. Microsoft have created a number of additions to C++ to aid with the data flow.
Now these methods etc... are all reasonably easy to use such as:

DDX_ManagedControl(pDX, IDC_EDIT_LOCN, m_setupTab);

Which creates a .Net control and allows DDX and DDV to work. But, as with the majority of Microsoft Help, the examples of actually "doing" the DDX/DDV are the simplest ones:

 if (pDX->m_bSaveAndValidate)
 {
    m_str = m_setupTab->textBox1->Text;
 }
 else
 {
    m_setupTab->textBox1->Text = gcnew System::String(m_str);
 }

But what about handling dates? Well, after a bit of testing, it was pretty simple in the end:

 if (pDX->m_bSaveAndValidate)
 {
    m_str = m_setupTab->textBox1->Text;
    m_fromDate.SetDate(m_setupTab->FromDate.Year,
       m_setupTab->FromDate.Month, m_setupTab->FromDate.Day);
 }
 else
 {
    m_setupTab->textBox1->Text = gcnew System::String(m_str);
    m_setupTab->FromDate.Parse(
       gcnew String(m_fromDate.Format("%Y-%m-%d")));
 }

Enjoy...

Databinding in ASP.NET to System.DBNull

I recently needed to show a database record in a DetailsView. The problem with doing this that I immediately hit was that I needed to show a date field, allow the edit of the date using a calendar control but where the date could be a NULL value. ASP.NET bombs out at the data binding phase with an invalid cast exception.

Unfortunately, the ASP.NET controls do not handle nullable dates and neither do the Telerik controls. After a bit of work I found a neat and relatively simple method to handle NULL dates.

Firstly, bind to the data field as normal, but at the start of the <EditItemTemplate> element enter a code slice like so:
<EditItemTemplate>
..<%# ResolveDate((Container.DataItem as DataRowView)) %>
..<asp:Calendar etc...

Now, create the method ResolveDate to put in the min value if necessary:

public static string ResolveDate(DataRowView row)
{
..if(row["date"] is System.DBNull)
..{
....row["date"] = DateTime.MinValue;
..}

..return string.Empty;
}

This will now allow the page to correctly show no data if the date is NULL, or the result of the data bind.
Now, when we come to editing we need to hook into the OnUpdating event for the data source in order to remove the DateTime.MinValue and use NULL instead, otherwise all the records when edited will end up with the minimum date value. This is simply done by hooking to the event and checking if the value is equal to DateTime.Value and set to null if it is, like so:

protected void database_Updating(Object source, SqlDataSourceCommandEventArgs e)
{
..if((DateTime)e.Command.Parameters["@date"].Value == DateTime.MinValue)
..{
....e.Command.Parameters["@date"].Value = null;
..}
}

More on Watir UI testing

Following from a previous post on Watir UI testing I deployed a UI test script to our CruiseControl .NET build server. Immediately the test failed, yet the test worked fine locally....

After a few head-scratching moments I found the source of the error...(yes, the nunit report was next to useless).... Internet Explorer was automatically popping up a dialog asking the user if they wanted to turn on AutoComplete...!!!!! I found this by simply running the Ruby script manually and voila the pop up can now be seen....

Easier log4Net declaration for a class

When declaring the log4Net property for a class that you wish to use log4Net in, I always used to define it like so:

private static readonly ILog _log = LogManager.GetLogger(typeof(BasicSearch));

But this is not a case of simply copy and paste from one class' declaration to another class because of the type passed into the GetLogger method.

Instead use the following that uses reflection to define the class type to pass into the method

private static readonly ILog _log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

Now you can copy and paste.....but if you've got ReSharper you can even add a user template called l4n for example that puts all of this in for you.....

Watir UI Testing

A collegue recently blogged about NUnit/Watir Integration where NUnit and Watir can be used together to create UI tests. As he pointed out, each NUnit test executes a complete Ruby script, but this shouldn't really cause too much of a problem.

I recently used this and whilst it worked great, there were a few stumbling blocks in the way, mostly on documentation. Now, we use CruiseControl.NET to automate our project builds, and the integration of the Watir tests into the build was a natural progression for the QA team. To acheive this we needed to include the Ruby test scripts into the build and reference them correctly. To acheive this we had to set the properties of each script:
Build Action to Content
Copy to Output Directory to Copy Always

Referencing the scripts was simple in the NUnit test:

WatirAssert.TestPassed(@"search.rb");

Now, with all that being so easy I hoped that the Ruby/Watir side of things would have been the same.......Not so unfortunately. Admittedly, i've never used Ruby before, but that's not the problem. Documentation and references were hard to find.

These are the references that i've so far found reasonably useful

Ruby:
Ruby Reference

Watir:
Watir API Reference

Another problem that I did come up against was the ASP.NET handling of server control's ID and Name attributes. There are; however; solutions out there to handle the ID and name manually, but I decide against this and stick with the standard ASP.NET output. I managed to find the controls on the page using other methods such as known text, styles etc....

Saying all of this, the NUnit and Watir to test UI functionality of your website is a very useful tool. We currently use it to test core functionality like the home page coming up correctly, a basic search working and checking that paging also works.

Enjoy....

Singleton Pattern

In the past i've always created a singleton in the easiest way possible...

    static Singleton instance=null;

   public static Singleton Instance
   {
       get
       {
           if (instance==null)
           {
               instance = new Singleton();
           }
           return instance;
       }
   }

but more recently i've needed to ensure thread-safety with my class instantiation. Here's a solution that I found on www.yoda.arachsys.com

public sealed class Singleton
{
   Singleton()
   {
   }

   public static Singleton Instance
   {
       get
       {
           return Nested.instance;
       }
   }
  
   class Nested
   {
       // Explicit static constructor to tell C# compiler
       // not to mark type as beforefieldinit
       static Nested()
       {
       }

       internal static readonly Singleton instance = new Singleton();
   }
}

This solution is just so much neater and still easy to follow (at least that's what I think!). Don't forget the static constructor though to force the compiler to not generate type initialisers. However, for simplicity I'd say the following is the easiest for someone to follow

public sealed class Singleton
{
   static readonly Singleton instance=new Singleton();

   // Explicit static constructor to tell C# compiler
   // not to mark type as beforefieldinit
   static Singleton()
   {
   }

   Singleton()
   {
   }

   public static Singleton Instance
   {
       get
       {
           return instance;
       }
   }
}

Credit to www.yoda.arachsys.com for the above code....

ASP.NET AJAX Cheat Sheets

Here's some downloadable cheat sheets for ASP.NET AJAX from ASP.NET Resources

There's another copy of the sheets here if this link isn't working

ASP.NET 2.0 Life Cycle

Found this useful image detailing the page life cycle. Note however that the Page Unload event is missing for some unknown reason, but apart from this it is useful

ASP.NET 2.0 Page Life-cycle

ASP.NET 2.0 Forum Software

Started using some forum software at my current job. It's from YetAnotherForum and it's an open-source platform written in ASP.NET 2.0 and uses a SQL database for it's data store.

In just a few minutes you'll be up-and-running. Simply create a new database in your SQL Server, create a new user and give it access to the database, load compile and run the solution and you're given an installation wizard that sets-up the database and that's it......!

It's packed fully of all the expected forum-type features and comes along with useful error logging aswell.....

enjoy....

Serving XML from a WebService in ASP.NET 2.0

Recently needed to server some XML from a SQL Server 2005 stored procedure and couldn't believe how simple it was to do...

Specify your WebMethod to return XmlDataDocument, then inside the method call your SP then:

XmlDataDocument xml = new XmlDataDocument();
xml.Load(command.ExecuteXmlReader());

return xml;

and that's it!!! I've added this article also to CodeProject

SQL to split a comma seperated string

Found a handy table-valued function (here) that splits a comma seperated string and returns the results as a table. The original code splits on a comma and a space but I only needed to split on the comma so I removed the code handling the spaces.

Simply call the function like so:
select * from dbo.SplitWords('Ashbourne,Derby,Nottingham')
and get the results back like so:

SQL Exception Handling

Here's a nice and simple way to catch, handle and report SQL exceptions that i've worked on. Firstly, add in your usual TRY/CATCH statement like so:

BEGIN TRY
-- some SQL code...
END TRY
BEGIN CATCH
EXEC [StandardCatch]
END CATCH

Then add the stored procedure below which generates a user-friendly message and re-throws the exception:

CREATE PROCEDURE [dbo][StandardCatch]
AS
BEGIN
SET NOCOUNT ON

IF ERROR_NUMBER() IS NULL
RETURN 0

DECLARE @ErrorLine int
DECLARE @ErrorMessage nvarchar(4000)
DECLARE @ErrorNumber int
DECLARE @ErrorProcedure nvarchar(126)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int

SELECT @ErrorLine = ERROR_LINE(),
@ErrorMessage = ISNULL(ERROR_MESSAGE(), '-'),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = CASE WHEN ERROR_STATE() < 1
OR ERROR_STATE() > 127 THEN 1
ELSE ERROR_STATE()
END
RAISERROR ( 'ERROR rethrown from CATCH block: Number: %i, Message: %s, Procedure: %s, Line %i.',
@ErrorSeverity, @ErrorState, @ErrorNumber, @ErrorMessage,
@ErrorProcedure, @ErrorLine )
RETURN -1
END


Now in your calling code, be it C# etc..., you can get and elegantly handle/report the error to the user

Reversable Collection

I came up to a problem the other day where I needed to find a way of easily iterating backwards through a generic collection. Now after a few Google searches I found a couple of examples using the standard ArrayList class but nothing using generics.
So I decided to quickly write on myself and here's the code...

Firstly, create a reverse enumerator class like so:

public sealed class ReverseEnumerator : IEnumerator
{
private IList _list;
private int _index;
public ReverseEnumerator(IList list)
{
_list = list;
Reset();
}

public T Current { get { return (T)_list[_index]; } }
object IEnumerator.Current { get { return _list[_index]; } }

public bool MoveNext()
{
if(_index == 0)
{
return false;
}
_index--;
return true;
}

public void Reset()
{
_index = _list.Count;
}

public void Dispose()
{
}
}


Then create the reversable collection class:

public sealed class ReversableCollection : List
{
private bool _reverse = false;

new public IEnumerator GetEnumerator()
{
if (_reverse)
{
return new ReverseEnumerator(this);
}
return base.GetEnumerator();
}

new public bool Reverse
{
get { return _reverse; }
set { _reverse = value; }
}
}

Now call the class like so:

ReversableCollection rc = new ReversableCollection();
rc.Reverse = true;
rc.Add("1");
rc.Add("2");
rc.Add("3");
rc.Add("4");

foreach (string s in rc)
{
_log.Info(s);
}

And that's it...! Simple