In my latest project we had a requirement to validate schemas, In several locations within the process flow schemas needed validation.

Now I know validation can be performed in an xml pipeline, but then again when looking in to the pipeline code using reflection validation happens using an XmlReader against the XSD schema, there are several benefits in writing a custom component.

The XmlReader takes in its constructor an XmlReaderSettings component, the XmlReaderSettings exposes an event we can subscribe to which will trigger if the schema validation fails, This allows to create an ESB fault message(we where using the ESB Exception management portal) without running within a try catch block, which is more preferment. 

The XmlReaderSettings uses the XmlSchemaSet object to store the schemas it will validate against which allows the ability to cache the schemas and thus improve performance. Note that XmlSchemaSet is guaranteed to be thread safe only when used as a static field(singleton), all schemas that need validation must be loaded in to the XmlSchemaSet in the static constructor and thus create a caching effect.

The code is available here SchemaValidation.cs

This post shows how to Consume a BizTalk WCF-Custom adapter with sqlbindings to create a polling mechanism from a SQL table.

A bit about polling.

In general I am a big fan of polling from SQL, compare to having a receive port within a listen shape and a delay, which will cause dehydration. In cases of a short time correlation when the response is expected in a fairly short time a listen shape is a good solution especially if we might not be notified of an exception happening later on or when implementing scatter gather patterns, but if waiting time can exceed minutes then “waiting in the DB”, to my opinion, is the correct approach.

I did come upon solutions with messages that could wait for a correlation response from an external system using a delay shape for month, which resulted in thousands of dehydrated messages, that stuffs up the message box, it’s hard to keep track of the messages and resolve failure scenarios, not to mention the overhead on the entire system.

The benefit of waiting in the DB and polling when a state was reached reduces message box overhead and hands a lot of control over the message in the sense of knowing what state a process is in, having the ability to store information from the message in additional columns (assuming for example the message is serialized to an XML column and key fields are saved in additional columns) and the obvious ability to query those fields, being able to initiate polling only when several conditions are met(for example values or rows form other tables), and easy to amend rules having the polling logic in a stored procedure.

There are several polling techniques available in the 2009 WCF-custom Adapter with SQL bindings as Polling, TypedPolling, XMLPolling and notification can be found here.

In this post I will show polling using the XMLPolling type.

A look in to the stored procedure:

      --how many messages in each batch   
      declare @BatchSize int = 5;
      declare @tblIDs table (BatchID int)
      declare @BatchCount int = 0;
      
    insert into @tblIDs
     select top(@BatchSize)  BatchID from Batch WITH(NOLOCK) where Operation = @Operation and Processed = 0
 
      IF (select count(BatchID) from @tblIDs) > 0
      BEGIN
            update Batch set Processed = 1 where BatchID in(select BatchID from @tblIDs)
            set @BatchCount = (select count(BatchID) from @tblIDs)
      END
      
      declare @xVar XML
      SET @xVar = 
      (
            select [xml] from Batch where BatchID in(select BatchID from @tblIDs)
            FOR XML AUTO
      );

      WITH XMLNAMESPACES ('http://BizTalk/BatchsMessages' as ns1)
       Select @Operation as Operation, @BatchCount as BatchCount ,@xVar as 'ns1:xmlMessage'
      FOR XML RAW('ns1:BatchsMessages')

The schema:

Use the xmlschema command to generate an XSD, use this XSD schema in the BizTalk project as the polling schema.

You need to change the import schemaLocation=”sqltypes.xsd” in the generated XSD to point to a local XSD schema, you can get it here.

Create a schema Envelope point the body XPath to the root element as shown below:

The properties of the schema and the root element should look something like this.

PipeLine:

Create a receive pipeline and add a dissembler component, add the pooling schema to the Document schemas (collection) and the envelope schema to the Envelope schema (collection).

This should strap the wrapper element the adapter will add to the message.

Orchestration:

In the orchestration create an activate receive port specified to receive a messages of the pooling schema type.

The port message type should be set to the polling schema type.

Physical port binding:

Create a WCF-Custom receive location with sqlbinding as shown bellow.

The root node and namespace must match the ones on the envelope schema, the pipeline will strip off that root element passing in to the orchestration the polling schema.

Attach the pipeline to the receive location.

Note you must have some select statement or a SP call in the polledDataAvailableStatement property, should be some sort of count, if the result from that statement is >0 a call is made to the next statement ”pollingStatement” (tip: you can initiate polling by specifying “select 1”).

You are good to go .

A walk through how to create XMLPolling WCF custom with SQL binding can be found here.

Enumerating the context properties of a BizTalk message can be useful when trying to solve an error in an orchestration.

I wrote the following method to our tracing component that will loop through the context properties of an XLANGMessage.

because the context properties are hidden within the XMessage object in the Microsoft.XLANGs.Core name space, I had to use reflection to unwrap  XMessage in order to expose the GetContextProperties() method.

Important:

This method uses the internal method "Unwrap" in the XMessage object that is NOT intended to be used by user code DO NOT USE this method in a production environment, I use it only for testing purposes and remove the call to the method when finished.

public void WriteMessageProperties(XLANGMessage message)
{
    try
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(Environment.NewLine);
        if (message is Microsoft.XLANGs.Core.MessageWrapperForUserCode)
        {
            Microsoft.XLANGs.Core..MessageWrapperForUserCode mwu = (Microsoft.XLANGs.Core..MessageWrapperForUserCode)message;
            Microsoft.XLANGs.Core.XMessage xmessage = (Microsoft.XLANGs.Core.XMessage)mwu.GetType()
            .GetMethod("Unwrap", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic)
            .Invoke(mwu, null);
            if (xmessage != null)
            {
                System.Collections.Hashtable ht = xmessage.GetContextProperties();
                Microsoft.XLANGs.Core.XmlQNameTable tbl = new Microsoft.XLANGs.Core.XmlQNameTable(ht);
                foreach (System.Collections.DictionaryEntry dic in tbl)
                {
                    Microsoft.XLANGs.BaseTypes.XmlQName N = (Microsoft.XLANGs.BaseTypes.XmlQName)dic.Key;
                    sb.Append(N.Name + " : " + dic.Value + Environment.NewLine);
                }
            }
        }
        LogEntry entry = new LogEntry();
        entry.Message = sb.ToString();
        entry.Categories = new string[1] { sourceType };
        entry.Severity = System.Diagnostics.TraceEventType.Verbose;
        Logger.Write(entry);
    }
    catch (Exception ex)
    {
        Debug.WriteLine(ex.ToString(), "Error");
    }
    finally
    {
        // Call Dispose on the XLANGMessage object 
        // because the message doesn't belong to the 
        // .NET runtime - it belongs to the MessageBox database 
        message.Dispose();
    }
}

We had a very severe problem recently on our BizTalk environments when building the solution some of the projects lose the reference to other projects in the solution.

We are using BizTalk 2009 Visual studio 2008 SP1 on Windows Server 2008 (SQL Server 2008  though i don't think this has anything to do with the problem)

The references are lost randomly, one moment the solution compiles, or for that mater even when just building a single project, and after a small change (that does not generate an error) the next build VS looses references and can not recognise external components and thus build will fail.

We worked around using file reference and not project reference which seems to respond better but still generates build errors on occasion because of reference loss. 

Another detail which is probably worth mentioning is all BizTalk environments are on VPC’s, don't think this should matter.

Searching around we found that this problem was recently raised to Microsoft.

How to raise an error on an SP and catch the exception in code

Some times, especially when working in a transactional environment,  we wound need to raise an exception from our SQL server Stored Procedure deliberately and catch this exception, for example, in a c# application within a try catch block in order to Roll Back the changes.

This is common behaviour when having the .NET code manage the transaction logic, when the code will call several Stored Procedures and the Roll Back is coordinated in code.

so our call to the DB will look something like this:

using (SqlDB db = new SqlDB(BusinessLogic.Constants.ConnectString))
{
     try
     {

    SqlParameter p;
    SqlCommand cmd;

         using (cmd = new SqlCommand("usp_MyUsp", db.Connection, db.Transaction))
         {
              p = new SqlParameter("@id", SqlDbType.NVarChar, 50); p.Value = data.id; cmd.Parameters.Add(p);
              p = new SqlParameter("@data1", SqlDbType.NVarChar, 40); p.Value = data.data1; cmd.Parameters.Add(p);
              p = new SqlParameter("@data1", SqlDbType.Int); p.Value = data.data1; cmd.Parameters.Add(p);

              cmd.CommandType = CommandType.StoredProcedure;

              cmd.ExecuteReader()

         }

    db.Commit();

     }
     catch (Exception ex)
     {
         db.Rollback();

     }

}

To raise an error in the SP that will be caught in the catch block we need to call the SQL server method RAISERROR() and immediately return a value. the Stored Procedure to raise the error would look something like this:

  DECLARE @DocID int;

  --find the id
  SELECT @DocID = id FROM table WHERE id = @id
    
  --if not exist send back an error       
  IF @DocID is null  
  BEGIN
      RAISERROR('ID not Found', 16, 1)
      RETURN -1
  END

note: concatenating the error message might create an error.

This example is very general, the two numbered parameters passed in to the method in this case (16 ,1) represent the severity and status of the raised error.

for more detailed information you can read it on the MSDN pages. http://msdn.microsoft.com/en-us/library/ms178592.aspx