As most of you already know, last month I presented a session at BizTalk Summit 2015 London event about “BizTalk Server tips and tricks for developers and admins” (You can check the video recording of my session here)
It was a lightweight session (30 minutes) about useful tips that we can use in our daily work, because it was a small session, I didn’t have enough time to cover everything so I end up creating a “Director’s cut…” session with additional tips that you can check in the presentation slides here.
Today, and because I already received some emails regarding this topic, I will address the “Database Lookup functoid”
We can use the Database Lookup functoid to extract information from a database and store it as a Microsoft® ActiveX® Data Objects (ADO) recordset. This functoid requires four input parameters in the following order:
- Parameter 1: A value for which to search in the specified database, table, and column.
- Parameter 2: The full connection string for the database with a provider, machine name, database and authentication (an ActiveX Data Objects .NET (ADO.NET) connection string)
- Parameter 3: The name of the table in the database in which to search.
- Parameter 4: The name of the column in the table in which to search.
The functoid is actually quite simple to use, however, the main problem that developers face when they use it refers to the second parameter: the connection string. And why?
First: What is the correct value for the connection string?
I always find hard to remember the correct value for the connection string to be used inside this functoid.
The Easiest way to make sure we are using the correct connection string value and for not having to remember this by my head, is to create a simple Universal Data Link (.udl) File… set OLE DB provider connection parameters and test the connection to check if everything is correct.
To accomplish this we need to:
- Navigate to a folder in your system, can be on the desktop or preferably in a folder under your BizTalk visual studio solution, let’s call it “Resources”.
- Create a text file and name it “ODBCConnectionTest.udl”
- The name of the file is not important, the important part is the extension, it must be “.udl”
- Double-click the file you just created.
- On the Provider tab, select the appropriate OLE DB provider for the type of data you want to access and then Next
- In my case, it is “Microsoft OLE DB Provider for SQL Server”
- In the Connection tab, specify:
- Where your data is located, typically the server and database name.
- How to connect to it using an OLE DB provider: Use Windows NT integrated security or Use a specific user name and password
- In this particular case, you need to provide the SQL Server, the database name we want to connect.
- Then you can and should click on “Test Connection…” button to attempt a connection to the specified data source. If no connection is made, review the settings. Otherwise, click “Ok”
After data Open the “ODBCConnectionTest.udl” file in notepad and you will find the connection string value that you can copy and use it in the second parameter of the Database Lookup Functoid.
This will lead us to the second problem that you can face using this functoid: using the connection string statically inside the Database Lookup Functoid
Second: What is the best way to set the connection string inside the Database Lookup Functoid?
Hard-coding the SQL connection strings might lead to maintenance overhead and serviceability issues.
Important considerations:
- You shouldn’t Hard-coding this value directly in the functoid otherwise, it will be a nightmare when you deploy this to a different environment.
- You can and you should store this parameter in a different storage location (SSO, Registry or others) and get this value using a scripting Functoid or custom functoid which can then be linked to the Database Lookup Functoid, like the:
- BTSNTSvc Config Get Functoid: This functoid allows you to get configuration parameters from BTSNTsvc.exe.config. If there is no section specified, the functoid reads from the AppSettings.
- Windows Registry Config Get Functoid: This functoid allows you to get configuration parameters from Windows Registry.
- SSO Config Get Functoid: This functoid allows you to get configuration parameters from SSO Database.
- Rule Engine Config Get Functoid: This functoid allows you to obtain a definition value from a Vocabulary in the Business Rules Engine.
In my personal opinion, I advise you to use SSO or BRE to store these configuration parameters. All of these custom functoids are available in the BizTalk Mapper Extensions UtilityPack.
You can found and download here:
BizTalk Mapper: How to use Database Lookup Functoid
GitHub
Thanks! Very usefull