[BTS] Functoid for BizTalk Learning (Database Lookup)

Development programs cannot avoid frequent access to the database. Here, the most common option is select * from SomeTable where ID =.
BizTalk must also provide a configurable Development Method for us !! It is the Database Lookup Functoid.

In this example, enter the user name, query the database, and obtain other data of the user.

Fegend-1 input data

Fegend-2 database table

Fegend-3 output data

The definition process of input and output Schema is omitted here, and the definition of Map is directly involved.
Add Database Lookup Functoid, Value Extractor Functoid, and Error Review Functoid to Map.

Map created by Fegend-4

Database Lookup Functoid has four input parameters
1. Value
2. Connection String
3. TableName
4. ColumnName
It completes the following code.
Connection conn = new Connection (ConnectionString);
Command cmd = new Command ("select top 1 * fromTableNameWhereColumnName=Value", Conn)

Configurations in Fegend-5 Database Lookup

After the Database Lookup Functoid is queried, An ADO Record is obtained.
Here, the Value Extractor Functoid must be used to read the Record data.
Of course, the following code
String value = Reader ["Name"]. ToString ();

Configuration of Fegend-6 Value Extractor

The Error Review is used to capture errors generated during Database Lookup Functoid running.

Fegend-7 capture exceptions

You will find that it is not very troublesome to set ConnectionString in Database Lookup Functoid. Of course there are methods. You can use UDL files!
Create a UDL file and double-click it for configuration.

Fegend-8 UDL File

Fegend-9 configuration data source

In this way, you can directly use
File Name = c: \ ...... \ *. UDL
File to set ConnectionString. However, its use may cause some performance problems, because every time you connect to the database, you have to read files.

Fegend-10 modify ConnectionString

It was not until today that a Database Lookup routine was completed, which is very convenient to use!