Author: Chaitra Yajman
Introduction
This article helps you know the steps to connect with the MySql Database via ODBC from MuleSoft using the .Net connector.
There are 3 parts to achieve the connection via ODBC using Dotnet connector:
- Creating the Datasource(DSN)
- Creating a .NET Component
- Creating and configuring a .NET Global Element and Connector
Creating the Datasource(DSN)
For local Database testing, we will have to create a DSN by following the below steps. Otherwise, it’s not required if you have the client shared DSN and database details.
- Go to windows and search for ODBC Data Sources
Check for the MySql ODBC driver
If drivers are not present for MySql, it can be downloaded from here – https://www.mysql.com/products/connector/
- Add a user DSN by selecting the required driver.
- Configure the below details. Data Source name(DNS) can be given anything. MySql DB User and password from which you want to fetch the data should be configured.
- Once it is configured it gets listed under user DSN
Creating a .NET Component
- To create the .NET component that will be called from Mule, start Visual Studio and create a new C# Class Library(.Net Framework) project.
Note: It’s very important to create the project of the below(.dll) type to call the methods from the Mule app.
A DLL file is a library that contains a set of code and data for carrying out a particular activity in Windows. Apps can then call on those DLL files when they need that activity performed.
Make sure framework .Net Framework 4.5 and above is selected.
Create a Function to connect to the ODBC server and then query the database using the same server.
- For the below code to work we will have to import some pre-built libraries to connect to the ODBC server.
- For this go to tools>Nuget Package Manager> Package Manager Console. This will open the Package Manager console.
- Run the below commands to install the package in your project.
Install-Package System.Data.Odbc -Version 6.0.0-preview.7.21377.19
Install-Package System.Text.Json -Version 6.0.0-preview.7.21377.19
The code looks as follows:
using System;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Text.Json;
namespace ODBCDSN
{
public class OdbcConn
{
public string Conn(string Query, string dsn)
{
string query = Query;
try
{
//Connect to MySQL using Connector/ODBC
OdbcConnection MyConnection = new OdbcConnection(“DSN=” + dsn);
MyConnection.Open();
Console.WriteLine(“\n !!! success, connected successfully !!!\n”);
//Display connection information
Console.WriteLine(“Connection Information:”);
Console.WriteLine(“\tConnection String:” +
MyConnection.ConnectionString);
Console.WriteLine(“\tConnection Timeout:” +
MyConnection.ConnectionTimeout);
Console.WriteLine(“\tDatabase:” +
MyConnection.Database);
Console.WriteLine(“\tDataSource:” +
MyConnection.DataSource);
Console.WriteLine(“\tDriver:” +
MyConnection.Driver);
Console.WriteLine(“\tServerVersion:” +
MyConnection.ServerVersion);
//Create a sample table
OdbcCommand MyCommand =
new OdbcCommand(query,
MyConnection);
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
for (int i = 0; i < MyOdbcException.Errors.Count; i++)
{
Console.Write(“ERROR #” + i + “\n” +
“Message: ” +
MyOdbcException.Errors[i].Message + “\n” +
“Native: ” +
MyOdbcException.Errors[i].NativeError.ToString() + “\n” +
“Source: ” +
MyOdbcException.Errors[i].Source + “\n” +
“SQL: ” +
MyOdbcException.Errors[i].SQLState + “\n”);
}
}
return query;
}
public string getData(string Query, string dsn)
{
List<object> objects = new List<object>();
OdbcConnection MyConnection = new OdbcConnection(“DSN=” + dsn);
MyConnection.Open();
OdbcCommand MyCommand =
new OdbcCommand(Query,
MyConnection);
OdbcDataReader MyDataReader;
MyDataReader = MyCommand.ExecuteReader();
while (MyDataReader.Read())
{
IDictionary<string, object> record = new Dictionary<string, object>();
for (int i = 0; i < MyDataReader.FieldCount; i++)
{
record.Add(MyDataReader.GetName(i), MyDataReader[i]);
}
objects.Add(record);
}
string json = JsonSerializer.Serialize(objects);
//Close all resources
MyDataReader.Close();
return json;
}
}
}
- Download and launch Anypoint Extensions for Visual Studio which provides binding and synchronization of Visual Studio projects with Mule applications in Anypoint Studio.
Link to download the extension – https://marketplace.visualstudio.com/items?itemName=MuleSoftInc.AnypointExtensionsforVisualStudio
- To bind a Visual Studio class library project to an Anypoint Studio project, follow the below steps.
- Right-click the Visual Studio class library project.
- Choose the Mule project settings option
- The Mule project settings window opens. Enter the path to the Anypoint Studio workspace, and the grid populates with all the existing Mule projects in the workspace.
- Click the checkbox of the Mule project you want to bind to the Visual Studio class library project.
- When the Visual Studio class library is built, Anypoint Extensions copies the build output of the project to a resources folder in the bound Mule Project. The subfolder within the Mule project resources directory is named after the class library project’s Assembly Name.
Note: In case if you don’t want to use Anypoint Extensions for Visual Studio, you can skip Points 7 – 11, which are optional steps. Follow the below steps to build and get the .dll file.
- After you run the command and install the packages to the project, in the menu bar click on Build>Build Solution or right-click on the project and select Build.
- This will build the solution for you and create the .dll file which we will be using in the .Net connector configuration.
Creating and configuring a .NET Global Element and Connector
- To get the.Net connector in palette click on add modules from the exchange in the Mule palette.
- Search for .Net connector and select the Microsoft .Net Connector and then click on Add. Click on finish.
To create the global element go to the global elements tab in your application XML. Click on create and search for Dotnet config.
Choose Microsoft DotNet config.
Select connection type.
Configure Assembly- path, and resource folder.
You will reference this global element when configuring the DotNet connector.
- Drag the .NET connector onto the palette, then place it into the canvas after the HTTP request. Configure the DotNet connector as shown below. The connector configuration field references the DotNet global element created previously.
Note: The “Type” dropdown in the .NET connector properties is the .NET type that will be reflected upon to see which method it should call. The “Method” reference is the method on the type selected in the “Type” dropdown, which the connector will invoke.
Demo flow:
After completing the above steps, your application flow should look like this:
Summary
DOTNet connector supports one operation, Execute, which enables users to select a class and method to execute and pass the corresponding arguments for that method. It executes code in an existing binary assembly (DLL) without modification.