Rounding-Out A Class Library – Extension Methods

November 1, 2010 by Ben Galluzzo    .NET |    Comments (0)

One potential improvement to a custom class library could lie in the use of extension methods.  In short, extension methods are a quick and simple way to extend a type without modifying or recompiling the actual type which is being extended.  Utilizing an extension method is as simple as calling the ToString() method on a type.  At first it may seem difficult to come up with uses for extension methods.  However, working through a project, potential use for an extension method may present itself and also could very well reside in commonly created utility classes.  Operations often performed against a type in the same context is a perfect use for converting a utility function into an extension method.

Defining an extension method might seem a little odd at first to the uninitiated.  Take a bit of time to think about what’s happening under the hood to provide this added functionality, automatically, to an existing type. The first parameter of an extension method takes on the this modifier to accept the type on which the method operates.  Utilizing the extension methods requires adding a using directive of the namespace for which the extension method resides. 

Below are a couple very simple examples of extension methods and their use.  These two extension methods provide a boolean value for a check of a null value or an empty value against an Array type and also a for String type.

using System;

namespace ProvingGrounds.Lib
{
    public static class LibraryExtensions
    {
        public static bool IsNullOrEmpty(this Array _value)
        {
            return (_value == null || _value.Length == 0);
        }

        public static bool IsNullOrEmpty(this string _value)
        {
            return (_value == null || _value.Length == 0);
        }

    }
}
Utilizing these new extension methods can only be done if applying the proper using directive to the calling class; in this case, using ProvingGrounds.Lib.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ProvingGrounds.Lib;

namespace ProvingGrounds.Console
{
    class Program
    {
        static void Main(string[] args)
        {
            string emptyTest = null;
            if (emptyTest.IsNullOrEmpty())
                System.Console.WriteLine("emptyTest is null or empty");
        }
    }
}

Omitting the proper using directive will result in the extension method not being available for use.

ExtensionMethod_271x101

 

 

 

 

Care should be taken when enabling more global usage. For instance, if the class housing the extension methods are added to the System namespace, their use can be made more readily available.

using System;

namespace System
{
    public static class SystemExtensions
    {
        public static bool IsNullOrEmpty(this Array _value)
        {
            return (_value == null || _value.Length == 0);
        }

        public static bool IsNullOrEmpty(this string _value)
        {
            return (_value == null || _value.Length == 0);
        }

    }
}

So there it is. A couple of very simply extension methods in practice. Have fun with them. Use them to simplify often used operations on types; operations whose functionality may already reside in common util classes.

Utilizing table variable and while loop to avoid cursor-style operations

May 7, 2010 by Ben Galluzzo    SQL |    Comments (0)

So, here is one of many scenarios in which the work performed can be accomplished by use of a cursor.  For a long running set of queries such as what's listed below, utilizing a cursor could have undesired painful effects such as blocking.  In a tough spot, those who look toward a procedural approach in SQL often quickly jump to using cursors.

Here the scenario is described.  There are two dissimilar systems. The first is the old system which will still be in production for an indefinite period of time.  The old system is actually comprised of multiple systems on multiple SQL servers; one for each client.  The second, the replacement system, is running along side the old system and is managing different data of the same type of business process.  Specific work data is to be retrieved from the old system and married to the work data being retrieved from the new system so team leads can keep tabs on the work data without going to both systems.

It's actually fairly easy to utilize a table variable and a while loop to avoid these cursor-style operations.  Here is an example of a very simplified operation to obtain the related client data from the older system in preparation for marrying it to the related client data from the new system.  This example also briefly looks at a way to generate dynamic SQL. There probably is a better way to do this type of work, but it is indeed easy enough for most to be able to wrap their mind around.

The key pieces of the query are the table variable containing the list of client databases, an iterator, a total row count variable, and the while loop.  

The table variable, of course, will only persist in the life time of the SQL connection.  Once the query has completed, the table variable is gone.  Creating the table variable with an auto-incrementing integer identity field provides for the ability to actually loop through the work list.

DECLARE @clientData table (                      
    RowNum int IDENTITY (1, 1) Primary key NOT NULL,                      
    ClientID uniqueidentifier,          
    ClientName varchar(100),               
    ClientSource varchar(50)  --location of old system
)                 

 

Now, populate the temp table with all of items for with which to work.  Next, is the preparation of the variables to be able to run through the loop properly.

DECLARE @rowCount int                      
DECLARE @maxRows int                    
                      
SELECT @rowCount = 1            
          
SELECT @maxRows = count(*) from @clientData          
PRINT @maxrows

 

Then the loop is built; being sure that the @rowCount variable is incremented by one for the next return back through the loop.  Note how the current temp table row data can be accessed for use inside the loop. 

WHILE @rowCount <= @maxRows                      
BEGIN              
    DECLARE @clientID uniqueidentifier          
    SET @clientID = (SELECT ClientID FROM @clientData WHERE rowNum = @rowCount)          
        
    DECLARE @clientName varchar(100)          
    SET @clientName = (SELECT ClientName FROM @clientData WHERE rowNum = @rowCount)          
              
    DECLARE @sourceName varchar(50)                      
    SET @sourceName = (SELECT ClientSource FROM @clientData WHERE rowNum = @rowCount)                      
          
    DECLARE @sql varchar(4500)          
          
    SET @sql = '          
    INSERT INTO WorkList          
    SELECT
        '''+@clientID+''' ClientID,
        WI.create_date CreatedDate,       
        '''+@clientName+''' Client,          
        ....
        ....
        ....
        ....
        ....
        ....
        ....
    LEFT JOIN          
        ' + @sourceName + '.dbo.WorkItem WI
    ON          
    '                            
    EXEC SP_EXECUTESQL @SQL
          
    SELECT @rowCount = @rowCount + 1   
END

 

To provide the big picture, here is the query in its entirety. 

-------------------------------------------------------          
-- Begin - Obtain client list          

DECLARE @clientData table (                      
    RowNum int IDENTITY (1, 1) Primary key NOT NULL,                      
    ClientID uniqueidentifier,          
    ClientName varchar(100),               
    ClientSource varchar(50)  --location of old system
)                 
          
INSERT INTO @clientData (ClientID, ClientName, ClientSource)          
SELECT          
    C.ID ClientID,          
    C.[Name] ClientName,            
    '[' + M.cServer + '].' + M.cDatabase ClientSource              
FROM [ModernServer].CoreClient.dbo.Client C
LEFT JOIN Mapping.dbo.Client M
ON C.MappingClientID = M.cID                  
WHERE C.IsActive = 1                      
          
-- End - Obtain client list          
-------------------------------------------------------          
          
-------------------------------------------------------                                  
-- Begin - Create temp table                                  
                                  
IF object_id('WorkList') IS NOT NULL                                  
BEGIN                                  
    DROP TABLE WorkList
    PRINT 'Table Dropped'                                  
END                                  
                                  
CREATE TABLE WorkList (
    ClientID uniqueidentifier,
    CreatedDate datetime,    
    ClientName varchar(100),
    CreatedByName varchar(100),          
    ProblemCode varchar(50),          
    CustomerName varchar(100),          
    AccountNumber varchar(50),          
    StatusType varchar(25),
    LastUpdateDate datetime,
    UpdatedByName varchar(100)
)                                  
     
-- End - Create temp table                               
-------------------------------------------------------           

DECLARE @rowCount int                      
DECLARE @maxRows int                    
                      
SELECT @rowCount = 1            
          
SELECT @maxRows = count(*) from @clientData          
PRINT @maxrows
          
WHILE @rowCount <= @maxRows                      
BEGIN              
    DECLARE @clientID uniqueidentifier          
    SET @clientID = (SELECT ClientID FROM @clientData WHERE rowNum = @rowCount)          
        
    DECLARE @clientName varchar(100)          
    SET @clientName = (SELECT ClientName FROM @clientData WHERE rowNum = @rowCount)          
              
    DECLARE @sourceName varchar(50)                      
    SET @sourceName = (SELECT ClientSource FROM @clientData WHERE rowNum = @rowCount)                      
          
    DECLARE @sql varchar(4500)          
          
    SET @sql = '          
    INSERT INTO WorkList          
    SELECT
        '''+@clientID+''' ClientID,
        WI.create_date CreatedDate,       
        '''+@clientName+''' Client,          
        ....
        ....
        ....
        ....
        ....
        ....
        ....
    LEFT JOIN          
        ' + @sourceName + '.dbo.WorkItem WI
    ON          
    '                  
          
    PRINT (@SQL)
    EXEC SP_EXECUTESQL @SQL
          
    SELECT @rowCount = @rowCount + 1   
END

 

That's all there is to it.  This is just one example of so many others to use a temp table or table variable and while loop in lieu of a cursor.   


SQL Saturday - 506 - Baltimore BI Edition

Month List