Backup and Restore SQL Server databases programmatically with SMO

http://www.mssqltips.com/tip.asp?tip=1849

Problem
In my last set of tips, I discussed SMO at a basic level.  In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO.  I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.

Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.

Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).

Examples

C# Code Block 1 – Full Backups – This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up.  The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set.  The Backup class also has a property called ExpirationDate which indicates how long backup data is considered valid and to expire the backup after that date. The backup object instance generates several events during the backup operation, we can write event-handlers for these events and wire them up with events. This is what I am doing for progress monitoring.  I am wiring up CompletionStatusInPercent and Backup_Completed methods (event-handlers) with PercentComplete and Complete events of backup object instance.  Finally, I am calling the SqlBackup method for starting up the backup operation, SMO provides a variant of this method called SqlBackupAsync if you want to start the backup operation asynchronously.

C# Code Block 1 – Full Database Backup

Backup bkpDBFull = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFull.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFull.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBFull.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File); bkpDBFull.BackupSetName = “Adventureworks database Backup”; bkpDBFull.BackupSetDescription = “Adventureworks database – Full Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBFull.Initialize = false;   /* Wiring up events for progress monitoring */ bkpDBFull.PercentComplete += CompletionStatusInPercent; bkpDBFull.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You can also use SqlBackupAsync method to perform the backup  * operation asynchronously */ bkpDBFull.SqlBackup(myServer);

private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) {     Console.Clear();     Console.WriteLine(“Percent completed: {0}%.”, args.Percent); } private static void Backup_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine(“Hurray…Backup completed.” );     Console.WriteLine(args.Error.Message); } private static void Restore_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine(“Hurray…Restore completed.”);     Console.WriteLine(args.Error.Message); }

Result:

C# Code Block 2 Differential Backups - The process of issuing differential backups is not much different from issuing full backups. To issue a differential backup, set the property Incremental = true. If you set this property the incremental/differential backup will be taken since last full backup.

C# Code Block 2 – Differential Database Backup

Backup bkpDBDifferential = new Backup(); /* Specify whether you want to backup database, files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to backup */ bkpDBDifferential.Database = myDatabase.Name; /* You can issue backups on several media types (disk or tape), here I am * using the File type and storing the backup on the file system */ bkpDBDifferential.Devices.AddDevice(@”D:\AdventureWorksDifferential.bak”, DeviceType.File); bkpDBDifferential.BackupSetName = “Adventureworks database Backup”; bkpDBDifferential.BackupSetDescription = “Adventureworks database – Differential Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media.  * You can specify Initialize = true to make the backup as the first set  * on the medium and to overwrite any other existing backup sets if the  * backup sets have expired and specified backup set name matches  * with the name on the medium */ bkpDBDifferential.Initialize = false;   /* You can specify Incremental = false (default) to perform full backup  * or Incremental = true to perform differential backup since most recent  * full backup */ bkpDBDifferential.Incremental = true;   /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer);

Result:

C# Code Block 3 Transaction Log Backups - Again the process of issuing transactional log backup is not much different from issuing full backups. To issue transactional log backups, set the property Action = BackupActionType.Log instead of BackupActionType.Database as in the case of a full backup.

C# Code Block 3 – Transaction Log Backup

Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File); bkpDBLog.BackupSetName = “Adventureworks database Backup”; bkpDBLog.BackupSetDescription = “Adventureworks database – Log Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBLog.Initialize = false;   /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBLog.SqlBackup(myServer);

Result:

C# Code Block 4 Backup with Compression - SQL Server 2008 introduces a new feature to issues backups in a compressed form.  As such, SMO for SQL Server 2008 has been enhanced to support this feature. If you look at the image below you will notice the compressed backup size is almost 25% of full backup, though the level of compression depends on the several factors.

C# Code Block 4 – Backup with Compression (SQL Server 2008)

Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008,  * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@”D:\AdventureWorksFullWithCompression.bak”, DeviceType.File); bkpDBFullWithCompression.BackupSetName = “Adventureworks database Backup – Compressed”; bkpDBFullWithCompression.BackupSetDescription = “Adventureworks database – Full Backup with Compressin – only in SQL Server 2008″; bkpDBFullWithCompression.SqlBackup(myServer);

Result:

C# Code Block 5 Full or Differential Restores – Thus far we have worked through SMO backup examples. Now let’s change gears to restore with SMO.  SMO provides a Restore class to restore a database, similar to the Backup class.  With these classes it is necessary to specify the Action property to indicate the type of restore i.e. database, files or log.  In a scenario where if you have additional differential or log backups to be restored after it is necessary to specify the NoRecovery = true except for the final restore.  In this example, I am wiring up events of the Restore object instance to event-handlers for progress monitoring. Finally the SqlRestore method is called to start the restoration. If you want to start the restore operation asynchronously you would need to call SqlRestoreAsync method instead.

C# Code Block 5 – Database Restore – Full or Differential

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database, files or log */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server  * instance. If you can specify ReplaceDatabase = true to create new  * database image regardless of the existence of specified database with  * the same name */ restoreDB.ReplaceDatabase = true;    /* If you have a differential or log restore after the current restore,  * you would need to specify NoRecovery = true, this will ensure no  * recovery performed and subsequent restores are allowed. It means it  * the database will be in a restoring state. */ restoreDB.NoRecovery = true;   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore the database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

To restore a database SQL Server needs to acquire exclusive lock on the database being restored.  If you try to restore a database which is in use, SQL Server will throw the following exception:

C# Code Block 6 Transaction Log Restore – The process of restoring a transactional log is similar to restoring a full or differential backup. While restoring a transactional log, it is necessary to set the property Action = RestoreActionType.Log instead of RestoreActionType.Database as in case of full/differential restore.  Here is an example:

C# Code Block 6 – Database Restore – Log

Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);   /* You can specify NoRecovery = false (default) so that transactions are  * rolled forward and recovered. */ restoreDBLog.NoRecovery = false;   /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You cab also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDBLog.SqlRestore(myServer);

Result:

C# Code Block 7 Database Restore to a new location – At times you need to create a new database and restore to a new physical location which differs from the original database. For that purpose, the Restore class has the RelocateFiles collection which can be completed for each file with the new location as shown in the code below.

C# Code Block 7 Database Restore – Different location

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + “New”; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server instance.  * You can specify ReplaceDatabase = true to create new database  * regardless of the existence of specified database */ restoreDB.ReplaceDatabase = true;   /* If you have a differential or log restore to be followed, you would  * specify NoRecovery = true, this will ensure no recovery is done  * after the restore and subsequent restores are completed. The database  * would be in a recovered state. */ restoreDB.NoRecovery = false;   /* RelocateFiles collection allows you to specify the logical file names  * and physical file names (new locations) if you want to restore to a  * different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Data”, @”D:\AdventureWorksNew_Data.mdf”)); restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Log”, @”D:\AdventureWorksNew_Log.ldf”));   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

Complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace BackupAndRestoreWithSMO2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Server myServer = new Server(@”ARSHADALI-LAP\ARSHADALI”);
            try
            {
                //Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                myServer.ConnectionContext.Connect();
                Database myDatabase = myServer.Databases["AdventureWorks"];
                BackupDatabaseFull(myServer, myDatabase);
                //BackupDatabaseDifferential(myServer, myDatabase);
                //BackupDatabaseLog(myServer, myDatabase);
                //BackupDatabaseFullWithCompression(myServer, myDatabase);
               
                RestoreDatabase(myServer, myDatabase);
                //RestoreDatabaseLog(myServer, myDatabase);
                //RestoreDatabaseWithDifferentNameAndLocation(myServer, myDatabase);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.InnerException.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
                Console.WriteLine(“Press any key to terminate…”);
                Console.ReadKey();
            }
        }
        private static void BackupDatabaseFull(Server myServer, Database myDatabase)
        {
            Backup bkpDBFull = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFull.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBFull.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBFull.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            bkpDBFull.BackupSetName = “Adventureworks database Backup”;
            bkpDBFull.BackupSetDescription = “Adventureworks database – Full Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBFull.Initialize = false;
            /* Wiring up events for progress monitoring */
            bkpDBFull.PercentComplete += CompletionStatusInPercent;
            bkpDBFull.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBFull.SqlBackup(myServer);
        }
        private static void BackupDatabaseDifferential(Server myServer, Database myDatabase)
        {
            Backup bkpDBDifferential = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBDifferential.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBDifferential.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBDifferential.Devices.AddDevice(@”D:\AdventureWorksDifferential.bak”, DeviceType.File);
            bkpDBDifferential.BackupSetName = “Adventureworks database Backup”;
            bkpDBDifferential.BackupSetDescription = “Adventureworks database – Differential Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBDifferential.Initialize = false;
            /* You can specify Incremental = false (default) to perform full backup
             * or Incremental = true to perform differential backup since most recent
             * full backup */
            bkpDBDifferential.Incremental = true;
            /* Wiring up events for progress monitoring */
            bkpDBDifferential.PercentComplete += CompletionStatusInPercent;
            bkpDBDifferential.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBDifferential.SqlBackup(myServer);
        }
        private static void BackupDatabaseLog(Server myServer, Database myDatabase)
        {
            Backup bkpDBLog = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBLog.Action = BackupActionType.Log;
            /* Specify the name of the database to back up */
            bkpDBLog.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);
            bkpDBLog.BackupSetName = “Adventureworks database Backup”;
            bkpDBLog.BackupSetDescription = “Adventureworks database – Log Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBLog.Initialize = false;
            /* Wiring up events for progress monitoring */
            bkpDBLog.PercentComplete += CompletionStatusInPercent;
            bkpDBLog.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBLog.SqlBackup(myServer);
        }
        private static void BackupDatabaseFullWithCompression(Server myServer, Database myDatabase)
        {
            Backup bkpDBFullWithCompression = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFullWithCompression.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBFullWithCompression.Database = myDatabase.Name;
            /* You can use back up compression technique of SQL Server 2008,
             * specify CompressionOption property to On for compressed backup */
            bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On;
            bkpDBFullWithCompression.Devices.AddDevice(@”D:\AdventureWorksFullWithCompression.bak”, DeviceType.File);
            bkpDBFullWithCompression.BackupSetName = “Adventureworks database Backup – Compressed”;
            bkpDBFullWithCompression.BackupSetDescription = “Adventureworks database – Full Backup with Compressin – only in SQL Server 2008″;
            bkpDBFullWithCompression.SqlBackup(myServer);
        }
        private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)
        {
            Console.Clear();
            Console.WriteLine(“Percent completed: {0}%.”, args.Percent);
        }
        private static void Backup_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine(“Hurray…Backup completed.” );
            Console.WriteLine(args.Error.Message);
        }
        private static void Restore_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine(“Hurray…Restore completed.”);
            Console.WriteLine(args.Error.Message);
        }
        private static void RestoreDatabase(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name;
            /* Specify whether you want to restore database or files or log etc */
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            /* You can specify ReplaceDatabase = false (default) to not create a new image
             * of the database, the specified database must exist on SQL Server instance.
             * If you can specify ReplaceDatabase = true to create new database image
             * regardless of the existence of specified database with same name */
            restoreDB.ReplaceDatabase = true;

            /* If you have differential or log restore to be followed, you would need
             * to specify NoRecovery = true, this will ensure no recovery is done after the
             * restore and subsequent restores are allowed. It means it will database
             * in the Restoring state. */
            restoreDB.NoRecovery = true;
            /* Wiring up events for progress monitoring */
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDB.SqlRestore(myServer);
        }
        private static void RestoreDatabaseLog(Server myServer, Database myDatabase)
        {
            Restore restoreDBLog = new Restore();
            restoreDBLog.Database = myDatabase.Name;
            restoreDBLog.Action = RestoreActionType.Log;
            restoreDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);
            /* You can specify NoRecovery = false (default) so that transactions are
             * rolled forward and recovered. */
            restoreDBLog.NoRecovery = false;
            /* Wiring up events for progress monitoring */
            restoreDBLog.PercentComplete += CompletionStatusInPercent;
            restoreDBLog.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDBLog.SqlRestore(myServer);
        }
        private static void RestoreDatabaseWithDifferentNameAndLocation(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name + “New”;
            /* Specify whether you want to restore database or files or log etc */
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            /* You can specify ReplaceDatabase = false (default) to not create a new image
             * of the database, the specified database must exist on SQL Server instance.
             * You can specify ReplaceDatabase = true to create new database image
             * regardless of the existence of specified database with same name */
            restoreDB.ReplaceDatabase = true;
            /* If you have differential or log restore to be followed, you would need
             * to specify NoRecovery = true, this will ensure no recovery is done after the
             * restore and subsequent restores are allowed. It means it will database
             * in the Restoring state. */
            restoreDB.NoRecovery = false;
            /* RelocateFiles collection allows you to specify the logical file names and
             * physical file names (new locations) if you want to restore to a different location.*/
            restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Data”, @”D:\AdventureWorksNew_Data.mdf”));
            restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Log”, @”D:\AdventureWorksNew_Log.ldf”));
            /* Wiring up events for progress monitoring */
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDB.SqlRestore(myServer);
        }
    }
}

Notes:

• Location of assemblies in SQL Server 2005 is the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• In SQL Server 2005, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.
• In SQL Server 2008, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.
• If you are restoring a transaction log, you can specify a particular point in time with ToPointInTime property of the Restore class.
• The Restore class methods (SqlVerify, SqlVerifyAsync and SqlVerifyLatest) to verify and validate (backup set is complete and the entire backup is readable) the backup media before restoration.
• The SQL Server service account must have access to the folders where backup or restore operations are executed.
• You need to have sufficient permissions to perform backup and restore operations. For example, for backup you need to be either in sysadmin/db_owner/db_backupoperator role or must have BACKUP DATABASE or BACKUP LOG permission on the database.
• If you try to connect SQL Server 2008 from SMO 2005, you will get an exception “SQL Server <10.0> version is not supported”.

Next Steps

• Review the following tips:
  • Getting started with SQL Server Management Objects (SMO)
  • SQL Script Generation Programmatically with SMO
  • Exploring Database Backup Compression in SQL Server 2008
• Review Backup and Restore classes on MSDN.
• Review the MSSQLTips Backup and Recovery category with 40+ tips.

Readers Who Read This Tip Also Read

• How to identify when a database was restored, the source of the backup and the date of the backup
• How to Backup and Restore a SQL Server FILESTREAM Enabled Database
• Restore a SQL Server Database to a New Database to Minimize Downtime
• Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008
• Copy Only Backup for SQL Server 2005 and SQL Server 2008
• More…

Snippet Compiler

http://www.sliver.com/dotnet/SnippetCompiler/

David Hayden on Design Patterns C#

http://davidhayden.com/blog/dave/category/22.aspx?Show=All

Day of Patterns & Practices Tomorrow – Tampa, Florida RoadShow

posted @ Wednesday, January 30, 2008 10:02 AM | Feedback (0)

Software Development Tip: Avoid Race Conditions Using Tester-Doer Pattern

One of the patterns that came up today in a code review was the Tester-Doer Pattern. One sees this pattern a lot especially when the “doer” side of the equation has a pretty decent performance penalty. Essentially, you test a condition to make sure you need to do the “doer“ operation and pay that penalty. Sometimes this can cause race conditions. Read more…

posted @ Wednesday, September 26, 2007 9:49 PM | Feedback (0)

Chain of Responsibility Pattern – Builder Pattern – Fluent Interfaces

Using the Chain of Responsibility Pattern, Builder Pattern, and Fluent Interfaces to find the appropriate constructor to inject dependencies into as part of the sample Dependency Injection Application Block I created using the Application Block Software Factory in Enterprise Library 3.1. Read more…

posted @ Wednesday, September 19, 2007 4:21 PM | Feedback (0)

Model View Presenter Pattern – User Interface Design Patterns

posted @ Wednesday, September 13, 2006 5:27 PM | Feedback (0)

Contract-First-Design and Dependency Injection ( Microkernel )

posted @ Tuesday, September 05, 2006 7:00 PM | Feedback (0)

O/R Mapping Caching for Performance vs. Uniquing ( Identity Map Design Pattern )

posted @ Tuesday, September 05, 2006 11:38 AM | Feedback (0)

Active Record To Domain Objects and Data Access Objects – Data Access Design Patterns

posted @ Friday, September 01, 2006 2:48 PM | Feedback (0)

Active Record Design Pattern Doesn’t Require Static Finder Methods

posted @ Friday, September 01, 2006 11:21 AM | Feedback (0)

Dependency Injection Tools and Inversion of Control – Applying Domain-Driven Design and Patterns

posted @ Friday, July 21, 2006 1:16 PM | Feedback (0)

Agile Principles, Patterns, and Practices in C# by Robert Martin

I just got back from a long and much needed vacation. While spending most of the day trying to make a dent in my Inbox, I came across an email from Amazon mentioning a new book from Robert Martin, called Agile Principles, Patterns, and Practices in C#…

posted @ Monday, July 03, 2006 11:34 PM | Feedback (0)

Domain-Driven Design Using Active Record in .NET

posted @ Wednesday, June 21, 2006 8:56 PM | Feedback (0)

ActiveRecord Example and Object-Relational Mapping Anti-Pattern – Not Always So Cut and Dry

posted @ Wednesday, June 14, 2006 2:03 PM | Feedback (0)

DLinq, LLBLGen Pro, and ActiveRecord Examples using ASP.NET 2.0

posted @ Monday, June 12, 2006 3:57 PM | Feedback (0)

Castle ActiveRecord – Active Record Pattern Built on NHibernate – ASP.NET C#

posted @ Monday, June 12, 2006 12:15 PM | Feedback (0)

ActiveRecord Pattern and Layer Supertype – Domain Model and Domain-Driven Design

posted @ Sunday, June 11, 2006 10:06 PM | Feedback (0)

Active Record Design Pattern – Domain Driven Design and Domain Layer – Object Persistence

posted @ Saturday, June 10, 2006 10:28 PM | Feedback (0)

Singleton Design Pattern and ReaderWriterLock Class in CLR via C# – Design Pattern Examples

posted @ Tuesday, June 06, 2006 10:50 AM | Feedback (0)

Abstract Factory Design Pattern – DbProviderFactory – Factory Method

posted @ Tuesday, April 04, 2006 4:51 PM | Feedback (0)

OOP and Design Patterns Resources – Books Websites Articles

posted @ Tuesday, March 14, 2006 9:45 PM | Feedback (0)

.NET 2.0 Provider Model – Polymorphism – Factory Method – ADO.NET 2.0 Data Providers

posted @ Sunday, October 30, 2005 7:22 PM | Feedback (0)

Builder Design Pattern Part 2 – Hotel Reservation EDI Example – Design Patterns in C#

posted @ Wednesday, October 26, 2005 7:07 PM | Feedback (0)

Builder Design Pattern Part I – DBConnectionStringBuilder and SqlConnectionStringBuilder in ADO.NET 2.0

posted @ Tuesday, October 25, 2005 1:59 PM | Feedback (0)

Web Applications: N-Tier vs. N-Layer – Benefits and Trade-Offs

posted @ Friday, July 22, 2005 2:06 PM | Feedback (1)

Strategy Design Pattern and Open-Closed Principle

posted @ Friday, July 01, 2005 5:22 PM | Feedback (0)

Strategy Design Pattern and Dependency-Inversion Principle

posted @ Monday, June 27, 2005 7:54 PM | Feedback (0)

Design Patterns in the .NET Framework – Adapter Factory Strategy Composite Template Method

posted @ Thursday, June 16, 2005 7:28 PM | Feedback (2)

Design Patterns and Agile Software Development

posted @ Friday, May 27, 2005 4:09 PM | Feedback (3)

Singleton Pattern in C# – Provider Model – Community Server and DotNetNuke

posted @ Wednesday, April 27, 2005 9:06 PM | Feedback (6)

DotNetNuke Architecture – Digging Into the DNN Source Code

posted @ Sunday, April 03, 2005 10:31 PM | Feedback (6)

Design Patterns in C# – Construction Pattern – Factory Method

posted @ Saturday, April 02, 2005 10:42 PM | Feedback (3)

GRASP Patterns – Information Expert – Create a Shopping Cart

posted @ Sunday, March 27, 2005 5:08 PM | Feedback (4)

PatternShare – Design Patterns Shared by .NET Architects and .NET Developers – Software Patterns

posted @ Saturday, February 05, 2005 8:06 PM | Feedback (1)

Command-Query Separation Principle Revisted – C# Int32.TryParse – Telling vs. Asking – Code Reading and Refactoring

posted @ Sunday, January 16, 2005 1:44 PM | Feedback (1)

Applying UML and Patterns – Command Query Separation Principle – GRASP Patterns – Ecommerce and Shopping Cart

posted @ Saturday, January 15, 2005 7:56 PM | Feedback (2)

Refactoring to Patterns – Thoughts on Test-Driven Development and Design Patterns and Extreme Programming

posted @ Sunday, December 19, 2004 12:04 PM | Feedback (2)

Domain-Driven Design – Layered Applications – More on High Cohesion and Separation of Concerns

posted @ Sunday, December 12, 2004 10:19 AM | Feedback (7)

Applying UML and Patterns – GRASP High Cohesion Pattern – Described using Controller and Creator GRASP Patterns

posted @ Friday, December 10, 2004 9:00 PM | Feedback (12)

Refactoring – Code Smells – Refactoring Patterns – Object Design – Somewhat Like Database Normalization

posted @ Thursday, December 09, 2004 1:23 PM | Feedback (14)

Applying UML and Patterns: Controller GRASP Pattern – Model View Controller Design Pattern – First Object Beyond UI Layer

posted @ Sunday, November 28, 2004 8:58 PM | Feedback (11)

.NET Application Development Guidelines – Factories vs. Constructors

posted @ Sunday, October 24, 2004 7:05 PM | Feedback (1)

Provider Model Design Pattern and Specification – ASP.NET Whidbey – Visual Studio 2005 – Design Patterns C#

posted @ Wednesday, May 19, 2004 12:21 PM | Feedback (4)

Design Patterns in C# Book – Sarasota Website Development – Florida

posted @ Tuesday, May 18, 2004 8:56 PM | Feedback (5)

Design Patterns C# .NET – Strategy Observer Adapter Singleton Template Method Wrapper Facade – Tampa Web Developer – Florida

posted @ Monday, May 17, 2004 8:55 AM | Feedback (4)

Exception Lovers

http://www.vijaymukhi.com/documents/books/csadv/chap9.htm

Chapter 9

Miscellaneous

Members

There are only two types of entities that can contain members, namely, types and namespaces. These members can be accessed using the same rules i.e. the name of an entity to be followed by a dot ‘.’ and then the member name to be given. This cardinal rule is never ever broken.

All derived classes inherit every member of a base class with the exception of constructors and destructors. This rule applies to private members also. Regardless of inheriting all the members, the base class members may/may not be accessible to the derived class members. Thus, the access modifiers have nothing to contribute to inheritance. In other words, the rules of inheritance ignore the access modifiers.

If a namespace is not specified for a member, it belongs to a default namespace called the global namespace. Like it or not, all members are part and parcel of a namespace. They cannot exist out of a namespace under any circumstance. It makes logical sense creating our own namespaces thereby avoiding the unnecessary clutter in the global namespace with the members we create.

The word int is an alias for System.Int32. A variable of type int is internally known as System.Int32. The basic types like int are internally not known by their name but something else that is entirely different.

a.cs

public class zzz

{

public static void Main()

{

System.Type t = typeof(int);

System.Console.WriteLine(t.FullName);

}

}

Output

System.Int32

The above example reconfirms the point we enumerated a little earlier. The compiler does not understand an int as a data type but knows all about a structure called System.Int32 instead. When we coached you earlier about the compiler understanding about data types like int at birth, what we truly meant was that there exists structures in the System namespace relating to these datatypes. The compiler is informed well in advance about the one-to-one connection between the alias name and their structures. When we get some time on our hand, we will list out and explain all the members of this structure.

To explain the above example, we use a keyword typeof, which accepts the name of a class or structure. This returns an object that looks like Type. The Type object consists of methods and properties that publish everything you ever wanted to know about a type but were afraid to ask. We are only using one member called FullName to prove our hypothesis.

a.cs

class zzz

{

public static void Main()

{

string yyy = “hell”;

string s = yyy;

System.Type t = typeof(yyy);

System.Console.WriteLine(s);

System.Console.WriteLine(t);

}

}

class yyy

{

}

Output

hell

yyy

typeof expects the name of a class as a parameter. C# allows a variable name and a class name to be the same. At the line s = yyy, C# assumes yyy to be the name of a variable. yyy is considered to be a class name and t is initialised to the typeof returned by it. Thus, C# uses a lot of intelligence to read between the lines.

Our resident astrologer informed us that at least 1.54234 per cent of our book must be copied from the documentation. To make sure that no ill luck befalls us, we copied a couple of lines from it. Thus an sbyte is an alias for System.Sbyte,byte System.Byte, short System.Int16, ushort System.UInt16, uint System.UInt32, long System.Int64, ulong System.UInt64, char System.Char, float System.Single, double System.Double, decimal System.Decimal, and, finally, bool System.Boolean. Enums are simple derived from System.Enum and are simple constants. Object is an alias for System.Object and string System.String. Interfaces are only derived from object and arrays form System.Array. Delegates are from System.Delegate. These have been mentioned earlier, they have been placed here only as a ready reckner.

Member Lookup

A member lookup is a way by means of which the meaning of a name in a type is determined or figured out. A class can have base classes and all classes have one base class object. The compiler first figures out all the names that match in the class and base class. We can have a constant and a property with the same name in a base class and derived class. We mentioned this earlier, but it merits repeating. If we ever have an override modifier in front of a member name, then this name has overridden the earlier name in the base class. It is thus removed from the list as it is a different name. If the name is a constant, field, property, event, type or enum, it hides all the members of the base class. If it is function, then all non-methods are hidden in the base class. The end result could either be a single non-method name or a series of methods. Anything else is an error.

Every known entity is derived from object. We have mentioned this at least a hundred times already. Value types cannot derive from any other type except object unlike classes and interfaces. However, arrays and delegates are derived from System.Array and System.Delegate respectively.

Name Resolution

After the giving a namespace and a period, we can either use the name of another namespace or the name of a type. If we start, however, with the name of a type, then what follows can be one of the following. Either a type as in a nested types, or a method, a static property, static readonly field or a static field, static event, constant, or finally an enum. Anything else signals an error.

a.cs

class zzz

{

public static void Main()

{

aaa aaa;

aaa bbb = aaa.bbb();

aaa ccc = aaa.ccc;

}

}

class aaa

{

public static aaa ccc = new aaa();

public static aaa bbb()

{

return new aaa();

}

}

If you want to confuse any person using your code, use the above code. The above explanation clearly states that we can create a constant, field, local variable or a parameter with the same name as the name of a type. Thus, we have a class aaa which contains two members. One is a static field called ccc and the other is a static function bbb which returns an aaa type. In Main, we create a variable called aaa, same as the data type. Then we call static members of the class aaa using the name of the type. All this leeway is given to us because we are allowed access to static members of the class aaa without any ambiguity.

An invocation expression can either be a method group or a delegate. Nothing else can be executable. If the function returns void, the result is nothing. Thus, an expression which results in nothing cannot obviously be the operand of a operator, it can only be used a statement.

A method group can either be one method or a group of methods. The parameter types decide which of the methods would be chosen. To execute a certain method, the compiler starts with the type before the method. It then proceeds up the inheritance chain and finds an applicable, accessible, non-override method. If it finds more than one, it uses overload resolution to decide the best one.

Constructor Signatures

a.cs

public class zzz

{

public static void Main()

{

int i = 3;

yyy a = new yyy(ref i);

int j;

yyy b = new yyy(out j);

System.Console.WriteLine(i + ” ” + j);

}

}

class yyy

{

public yyy(ref int p)

{

p = 100;

}

public yyy(out int q)

{

q = 1000;

}

}

Compiler Error

a.cs(18,8): error CS0663: ‘.ctor’ cannot define overloaded methods which differ only on ref and out

a.cs(14,8): (Location of symbol related to previous error)

A constructor cannot define overloaded methods differing only on ref or out. The above logic makes sense as a constructor is nothing but a function that is called automatically at birth. It is in no way different from any other functions that we have worked with.

a.cs

public class zzz

{

public static void Main()

{

yyy a = new yyy(1,2,3);

}

}

class yyy

{

public yyy(params int [] q)

{

foreach ( int i in q)

System.Console.Write(i + ” ” );

}

}

Output

1 2 3

We can use the params modifier to our hearts content in displaying the parameters to the constructors.

a.cs

public class zzz

{

public static void Main()

{

yyy a = new yyy(1,2,3);

}

}

class yyy

{

public yyy(params int [] q)

{

foreach ( int i in q)

System.Console.Write(i + ” ” );

}

public yyy ( int i, int j , int k)

{

System.Console.Write(“int”);

}

}

Output

int

However, the params modifier is given the last priority by the compiler. If there is an exact match, a one-to-one with the parameters in the constructors, the compiler gives precedence to it and the params is ignored for good. For three ints, the constructor with three parameters will be called and for any other combination of ints, the params constructor will be called.

a.cs

public class zzz

{

public static void Main()

{

yyy a = new yyy(1,2,3);

}

}

class yyy

{

public yyy(int p, int p1,int p2,params int [] q)

{

}

public yyy ( int i, int j , int k)

{

System.Console.Write(“int”);

}

}

Output

int

Regardless of both the constructors matching very closely, the compiler yet does not give us any error. This is because it treats the params as a second class citizen of the world. We would never like to be in the shoes of a params parameter.

This

This is only permitted to be used in three places namely a constructor, instance method and an instance accessor.

a.cs

class zzz

{

public static void Main()

{

yyy a = new yyy();

}

}

class yyy

{

public yyy()

{

System.Console.WriteLine(this);

if ( this is yyy)

System.Console.WriteLine(“yes yyy”);

if ( this is zzz)

System.Console.WriteLine(“yes zzz”);

}

public static implicit operator string (yyy a)

{

return “hi”;

}

}

Compiler Warning

a.cs(13,6): warning CS0183: The given expression is always of the provided (‘yyy’) type

a.cs(15,6): warning CS0184: The given expression is never of the provided (‘zzz’) type

Output

hi

yes yyy

A this in a constructor is considered to be the value of the data type of the class that contains the constructor. In the above program, the is operator is used to reconfirm the above statement. Also, the implicit string operator is called as the this has to be converted to a string for WriteLine function. Ditto for instance members and accessors. Comment out the string operator and the output will be

yyy

yes yyy

For a constructor in a structure, the rules change dramatically. Here it is a variable. Like earlier, it stands for a reference to the structure it is placed in, but a major difference is that it is classified as an out parameter. Thus, all the members of the structure must be initialized before we leave the constructor.

In the case of an instance method, everything remains the same as above, but now it behaves as a ref parameter instead of an out. Being a ref parameter, someone else is responsible for initializing the variable. The function can access all the instance variables in the structure and also infringe the benefit of changing them if we so choose to. A this is not used on anything remotely connected with static or in variable initializer of a field declaration. A program relating to this is as follows.

a.cs

class zzz

{

int j = 10;

int i = this.j;

public static void Main()

{

}

}

Compiler Error

a.cs(4,9): error CS0027: Keyword this is not available in the current context

This variable is available only in certain places within a class type and can be assumed as the first parameter to the functions in the type. If we have a function abc accepting two ints as a parameter, we would write it as abc(int i, int j). In class yyy, the function gets rewritten as abc( yyy this, int i, int j).

This is passed as the first parameter and refers to the class the function resides in. As the programming language C++ called this reference variable as this, C# copied the same concept but with a small change for structures.

Base

We use the variable base in a completely different manner. A function in the base class gets hidden by the same function name in a derived class.

a.cs

class zzz

{

public static void Main()

{

aaa a = new aaa();

a.abc();

}

}

class yyy

{

public virtual void abc()

{

System.Console.WriteLine(“yyy abc”);

}

}

class xxx : yyy

{

public override void abc()

{

System.Console.WriteLine(“xxx abc”);

base.abc();

}

}

class aaa : xxx

{

public override void abc()

{

System.Console.WriteLine(“aaa abc”);

base.abc();

}

}

Output

aaa abc

xxx abc

yyy abc

The base variable has absolutely nothing to do with the modifiers new and override. It simply calls the function in the base class without bothering about new or override to its existing function. It simply does not care at all. Thus, using base, we can call a function from the base class irrespective of the modifiers on it.

We will get an error if base is used in an abstract function. Base, similar to this, is valid only in a constructor, instance method or accessor. All the methods from the viewpoint of base, are non virtual.

Writing base.abc will caste the this pointer of the current class to its lower class, i.e. the base class. Internally base.abc in class aaa derived from class xxx becomes ((xxx)this).abc and ditto for an indexer access.

Thus, base and this are conceptually similar, except that one acts on a base class, the other on a derived class.

a.cs

class zzz

{

public static void Main()

{

xxx a = new xxx();

a.abc();

}

}

class yyy

{

public virtual void abc()

{

System.Console.WriteLine(“yyy abc”);

}

}

class xxx : yyy

{

public override void abc()

{

System.Console.WriteLine(“xxx abc”);

((yyy)this).abc();

}

}

Do not run the above program as it will take you to a trip to the moon. Just does not stop at all. All that we said earlier was only conceptually true. You cannot replace a base with a this. The programming language contains both of them and cannot be used interchangeably.

A struct variable has a free default constructor always available. Thus unlike a class initialization, which explicitly initializes the instance variables to zero, the constructor in the case of the struct does the same.

Name Hiding

Name hiding occurs when we inherit from a class or struct and erroneously or otherwise, we introduce a similar name as that in the base class. A constant, field, property, event, or type hides the base class members with the same name.

a.cs

public class zzz

{

public static void Main()

{

yyy a = new yyy();

System.Console.WriteLine(a.i);

a.i = 100;

System.Console.WriteLine(a.i);

}

}

class yyy : xxx

{

public int i = 3;

}

class xxx

{

public const int i = 10;

}

Compiler Warning

a.cs(13,12): warning CS0108: The keyword new is required on ‘yyy.i’ because it hides inherited member ‘xxx.i’

Output

3

100

A million years ago, someone somewhere in the world created a class called xxx. That gentleman, then, added one const member called i. For some reason, we decided to derive from class xxx.

We are allowed to create our own variable i notwithstanding the fact that it was declared to be a const in class xxx. Other than a warning issued by the compiler, we are allowed complete freedom in using whatever names we like in our derived classes, thus oblivious to what the base class has given.

a.cs

public class zzz

{

public static void Main()

{

yyy a = new yyy();

a.i();

}

}

class yyy : xxx

{

public void i()

{

System.Console.WriteLine(“hi”);

}

}

class xxx

{

public const int i = 10;

}

Output

hi

As stated earlier, we have total freedom in doing what we like within the derived class. In the above example, the function i has nothing to do with the const i in class xxx. The compiler does send you a feeble protest in the form of a simple warning but that can be ignored with no loss of life or limb. The same rules apply to indexers also. As repeated earlier, operators can never hide each other ever.

Concealing an inherited member does not issue any error as it would then prevent evolving any base classes independently. Lets us explain why a warning and not an error.

Let us assume that we are deriving from a base class that has a function called pqr. We now decided to create an abc function in the derived class. After a while, the next version of the base class, for some reason, introduces a new function called abc. At that moment, all derived classes from the base class should not break or give an error. In the scheme of things followed by C#, they are different functions and making changes to a base class does not invalidate existing derived classes at all.

Functions

There are five places in the C# language where we can place executable code. These places are constructors, methods, properties, indexers and user-defined operators. Anywhere else, and you permission from the silicon god that is not very forthcoming. Function members are not members of a namespace and thus we can only place the above in a type. You cannot have global functions that are not associated with a type. We cannot use ref and out parameters for indexers, properties or operators. These have to be value parameters only.

a.cs

class zzz

{

public static void Main()

{

zzz a = new zzz();

int i = 0;

a.abc(i++,i++,i++);

System.Console.WriteLine(i);

}

public void abc( int x, int y, int z)

{

System.Console.WriteLine(x + ” ” + y + ” ” + z);

}

}

Output

0 1 2

3

The parameters to a function are read in the order they are written, from left to right. Thus even though we are using a postfix notation, i++, the compiler uses the current value of i to initialize parameter x in function abc, and then increase i by one. Thus, i now has a value of one, that is what the parameter y is initialized to and then it is increased by one. This z becomes two and at the end of the function invocation variable i has a value of three.

Object Elements

a.cs

class zzz

{

public static void Main()

{

byte [] b = new byte[2];

b[1] = 10;

yyy a = new yyy();

System.Console.WriteLine(b[a]);

}

}

class yyy

{

public static implicit operator int(yyy a)

{

return 1;

}

}

Output

10

If we remove the overloaded operator int, we get the following error.

Compiler Error

a.cs(8,28): error CS0029: Cannot implicitly convert type ‘yyy’ to ‘int’

The variable we use in the [] brackets is called the element access and must be one of the following data types namely int, uint, long, ulong or any type that can be implicitly converted to the above type. Thus, without the operator int which was responsible for converting the yyy to an int, we got the above error. On returning 1 in the operator, the array variable becomes b[1].

a.cs

class zzz {

public static void Main()

{

byte [] b = new byte[2];

b[1] = 10;

yyy a = new yyy();

System.Console.WriteLine(b[a]);

}

}

class yyy

{

public static implicit operator uint(yyy a)

{

System.Console.WriteLine(“uint”);

return 1;

}

public static implicit operator long(yyy a)

{

System.Console.WriteLine(“long”);

return 1;

}

}

Output

uint

10

Rules, rules everywhere, but not a drop to drink. The element access of an array as stated earlier must either be an int, uint, long or a ulong. The above order must be followed. The compiler checks for the operators in the above order and on finding the very first match, it uses that operator. It stops short in its tracks and does not complain that it could use both the above operators.

It is one of the few cases where although both the operators were applicable, it does not give us an error. In the above types, a short is not mentioned. Recall that sometime back, we spoke of a short being converted into a int. Operators are allowed to throw an exception. If one takes place then the processing stops.

a.cs

class zzz

{

public static void Main()

{

byte [] b = null;

System.Console.WriteLine(b[1]);

}

}

Output

Unhandled Exception: System.NullReferenceException: Value null was found where an instance of an object was required.

at zzz.Main()

Like a delegate, if we try and access any null object, an exception is thrown. No compile time checks are performed for null as the value. Maybe the next version of the compiler will check for a null and it is on our wish list for Santa Claus. Ditto if we try and exceed the bounds of an array. The exception thrown is IndexOutOfRangeException instead.

a.cs

class zzz {

public static void pqr(ref object x)

{

System.Console.WriteLine(“pqr ” + x);

}

public static void abc(object x)

{

System.Console.WriteLine(“abc ” + x);

}

public static void Main() {

object[] a = new object[2];

object[] b = new string[2];

abc(a[0]);

abc(b[1]);

pqr(ref a[0]);

pqr(ref b[1]);

}

}

Output

abc

abc

pqr

Unhandled Exception: System.ArrayTypeMismatchException: Exception of type System.ArrayTypeMismatchException was thrown.

at zzz.Main()

The rules of array co-variance allow an array to be populated by any data type provided there exist an implicit conversion between them. Array b is an array of objects and there exists an implicit conversion from a string to an object. Hence, we can initialize an array of objects to an array of strings. The array object b is not an array of objects but one that comprises of an array of strings. The compile time data type may be that of an object but the run time data type must be that of a string.

Whenever we have a ref parameter being passed a reference, we use an array instead. The compiler is smart enough to perform a run time check on the data type being passed as an array now can hold dual data types. In the last call to function pqr, we are passing b[0] which at compile time is an object but at run time is a string. Thus, an exception is thrown. Remember exceptions can only be thrown at run time. This holds true only for ref parameters and not value parameters.

a.cs

class zzz {

public static void Main() {

}

int x;

void abc( int a)

{

x = 1;

if (a > 10)

{

float x = 1.0;

}

}

}

Compiler Error

a.cs(10,7): error CS0136: A local variable named ‘x’ cannot be declared in this scope because it would give a different meaning to ‘x’, which is already used in a ‘parent or current’ scope to denote something else

a.cs(10,11): error CS0029: Cannot implicitly convert type ‘double’ to ‘int’

A block is represented by a {} braces. Any variable or identifier created within a block must primarily be unique within that block. Then it must also be unique within the immediate enclosing block. Thus in both blocks, the name must be unique and therefore refer to the same entity. The meaning of the identifier must remain the same within the block.

We get an error in the above program as in the outer block we have an int x and in the inner block, within the if statement we have another variable called x again. The second error comes in handy as it very clearly states that it assumes the variable x to be an int within the if statement. The golden rule again. We cannot have a variable with the same name in the inner and outer block. If we remove the statement x = 1 from the above program we will get a slightly different error as follows.

Compiler Error

a.cs(10,11): error CS0664: Literal of type double cannot be implicitly converted to type ‘float’; use an ‘F’ suffix to create a literal of this type

a.cs

class zzz

{

public static void Main()

{

}

int x;

void abc( int a)

{

if (a > 10)

{

x = 1;

}

else

{

double x = 1.0;

}

}

}

Compiler Warning

a.cs(15,8): warning CS0219: The variable ‘x’ is assigned but its value is never used

a.cs(6,5): warning CS0169: The private field ‘zzz.x’ is never used

The compiler gives us no errors as the variable x created outside of a function in the outer block is not used in the main function block. It is only used in the if statement. Since the else block of the if statement, which is at the same level is not executed, the compiler adjourns after giving a few warnings.

a.cs

class zzz

{

public static void Main()

{

}

int x;

void abc( int a)

{

x = 3;

if (a > 10)

{

x = 1;

}

else

{

double x = 1.0;

}

}

}

Compiler Error

a.cs(16,8): error CS0136: A local variable named ‘x’ cannot be declared in this scope because it would give a different meaning to ‘x’, which is already used in a ‘parent or current’ scope to denote something else

a.cs(16,12): error CS0029: Cannot implicitly convert type ‘double’ to ‘int’

If we knowingly initialize the instance variable x in the function abc, we are using the one created in the outer block. So, we are not allowed to recreate the same variable x the inner block under any circumstance. If you comment the line with x=1 or double x=1.0, the compiler will give you warnings. This proves that the compiler is confused on the variable it should work with while initializing x to 1 as there are two variables available by the same name.

a.cs

class zzz

{

public static void Main()

{

}

void abc( bool a)

{

if (a)

{

int i = 0;

}

int i = 3;

}

}

Compiler Error

a.cs(12,5): error CS0136: A local variable named ‘i’ cannot be declared in this scope because it would give a different meaning to ‘i’, which is already used in a ‘child’ scope to denote something else

a.cs(12,5): error CS0103: The name ‘i’ does not exist in the class or namespace ‘zzz’

What we forgot to inform you earlier is that the reverse is not true. That is, a variable created in an inner block or scope cannot be defined outside the parent or outer scope. This is in contrast to the above explanation.

a.cs

class zzz

{

public static void Main()

{

}

void abc( bool a)

{

if (a)

{

int i = 0;

}

if ( a)

{

int i = 3;

}

}

}

We get no error above as the two if statements are at the same level and the variables created within the blocks are independent of each other. They do not interfere with each other.

The above explanation, called the rules of invariant, apply only to simple names. It is not applicable to member access, an example of this is seen below.

a.cs

class zzz

{

public static void Main()

{

}

int x;

void abc( int x)

{

this.x = x;

}

}

We have two variables called x in the above program. One is an instance variable and the other is a parameter to a function. Within the function abc, the parameter variable x hides the instance variable. If we want to access the instance variable, however, we have to preface the variable name with this. It is therefore, a good idea to preface all instance variables with the reserved word this.

Indexers

a.cs

class zzz

{

public static void Main()

{

yyy a = new yyy();

xxx b = new xxx();

a[1] = b;

System.Console.WriteLine(a[1]);

}

}

class yyy

{

public xxx this[int i]

{

get

{

return new xxx();

}

set

{

}

}

}

class xxx

{

}

Output

xxx

An indexer can store any value provided it is a class, struct or interface. However, the return value of an indexer is not part of its signature. We can have only one return type but multiple types of parameters.

Throw

a.cs

class zzz {

public static void Main()

{

throw null;

}

}

Output

Unhandled Exception: System.NullReferenceException: Exception of type System.NullReferenceException was thrown.

at zzz.Main()

The keyword null is like a rubber man. Fits everywhere. If we throw a null, the actual exception thrown is NullReferenceException.

a.cs

class zzz

{

public static void Main()

{

throw ;

}

}

Compiler Error

a.cs(5,1): error CS0156: A throw statement with no arguments is not allowed outside of a catch clause

A throw can be used without the name of an expression only in a catch. This is one of the few error messages that actually sound English-like. Hope, he is not sacked for committing blasphemy.

a.cs

class zzz

{

public static void Main()

{

try

{

throw null;

}

catch ( System.Exception e)

{

System.Console.WriteLine(“catch”);

throw;

}

}

}

Output

catch

Unhandled Exception: System.NullReferenceException: Exception of type System.NullReferenceException was thrown.

at zzz.Main()

In this program, a throw is used without any parameters in a catch statement. It is actually a short form of the throw and it throws the same exception that the catch invokes.

In this case, the earlier throw showed a NullReferenceException Exception and the inner throw throws the same exception. Also, remember the end point of a throw can never be reached even by a miracle. Exception propagation is the process of transferring control from a throw to one of the exception handlers that may exist.

a.cs

using System;

class zzz

{

void abc()

{

try

{

pqr();

}

catch (Exception e)

{

Console.WriteLine(“abc: ” + e.Message);

e = new Exception(“abc”);

Console.WriteLine(“abc: ” + e.Message);

throw;

}

}

void pqr() {

throw new Exception(“pqr”);

}

public static void Main()

{

zzz a = new zzz();

try {

a.abc();

}

catch (Exception e) {

Console.WriteLine(“Main: ” + e.Message);

}

}

}

Output

abc: pqr

abc: abc

Main: pqr

The throw by itself re-throws the same exception. The exception parameter is like a value variable and even though we are re initializing it, the original remains the same. Thus the message displayed by e.Message does not change from pqr to abc ever. The change is only affected within the catch block as shown by the WriteLine function. The behaviour is the same as that of a value variable.

a.cs

class zzz

{

public static void Main()

{

try

{

}

}

}

Compiler Error

a.cs(8,1): error CS1524: Expected catch or finally

Man cannot live on love alone. So also, a try needs either a catch or a finally or both to live. The variable offered to a catch is just like a parameter to a function and it can be definitely assigned like a value parameter. It also unfortunately dies at the end of the catch. Life-time and visibility do not exceed the catch block!

a.cs

class zzz

{

public static void Main()

{

try {}

catch ( System.Exception ) {}

}

}

We do not have to supply the exception name in a catch. If we do not, as in the above program, there is no known way of figuring out what exception took place. Common sense dictates that we give the exception a name and try and use it in the code written in the catch.

a.cs

class zzz

{

public static void Main()

{

try {}

catch () {}

}

}

Compiler Error

a.cs(6,8): error CS1015: An object, string, or class type expected

a.cs(6,10): error CS1026: ) expected

What is mandatory however, is the name of the exception class. The documentation says it is possible to have no parameters and the catch is then called a general catch. Does not work as advertised for us. The documentation also claims that it has to be the last. No such luck.

a.cs

class zzz

{

public static void Main()

{

try {}

catch (System.NullReferenceException e) {}

catch (System.NullReferenceException e) {}

}

}

Compiler Error

a.cs(7,8): error CS0160: A previous catch clause already catches all exceptions of this or a super type (‘System.NullReferenceException’)

You cannot have two catch statements of the same type as the compiler examines them in textual order, first come first serve. The first match is the chosen exception handler, thus the second catch would never be called making it unreachable. This is a no-no as the compiler will have to execute a catch that is unreachable.

a.cs

class zzz

{

public static void Main()

{

zzz a = new zzz();

try {}

finally

{

goto aa ;

System.Console.WriteLine(“aa”);

aa:

goto bb ;

}

bb:

System.Console.WriteLine(“bb”);

}

}

Compiler Warning

a.cs(10,1): warning CS0162: Unreachable code detected

Compiler Error

a.cs(12,1): error CS0157: Control cannot leave the body of a finally clause

We can use a goto statement to jump around within a finally. It would be an act of God to jump out of a finally. The same rules hold for a break or continue. No leaving a finally under any circumstance including a natural calamity. If we have forgotten to tell you this earlier, no returns help either.

a.cs

class zzz

{

public static void Main()

{

zzz a = new zzz();

try

{

goto aa ;

}

finally

{

System.Console.WriteLine(“aa”);

}

aa:

System.Console.WriteLine(“bb”);

}

}

Output

aa

bb

No such restrictions apply to a try block. Everything that we taught you earlier can also be used in a try block. The only proviso is that we have to first execute code in the finally block before leaving the try block.

Hierarchical Tree Represented by Modified Preorder Tree Traversal Technique using C# 3.0 and SQL 2005.

http://www.codeproject.com/KB/tree/Tree_Traversal.aspx

• Download demo project – 47.54 KB

Introduction

During my work in a (CMS) Content Management System, and in the module of Content-mapping (which is a .NET desktop application) I needed to classify my contents into a chain of categories like hierarchical tree, but I also needed to represent that tree into more than a format, such as Textual, Tabular, Nodes and Database.

I searched the web about which .NET library is going to represent my tree into the SQL2005 database. I couldn’t found that library, but I found an article (Gijs Van Tulder) is titled Storing Hierarchical Data in a Database that explains the concept of the MPTT (Modified Preorder Tree Traversal). Unfortunately, it was written for PHP developers, so, I translated the concept into C# and SQL2005 code. Moreover, I wrote some algorithms to represent trees into textual, tabular, tree view nodes and graphical format. I also used provider model techniques as a step to support various databases such as SQLite.

Finally, I gathered all these staff into one project, but, the entire classes could be easily reused and separated into class libraries, even the innovative algorithms (that I wrote) could be reused individually like the one that is written to render the graphical representation of the tree.

Now I think that I have an efficient way to add, insert, delete and retrieve tree-elements using many ways including a way for SQL2005 database with a very effective schema, which is MPTT that minimizes the number of database queries by having just one query for each activity.

What is Hierarchical Tree?

Simply, it is a structure of data that looks like a real tree, even though the hierarchical-tree is generally shown upside down compared with a real tree; that is to say with the root at the top and the leaves at the bottom, elements of that structure are related with each other by a parent-child relationship, those relationships are represented by a connecting-lines between elements and they are called “Branches”, moreover, elements that have no superior are called “ROOTs” and elements that have no children are called “Leaves”.

The following example demonstrates a textual representation for the Animal-kingdom tree:

Collapse Copy Code

Animal Kingdom
#Backbones
##Mammal
##Lungs
##Reptile
##Bird
##Gills
###Fish
###Amphibian
#No Backbones
##Starfish
##Mollusk
###Snail
###Clam
##Jointed Legs
###Insect
###Spider
###Crustacean

Collapse Copy Code

Figure 1: Hierarchical tree, textual representation.

So we have 1 root (Animal Kingdom) and 12 leaves (Mammal, Lungs, Reptile, Bird, Fish, Amphibian, Starfish, Snail, Clam, Insect, Spider, Crustacean), we have 18 lines where each line contains one tree element/node, the count of (#)s in front of the node-title represents the depth level of the element in the tree, and elements that have the same level and have the same parent are called “SIBLINGs”.

A graphical representation of such trees exist in Figure 2, where a rectangle is a symbolic representation of a node, and also a tabular representation for such trees exist in Figure 3.

Figure 2: Hierarchical tree graphical representation.

ID	Parent ID	Title	Left	Right
1	0	Animal Kingdom	1	36
2	1	Backbones	2	17
3	2	Mammal	3	4
4	2	Lungs	5	6
5	2	Reptile	7	8
6	2	Bird	9	10
7	2	Gills	11	16
8	7	Fish	12	13
9	7	Amphibian	14	15
10	1	No Backbones	18	35
11	10	Starfish	19	20
12	10	Mollusk	21	26
13	12	Snail	22	23
14	12	Clam	24	25
15	10	Jointed Legs	27	34
16	15	Insect	28	29
17	15	Spider	30	31
18	15	Crustacean	32	33

Figure 3: Hierarchical tree tabular representation.

Using the Demo Project

Back to the previous section, copy the text of Figure 1, and paste it into the TextBox of the right hand side on the interface of demo project, then, press the button that is titled “<<” to convert the text into the traditional tree representation in the TreeView control in the left hand side.

Show a tabular representation of the tree by clicking the radio button that is called “Tabular,” and toggle between textual and tabular using the the other radio button that is called “Textual”.

To save the tree you just converted into SQL2005 database you should first select an existing database, but you don’t have to create any tables, just construct a working connection-string by clicking the button that is called “Connection String,” after composing the connection string, simply click the button that is called “Save” to save the tree into a table called “tblTree,” the table will be created automatically if it does not exist or is truncated if it is existing.

Of course, you could load this tree any time again from the same connection-string if you provided it correctly, just compose the connection string and click the “Load” button. However, you don’t have to enter the connection string each time you open the demo project, it saves the connection string into a file named constring.txt automatically.

Now you can enjoy showing the graphical representation of the tree by clicking the “Draw” button.

Using the Code

You simply have to create an object from the MpttCoreEngine class and call any method from its interface.

Collapse Copy Code

MpttCoreEngine engine = new MpttCoreEngine();

This class contains all the algorithms that were written to represent hierarchical trees in various formats, the following list contains the most important methods in this class.

SetConnectionString	Use this function to provide a connection string to the engine
ConvertMpttIntoTree	Use this function to load tree nodes from the database
ConvertTreeIntoMptt	Use this function to save tree nodes to database
ConvertTextIntoTree	Converts textual tree into TreeNode suitable to TreeView
ConvertTextTableIntoTree	Converts tabular tree into TreeNode suitable for TreeView
ConvertTreeIntoText	Converts TreeNode back to textual representation
ConvertTreeIntoTable	Converts TreeNode back to tabular representation
DrawTree	Draws TreeNode on an Image and returns the Image for later use

The following code is a snapshot from the demo project which demonstrates how to use those methods.

Collapse Copy Code

	private void LoadDb()
	{
	    try
	    {
	        treeView.Nodes.Clear();
	        TreeNode node = null;
	        engine.SetConnectionString(_connectionString);
	        node = engine.ConvertMpttIntoTree();
	        if (node != null)
	        {
	            treeView.Nodes.Add(node);
	            treeView.ExpandAll();
	        }
	    }
	    catch (Exception e)
	    {
	        MessageBox.Show(e.Message +
                     "\r\nThe Connection string may not be correct");
	    }
	}

	private void SaveDb()
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    try
	    {
	        engine.SetConnectionString(_connectionString);
	        engine.ConvertTreeIntoMptt(treeView.Nodes[0]);
	    }
	    catch (Exception e)
	    {
	        MessageBox.Show(e.Message +
                     "\r\nThe Connection string may not be correct");
	    }
	}

	private void TreeReflection()
	{
	    char t = (delimiter.Text.Length > 0 ? delimiter.Text[0] : '#');
	    engine.LevelDelimiter = t;
	    //
	    treeView.Nodes.Clear();
	    TreeNode node = null;
	    if (textual.Checked)
	        node = engine.ConvertTextIntoTree(textView.Text, _delimiter, '*');
	    else//tabular
	        node = engine.ConvertTextTableIntoTree(textView.Text, true);
	    if (node != null)
	    {
	        treeView.Nodes.Add(node);
	        treeView.ExpandAll();
	    }
	}

	private void TextReflection()
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    char t = (delimiter.Text.Length > 0 ? delimiter.Text[0] : '#');
	    engine.LevelDelimiter = t;
	    if (textual.Checked)
	    {
	        textView.Text = engine.ConvertTreeIntoText(treeView.Nodes[0], false);
	    }
	    else
	    {
	        textView.Text = engine.ConvertTreeIntoTable(treeView.Nodes[0], false);
	    }
	}

	private void btnDraw_Click(object sender, EventArgs e)
	{
	    if (treeView.Nodes.Count <= 0)
	        return;
	    Bitmap bmp = engine.DrawTree(treeView.Nodes[0]);
	    TreePreview preview = new TreePreview(bmp);
	    preview.Show();
	}

Points of Interest

The code is full of interest points, where most of them may need individual articles to explain. Here are the most important:

1. 1- The algorithm of drawing the tree nodes is so complex, I used anonymous functions, recursion and callbacks to calculate accurate locations for the nodes. You can find that algorithm on the function DrawTree.
2. The MPTT concept is explained in several places on the web, though, I composed that concept with a design pattern known by “provider model“, where, an abstract class called TreeProvider is created to be implemented and supports any database type like SQL2005, MySQL or SQLite.
3. You could simply separate the core of the demo projects into a class library by copying only the folder named Core to any project you want.

History

Version 1.0:

1. TreeView representation
2. Textual representation
3. Tabular representation
4. MPTT representation
5. Graphical representation
6. SQL2005 support

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Wael Alghool

Programming since 1990. I’m Experience in VC++, COM, MFC, API, ISAPI, ASP.NET, C#, jQuery, SQLite, SQL2005, Fatwire-CMS and J2EE. Since 2004, I’m SME (Subject matter expert) in requirements gathering. Since 2005, I’m PSP (Personal Software Process) certified. Since 2005, I Lead teams in between 8 to 30 members in several companies. Since 1999, I Worked for international companies like Harf-IT, ISlamweb, Qatar-awqaf ministry and Islamonline. Occupation: Architect Company: Government Location: Qatar

Tree structures in ASP.NET and SQL Server

http://www.developerfusion.com/article/4633/tree-structures-in-aspnet-and-sql-server/

Library tutorials & articles

Tree structures in ASP.NET and SQL Server

By James Crowley, published on 28 Dec 2004

 James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby – to a regular monthly audience …

• Download.zip

Page 1 of 8

1. Introduction
2. Storing Trees in SQL Server
3. Self-maintaining Trees Using Triggers
4. Basic Operations over Trees in SQL Server
5. Displaying our Nodes
6. Displaying Breadcrumbs
7. Previewing Sub-levels
8. Populating the TreeNode Children

Introduction

Trees can be an intuitively simply way of organising large amounts of information. We’re exposed to them everywhere – from directories in file systems and categories in a web directory to hierarchies in organisations and family trees! Something like XML handles hierarchical data well, but if you’ve got a database full of data we want to associate with the tree – for example, a table full of articles – splitting our data store between XML and something like SQL Server isn’t a particularly elegant option. Unfortunately, relational SQL doesn’t make it especially easy to store these structures in such a way that we can perform useful (and efficient) operations over the trees.

This article looks at one way to represent trees in .NET – and how to map them to a table in SQL Server and back again – which should hopefully take the pain out of storing trees and manipulating them from .NET. Our aim will be to create an ASP.NET page that provides some standard “web directory” features including

• Breadcrumbs
• Listing sub-nodes in the current section
• Indicating some of the children of these child nodes

whilst ensuring that we have zero limitations on the number of items in the tree, or its depth.

I’ve referenced many articles on the subject in order to implement the SQL Server portion of this – in particular, Maintaining Hierarchies and More Trees & Hierarchies in SQL – so many thanks to those authors. I hope this proves to be a useful extension to their discussions, rather than just a rehash of old material! Naturally, any mistakes in the implementation I show here are mine alone – but please do point them out to me.

Representing trees in .NET

To start with, its probably useful to remind ourselves what exactly a tree is (!). Each element in a tree is known as a node – each node has zero or more child nodes, and the one at the top – with no parent – is the root of the tree. .NET doesn’t currently have a built-in datatype for representing trees, but it’s fairly straightforward to to create our own. We’ll simply create a class to represent each “node” in the tree – which will consist of the following.

Property Name	Data Type	Description
UniqueID	int	A unique identifier for the node in this tree. As we’re looking to store the tree in a relational database, this maps nicely to a primary/identity key, so we’ll use an integer here. If we are creating a new TreeNode object that has not yet been associated with a unique identifier, this value will be zero.
ParentID	int	Used to identify the parent node of this object by storing the unique id of the parent. A parent ID of zero indicates that the node has no parent (ie it is a root node).
Name	string	A text value (not necessarily unique) to be associated with this node.
Children	ArrayList<TreeNode>	A collection of TreeNode objects that are children of this node. This will not necessarily contain all children that are in the original tree stored in our relational database – but will be populated appropriately depending on which queries we run.

The simple class shown below encapsulates this.

[Serializable]
public class TreeNode
{
    private int _uniqueID;
    private string _name;
    private int _parentID;
    private int _depth;
    private ArrayList _children;
    public TreeNode() { }
    public TreeNode(string name, int parentID) : this(0,name,parentID,-1)
    {
    }
    public TreeNode(int uniqueID, string name, int parentID, int depth)
    {
        _uniqueID = uniqueID;
        _name = name;
        _parentID = parentID;
        _depth = depth;
    }
    /// <summary>
    /// Gets or sets the unique ID associated with this category
    /// </summary>
    /// <remarks>Once a non-zero ID has been set, it may not be modified.</remarks>
    public int UniqueID
    {
        get { return _uniqueID; }
        set
        {
            if (_uniqueID == 0)
                _uniqueID = value;
            else
                throw new Exception("The UniqueID property cannot be modified once it has a non-zero value");
        }
    }
    public int Depth
    {
        get { return _depth; }
    }
    /// <summary>
    /// Gets or sets the label for this node
    /// </summary>
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }
    /// <summary>
    /// The ID of the parent node
    /// </summary>
    public int ParentID
    {
        get { return _parentID; }
        set { _parentID = value; }
    }
    /// <summary>
    /// Gets the children TreeNode objects for this category
    /// </summary>
    /// <remarks>In .NET 2.0, this can be modified to use generics, and have type ArrayList&lt;TreeNode></remarks>
    public ArrayList Children
    {
        get { return _children; }
        set { _children = value; }
    }
}

We’re not going to actually use this class to create standalone tree structures along the lines we would if were were adding elements to, say, a TreeView control. The TreeNode’s will be used to represent what we have stored in the database as a tree. However, if we want to add a TreeNode to the structure, the request is going to be made directly to the database – and the change will be reflected in a future request on the state of the tree structure. Likewise, if we modify the Name or ParentId of a TreeNode object, we’ll need to tell the database that we’ve made this change.

• Storing Trees in SQ

csharp-source.net

http://csharp-source.net/

C# Programming Guide

http://msdn.microsoft.com/en-us/library/67ef8sbd.aspx

DATABASE JOURNAL – Featured Database Articles – MSSQL

http://www.databasejournal.com/features/mssql/archives.php

Archives
Combine BottomCount() with Other MDX Functions to Add Sophistication – 11/21/2008
Microsoft Windows PowerShell and SQL Server 2008 AMO – 11/18/2008
SQL Server: Measuring Space Allocation and Index Distribution – 11/14/2008
Installing a Two-node SQL Server 2008 Cluster – Advanced option – 11/12/2008
Establishing Distributed SQL Server Express’ Service Broker Conversations Using Certificate-based Authentication – 11/10/2008
SQL Server 2008 Recovery Models and Backups – 11/07/2008
Using Windows PowerShell to get SQL Server connection information – 11/05/2008
Attribute Member Values in Analysis Services – 11/04/2008
Introducing Reporting Services Charts for Analysis Services – 10/29/2008
Reports for SQL Server 2008 System Data Collections – 10/27/2008
Exploring SQL 2005’s Ranking Functions – NTILE() and ROW_NUMBER() – 10/24/2008
Configuring Certificate-based Authentication in SQL Server Express’ Distributed Service Broker Environment – 10/20/2008
Check your SQL Server using Windows PowerShell – Part 7 – 10/15/2008
MSSQL Analysis Services – Attribute Member Names – 10/13/2008
SQL Server 2005 Express Edition – Part 32 – Distributed Service Broker Environment – Conducting Dialogs – 10/10/2008
Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation – 10/08/2008
Basic Set Functions: The BottomCount() Function, Part I – 10/06/2008
SQL 2008 Backup and Restore Part 1 – 10/03/2008
Check your SQL Server using Windows PowerShell – Part 6 – 10/01/2008
SQL Server 2008 Data Collections and the Management Data Warehouse – 09/29/2008
SqlCredit – Part 19: Exploring SQL 2005’s Ranking Functions – RANK() and DENSE_RANK() – 09/26/2008
Mastering OLAP Reports: Parameterized Grouping – 09/23/2008
SQL Server 2005 Express Edition – Part 31 – Distributed Service Broker Environment – Routing – 09/22/2008
Attribute Member Keys – Pt II: Composite Keys – 09/19/2008
Check your SQL Server using Windows PowerShell – Part 5 – 09/17/2008
Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation – 09/16/2008
Intrinsic Member Properties: The MEMBER_VALUE Property – 09/12/2008
SQL Server 2005 Express Edition – Part 30 – Distributed Service Broker Environment – Endpoints – 09/08/2008
What is SQL Server – 09/05/2008
Terminate User processes in SQL Server – 09/03/2008
Attribute Member Keys – Pt 1: Introduction and Simple Keys – 08/29/2008
What Kind of DBA Are You? – 08/26/2008
SQL Server 2005 Express Edition – Part 29 – Implementing Service Broker Conversation – 08/25/2008
SqlCredit, Part 18: Exploring the Performance of SQL 2005’s OUTPUT Clause – 08/21/2008
Audit your Windows domain DBA group using PowerShell – 08/20/2008
Discover SQL Server TCP Port – 08/19/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part II – 08/18/2008
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property – 08/08/2008
Check your SQL Server using Windows PowerShell – Part 4 – 08/06/2008
SQL Server 2005 Express Edition – Part 28 – Implementing Service Broker Conversation – 08/05/2008
Create Your First SQL Server Database in 3 Quick Steps – 08/01/2008
Mastering OLAP Reports: Parameterizing Number of “Top” Items with the MDX TopCount() Function, Part I – 07/29/2008
SQL Server 2005 Express Edition – Part 27 – Implementing Basic Service Broker Objects – 07/28/2008
SqlCredit – Part 17: Exploring SQL 2005’s OUTPUT Clause – 07/25/2008
SQL Server 2008 MERGE Statement – 07/24/2008
Intrinsic Member Properties: The MEMBER_NAME Property – 07/21/2008
Check your SQL Server using Windows PowerShell – Part 3 – 07/16/2008
SQL Server Audit in SQL Server 2008 – Part 2 – 07/14/2008
SQL Server 2005 Express Edition – Part 26 – Introduction to Service Broker – 07/11/2008
Dimension Attributes: Introduction and Overview, Part V – 07/07/2008
SQL Server Profiler Part 2 – 07/02/2008
Check your SQL Server using Windows PowerShell – Part 2 – 07/01/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part II – 06/27/2008
SQL Server 2008 Date Functions, Part 2 – 06/26/2008
Enhancement in variable declaration – SQL Server 2008 – 06/24/2008
Implementing Upgrade of SQL Server 2005 Express Edition – 06/23/2008
Intrinsic Member Properties: The MEMBER_KEY Property – 06/20/2008
Check your SQL Server using Windows PowerShell – Part 1 – 06/18/2008
Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I – 06/13/2008
SQL Server 2005 Express Edition – Part 24 – Planning Upgrade of SQL Server 2005 Express Edition – 06/09/2008
SQL Server Audit in SQL Server 2008 – Part 1 – 06/06/2008
Compound Assignment Operators in SQL Server 2008 – 06/04/2008
Introduction to SQL 2005 Profiler Part 1 – 06/02/2008
Dimension Attributes: Introduction and Overview, Part IV – 05/29/2008
New Date Data Types with SQL Server 2008 – 05/27/2008
SqlCredit – Part 16: The Cost of Bloat – 05/23/2008
Intrinsic Member Properties: The MEMBER_CAPTION Property – 05/22/2008
SQL Server 2005 Express Edition – Part 23 – Manual Upgrade from Microsoft SQL Server Desktop Engine (MSDE) – 05/22/2008
Table-valued parameters – SQL Server 2008 – 05/21/2008
Dimension Attributes: Introduction and Overview, Part III – 05/15/2008
Policy-based Management in SQL Server 2008 – Part II – 05/13/2008
SQL Server 2005 Express Edition – Part 22 – Upgrading from Microsoft SQL Server Desktop Engine (MSDE) – 05/09/2008
Row Value Constructor in SQL Server 2008 – 05/06/2008
SQL Server Management Studio Reports and Dashboard – 05/02/2008
Support Parameterization from Analysis Services – Parameter Defaults – 04/29/2008
SqlCredit – Part 15: The Cost of Distribution – 04/25/2008
SQL Server 2005 Express Edition – Part 21 – Using Replication Management Objects – 04/24/2008
Connection Strategy for Multiple Database Environments – 04/22/2008
Dimension Attributes: Introduction and Overview, Part II – 04/18/2008
Policy-based Management in SQL Server 2008 – Part I – 04/17/2008
UPSERT Functionality in SQL Server 2008 – 04/16/2008
SQL Server 2005 Express Edition – Part 20 – Authenticating Merge Web Synchronization – 04/11/2008
Set Functions: The StripCalculatedMembers() Function – 04/07/2008
Storing Images and BLOB files in SQL Server Part 4 – 04/04/2008
Top Queries in SQL Server 2005 – 04/02/2008
Exam 70-443 Practice Test from uCertify.com – 03/31/2008
SqlCredit – Part 14: The Cost of Translation – 03/28/2008
Parameterization from Analysis Services – Cascading Picklists – 03/26/2008
SQL Server 2005 Express Edition – Part 19 – Authenticating Merge Web Synchronization – 03/24/2008
Using dtutil to copy SSIS packages stored in SQL Server – 03/20/2008
Find space Usage by Table , Schema in SQL Server 2005 and 2008 – 03/18/2008
Dimension Attributes: Introduction and Overview, Part I – 03/13/2008
SQL Server 2005 Express Edition – Part 18 – Merge Web Synchronization Setup – 03/11/2008
Storing Images and BLOB files in SQL Server Part 3 – 03/07/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 4 – 03/05/2008
Set Functions: The AddCalculatedMembers() Function – 03/03/2008
SQL Server DBA Dashboard – 02/29/2008
Support Parameterization from Analysis Services – 02/26/2008
SQL Server 2005 Express Edition – Part 17 – Merge Web Synchronization – 02/25/2008
SqlCredit – Part 13: More on Indexed Persisted Computed Columns – 02/22/2008
DST checking with Windows Powershell – 02/21/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 3 – 02/20/2008
Dimensional Model Components: Dimensions Part II – 02/15/2008
SQL Server 2005 Express Edition – Part 16 – Transactional and Merge Replication – 02/08/2008
Microsoft SQL Server 2008 – Change Data Capture – Part 2 – 02/06/2008
MDX Numeric Functions: The Min() Function – 02/04/2008
Storing Images and BLOB files in SQL Server Part 2 – 02/01/2008
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets – 01/31/2008
Building Custom Reporting Services Reports for SQL Server Management Studio – 01/30/2008
SQL Server 2005 Express Edition – Part 15 – Snapshot Replication – 01/28/2008
SqlCredit – Part 12: Exploring Indexed Persisted Computed Columns – 01/25/2008
Dimensional Model Components: Dimensions Part I – 01/24/2008
Microsoft Windows PowerShell and SQL Server 2005 WMI Providers – Part 2 – 01/22/2008
Reporting Services: Customize Automatically Created Parameter Support Objects – 01/16/2008
Microsoft SQL Server 2008 – Change Data Capture – Part I – 01/11/2008
SQL Server 2005 Express Edition – Part 14 – Replication Support – 01/07/2008
Storing Images and BLOB files in SQL Server – 01/04/2008
New datetime datatypes in SQL Server 2008 – 01/02/2008
MDX Numeric Functions: The Max() Function – 12/31/2007
SQL Server 2005 – Hacking password Encryption – 12/28/2007
Disk Space Usage and SQL Server Performance – 12/26/2007
SQL Server 2005 Express Edition – Part 13 – ClickOnce Deployment and Security – 12/24/2007
BACKUP compression in SQL Server 2008 – 12/19/2007
Manage Unknown Members in Analysis Services 2005, Part II – 12/14/2007
SQL Server 2005 Express Edition – Part 12 – ClickOnce Deployment and Updates – 12/10/2007
Set Functions: The .AllMembers Function – 12/07/2007
SQL Server 2005 Encryption types – 12/05/2007
Installing SQL Server 2008 – 12/03/2007
Snapshot Reports II: SQL Server Management Studio Perspective – 11/30/2007
SQL Server Security Model – 11/28/2007
SqlCredit – Part 11: Change Tracking Using History Records – 11/26/2007

MS SQL Archives

2008   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2007   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2006   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2005   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2004   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2003   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2002   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2001   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
2000   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1999   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
1998   Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Visual C# IDE

http://social.msdn.microsoft.com/forums/en-US/csharpide/threads/