Anastasiosyal.com

In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR

Just Fire up Visual Studio and create a new library project (I called it TextFunctions)

Add a new Class and Call it Regular Expressions and simply paste in the following Code:

using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;


    public class RegularExpressions
    {
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)]
        public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
        {
            if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
                return SqlBoolean.False;
            
            //Use the static IsMatch method. This is more performant than creating a 
            // new instance of Regex as the static method also caches the last expressions we used. 
            return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
        }

        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
        {
            if (input.IsNull || pattern.IsNull || replacement.IsNull)
                return SqlString.Null;

            return new SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase));
        }
    }

In this example we have two functions: one to Match a Regular expression pattern and one to perform a Replace based on a regular expression pattern.

To enable our SQL Server database to make use of the above functions we need to follow the steps below:

• Compile the library for Release
• CLR Enable Sql Server with:

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Reference the assembly and register the functions:

CREATE ASSEMBLY TextFunctions FROM 'c:\Development\Testing\CLR\TextFunctions\bin\Release\TextFunctions.dll'
GO
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO

Now we're ready to put our new functions to the test:

SELECT dbo.RegExMatch('12354', '\d')
--Verifies number Returns 1

SELECT dbo.RegExReplace('Poem Title (Author)', '.*?\((.*?)\).*', '$1')
-- Returns the match within the parenthesis: returns Author

select dbo.RegExMatch('me@mymail.com','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
--Verifies an email address

SELECT dbo.RegExMatch('12354', '\d')
--Verifies number Returns 1

SELECT dbo.RegExReplace('Poem Title (Author)', '.*?\((.*?)\).*', '$1')
-- Returns the match within the parenthesis, returns: Author (Useful for splitting one column in two)

SELECT EmailAddress, dbo.RegExMatch(EmailAddress,'^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$') 
FROM Users 
ORDER BY 2 
--Checks your users table for Invalid Email addresses, 
--at the top of the results all users with invalid email addresses

Regular expressions can come in really handy especially in data cleansing/transformation operations in your database.

Here are some further pointers to boost creativity with regular expressions:

Email Validation with Regular Expressions
Regular Expressions Cheat Sheet
RegExLib - Library of Regular Expressions for .Net

Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.

It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not.

Why disable text wrapping?

 

In the above screenshot we can see that when a given cell is constrained in size (i.e fixed height).
With text wrap on: the cell will not show the complete text unless it is wide enough.
With text wrap off: the complete text will display by overflowing onto the adjacent empty cells. So long as the next cells are empty.

But why would I want to do that when I could simply merge cells B + C + D together? It all comes down to usability and user expectations. If the end user expects to be able to sort the data in the columns then merged cells is just a feature that will be an obstacle in his path. In the screenshot below I try to sort by column B, but 1 of the rows contains merged cells so Excel naturally prompts the user.

If you do find yourself with a reporting services report that you have spent hours creating in your report designer and you are handed the above niche requirement, there are the following options for you friend:

• Fix it manually through automation
• Buy a third party product that has more control over the export such as http://officewriter.softartisans.com/OfficeWriter-250.aspx

If a third party product is not an option for you, you may find the following helpful.

Disable Text Wrap using Microsoft.Office.Interop.Excel

Office.Interop requires that Excel is installed on the machine that will render the report. Here is the function required:

        private static void DisableTextWrap(string fileName)
        {

            Application excel = new Application();
            try
            {
                Workbook workbook = excel.Workbooks.Open(
                        fileName,
                        Type.Missing, false, Type.Missing, Type.Missing,
                        Type.Missing, true, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                try
                {
                    Worksheet wsheet = workbook.Sheets[1] as Worksheet;
                    try
                    {
                        Range excelRange = wsheet.UsedRange;
                        try
                        {
                            excelRange.WrapText = false;
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(excelRange);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(wsheet);
                    }
                    workbook.Save();

                }
                finally
                {
                    workbook.Close(false, fileName, null);
                    Marshal.ReleaseComObject(workbook);
                }
            }
            finally
            {
                excel.Quit();
                Marshal.ReleaseComObject(excel);
            }
        }

The above function will open an excel file and disable text-wrap in the used area of only the first spreadsheet

Enable a Web Server to run Excel through COM automation

In order to enable a web server to run Excel through COM automation you can follow this very helpful and detailed advice:

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

One point to note after following the above instructions
After running DCOMCNFG
In the Security Tab
Under Activation 
Click customise. I would recommend that the excel is launched (activated) by a user that has already used excel once on the server you are trying to perform this. So, first create a user, then go to Excel right click and use 'Run As..' option and select the newly created user. The reason for doing this is that when a user runs excel for the first time, excel creates a profile for them and does some custom installation and also pops up a dialog box which is something we would like to avoid during the COM automation. Doing this will ensure that the installation of the user profile is not trying to take place under the COM automation. 

Hope this helps someone!

Parameters are the algorithm you wish to hash to and the input string to hash.
Here is a sample along with the return values commented in the next line:

Select HashBytes('MD2', 'Hello world!') 
--0x63503D3117AD33F941D20F57144ECE64

Select HashBytes('MD4', 'Hello world!') 
--0x0D7A9DB5A3BED4AE5738EE6D1909649C

Select HashBytes('MD5', 'Hello world!') 
--0x86FB269D190D2C85F6E0468CECA42A20

Select HashBytes('SHA', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

Select HashBytes('SHA1', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

MSDN: http://msdn2.microsoft.com/en-us/library/ms174415.aspx

Quite often we create systems that make use of web services so as to expose our Data, or Business methods for client applications to consume them. In a classic 3 tier scenario it may be either the data layer exposed through the web service as a data service, or the business layer exposed through the web service as a business service. I would say that both are perfectly valid for exposure through a web service depending on our needs and requirements. But since web services already exist, why make a custom method and use this method instead of web services? The following paragraph contains some points I dont like when coding standard microsoft web services.

Points I don't like when using Microsoft Web Services.

What I do not like when coding a web service when using the standard .Net framework approach is the feeling of redundant coding I get. Surely you've felt the same at some point, especially when all we are doing is simply coding pass through functions to my business or data layer.

Further to that, I end up having to maintain a mess of web service references in my client application. I need to add one reference to each asmx page created. Further to this, when creating web service references, the Microsoft .Net framework will create a custom Proxy to access this web service. This custom proxy will also re-define any complex types (eg typed datasets or custom classes) returned through the web service. What i dont like about this, is that even though two seperate asmx files may expose the same type, each web service reference added will redifine the same type on its own copy of the proxy. This leaves the client application with multiple definitions of what essentially is the same data type.

  What would I like to see instead?

• I would like to be able to switch between 'web service' mode and 'non web service' mode easily, with just a simple switch in the client application.
  
• I do not want to create redundant code in webmethods just so that I can expose my data or business objects over HTTP. The code is already in those layers, I just want the ability to consume them over an HTTP connection.
  
• i would just like to add a 'magic' attribute to my business/data classes that would mean 'This object can be invoked over HTTP.
  
• I would like to be able to consume my business or data objects seamlessly, with no requirements to add tons of web references on my client application.

For these reasons, i thought it would be worthwhile to find a method of dynamically exposing my data or business objects over an HTTP channel. I'd like to share a solution which addresses the above issues, but as in most cases there are tradeoffs.

Whats the tradeoff though? The tradeoff is that this is not webservices and because of that, this custom HTTP invocation protocol is not interoperable across other non .Net platforms.

If interoperability is a requirement in your application then this post does not offer any solutions to the above points. If however interoperability with other platforms is not a requirement, then you may find this helpful. Furthermore this solution requires that your Base Business or Data objects inherit from ContextBoundObject, but this requirement could be overcome in a different implementation.

Method Interception and how this solution works.

This solution is based on the following bullet points:

• Create an object and intercept all method calls to that object
• Determine if we are in a 'web service mode'
• if we are not in 'web service mode', then just call the underlying dataobject method and return its value.
• If we are in 'web service mode' then marshal the call accross to a custom HTTP Handler, this handler will create the dataobject via reflection execute the method and return the value (if any) and output parameters.

The key point of this architecture is the ability to intercept method calls to the object, as soon as we intercept method calls we can decide whether we route the request to the underlying object, or route the request over HTTP to our Handler. This handler is generic and does not require any code modifications. It does however require a reference to our dll that contains our DAL classes.

Thomas Danecker has an excellent post about how to intercept methods with a custom proxy.
http://tdanecker.blogspot.com/2007/09/interception-with-proxies.html

I like that solution because it uses only classes from the .Net framework. What I dont like about it, is the requirement it emposes on the proxyable types to derive from the ContextBoundObject type.

Oren has a nice short post where he compares 7 different options when it comes to method interception, with pros and cons of each:
http://www.ayende.com/Blog/archive/2007/07/02/7-Approaches-for-AOP-in-.Net.aspx

This solution uses interception with native Microsoft .Net classes as per Thomas's example.

Ok, now some code!

Our sample application consists of a DAL, a BLL a UI and a Test Project with some Test Cases. Our project also includes the Dynamic Service itself. So, let's see some of this in action and how it would all fit together. For the purpose of this test, our UI is a console application. This is what it would typically look like.

Listing #1:

using System;
using System.Text;
using DynamicService.Library;
using DynamicService.DAL;

namespace DynamicService.UI
{
    class Program
    {
        static void Main(string[] args)
        {

            using (WebServiceSettingsScope settingScope = 
                    new WebServiceSettingsScope(
"http://localhost:4040/Dataservice.ashx?db=TestDb", "", "", 3000))
            {
                TestDal dal = new TestDal();
                int i = dal.ReturnInputParameter(10);

                Console.WriteLine(i);
            }

            Console.ReadKey();
        }
    }
}

And this is what our TestDal class would look like (listing #2):

using System;
using System.Text;
using System.Data;
using DynamicService.Library;

namespace DynamicService.DAL
{
    [HttpRemotable]
    public class TestDal : ContextBoundObject
    {

        public TestDal()
        {
            //This is how the dal knows it's connection string for example...
            //the code below could be added in a base dal class that would allow the derived data objects 
            //to know about the database they are talking to. (useful for multi database scenarios)
            DbConnectionStringSettings connectionStringSettings = 
                ConnectionSettingsScope.ActiveConnectionSettings as DbConnectionStringSettings;
            string connectionString;
            if (connectionStringSettings != null)
                connectionString = connectionStringSettings.ConnectionString;
        }

        public int ReturnInputParameter(int param)
        {
            return param;
        }
    }
}

Well, this should get us going! So, the solution proposes that objects that can be invoked remotely over HTTP are decorated with an HttpRemotable attribute. They must also derive from ContextBoundObject so that their methods can be intercepted by the appropriate proxy and routed accordingly, either to the object directly, or to our generic handler.

Listing #1 shows that the remotable object, in this case our TestDal, should be instantiated within a ConnectionSettingsScope. There are two types of connectionSettingsScope, two classes that derive from this class. There is a DbConnectionSettingsScope and a WebServiceSettingsScope.

Depending on the scope that the TestDal object is being created it will be wrapped with either a ServiceProxy or an InterceptorProxy. The serviceproxy will route the requests to the service defined in the WebServiceSettingsScope. If created within a DbConnectionSettingsScope the InterceptorProxy will simply forward the requests on to the underlying object within the same application domain.

How does this work behind the scenes? The decision of which Proxy is created is made within the HttpRemotableAttribute.

using System;
using System.Text;
using System.Runtime.Remoting.Proxies;

namespace DynamicService.Library
{
    public class HttpRemotableAttribute : ProxyAttribute
    {
        public override MarshalByRefObject CreateInstance(Type serverType)
        {
            if (!ConnectionSettingsScope.Exists)
                throw new Exception("Error creating serviced object: "  + serverType.FullName + "\r\n\r\n You must define the ServiceScope to create a ServicedObject\r\ne.g. using(ServiceScope svcScope = new svcScope(...)) \r\n{ //object instantiation } \r\n\r\n");

            RealProxy proxy = null;

            //if we are using a remote invocation then create a service proxy 
            if (ConnectionSettingsScope.UseRemoteInvocation)
            {
                WebServiceSettings webServiceSettings = ConnectionSettingsScope.ActiveConnectionSettings as WebServiceSettings;
                if (webServiceSettings == null)
                    throw new Exception("Unexpected error: the ConnectionScope.ActiveConnectionSettings is null.");

                proxy = new ServiceProxy(serverType, webServiceSettings);
            }
            else
            {
                //just create a dumb proxy that will forward on the requests locally
                proxy = new InterceptorProxy(serverType);
            }

            MarshalByRefObject transparentProxy = (MarshalByRefObject)proxy.GetTransparentProxy();
            return transparentProxy;
        }
    }
}

HttpRemotable is a special attribute, inheriting from ProxyAttribute. What makes it so special is the CreateInstance method which is called when we try to create an instance of an object that is derived from ContextBoundObject and that is decorated with a ProxyAttribute derived class such as HttpRemotable. It determines the type of proxy to be created depending on the Active ConnectionSettings on our ConnectionSettings stack.

What is this ConnectionSettingsScope that I keep refereing to?

using System;
using System.Text;
using System.Threading;
using System.Collections.Generic; 
using DynamicService.Library;

namespace DynamicService.Library
{

    public class ConnectionSettingsScope : IDisposable
    {

        [ThreadStatic] //Each thread will have its own copy of a ServiceScope stack
        private static Stack<ConnectionSettings> _ConnectionSettings;

        protected static Stack<ConnectionSettings> ConnectionSettingsStack
        {
            get 
            {
                //this is thread safe, ConnectionSettings is a ThreadStatic variable.
                if (_ConnectionSettings == null)
                    _ConnectionSettings = new Stack<ConnectionSettings>();

                return _ConnectionSettings;
            }
        }

        public ConnectionSettingsScope(ConnectionSettings ConnectionSettings)
        {
            ConnectionSettingsStack.Push(ConnectionSettings);
        }

        static ConnectionSettingsScope()
        {

        }

        public ConnectionSettingsScope()
        {

        }


        public static bool UseRemoteInvocation
        {
            get { return ActiveConnectionSettings is WebServiceSettings; }
        }
        


        public static bool Exists
        {
            get { return (ConnectionSettingsStack!= null) && (ConnectionSettingsStack.Count > 0); }
        }

        public static ConnectionSettings ActiveConnectionSettings
        {
            get { return GetCurrentThreadConnectionSettings(); }
        }

        private static ConnectionSettings GetCurrentThreadConnectionSettings()
        {
            if (ConnectionSettingsStack.Count > 0) 
                return ConnectionSettingsStack.Peek();
            else
                return null;
        }

        // Dispose() calls Dispose(true)
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // The bulk of the clean-up code is implemented in Dispose(bool)
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                // free managed resources
                ConnectionSettingsStack.Pop(); // Remove the latest server url from the threadstatic stack
            }
        }
    }
}

Maybe I need to explain myself a little on the above code snippet. ConnectionSettingsScope contains a ThreadStatic stack of ConnectionSettings. Each time we create a new ConnectionSettingsScope object it pushes the latest ConnectionSettings class on to its ThreadStatic stack (ThreadStatic means that the stack is static to the thread level, each thread has its own ConnectionSettings stack). Each time we create an HttpRemotable object, the Settings used on it, will be based on the latest ConnectionSettingsScope.

DataService.ashx explained

So what happens on the other side of the fence. What happens when I we are creating our HttpRemotable object within a WebServiceSettingsScope?
All method calls are intercepted by the ServiceProxy and a ServiceRequest is sent over to our ServiceHandler. The service request contains all sorts of information of what we are trying to do, it contains information such as the fully qualified name of the underlying real type that has been proxied, the method we want to execute, and all the parameters passed to the method.

This information is used by the handler to instantiate the object via reflection and call the appropriate method. When method execution completes, the handler constructs a ServiceResponse to send back to the client. This response contains the return value along with the values of any output parameters.

The actual connection string to the database is stored on the server side. To allow for a multidatabase scenario, the handler is called in the format of DataService.ashx?db=ConnectionStringKeyName

ConnectionStringKeyName is the key in the web.config of the connectionstring to the database we want to use.

All objects are created within a DbConnectionSettingsScope region on the service handler. They will be pointing to the database in the selected connectionstring entry of the web.config as described above.

The DataService.ashx must contain a reference to the dlls that contain the types to be remotely invoked. In this case a single reference to our Dal will suffice. Furthermore, when publishing this web project we must ensure that the referenced dll is also in place.
I can see some are already thinking that adding references is what i wanted to avoid. True to some extent, what i wanted to avoid was the volume of references added. In classic web services I would have typically created one seperate asmx file per data object that i wanted to expose.

The Service Handler supports:

• Methods with void result
• Methods with parameters whose values are serialisable
• Graceful handling of exceptions and returning them to the client
• Methods with output parameters
• Overloaded methods with different parameter signatures.
• ServiceResponses are gzipped by default to minimise data traffic

The Service Handler does not support:

• Methods whose parameter values are not serialisable
• Generic methods

Testing and Performance

As mentioned above, the generic service handler uses reflection to reconstruct the underlying object being invoked. In order to assess whether this would mean a serious performance hit or not I created a few tests that benchmark it against equivalent invocations against a microsoft .Net web service.

The performance is pretty much similar, and in some cases the custom ServiceHandler calls outperform the equivalent calls in web services. 

The following image contains the various test cases that validate the features supported by the generic Service Handler.

Summing it all up

This is a solution that you may consider, so long that cross-platform interoperability is not a concern to your application. The remotable objects should only have stateless atomic operations invoked upon them just as the case would be in Web Services.  If you require to have the object alive on the remote tier then Remoting is probably for you. This solution allows you to easily switch between a web service mode and a non webservice mode by literally changing 1 line of code in your application.  This solution minimises redundant coding in a web service tier and allows you to expose your objects over an HTTP channel by simply decorating them with one attribute and deriving from ContextBoundObject. I hope this helps yeou shave off some development time from your projects!

Suggestions, complaints, errors? I will be very happy to hear your opinion and feedback on this. There is a visual studio 2008 solution file that can be downloaded from here to get you started. Happy Coding!

Sample solution for this post: DynamicService.zip 

For those who have been playing with Linq and getting up to speed with the latest C# 3.0 features, you are most likely aware of the new language feature of Anonymous Types.

An anonymous type allows us to create and an instance of a an object whose type has not been defined in our code but whose properties are inferred from the object initializer.

The following example illustrates this by creating an instance of a variable of an unknown type that has 3 properties

var product = new
{
    Name = "Acme Rocket",
    Description = "The quick way to the moon",
    Stock = 10
};

The variable type of product is not defined in our code. It is inferred through type inference and is of a compiler generated anonymous type that has 3 properties whose types again are inferred through type inference.

Anonymous types are a very powerful feature since they allow for creating LINQ projections without the need to define a class containing the projected fields of our query.

So in the following adapted sample taken from microsoft:

    string[] words = { "aPPLE", "BlUeBeRrY", "cHeRry" };
    var upperLowerWords =
        (from w in words
        select new { Upper = w.ToUpper(), Lower = w.ToLower() }).ToList();

    foreach (var ul in upperLowerWords)
    {
        Console.WriteLine("Uppercase: {0}, Lowercase: {1}", ul.Upper, ul.Lower);
    }

upperLowerWords is an instance of an IEnumerable<anonymousType> and is also intellisensed as such:

This is all very good. We can create and use anonymous types in our code.

So what is the problem and why am I saying that anonymous type support is incomplete?

The problem lays in the fact that type inference only works on local variables. Which means that the anonymous type in our example (and anonymous types in general) is only useful as a strongly bound object in the scope of the procedure in which it has been created. Anonymous types cannot be returned as strongly typed objects in function return values. They can only be returned as objects as the following code shows

object ReturnAnonymous()
{
    return new { Name = "Acme Rocket", Description = "Quick way to the moon", Stock = 10};
}

There is an  interesting yet dirty workaround for returning  anonymous types and getting strongly typed access to a return value of type anonymousType from the calling code.

However, the suggested approach is to define a solid class in your code and use that as the return type instead of returning an anonymous type. In a LINQ Datalayer scenario this means that for each function that returns a projection of Data we would have to define and maintain empty classes that are merely there just to map to the projected object...

Further reading about this issue

Msdn forum post and proposed resolutions: 
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2434363&SiteID=1

Rick also talks about this issue in one of his posts (http://www.west-wind.com/WebLog/posts/33570.aspx)

I really enjoy the new features of C# 3.0 but not being able to return anonymous types across functions cripples the  potential of this language feature. This appears to be due to limitations in type inference which only works locally, and also seems to be a bit flaky if you ask me, from what I've been reading anyway:

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=295134

http://blogs.msdn.com/ericlippert/archive/2007/11/05/c-3-0-return-type-inference-does-not-work-on-member-groups.aspx

Conclusion
Anonymous types even only accessible through local type inference are a really powerful language construct that have added value to LINQ queries (projections) . But I sure do look forward to seeing the C# compiler team unleashing the full potential of type inference and anonymous types by allowing us to return anonymous types as return values in our functions.

I recently found myself in the situation where I had to loop through all the days from a StartDate to an EndDate.

Sure easy, all you need to do is something like this:

DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(3);

DateTime curDate = startDate;
while (curDate <= endDate)
{
    //Do Something with curDate ...

    curDate = curDate.AddDays(1);
}

Does the job, but it is not very intuitive in my opinion. It is not that easy to read.
Wouldn't it be so much nicer if we could do something like this:

DayIterator dayIterator = new DayIterator(startDate, endDate);
foreach (DateTime dt in dayIterator)
{
     //Do Something with dt... 
}

I think the second version really shows the intent of what we are trying to do and is much more easy to follow and maintain.

So how do we go about implementing the DayIterator then? As you will see for yourself it really is simple code that makes use of the yield return statement for Iterators.

Here is the implementation of the DayIterator:

    public class DayIterator : IEnumerable<DateTime>
    {

        private DateTime _StartDate;
        private DateTime _EndDate;

        public DayIterator(DateTime startDate, DateTime endDate)
        {
            _StartDate = startDate;
            _EndDate = endDate;
        }

        public IEnumerator<DateTime> GetEnumerator()
        {

            DateTime currentDate = _StartDate;
            while (currentDate <= _EndDate) // Note that our Iterator is inclusive of endDate behaving like 'between'
            {
                yield return currentDate; // <-- This is the key line
                currentDate = currentDate.AddDays(1);
            }
        }

        #region IEnumerable Members

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {

            throw Exception("Not Implemented");

        }

        #endregion
    }

Thats all it takes! Now we have our custom DayIterator class. Now when we do foreach (DateTime dt in dayIterator) what foreach does is it calls GetEnumerator to start the loop. The yield return statement within GetEnumerator returns an instance of a DateTime class so the outer foreach loop starts looping using this as the first element. Each time our code reaches the foreach statement it calls GetEnumerator again resuming from the last yield return statement. When our GetEnumerator function finishes then the outer foreach loop stops looping. So in this example the GetEnumerator will finish when the condition
while (currentDate <= _EndDate) is not true anymore and therefore the foreach loop will terminate.

We can also use yield return break within the GetEnumerator implementation if we want to terminate the foreach looping.

So I guess some of you may be wondering What's with the two GetEnumerator functions instead of just one? Well In this example we want to loop through a collection of DateTime objects for this reason we inherited the DayIterator class from IEnumerable<DateTime>. This requires us to implement both the IEnumerable<DateTime> GetEnumerator() function and the IEnumerbale GetEnumerator (the latter returns only objects and we need not implement)

Ok, this is all good but does it really work?! Well, lets put it to the test with the following little sample code:

DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(5);

Console.WriteLine("StartDate: {0:dd MMM yyyy}", startDate);
Console.WriteLine("EndDate: {0: dd MMM yyyy}", endDate);

DayIterator dayIterator = new DayIterator(startDate, endDate);
foreach (DateTime dt in dayIterator)
{
    Console.WriteLine("In foreach: {0:dd MMM yyyy} ", dt);
}

This is what we get:

Now the DayIterator Class can be easily expanded to include functions such as IsCurrentDayInSameMonthAsPrevious and the resulting code again looks even more elegant in a scenario where while we are looping through the days, we want to do something on when the month changes from one day to the next for example. It is quite straightforward to implement but as an excercise I will leave it up to you to implement :)

So if you find yourself looping in a for or a while loop, take another careful look at your code, you may be able to use an Iterator and express your code intent in a more clear and concise fashion. Of course this does not mean that we take it to the other extreme abandoning all loops for Iterators!

Question:
Can I implement the IEnumerbale GetEnumerator with the same logic as IEnumerable<DateTime> GetEnumerator() but without copying down the same code in two places, how?

Dont Repeat Yourself, we see it all over, but yet the suggested way of doing things with a Repeater control makes many developers repeat code. Let me explain what I mean, say we want to use a repeater to render a table containing rows of alternating colors. Sure there is a GridView for this, but there are some cases when we would like to use the more lightweight repeater (especially if we just want a readonly representation and do not want editing capabilities)

As you already know, alternating rows in the repeater can be implemented by entering corresponding markup in the <ItemTemplate> tag for one style and more markup in the <AlternatatingItemTemplate> for the alternating template code. So we basically manage to end up with duplicate code just to set our style.

I mean, just look at the following snippet. The main markup is placed twice just to set the alternating row style.

<table>
    <asp:Repeater ID="Repeater1" runat="server">
        <ItemTemplate>
            <tr class="RowStyle0">
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "FirstName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "LastName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "Age") %>
                </td>
            </tr>
        </ItemTemplate>
        <AlternatingItemTemplate>
            <tr class="RowStyle1">
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "FirstName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "LastName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "Age") %>
                </td>
            </tr>
        </AlternatingItemTemplate>
    </asp:Repeater>
</table>

I just dont like seeing <AlternatingItemTemplate> in my code for something as simple as setting the style of a row.
The workaround is just to use the ItemTemplate and determine the css class of the each TR by evaluating the Container.ItemIndex modulo 2. So, here it goes:

<table>
    <asp:Repeater ID="Repeater2" runat="server">
        <ItemTemplate>
            <tr class="RowStyle<%# Container.ItemIndex % 2 %>">
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "FirstName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "LastName") %>
                </td>
                <td>
                    <%# DataBinder.Eval(Container.DataItem, "Age") %>
                </td>
            </tr>
        </ItemTemplate>
    </asp:Repeater>
</table>

Just make sure you have a .RowStyle0{ } and .RowStyle1{ } in your css with your colors of choice

Now this is a simple showcase example, imagine having a more complex repeater (containing more columns). IMHO, avoiding the AlternatingItemTemplate leeds to DRYer, more maintainable code. Overall this is a very simple example but im pretty sure that we've all come across AlternatingItemTemplate for such a trivial task.

CREATE Proc dbo.ScriptInsertStatements(
      @TableName varchar(128),
      @WhereClause varchar(400) = Null

)
AS

DECLARE @sql varchar(Max)
DECLARE @sqlColumns varchar(Max)
DECLARE @SqlColumnValues varchar(Max)

SELECT @sqlColumns = Coalesce(@SqlColumns + ',','') + Column_Name from Information_Schema.Columns where Table_name = @TableName

SELECT @SqlColumnValues = Coalesce(@SqlColumnValues + '+  '','' + ','') +
      CASE
            WHEN Data_Type in ('varchar','nvarchar', 'char', 'nchar', 'datetime' ) THEN  'QuoteName(' + Column_Name + ', '''''''')'
            ELSE 'Cast(' + Column_Name + ' as varchar(MAX))'
      END
FROM
      Information_Schema.Columns where Table_name = @TableName

 
SELECT @sql = 'Select ''Insert Into ' + @TableName + ' (' + @SqlColumns + ') Values ('' + ' + @SqlColumnValues +' + '')'' FROM ' + @TableName + IsNull(' WHERE ' + @WhereClause, '')

Exec(@sql)

To use the above stored proc simply call it passing the Table Name
e.g.
    Exec dbo.ScriptInsertStatements 'Users'

You may also want to partially script a table based on a condition. You can supply that condition in the whereclause like so:
--only script insert statements of Users Table for records with field 'Active' set to 1
    Exec dbo.ScriptInsertStatements 'Users', 'Active=1' 

1. A couple of words about the PE file format

2. IsDotNetAssembly - Delphi version

function IsDotNetAssembly(FileName:  string):boolean;
var
  fs: TFileStream;
  peHeader: LongWord;
  peMagicNumber: Word; //contains if it is 32bit or 64bit image
  RVA15Value: LongWord;
  DictionaryOffset: LongWord;
begin
 
  result := false;
  fs := TFileStream.Create(FileName, fmOpenRead or fmShareDenyNone);
 
    try
      fs.Position := $3C; //PE Header start offset.
      fs.ReadBuffer(peHeader,sizeOf(peHeadeR));
 
 
      fs.Position := peHeader + $18;
      fs.ReadBuffer(peMagicNumber, sizeOf(peMagicNumber));
 
      case peMagicNumber of
        $010B: DictionaryOffset := $60;  //32 bit Image
        $020B: DictionaryOffset := $70;  //64 bit Image
      else
        raise Exception.Create('Invalid Image Format');
      end;
 
      //Position to RVA 15 of the DataDictionary.
      fs.Position := peHeader + $18 + DictionaryOffset + $70;
 
      //Read the value.
      fs.ReadBuffer(RVA15Value,sizeOf(RVA15Value));
 
      //If this value is non zero this is a clr assembly
      result := RVA15Value <> 0;
 
    finally
      fs.free;
    end;
end;

3. IsDotNetAssembly C# Implementation