BizTalk 2013 Intorduction

http://blogs.msdn.com/b/biztalk_server_team_blog/archive/2012/11/06/announcing-biztalk-server-2013-beta.aspx
http://biztalkadmin.com/biztalk-2013-sql-2012-and-server-2012/
http://www.zdnet.com/microsofts-biztalk-2013-enterprise-integration-server-hits-beta-7000007054/

Can we do method overloading in WCF services

The soap xml not support OOPs and WCF generate proxy and it'll not follow method overloading.
Inside the WCF service code it'll allow us Method Overloading.

By default overload operations (methods) are not supported in WSDL based operation. However by using Name property of OperationContract attribute

Sample:
[ServiceContract]
public interface IService1
{
[OperationContract(Name="GetData")]
string GetData(int value);

[OperationContract(Name="GetData1")]
string GetData(int value, int value1);

}

public class Service1 : IService1
{
public string GetData(int value)
{
return string.Format("You entered: {0}", value);
}

public string GetData(int value, int value1)
{
return string.Format("You entered: {0}, {1}", value, value1);
}
}

Notice that both method name in the above interface is same (GetData), however the Name property of the OperationContract is different. In this case client proxy will have two methods with different name GetData() and GetData1().

Accessing Application Configuration Settings from SQL CLR

In some (most?) cases we don’t want to hard-code connection details. In this case it means the path to the web service. Imagine a situation where you are consuming a web service that you’ve developed yourself, or another team in your company. And the web service is running in several environments: development, acceptance, production. Of course we don’t want to change our code and recompile each time we need to deploy to a different environment. So we need a way to configure the web service location.

The answer here lies in the sqlservr.exe.config file. This is a file like any other .config file, with a possible content like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="MyWebService" value="http://MyWebServiceServer:MyWSPort/PathTo/MyWebService.asmx" />
  </appSettings>
</configuration>

This file needs to be put in the same folder as your sqlservr.exe that’s running your SQL Server instance.  By default this path is C:\Program Files\Microsoft SQL Server\MSSQL.1\{MSSQL10_50.MSSQLSERVER}\Binn\.  When you restart your SQL Server instance while this file is in that folder, SQL Server will pick up any configuration settings from that file.  These can then be accessed from your custom stored procedure in this way:

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

One of the issues I encountered (a bug) is if the first call through ConfigurationManager is to the static AppSettings method an error is returned indicating it couldn't read the key value. That error is:

"The value of the property 'key' cannot be parsed. * The error is: Request failed. (sqlservr.exe.Config line X)".

Now, the line that causes this exception is:

ConfigurationManager.AppSettings["MyWebService"]

There’s absolutely nothing wrong with that line of code. And, there’s nothing wrong with the configuration file.

So, after some debugging, I found that if I make some “dummy” call through ConfigurationManager to another method, then I can make the call to AppSettings with absolutely no issue. So, the following line is added just to provide a kludge of a workaround :

int firstcall = ConfigurationManager.ConnectionStrings.Count;

Now, this line does nothing other than initialize ConfigurationManager and force a de-serialization of the configuration file.

int firstcall = ConfigurationManager.ConnectionStrings.Count;

MyWebService myWS = new MyWebService();
myWS.Url = ConfigurationManager.AppSettings["MyWebService"];
myWS.MyWebMethod();

Steps to Create SQL CLR component

1. Let’s start with creating our .NET assembly with the custom stored procedure.  Using Visual Studio 2010, create a new Database Project:

2. First, you must change the version of the.NET Framework used to 3.5 at the top of this screen or your project will not deploy. Then change the project name to MyExtendedDBFunctions

Add a reference to the Your (AdventureWorks) database by clicking Add New Reference.

    Fill out the form and click OK.

3. Select AdventureWorks and click OK. Then right click the solution in Solution Explorer and select New -> Stored Procedure

Change the name to TestUserDefinedFunction.

Change the code to look like this:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class TestUserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

   public static SqlString TestUDF(int number)

   {

    BasicHttpBinding basicHttpBinding = new BasicHttpBinding();

   EndpointAddress endpoint2 = new EndpointAddress(new Uri("http://localhost:51610/TestService2.svc"));

    TestService2Client client2 = new TestService2Client(basicHttpBinding, endpoint2);

       return new SqlString(client2.GetData(internalID));    

   }

};

Create a WCF service with the default .net Framework 4.0 and expose that service and use the existing GetData().

4. Before we build our assembly there are a couple of project settings that need modification.

Here we ask Visual Studio to also generate an assembly containing the XML Serializers.  This is needed because our code is calling a web service and code running in SQL Server is not allowed to use the serializers that are normally generated dynamically.

5. Another setting that we need to change is the Permission Level.  This is also required because our code is calling a web service, hence UNSAFE.

6. Server and Database Settings

USE custDB

EXEC sp_configure 'clr enabled', '1';

RECONFIGURE;

ALTER DATABASE custdb SET TRUSTWORTHY ON;

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY  [System.SMdiagnostics]

FROM 'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\SMdiagnostics.dll'

WITH PERMISSION_SET = UNSAFE

GO

-- Adding required assemblieds for the WCF service.

CREATE ASSEMBLY  [System.IdentityModel]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.IdentityModel.Selectors]

FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'     

WITH PERMISSION_SET = UNSAFE

GO

CREATE ASSEMBLY  [System.Messaging]

FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

--Attempted to perform an operation that was forbidden by the CLR host.

CREATE ASSEMBLY  [Microsoft.Transactions.Bridge]

FROM  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'     

WITH PERMISSION_SET = UNSAFE     

GO

7. Click Build -> Deploy Solution and you should receive the following output:

If deploy failed, please follow below steps to add your CLR component and procedure/function to the sql server

Register our SQL CLR component

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyExtendedDBFunctions')

BEGIN

     DROP ASSEMBLY [MyExtendedDBFunctions]

ALTER ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE ;

END

ELSE

BEGIN

CREATE ASSEMBLY MyExtendedDBFunctions

FROM '{our SQLCLR project.dll path}'

WITH PERMISSION_SET = UNSAFE;

END

Once our assemblies are created we have to create Procedure/Function in the below way.

CREATE PROCEDURE WeatherSP

    @zipCode nvarchar(10)

AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;

GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)

AS EXTERNAL NAME MyExtendedDBFunctions.TestUserDefinedFunctions.TestUDF;

Below are the possible errors and solutions 

1. Attempted to perform an operation that was forbidden by the CLR host.

    Sol: Set PERMISSION_SET to UNSAFE Because EXTERNAL_ACCESS did not work for us.

2. The type 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior, Microsoft.VisualStudio.Diagnostics.ServiceModelSink, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' registered for extension 'Microsoft.VisualStudio.Diagnostics.ServiceModelSink.Behavior' could not be loaded. (C:\Windows\Microsoft.NET\Framework\v2.0.50727\Config\machine.config line 233)

    Sol: Note that everything is configured properly and WCF client inside stored procedure should initialize just fine. The problem is somehow connected to machine.config file or better, to some initialization of types defined there.

          Looks like the exception is linked to WCF debugging feature, usually installed on development machines - this debugging service has problems with WCF client running under SQL Server.

         

        Workaround: (Temporary) disable WCF debugging by invoking this command line utility:

        [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -u

        Now the WCF client inside SQL Server runs just fine. After you've done you can re-enable WCF debugging 

services by calling same exe with different parameter:

       [Program Files]\Microsoft Visual Studio 9.0\Common7\IDE\vsdiag_regwcf.exe -i

Note that administrator privileges are required to disable/enable it.

Calling WCF services from within Sql Server 2008 (SQLCLR)

SQLCLR(Common Language Runtime) makes us use .NET framework in SQL   Server.

I am developing a component which installed on a database server to connect WCF service on different hosts.
     Advantage of using CLR in SQL is that T-SQL itself doesn't provide the richer experienced programming environment in comparison with .net framework. We can't use arrays, collection, foreach statement and classes. By integrating CLR, makes it possible to have extensible features in stored procedure, trigger and user defined function.


My module uses .net framework 3.5 and SQL Server 2008 (sp1).
Originally, it built in .net 4.0. but 4.0 hasn't been supported SQL 2008.
So, I changed to 3.5.


Steps.
1. Go to the management studio of SQL Server, first makes sure we are CLR enabled.

     USE custDB
     EXEC sp_configure 'clr enabled', '1';
     RECONFIGURE;


2. Set the database trushworthy
ALTER DATABASE custdb SET TRUSTWORTHY ON;


3. Create below listed assemblies.

-- Adding System.Web assembly

CREATE ASSEMBLY [System.Web]
FROM  'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
WITH PERMISSION_SET = UNSAFE

-- Adding required assemblieds for the WCF service.
CREATE ASSEMBLY  [System.IdentityModel]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.dll'
WITH PERMISSION_SET = UNSAFE
GO


CREATE ASSEMBLY  [System.IdentityModel.Selectors]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\System.IdentityModel.Selectors.dll'    
WITH PERMISSION_SET = UNSAFE
GO


CREATE ASSEMBLY  [Microsoft.Transactions.Bridge]
FROM  'C:\Windows\Microsoft.NET\Framework\v3.0\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'    
WITH PERMISSION_SET = UNSAFE    
GO

4.  Register our SQL CLR component


IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyStoredProcedures')
BEGIN
     DROP ASSEMBLY [MyStoredProcedures]
ALTER ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE ;
END
ELSE
BEGIN
CREATE ASSEMBLY MyStoredProcedures
FROM '{our SQLCLR project.dll path}'
WITH PERMISSION_SET = UNSAFE;
END

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'MyXmlSerializers')
BEGIN
DROP ASSEMBLY [MyXmlSerializers]
END


CREATE ASSEMBLY MyXmlSerializers
FROM{our SQLCLR project.XmlSerializers.dll path}'
WITH PERMISSION_SET = SAFE;

Once our assemblies are created we have to create Procedure/Function in the below way.


CREATE PROCEDURE WeatherSP
    @zipCode nvarchar(10)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.WeatherSP;
GO

CREATE Function TestSP(@name nvarchar(10)) returns nvarchar(150)
AS EXTERNAL NAME MyStoredProcedures.StoredProcedures.TestSP;




Finally, Don't forget that SQLCLR's data types are different to T-SQL.
For example, we should use NVARCHAR in SQLCRL instead of varchar as parameter for SQLCLR procedure.

For more info

ALTER ASSEMBLY failed because the source assembly is, according to MVID, identical to an assembly that is already registered under the name "someCLR".

We get this error when we try to alter the assembly to load the exact same compile as you already have in the database  (the source code
to the assembly unchanged). If you recompile the assembly, you will not get this error.

Static Classes & Singleton

Singleton is the most widely known Design Pattern. Singleton makes sure that one only one instance of a class is created and the same instance is used by other programs and classes.An implementation of a Singleton class is shown below:


class Program
{
    static void Main(string[] args)
    {
            SingletonDemo s = SingletonDemo.GetInstance();
            s.M1();
            Console.Read();
        }
}
   
    public class SingletonDemo
    {
        //Single Instance
        private static SingletonDemo instance = new SingletonDemo();
        //Private Constructor
        private SingletonDemo() { }
        //Method to get the single instance

        public static SingletonDemo GetInstance()
        {
            return instance;
        }

        public void M1()
        {
            Console.WriteLine("SingletonDemo::M1 called");
        }
    }

This is just like any normal class, we are just preventing the creation of multiple instances by making the constructor private and the GetInstance method makes sure that the same instance is returned in all the calls to it.


Now the question is Why Can’t We Make Them Static Classes?
Let change this code to make it a static class.

class Program

{

    static void Main(string[] args)

    {

        StaticDemo.M1();

        Console.Read();

    }

}

public static class StaticDemo

{

    public static void M1()

    {

        Console.WriteLine("StaticDemo::M1 called");

    }

}

This looks all very simple. But there are some restrictions and limitations of the static classes.

Static classes cannot be instantiated.Static classes cannot have instance constructors.

Static classes can only have static constructors

Static classes are sealed. You cannot derive from a static class.

Static classes cannot derive from any other classes other than System.Object

Static classes cannot implement interfaces.

Static classes can only have static members

So from the above list of don’t haves its quite clear that static classes lack certain basic OO features like inheritance & polymorphic behavior. Static classes are containers to logically group a set of members.

The Singleton pattern has several advantages over static classes. First, a singleton can extend classes and implement interfaces, while a static class cannot.

Singleton can implement interfaces, inherit from other classes and allow inheritance.

Singleton object stores in Heap but, static object stores in stack

We can clone the object of Singleton but, we can not clone the static class object

Singleton class follow the OOP(object oriented principles) but not static class

we can implement interface with Singleton class but not with Static class.

Another advantage of a singleton is that it can easily be serialized, which may be necessary if you need to save its state to disc, or send it somewhere remotely.

Implementing Two interface having the same method signature in the same class

There can be scenario when we would have two interface with the same method name and same type of arguments, same number of arguments and even the return type can be same and we need to implement both the interface in the same class. How to implement the interface in our class? Most of you will be thinking whats so tough in this, just implement the interface and the method and move on. If the signature of the methods in the interface were different then there would have been no problem but here the signature of the methods in two different interface are same and both the interfaces are gonna be implemented in the same class. The two interface are as below.

class Test 
{
    static void Main()
    {
        SampleClass sc = new SampleClass();
        IControl ctrl = (IControl)sc;
        ISurface srfc = (ISurface)sc;

        // The following lines all call the same method.
        sc.Paint();
        ctrl.Paint();
        srfc.Paint();
    }
}


interface IControl
{
    void Paint();
}
interface ISurface
{
    void Paint();
}
class SampleClass : IControl, ISurface
{
    // Both ISurface.Paint and IControl.Paint call this method.  
    public void Paint()
    {
        Console.WriteLine("Paint method in SampleClass");
    }
}

// Output: 
// Paint method in SampleClass 
// Paint method in SampleClass 
// Paint method in SampleClass

From the above we have two interface with names IControl and ISurface and both have a single method named “Paint”. The signature of both the methods are same and we need to implement the interfaces in our class say “SampleClass”. One way of impelementing the interface is as shown above i.e. just having a “public” implementation of the interface method only once.

The above code implementation has got a limitation i.e the method “Paint” is considered to be a common method for all i.e common method for the class, and for the interfaces IControl and ISurface.

If you want to give different implementation to the methods in interface IControl and ISurface  what will you do? Its also simple, just have two impelementation of the same method and prefix the method names with the interface name as shown below.

public class SampleClass : IControl, ISurface
{
    void IControl.Paint()
    {
        System.Console.WriteLine("IControl.Paint");
    }
    void ISurface.Paint()
    {
        System.Console.WriteLine("ISurface.Paint");
    }
}
The class member IControl.Paint is only available through the IControl interface, and ISurface.Paint is only available through ISurface. Both method implementations are separate, and neither is available directly on the class. For example:
// Call the Paint methods from Main.
SampleClass obj = new SampleClass();
//obj.Paint();  // Compiler error.

IControl c = (IControl)obj;
c.Paint();  // Calls IControl.Paint on SampleClass.

ISurface s = (ISurface)obj;
s.Paint(); // Calls ISurface.Paint on SampleClass. 

// Output: 
// IControl.Paint 
// ISurface.Paint

BizTalk Orchestration Port Bindings

There are a lot of conflicting ideas out there regarding orchestration/logical port binding options for BizTalk server. In this post I want to provide a short description of each option and some best practices for use.

· Specify Now/Specify Later

These two are commonly referred to as Static bound ports. Both of these options will require that the orchestration port is associated with a physical port before the orchestration can be enlisted or started. The difference between the two lay in the creation of the physical port.

Specify Now requires that you specify the configuration of the physical port inside Visual Studio before the orchestration assembly is compiled. The physical port configuration is baked-into the assembly and will be created in the run-time environment automatically when the assembly is deployed. This is considered the worst port binding option as the created physical port will have the same configuration each time the assembly is deployed. Also, the name of the automatically created port is pretty ugly.

Specify Later is essentially the same in function, but the definition for the physical port is done exclusively in the BizTalk Server Administration Console. This provides more flexibility for port configuration, such as having multiple logical ports connected to the same send port or binding the logical send port to a send port group. This is the more acceptable form of static binding, but shouldn’t be overused especially in cases where subscribers are likely to change over time.

· Direct (Partner Orchestration)

This enables two different orchestration models to communicate with one another. The two orchestration ports must use the same port type. A send port in orchestration A will publish the message to the MessageBox and a receive port in orchestration B will subscribe to the message. The logical ports used for this type of communication will not be displayed in the orchestration configuration options in the BizTalk Server Administration Console. The biggest concern when using this form of binding is that the connection between the two orchestrations is static and can only be changed through modifications to each orchestration and then recompiling.

· Direct (MessageBox database)

This option provides the purest use of the publish/subscribe architecture. A logical receive port that uses this binding will have a subscription that includes only the MessageType used by the logical port operation and the conditions defined in the Filter Expression of an Activating Receive shape. This means that potentially ANY process can feed that orchestration.

A logical send port that uses this binding will publish the message and assume that there is at least one subscriber for it, but there could be many subscribers (i.e. orchestrations, send ports, or send port groups). Assuming that the right subscriptions exist; this type of send port could feed any other process.

Things to beware of: (1) Invisible Subscriptions – there’s no reliable way to see which processes will feed your orchestration or which processes your orchestration will feed; even across application boundaries. (2) “Infinite” loops –the orchestration publishes a message that creates a new instance of the same model, which will publish a message that creates a new instance of the same model…

Upsides of Direct bound to the MessageBox: (1) Very loosely-coupled design – there are no direct dependencies or static relationships between publisher and subscriber. (2) Rip and replace-ability – with appropriate project partitioning, an orchestration could be replaced without affecting upstream or downstream processes.

· Direct (Self-Correlating)

This option exists to enable “callbacks” to the parent orchestration from a child orchestration when the Start Orchestration shape is used. The parent passes an instance of the logical port as a parameter to the child orchestration during invocation; therefore providing the callback channel used by the child to send a message back to the parent later on in the process.

· Dynamic

This option only exists for send ports. Dynamic ports enable you to assign the address used for transmission of the message on an instance-by-instance basis. Typically an orchestration will include a resolution mechanism that determines the address used for that instance and assigns it to the message being published by the logical dynamic port. Logical dynamic ports are bound to physical dynamic ports which look at the previously set address information to know which adapter and address to use for transmission.

This seems like the coolest of the send options but I’ve seen a lot of overuse around the dynamic send ports (especially in the ESB toolkit). The line in the sand I use for deciding whether to use it or not is this: “Do I know the address before I get the message?” If yes, use static physical send ports and role links. If no, ask “Does the message tell me where to send?” If yes, uses dynamic send port. If no, sounds like a big problem.

The perfect example for using a dynamic send port is this: I have a form on the internet; people fill out the form and click Submit. The submitted message is received into BizTalk where some processing is done. At some point during that processing I need to create an email that is sent back to the person that submitted the original message. I can create an orchestration that looks at the email inside the submitted message and assigns it as the address used by that later send.

I hate to see addresses statically stored in custom databases or configuration files and then assigned as the address of a dynamic send port. It really just creates an extra layer of management.

I hope this helped you out on your future BizTalk endeavors, and always remember it doesn’t matter the binding option you use logical ports are only used for communication between orchestrations and the MessageBox database.