| Profil von Denzil PintoTheBuzzFotosBlogListen |  Extras  Hilfe |
|
19 Mai How to retreive values from SQL Table in a MapYou will definitely need to use the Database functoid for this example.
If you have been following the examples from the previous blogs then you don't need to create the schema files all over again. There is just one small change in the OrderItems schema, the UnitID datatype has been changed to xs:int. You can scroll down all the way to the SQL Table section.
If this is your first time. Then we will need a biztalk solution file and two schemas: OrderItems and Purchase
OrderItems
---------------
1. Right click on your project file and click on Add > Add New Item . Select Schema from the Templates provided and enter the name OrderItems.xsd and click on Open.
2. In the Schema Editor Right click on the Root Node and select Rename from the list and enter the name Order and press Enter.
3. Right Click on Order and select Insert Schema Node and select Child Field Element. Enter the name OrderID and press Enter. Again Right click on order and select Insert Schema node and select Child Field Element. Enter the name Description and press Enter.
4. Now we are going to add a new Child Record. Again Right click on Order and select Insert Schema Node and select Child Record, enter the name Unit and press Enter.
5. Follow the 3 step and create new Child Field Elements called UnitID, Description,TotalUnits,Price.
6. Right click on UnitID and select Properties, in the properties window scroll down to the DataType and select xs:int.
6. Now click on TotalUnits and right click and select properties, goto the DataType and select xs:int. Follow the same steps for the Price and select xs:int for the datatype.
7. We have now created our first schema
Now lets create our second schema called Purchase.xsd
Purchase
------------
1. Right Click on your project file and click on Add > Add New Item. Select Schema from the templates provided and enter the name Purchase.xsd and click on Open.
2. In the Schema Editor Right click on the Root Node and select Rename from the list and enter the name Purchase and press Enter.
3. Right Click on Purchase and select Insert Schema Node and select Child Field Element. Enter the name OrderID. Follow the same step and create another Child Field Element called Description.
4. Now add a new Child Record called Unit. To do that right click on Purchase node and select Insert Schema Node and select Child Record, enter the name Unit and press Enter.
5. Follow the 3 step and create new child field elements called Description, Units, Price and Total.
6. We have now created our second schema. The Total in this schema will be (Price X Units).
Map
-----
1. Now lets create a new Map call it OrderItemsToPurchasemap.btm. For that you need to right click on the project file and select Add > Add New Items and from the templates select map, enter the name "OrderItemsToPurchasemap.btm" and select open.
2. In the Mapper tool, click on open source schema and drilldown Schema and select Order Items and then click on Open Destination Schema and drilldown Schema and select Purchase.
3. Now click on OrderID in the Source Schema and then drag it across the grid to the destination field " OrderID" in the destination schema. Do the same for Description.
4. Click on Description the child field element from Unit and drag it across the grid to the destination field "Description" in the destination schema.
5. Click on TotalUnits and drag it across the grid to the destination field Units in the destination schema. Click on Price and drag it across the grid to the destination field Price in the destination schema.
Now we are going to add a functoid.
1. If you dont have the Toolbox open Click on View > ToolBox.
2. Click on Mathematical Functoids and click on multiplication, drag and drop it on the grid portion of the mapper.
3. Click on TotalUnits in your source schema and drag and drop it to the multiplication functoid, do the same for Price.
4. Now right click on the multiplication functoid and select properties.
5. Enter a label for the functoid. If you click on Input Parameter and click on the ellipsis, you will see the two input parameters you have just selected ie Total Units and Price.
6. From the Toolbox select Advanced Functoids and click on Scripting functoid, drag and drop it to the grid.
7. Click on the multiplication functoid and drag and drop it to the Scripting Functoid.
Well just lets step back and see why we need a scripting functoid. The TotalUnits and Price have their datatypes set to xs:int or integer and Total in the destination Schema is a string. Total in the destination schema is the value of (TotalUnits X Price) in the source schema and that is what we accomplished by adding the multiplication functoid. Now we are adding the scripting functoid to convert the integer to a string.
8. Right Click on the Scripting functoid and select properties if you click on input parameters and click on the ellipsis you will find the multiplication functoid as the input parameter which means that the result of multiplying TotalUnits and Price is the input for the scripting functoid.
9. Now click on Script and click on the ellipsis again. From the Script type, select Inline C# and in the box provided below, type in the following function.
public string IntToString(int param1)
{
return param1.ToString();
}
10. Click on OK.
11. Click on the Scripting Functoid and drag it to the Total in the destination Schema.
SQL Table
--------------
1. Lets add a new table to our Northwind database. The table should be called TestProduct.
2. There should be two fields
UnitID int
Description varchar 50
3. Add some data to your new table.
UnitID Description
1 Product1
2 Product2
3 Product3
Database Functoid
------------------------
Now lets add our new Database functoid. In order to start lets first click on BizTalk in our menubar and select Add Page. If you have lots of functoids in your map, you can add new pages to avoid cluttering of everything on one page. You can click on BizTalk > Rename Page. To Rename your page accordingly.
1. Now lets delete the link that we created earlier between Description source schema and Description destination schema link
2. Now lets add the Database functoid click on the Database functoids in your toolbox. Click on Database Lookup and drag and drop it to the grid. Now click on UnitID and drag and drop it to your database lookup functoid.
3. Now Right Click on the Database functoid and select properties. In the properties windows click on Input Parameters and click on the ellipsis. link
So lets discuss about the Input Parameters with regards to the database lookup
To add the above parameters in the Configure Functoid Inputs click on the Add Button bitmap (rectangle with a flash in the corner bitmap). Click on OK when you have finished. Now lets add another functoid that will extract the value for us. So click on Value extractor from the toolbox and drag and drop it to the grid. Now click on the database lookup functoid and drag and drop it to the Value extractor.
1. Right click on the Value Extractor functoid and click on Properties. In the Properties Window click on the Input parameters and click on the ellipsis. 2. In the configure functoids Inputs you have two input parameters.
3. Now click on Description in your destination schema and drag and drop it to the value extractor. Link.
Test Map -----------
Now lets test our map. Inorder to test we need a xml file for input
XML File ------------ <Order xmlns="http://BizTalk_Mapper.OrderItems">
Now save the above xml to your hard drive. In your solution explorer right click on the map and click on properties. Update the values
Validate TestMap Input - True
Validate TestMap Output - True
TestMap Input Instance - c:\test.xml (XML file that you just created).
TestMap Input - XML
TestMap Output - XML.
Click on OK.
Now right click on your map in your solution explorer and click on Test Map. In the output window ( View > Other Windows > Output) you will see a file where you output is stored. Open it and you will notice that description for Unit is "Product 2"
Kommentare (2)Melden Sie sich zum Hinzufügen eines Kommentars mit Ihrer Windows Live ID an (wenn Sie Hotmail, Messenger oder Xbox LIVE verwenden, besitzen Sie eine Windows Live ID). Anmelden Sie haben noch keine Windows Live ID? Registrieren
TrackbacksDie Trackback-URL für diesen Eintrag ist: http://denzilpinto.spaces.live.com/blog/cns!A2256C53CD2EC18E!197.trak Weblogs, die sich auf diesen Eintrag beziehen
|
|
|
