BizTalk Scopes and SQL Data Objects

If I had hair to pull out I would have done it today!  Fortunately, I am not sadistic enough to scratch away the skin from my bald head; rather, I chose a more technically viable approach to solving my problem.

I have a Sequential Convoy orchestration which employs a long-running scope to contain the processing and convoy loop.  Inside the scope, I use an Expression Shape to call a C# method inside a serialized class that has some SQL data objects defined…

namespace XXXXX.YYYYY.HelperClasses
{
[Serializable]
public class ProcessBusinessLogic
{

SqlConnection cnPOLICIES;
SqlDataReader drPOLICIES;

}

The orchestration enters the scope, instantiates the class and invokes the method which finishes normally.  The orchestration then tries to route a message to a folder via a Send Shape which throws this error…

Type ‘System.Data.SqlClient.SqlConnection’ in Assembly ‘System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ is not marked as serializable.

I also get this error for the data reader that is defined.  I could not for the life of me figure out why my orchestration was throwing a SQL error until I did a bit of research – like most of the days worth; and found that orchestrations will attempt to serialize everything in memory when it initializes a Scope Shape, Send Shape, etc.  Well, because SQL objects are not serializable,I was destined to get this error.  I opted to fix this by specifically indicating in the C# code that the objects are not to be serialized.  So, my code simply changed to this…

namespace XXXXX.YYYYY.HelperClasses
{
[Serializable]
public class ProcessBusinessLogic
{

[NonSerialized] SqlConnection cnPOLICIES;
[NonSerialized] SqlDataReader drPOLICIES;

}

Now, I am a happy camper and can move on to the next task.  I know there are other ways to have addressed this; however, this is what worked for me.  I hope it can help someone else too.

One thought on “BizTalk Scopes and SQL Data Objects

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s