A scenario I've repeatedly encountered in database development is to create a stored procedure that accepts a range of values as a comma delimited list and returns a result set that contains at least one of the values from the list.  To do this, the parameter value must be parsed as something that can be understood by the IN clause.  I prefer to turn the delimited list into a single tabled column.  Because this task is so common, I wrote the following function that takes a comma delimited string and returns a table variable.

CREATE FUNCTION [dbo].[fnCommaDelimitedToTable]
(
        @CommaDelimited varchar(5000)
)
RETURNS @returntable TABLE
(
         FilterColumn varchar(50)
)
AS
BEGIN
SET @CommaDelimited = REPLACE(@CommaDelimited, ', ', ',')
WHILE CHARINDEX(',' , @CommaDelimited) > 0
BEGIN
        INSERT INTO
                @returntable
        VALUES
        (
                SUBSTRING(@CommaDelimited, 0, CHARINDEX(',', @CommaDelimited))
        )

        SET @CommaDelimited = SUBSTRING(@CommaDelimited,  CHARINDEX(',', @CommaDelimited) + 1,
LEN(@CommaDelimited))
END
-- Insert the last one, or if there was only one supplied.
INSERT INTO
        @returntable
VALUES
(
        @CommaDelimited
)
        RETURN
END

With this function you can now write queries like

SELECT *
FROM
    Customers
WHERE 
    Name IN (SELECT SearchFilter FROM dbo.fnCommaDelimitedToTable(@DelimitedNames))

In SQL Server Integration Services (SSIS) the DataReader Source component is analogous to ADO.NET's DataReader classes.  It retrieves a Binary Large Object (BLOB) stream from the data source and is therefore efficient and the least expensive option in terms of memory.  Having said that, there are times when it is convenient to convert the returned BlobColumn into a string object.  Within a script component, the following will build a binary array from the BLOB stream and then encode the binary array to a Unicode string.

Dim result As String = _
System.Text.Encoding.Unicode.GetString(Row.FieldName.GetBlobData(0, CInt(Row.FieldName.Length)))

Again, one should be cautious when doing this.  It is similar to using the DataAdapter class to convert a DataReader to a DataSet, so depending on the size of the data, this may or may not be a good idea.

Microsoft is offereing Visual Studio 2008 along with Windows Server 2003, Expression Studio, and XNA game studio at no cost to college students.  Visit the Microsoft DreamSpark site to register and download .  Enjoy.

Disabling a range of dates on a calendar control is a great way to reduce data-entry error.  For example, imagine a scheduling system for an organization whose doors are open Monday through Friday.  Ideally, any calendar controls should disable Saturday and Sunday to prevent users from accidentally choosing them.  This can be accomplished by taking advantage of the ondayrender event of the calendar control.  First, in your aspx  markup, wire up a method to the event.

<asp:Calendar Visible="false" ID="DisabledWeekendsCalendar" runat="server" ondayrender="DisabledWeekendsCalendar_DayRender"></asp:Calendar>

Finally, in your .NET code, create the method which checks to see if a day is a weekend, and if so disables it.

protected void DisabledWeekendsCalendar_DayRender(object sender, DayRenderEventArgs e)
    {
        if (e.Day.Date.DayOfWeek == DayOfWeek.Saturday || e.Day.Date.DayOfWeek == DayOfWeek.Sunday)
        {
            e.Day.IsSelectable = false;
            e.Cell.ForeColor = System.Drawing.Color.Gray;
        }
    }

With Microsoft SQL Server 2005, paging on the database side is simple.  There is a built-in ROW_NUMBER function designed specifically for this task.  However, versions of SQL Server prior to 2005 do not have this capability.  Below is one way to implement similar functionality in pre-2005 versions. 

SELECT 
    TOP X * 
FROM 
    Records 
WHERE 
    RecordID NOT IN (SELECT TOP Y RecordID FROM Records)

You must replace X and Y with actual integer values, Unfortuantly, due to sql sytanx, you can’t use varaibles.  The records returned from the above query will follow

Y + 1 = Beginning record
X + Y + 1 = Ending Record

So 10 and 20 plugged in to X and Y respectively would return records 21 thorugh 31.

Microsoft just released the “Microsoft BizTalk Server Operations Guide” document.  They describe it as, “…detailed information for planning a BizTalk Server environment, as well as recommendations and best practices for configuring, testing, maintaining, monitoring, and optimizing this environment.”  You can view it from MSDN or download a copy for offline use.

To debug a piece of .NET code called by the BRE you must first attach the Visual Studio debugger to the BRE process.   To accomplish this, open the debug menu and select “attach to process…” and select the Microsoft.RuleComposer process.  Now you can put breakpoints on the desired lines of .NET code and when the rule executes from the BRE, the execution will catch at the first breakpoint.

Facts can be asserted to the BRE from .NET code simply by passing them as an argument to the policy’s execute method which takes a variable number of parameters.  For example,

Microsoft.RuleEngine.Policy policy = new Microsoft.RuleEngine.Policy(“PolicyName”);
Policy.Execute(Object1, Object2,…);

There are three types of facts used by the BRE:  An xml document, a Database table, and a .NET class.  The latter two have a couple peculiarities about them which require further explanation. 

Asserting a Database Table

When using a database table, the BRE expects an object of type Microsoft.RuleEngine.DataConnection to be asserted, which tells the BRE where to find the table.  This is achieved with the below code.

//Create the DataConnection
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(“connectionstring”);
Microsoft.RuleEngine.DataConnection dconn = new Microsoft.RuleEngine.DataConnection(connection);

//Create the policy and assert the DataConnection
Microsoft.RuleEngine.Policy policy = new Microsoft.RuleEngine.Policy(“PolicyName”);
Policy.Execute(dconn);

Static .NET Methods

Calling a static .NET method from the BRE can be achieved without asserting an instance of the class by setting the the StaticSupport (DWORD value) registry key located under HKEY_LOCAL_MACHINE\Software\Microsoft\BusinessRules\3.0 to a value of 1.  Without this registry entry, static method calls within the BRE will require asserting instances of the class that contain the method.

Even though the Business Rules Engine (BRE) is included with BizTalk 2006, it does not need to be exclusively called by the BizTalk host.  The BRE has a .NET API library that enables any host to communicate with the engine programmatically.
You must first reference the Microsoft.BizTalk.RuleEngineExtensions.dll from the BizTalk installation directory (the default installation is C:\Program Files\Microsoft BizTalk Server 2006).

You can then call BRE policies with the following code

Microsoft.RuleEngine.Policy policy = new Microsoft.RuleEngine.Policy(“PolicyName”);
Policy.Execute();

The execute method can take any number of objects as a parameter that need to be asserted as facts to the BRE for execution.

If you are using the Business Rules Engine (BRE) as part of BizTalk or Windows Workflow Foundation and are asking yourself “Why are my rules firing in the wrong order?” you probably need a better understanding of how the BRE executes a set of rules (policy).  The priority property of a rule will control the order in which the action of that rule will fire relative to the other actions in a policy.  Specifically, an action of a rule with a higher priority will fire before an action of a rule with a lower priority.  Notice it is the action of a rule which is affected by the priority and not the evaluation of the rule.  The evaluations themselves will still occur in an arbitrary order.  For example, assume three rules exist in the same policy, “Rule 1”, “Rule 2”, and “Rule 3” with their priority properties set to 3, 2, and 1 respectively.   When the policy is invoked, Rule 2 may be evaluated, then rule 3, and finally rule 1.  Only after evaluation of all rules will the actions of the rules fire in the order specified by the priority values.  What are the practical consequences of this?  A rule dependent on the action of a previous rule within the same policy won’t evaluate as expected. 

There are a couple ways to overcome this problem.  The first is to place rules dependent on each other in separate polices.  This requires the host application to call multiple policies and to be knowledgeable about the order in which they should be called, a detail which should be abstracted from the calling process.  The second technique is to use an update action to let the BRE know an object has changed its state and that any rules that had evaluated this fact need to be re-evaluated.  The process look like this

1. Execute all rules of a policy (these will be evaluated in an arbitrary order)
2. Fire any Actions for any rules that evaluated to true  (these will be fired according to the rule’s priority property)
3. Check if any facts used the update method to notify the BRE that a fact changed its state.
4. Re-evaluate any rules that contained updated facts
5. Repeat 2-4 until no facts are updated