C# JDBC-ODBC Bridge Connector
In this article you will learn how to integrate Using JDBC-ODBC Bridge Connector you will be able to connect, read, and write data from within C#. Follow the steps below to see how we would accomplish that. Driver mentioned in this article is part of ODBC PowerPack which is a collection of high-performance Drivers for various API data source (i.e. REST API, JSON, XML, CSV, Amazon S3 and many more). Using familiar SQL query language you can make live connections and read/write data from API sources or JSON / XML / CSV Files inside SQL Server (T-SQL) or your favorite Reporting (i.e. Power BI, Tableau, Qlik, SSRS, MicroStrategy, Excel, MS Access), ETL Tools (i.e. Informatica, Talend, Pentaho, SSIS). You can also call our drivers from programming languages such as JAVA, C#, Python, PowerShell etc. If you are new to ODBC and ZappySys ODBC PowerPack then check the following links to get started. |
See also
|
Create ODBC Data Source (DSN) based on ZappySys JDBC Driver
To get data from JdbC-OdbcBridge using C# we first need to create a DSN (Data Source) which will access data from JdbC-OdbcBridge. We will later be able to read data using C#. Perform these steps:
-
Install ZappySys ODBC PowerPack.
-
Open ODBC Data Sources (x64):
-
Create a User Data Source (User DSN) based on ZappySys JDBC Bridge Driver
ZappySys JDBC Bridge DriverYou should create a System DSN (instead of a User DSN) if the client application is launched under a Windows System Account, e.g. as a Windows Service. If the client application is 32-bit (x86) running with a System DSN, use ODBC Data Sources (32-bit) instead of the 64-bit version. -
Now, we need JDBC Bridge Connection. Lets create it. When you see DSN Config Editor with zappysys logo first thing you need to do is change default DSN Name at the top and Configure JDBC Bridge Driver. Enter Credentials (In this Example We have used PostgreSQL Credentials) and then Click on Test Connection to verify your credentials.
Note: Enter suitable JDBC Driver Credentials, You can read data from any JDBC Driver -
This example shows how to write simple SOQL query (JDBC Bridge Object Query Language). It uses WHERE clause. For more SOQL Queries click here.
SOQL is similar to database SQL query language but much simpler and many features you use in database query may not be supported in SOQL (Such as JOIN clause not supported). But you can use following Queries for Insert, Update, Delete and Upsert(Update or Insert record if not found).SELECT orderid, customerid, orderdate, orderamount FROM "public"."zappysys" -
Click OK to finish creating the data source
Read data in C# from the DSN
-
Create a new Console project and use this code to read the data:
"JdbC-OdbcBridgeDSN"; -
Press F5 to run the code and read the data:
-
Here is the code in text format:
using System; using System.Data.Odbc; namespace ConsoleApp { class Program { static void Main() { var dsn = "JdbC-OdbcBridgeDSN"; using (var conn = new OdbcConnection(String.Format("DSN={0}", dsn))) { conn.Open(); var cmd = new OdbcCommand("SELECT * FROM Products", conn); //Increase the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios cmd.CommandTimeout=600; // 600-Seconds var rdr = cmd.ExecuteReader(); while (rdr.Read()) { for (int i = 0; i < rdr.FieldCount; i++) { Console.Write("{0}\t", rdr[i]); } Console.WriteLine(); } } Console.ReadKey(); } } }
-
If you want to avoid being dependent on a DSN and creating multiple DSNs for each platform (x86, x64), then you can use a fully qualified connection string. Simply go to your DSN and copy the Connection String . Then in your C# code, pass the connection string as an argument when calling the OdbcConnection object's constructor, for example:
var connectionString = "DRIVER={ZappySys JDBC Bridge Driver};ServiceUrl=https://yourservices.provider.com/api/xxxx....;AuthName=Http;"; using (var conn = new OdbcConnection(connectionString)) { // ... }
Please follow the instructions below to retrieve the connection string for the ZappySys driver.
Click on the Windows Start menu.
In the search bar, type ODBC and press Enter.
From the search results, choose ODBC Data Sources or ODBC Data Sources (32-bit) or a similar option depending on your system architecture and ODBC driver configuration.
- Choose your data source from the list, then click on the Configure button.
- After opening the Data Source UI, you should copy the connection string to a Notepad or text file for reference.
-
Click on Copy Connection String button.
When you click 'Copy Connection String,' you may encounter the following option:
Choose the third option All Settings to copy everything and click on OK button.
That's it connection string has been successfully copied.
How to Overcome the Long Connection String Issue
This issue is typically caused when the connection string includes a long RefreshToken attribute (i.e. RefreshToken=xxxxxxxxxxxx...xxx, especially if it's more than 1,000 characters).
A good solution is to supply the RefreshToken by a file. Simply copy and paste the RefreshToken into a text file and save it, and then pass that file path in the connection string instead of directly supplying the RefreshToken.
Here is an example of a connection string that can cause the issue:
var connectionString = "Driver={ZappySys JDBC Bridge Driver};RefreshToken=xxxxxxxxxxxx...xxxxxxx;ServiceUrl=...;"; using (var conn = new OdbcConnection(connectionString)) { // ... }
Here is an example of a connection string that has resolved the issue by moving the RefreshToken attribute to the file c:\my_refresh_token.txt:
var connectionString = "Driver={ZappySys JDBC Bridge Driver};RefreshTokenFile=c:\my_refresh_token.txt;ServiceUrl=...;"; using (connectionString) { // ... }
Insider Insights
While using ExecuteNonQuery make sure to use output=0.
Sometime Executing `cmd.ExecuteNonQuery` in C# didn't trigger the API call immediately specifically for `INSERT`, `UPDATE`, or `DELETE` statement, as it only initiates the call when the iterator is requested. When using ExecuteNonQuery(), it's primarily intended for SQL statements that don't return data, such as UPDATE, INSERT, or DELETE. If you're not seeing any changes made by the UPDATE statement, there could be several reasons for this.
One potential reason, as you've mentioned, is related to deferred execution. In some cases, the changes made by the UPDATE statement might not be visible immediately, especially if you're not requesting any output from the query. In such cases, explicitly setting Output = 0 can force the iterator to be called, ensuring that the changes are applied.
Here's an example of how you might modify your code to include this:
using System;
using System.Data.Odbc;
namespace ConsoleApp {
class Program {
static void Main() {
var dsn = "JdbC-OdbcBridgeDSN";
using (var conn = new OdbcConnection(String.Format("DSN={0}", dsn)))
{
conn.Open();
//We're currently referring to the example with the Products table. Please substitute it with the table of your choice.
var cmd =new OdbcCommand("UPDATE Products SET ProductName='Chai' Where ProductID=1 WITH(Output=0)", conn);
//Increase the timeout duration from the default 30 seconds, which may be insufficient in certain scenarios
cmd.CommandTimeout=600; // 600-Seconds
// Execute the query
int rowsAffected = cmd.ExecuteNonQuery();
// Check the number of rows affected
if (rowsAffected > 0)
{
Console.WriteLine("Update successful.");
}
else
{
Console.WriteLine("No rows were updated.");
}
}
Console.ReadKey();
}
}
}
Conclusion
In this article we discussed how to connect to JDBC-ODBC Bridge in C# and integrate data without any coding. Click here to Download JDBC-ODBC Bridge Connector for C# and try yourself see how easy it is. If you still have any question(s) then ask here or simply click on live chat icon below and ask our expert (see bottom-right corner of this page).
Download JDBC-ODBC Bridge Connector for C#
Documentation
Other App Integration scenarios for JDBC-ODBC Bridge
Other Connectors for C#
Download JDBC-ODBC Bridge Connector for C#
Documentation
How to connect JDBC-ODBC Bridge in C#?
How to get JDBC-ODBC Bridge data in C#?
How to read JDBC-ODBC Bridge data in C#?
How to load JDBC-ODBC Bridge data in C#?
How to import JDBC-ODBC Bridge data in C#?
How to pull JDBC-ODBC Bridge data in C#?
How to push data to JDBC-ODBC Bridge in C#?
How to write data to JDBC-ODBC Bridge in C#?
How to POST data to JDBC-ODBC Bridge in C#?
Call JDBC-ODBC Bridge API in C#
Consume JDBC-ODBC Bridge API in C#
JDBC-ODBC Bridge C# Automate
JDBC-ODBC Bridge C# Integration
Integration JDBC-ODBC Bridge in C#
Consume real-time JDBC-ODBC Bridge data in C#
Consume realtime JDBC-ODBC Bridge API data in C#
JDBC-ODBC Bridge ODBC Driver | ODBC Driver for JDBC-ODBC Bridge | ODBC JDBC-ODBC Bridge Driver | SSIS JDBC-ODBC Bridge Source | SSIS JDBC-ODBC Bridge Destination
Connect JDBC-ODBC Bridge in C#
Load JDBC-ODBC Bridge in C#
Load JDBC-ODBC Bridge data in C#
Read JDBC-ODBC Bridge data in C#
JDBC-ODBC Bridge API Call in C#