System.FormatException throw after applying BizTalk Adapter Pack 2010 CU2 when insert an EMPTY int element into SQL database using WCF-Custom adapter

System.FormatException throw after applying BizTalk Adapter Pack 2010
CU2 when insert an EMPTY int element into SQL database using WCF-Custom adapter

Symptoms

Consider the following scenario:

- You installed Adapter Pack 2010 CU2 on BizTalk Server 2010.

You create a send port on this BizTalk Server  
2010 that uses the WCF-Custom adapter to call a stored procedure that inserts  
rows in the column of a Microsoft SQL Server table. The column of the table is  
nullable.

• An input message that arrives at the send port
  has empty elements.

For example, the message has the
<ns0:input></ns0:input> or <ns0:input/> empty elements

In this scenario, an error message that resembles the
following is logged in the Application log:

 
 
  
   

Cause

Before Adapter Pack 2010 CU2, if
an empty element is specified for a stored procedure argument in the instance
XML, the WCF-Custom adapter will specify DBNull as the parameter value.
However, this is not the case for table operations(such as table insert), an
empty string will be specified as the parameter value unless the
"nil" attribute is present in the instance XML. If the “nil”
attribute is set as true in the instance XML for table operation, then DBNull
will be used as the parameter value, the FormatException won’t be thrown out.
That’s why we didn’t see any exception reported previously.

After Adapter Pack 2010 CU2, we
made the code paths consistent between table operations and stored procedure
invocation.  That is, when the element is empty, the DBNull will be used
as parameter value if “nil” attribute is set as “true”, Otherwise an empty
string will be used as the parameter value. Please note that using DBNull
always can cause problems if the parameter is used to populate a table column
that is marked as not nullable. This is the right behavior after Adapter Pack 2010
CU2.

Solution

Thus to make the application work, you should tag the
empty elements with xsi:nil=”true” attribute. In this way, WCF-Custom adapter
will pass a DBNull instead of empty string for the int.

Below is an example of instance XML for your reference:

<ns0:insertcol xmlns:ns0=”https://schemas.microsoft.com/sql/2008/05/TypedProcedures/dbo”>

      <ns0:Address>Addr</ns0:Address>

       <ns0:Number xsi:nil=”true” xmlns:xsi=” https://www.w3.org/2001/XMLSchema-instance ” ></ns0:Number>

</ns0:insertcol>

You should add the above highlighted code into your
current instance XML.

 

Below is a summary chart for your reference:

 Before Adapter Pack 2010 CU2

Operation	Is xsi:nil=”true” set?	Results
Table Operation	Yes	No Exception + DBNull will be inserted
Table Operation	No	System.FormatException
Stored Procedure	Yes	No Exception + DBNull will be inserted
Stored Procedure	No	No Exception + DBNull will be inserted

 

After Adapter
Pack 2010 CU2

Operation	Is xsi:nil=”true” set?	Results
Table Operation	Yes	No Exception + DBNull will be inserted
Table Operation	No	System.FormatException
Stored Procedure	Yes	No Exception + DBNull will be inserted
Stored Procedure	No	System.FormatException

 

Best regards,

Rachel Huang