Connect to Any SQL Database in C# With a Universal ADO.NET Codebase
Managing multiple database engines across projects can become too complicated. In many cases, it leads to fragmented code, duplicated logic, and more maintenance overhead. The solution can be dotConnect Universal, a robust data provider that offers a single ADO.NET interface for working with all popular relational databases, including Oracle, PostgreSQL, MySQL, and more.
This tutorial will focus on building .NET applications that need to deal with numerous databases with the help of dotConnect Universal. Whether you're supporting multi-tenant environments, preparing for database migrations, or monitoring the health of various systems, this guide will explain how to simplify the data access layer without sacrificing flexibility or control.
Why dotConnect Universal?
dotConnect Universal is an enterprise-grade connector that unifies data access code and thus eliminates the need for rewriting logic for each specific backend database. Therefore, you can rely on the broadest possible compatibility with database server versions and .NET platforms.
With full ADO.NET compliance and support for OLE DB and ODBC providers, dotConnect Universal ensures direct access to a wide range of databases using a consistent API and with no need for additional client libraries or complex configurations.
Key advantages:
- Universal access: Oracle, PostgreSQL, MySQL, SQL Server, SQLite, DB2, and more from one codebase
- ADO.NET standard compliance: Utilize familiar classes like DbConnection, DbCommand, and DbDataReader
- UniSQL query language: Write SQL queries that run across different database engines
- Design-time tools and wizards: Fully integrated with Visual Studio, these tools enhance its capacities and boost your development productivity
- Support for native types and provider-specific features: Work efficiently with all specific features without sacrificing compatibility
- Abstraction and maintainability: Create and handle multi-database projects easily
This way, dotConnect Universal simplifies cross-database development and keeps performance and code clarity at the forefront.
Download and activate dotConnect Universal
Depending on your conditions, you may choose a fully-functional free trial or a full version.
30-day free trial version
You can start using dotConnect Universal immediately with a 30-day free trial. Choose one of the installation options.
- Installer: Download and install dotConnect Universal directly on your machine.
- NuGet: Add the Devart.Data.Universal package to your project.
No license key is necessary for the trial version — just install it and explore.
Full version
After purchasing the full version, follow these steps:
1. Go to the Products section in your Devart profile.
2. Click the product name, or hover over it and click Details. The License details page opens; find the Activation Key there.
3. To activate dotConnect Universal in your application, copy the Activation Key and paste it into your connection string as the License Key parameter.
Create a .NET project
To illustrate this tutorial, we create a simple console application that will connect to our databases from Visual Studio. Also, we need to install the NuGet packages for dotConnect Universal. Do the following:
1. Launch Visual Studio and click Create a new project.
2. Choose Console App (.NET Core) and click Next.
3. Give this project a name (in our tutorial, we name it UniConnection), specify the path to its folder, and click Create.
4. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.
5. Go to the Browse tab and search for the following packages:
When found, install these packages. Alternatively, you can run the below commands in the terminal:
dotnet add package Devart.Data.Universal.MySql dotnet add package Devart.Data.Universal.PostgreSql dotnet add package Devart.Data.Universal.Oracle
Connect to the data source with Data Explorer
To connect to your data source using the built-in Data Explorer, click Tools and select Connect to Database. Choose Universal Servers as the data source.
Choose a database to connect to. For instance, it may be MySQL.
Enter your server details and credentials, and click Connect.
After establishing a connection this way, you can work with the database: browse tables, execute queries, and manage data directly within the Data Explorer.
With dotConnect Universal, you can connect to any supported data source from Visual Studio directly and use a unified provider interface.
Multi-tenant data access in .NET
A multi-tenant application often suggests handling different database engines for various clients. Our demo .NET console app will use the same data access logic to connect to Oracle, PostgreSQL, and MySQL, retrieving data from the Actor table that is present in all three databases. For that, we will not need to change the core codebase, as dotConnect Universal grants the direct connection and enables data operations for us.
Create DatabaseConnection.cs (connection strings)
Introduce DatabaseConnection.cs as a dedicated file for the DatabaseConnections class, which gathers all connection settings in one place and isolates them from the primary application logic.
namespace UniConnectionApp
{
public static class DatabaseConnections
{
public const string MySqlConnectionString = "Provider=MySQL;" +
"Server=127.0.0.1;" +
"Port=3306;" +
"User ID=TestUser;" +
"Password=TestPassword;" +
"Database=sakila;" +
"License key={0}";
public const string PostgreSqlConnectionString = "Provider=PostgreSQL;" +
"Host=127.0.0.1;" +
"Port=5432;" +
"User Id=TestUser;" +
"Password=TestPassword;" +
"Database=TestDatabase;" +
"Initial Schema=Public;" +
"License key={0}";
public const string OracleConnectionString = "Provider=Oracle;" +
"Direct=True;" +
"Host=127.0.0.1;" +
"Service Name=TestDb;" +
"User ID=TestUser;" +
"Password=TestPassword;" +
"License key={0}";
public const string LicenseKey = "**********";
}
}
Basic connection string properties
| Name | Description |
| Provider | MySQL, PostgreSQL, Oracle, etc. |
| Server or Host | The name or IP address of the server to connect to. |
| Port | The port of the server to connect to. |
| User Id | The login account. |
| Password | The password for the login account. |
| Database | The name of the database to connect to. |
| Initial Schema | The name of the initial schema. |
| License Key | The activation key for dotConnect Universal. |
Update the connection string
Replace the placeholders in the connection string with your actual database credentials.
Add the code below to your Program.cs file.
using Devart.Data.Universal;
namespace UniConnectionApp
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("--- Querying MySQL ---");
QueryDatabase(DatabaseConnections.MySqlConnectionString, "MySQL");
Console.WriteLine("\n--- Querying PostgreSQL ---");
QueryDatabase(DatabaseConnections.PostgreSqlConnectionString, "PostgreSQL");
Console.WriteLine("\n--- Querying Oracle ---");
QueryDatabase(DatabaseConnections.OracleConnectionString, "Oracle");
Console.ReadKey();
}
static void QueryDatabase(string connectionString, string providerName)
{
try
{
// Use the aliased 'UniConnection' type
string formattedConnectionString = string.Format(connectionString, DatabaseConnections.LicenseKey);
using (var connection = new UniConnection(formattedConnectionString))
{
connection.Open();
string sql;
string baseQuery = "SELECT actor_id, first_name, last_name FROM actor";
// Select the correct SQL syntax for limiting rows based on the provider
switch (providerName.ToUpper())
{
case "MYSQL":
case "POSTGRESQL":
sql = $"{baseQuery} ORDER BY actor_id LIMIT 5";
break;
case "ORACLE":
// Using ROWNUM for broader compatibility with Oracle versions
sql = $"SELECT * FROM ({baseQuery} ORDER BY actor_id) WHERE ROWNUM <= 5";
break;
default:
Console.WriteLine($"Unknown provider: {providerName}. Using standard LIMIT clause.");
sql = $"{baseQuery} ORDER BY actor_id LIMIT 5";
break;
}
// Use the aliased types for Command and DataReader
using (var command = new UniCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
Console.WriteLine($"{"Actor ID",-10} | {"First Name",-12} | {"Last Name"}");
Console.WriteLine("------------------------------------\n");
while (reader.Read())
{
// Using Convert.ToString to handle potential DBNull values gracefully
string actorId = Convert.ToString(reader["actor_id"]);
string firstName = Convert.ToString(reader["first_name"]);
string lastName = Convert.ToString(reader["last_name"]);
Console.WriteLine($"{actorId,-10} | {firstName,-12} | {lastName}");
}
}
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"\nError connecting to or querying {providerName}:");
Console.WriteLine(ex.Message);
Console.WriteLine("Please check the connection string in DatabaseConnections.cs and ensure the database is accessible.");
Console.ResetColor();
}
}
}
}
Run the application
Build and run your application by pressing F5 or selecting Start from the menu.
Migrate from MySQL to PostgreSQL with no ADO.NET logic changes
Database migrations often involve code rewrites, which take plenty of time and effort and may cause complications. However, dotConnect Universal allows us to avoid code rewriting.
In this section, we'll demonstrate how to read from a MySQL database and write to a PostgreSQL database using a unified API. It is a clean and practical approach to cross-database data transfer without changing the ADO.NET logic.
The code is as follows:
using System;
using System.Collections.Generic;
// Using aliases to manage types clearly
using UniConnection = Devart.Data.Universal.UniConnection;
using UniCommand = Devart.Data.Universal.UniCommand;
using UniDataReader = Devart.Data.Universal.UniDataReader;
using UniTransaction = Devart.Data.Universal.UniTransaction;
using UniParameter = Devart.Data.Universal.UniParameter;
namespace UniConnectionApp
{
/// <summary>
/// A simple class to hold actor data during migration.
/// </summary>
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime LastUpdate { get; set; }
}
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting database migration...");
try
{
// 1. Read actors from the source MySQL database
List<Actor> actorsToMigrate = ReadActorsFromMySql();
if (actorsToMigrate.Count > 0)
{
// 2. Write the collected actors to the destination PostgreSQL database
WriteActorsToPostgreSql(actorsToMigrate);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine($"\nSuccessfully migrated {actorsToMigrate.Count} actors from MySQL to PostgreSQL.");
Console.ResetColor();
}
else
{
Console.WriteLine("No actors found in MySQL database. Nothing to migrate.");
}
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("\nAn error occurred during the migration process:");
Console.WriteLine(ex.Message);
Console.ResetColor();
}
Console.WriteLine("\nMigration process finished. Press any key to exit.");
Console.ReadKey();
}
/// <summary>
/// Connects to MySQL and reads the first 10 actors.
/// </summary>
/// <returns>A list of Actor objects.</returns>
private static List<Actor> ReadActorsFromMySql()
{
Console.WriteLine("\nStep 1: Reading actors from MySQL...");
var actors = new List<Actor>();
string connectionString = string.Format(DatabaseConnections.MySqlConnectionString, DatabaseConnections.LicenseKey);
using (var connection = new UniConnection(connectionString))
{
connection.Open();
string sql = "SELECT actor_id, first_name, last_name, last_update FROM actor ORDER BY actor_id LIMIT 10";
using (var command = new UniCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var actor = new Actor
{
ActorId = Convert.ToInt32(reader["actor_id"]),
FirstName = Convert.ToString(reader["first_name"]),
LastName = Convert.ToString(reader["last_name"]),
LastUpdate = Convert.ToDateTime(reader["last_update"])
};
actors.Add(actor);
Console.WriteLine($" - Read actor: {actor.FirstName} {actor.LastName}");
}
}
}
return actors;
}
/// <summary>
/// Connects to PostgreSQL and writes the list of actors.
/// </summary>
/// <param name="actors">The list of actors to insert.</param>
private static void WriteActorsToPostgreSql(List<Actor> actors)
{
Console.WriteLine("\nStep 2: Writing actors to PostgreSQL...");
string connectionString = string.Format(DatabaseConnections.PostgreSqlConnectionString, DatabaseConnections.LicenseKey);
using (var connection = new UniConnection(connectionString))
{
connection.Open();
// Use a transaction to ensure all writes succeed or none do.
using (UniTransaction transaction = connection.BeginTransaction())
{
// Clear the actor table, reset its ID sequence, and cascade to dependent tables (like film_actor).
// This ensures a clean slate for the new data.
Console.WriteLine(" - Truncating PostgreSQL 'actor' table with CASCADE...");
using (var truncateCommand = new UniCommand("TRUNCATE TABLE actor RESTART IDENTITY CASCADE;", connection, transaction))
{
truncateCommand.ExecuteNonQuery();
}
// Prepare the INSERT statement once. Note we don't insert actor_id, as the sequence will generate it.
string sql = "INSERT INTO actor (first_name, last_name, last_update) VALUES (:first_name, :last_name, :last_update)";
using (var insertCommand = new UniCommand(sql, connection, transaction))
{
// Add parameters to the command
insertCommand.Parameters.Add(new UniParameter("first_name", ""));
insertCommand.Parameters.Add(new UniParameter("last_name", ""));
insertCommand.Parameters.Add(new UniParameter("last_update", DateTime.Now));
foreach (var actor in actors)
{
// Set parameter values for each actor
insertCommand.Parameters["first_name"].Value = actor.FirstName;
insertCommand.Parameters["last_name"].Value = actor.LastName;
insertCommand.Parameters["last_update"].Value = actor.LastUpdate;
insertCommand.ExecuteNonQuery();
Console.WriteLine($" - Migrated actor: {actor.FirstName} {actor.LastName}");
}
}
// If all commands succeed, commit the transaction
transaction.Commit();
Console.WriteLine(" - Transaction committed.");
}
}
}
}
}
We can see the results in our application:
Multi-database health checking in .NET
What if you need to monitor multiple database systems in production? For that, we can build a lightweight health-check console application. That app will connect to different databases, confirm connection status, and count records in the Actor table. This approach allows us to verify availability across environments.
Use the following code:
using System;
// Using aliases for Devart types for clarity
using UniConnection = Devart.Data.Universal.UniConnection;
using UniCommand = Devart.Data.Universal.UniCommand;
namespace UniConnectionApp
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("Starting Database Health Check...");
Console.WriteLine("=================================");
// Check the status of each configured database
CheckDatabaseStatus(DatabaseConnections.MySqlConnectionString, "MySQL");
CheckDatabaseStatus(DatabaseConnections.PostgreSqlConnectionString, "PostgreSQL");
CheckDatabaseStatus(DatabaseConnections.OracleConnectionString, "Oracle");
Console.WriteLine("=================================");
Console.WriteLine("Health check complete. Press any key to exit.");
Console.ReadKey();
}
/// <summary>
/// Connects to a database, checks its availability, and retrieves the record count from the 'actor' table.
/// </summary>
/// <param name="connectionStringFormat">The connection string with a placeholder for the license key.</param>
/// <param name="providerName">The name of the database provider (e.g., "MySQL").</param>
private static void CheckDatabaseStatus(string connectionStringFormat, string providerName)
{
Console.WriteLine($"\n--- Checking {providerName} ---");
UniConnection connection = null;
try
{
// Format the connection string with the license key
string connectionString = string.Format(connectionStringFormat, DatabaseConnections.LicenseKey);
connection = new UniConnection(connectionString);
// 1. Check Connection Availability
Console.Write(" Status: ");
connection.Open();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Connection Successful");
Console.ResetColor();
// 2. Check Record Count in 'actor' table
Console.Write(" 'actor' table records: ");
using (var command = new UniCommand("SELECT COUNT(*) FROM actor", connection))
{
// ExecuteScalar is efficient for retrieving a single value
object result = command.ExecuteScalar();
long count = Convert.ToInt64(result);
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine($"{count} rows");
Console.ResetColor();
}
}
catch (Exception ex)
{
// Report connection or query failure
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Connection FAILED");
Console.WriteLine($" Error: {ex.Message}");
Console.ResetColor();
}
finally
{
// Ensure the connection is always closed
if (connection?.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
}
}
Have a look at the output in the application:
Conclusion
dotConnect Universal is a simple and robust solution for multi-database development in .NET. This data provider offers a consistent interface you can use to connect to Oracle, PostgreSQL, MySQL, SQL Server, and other popular relational databases.
Whether you build multi-tenant applications, migrate between database systems, or monitor environments, you can create the data access code once and then reuse it wherever necessary. Add the full Visual Studio integration and support for ADO.NET standards, and you get a comprehensive tool that gives you flexibility without added complexity.