Getting Started with dotConnect Universal Standard — Quick GuidedotConnect Universal Standard is a versatile ADO.NET data provider that simplifies working with multiple databases through a unified API. This quick guide will walk you through what dotConnect Universal Standard is, why you might use it, how to install and configure it, and basic examples to get you up and running quickly.
What is dotConnect Universal Standard?
dotConnect Universal Standard is a single ADO.NET provider designed to work with many different database engines using a unified interface. It exposes common ADO.NET classes (like Connection, Command, DataAdapter, and DataReader) and adds convenience features that reduce the need to write database-specific code. The provider supports popular databases such as Microsoft SQL Server, MySQL, PostgreSQL, Oracle, SQLite, and several others via a unified connection string and provider model.
Why choose dotConnect Universal Standard?
- Single codebase for multiple databases: Write data access code once and run it against different backends by changing the connection string and provider name.
- ADO.NET compatibility: Works with existing ADO.NET patterns and tools (DataSets, Entity Framework support where applicable, etc.).
- Reduced maintenance: Easier to support applications that must target multiple database systems.
- Productivity features: Includes utilities for schema discovery, type mapping, and simplified SQL generation.
Prerequisites
- .NET runtime compatible with the dotConnect Universal Standard version you plan to use (check the provider’s documentation for specific supported versions).
- A development environment such as Visual Studio, Rider, or VS Code.
- Access credentials to a target database (connection string components: server/host, database, user, password, port, and any provider-specific options).
Installation
-
Using NuGet (recommended):
- Open your project in Visual Studio or use the dotnet CLI.
- Install the package. From the CLI:
dotnet add package Devart.Data.Universal.Standard - Or use the NuGet Package Manager GUI and search for “dotConnect Universal Standard” or “Devart.Data.Universal.Standard”.
-
Manual reference:
- Download the provider from the vendor if you require a specific distribution.
- Add a reference to the provider DLLs in your project.
Basic configuration
dotConnect Universal Standard uses a provider-agnostic connection string and a provider name to identify the underlying database. The provider typically exposes a factory you can use to create connections in a provider-independent way.
Example connection string patterns (these vary by target database — replace placeholders):
- SQL Server:
Server=SERVER_NAME;Database=DB_NAME;User Id=USERNAME;Password=PASSWORD; - MySQL:
Host=HOST;Database=DB;User Id=USER;Password=PASSWORD;Port=3306; - PostgreSQL:
Host=HOST;Database=DB;Username=USER;Password=PASSWORD;Port=5432;
You’ll also specify the provider type when creating factory objects or provider-specific connections. Consult the provider’s docs for exact provider invariant names (for example, Devart.Data.SqlServer or similar aliases).
Example: Basic CRUD with ADO.NET pattern
Below is a conceptual example demonstrating establishing a connection, executing a simple SELECT, and performing an INSERT using the universal API. Replace types and namespaces with the exact ones from the package you installed.
using System; using System.Data; using Devart.Data.Universal; // Example namespace — verify with package class Program { static void Main() { string providerName = "Devart.Data.MySql"; // change to your provider string connectionString = "Host=localhost;Database=testdb;User Id=root;Password=pass;"; var factory = DbProviderFactories.GetFactory(providerName); using (var connection = factory.CreateConnection()) { connection.ConnectionString = connectionString; connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "SELECT Id, Name FROM Users"; using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Console.WriteLine($"{reader.GetInt32(0)} - {reader.GetString(1)}"); } } } using (var insertCmd = connection.CreateCommand()) { insertCmd.CommandText = "INSERT INTO Users(Name) VALUES(@name)"; var p = insertCmd.CreateParameter(); p.ParameterName = "@name"; p.Value = "New User"; insertCmd.Parameters.Add(p); int affected = insertCmd.ExecuteNonQuery(); Console.WriteLine($"Rows inserted: {affected}"); } } } } Connection pooling and performance tips
- Enable and configure connection pooling via the connection string if the provider supports it (usually enabled by default).
- Use parameterized queries to prevent SQL injection and enable query plan reuse.
- Prefer streaming large result sets via DataReader instead of loading into memory.
- Use prepared statements or command caching if the provider exposes these features.
Schema discovery and metadata
dotConnect Universal Standard provides utilities to retrieve schema and metadata in a consistent way across databases (tables, columns, data types). Use methods like GetSchema on the connection object:
DataTable tables = connection.GetSchema("Tables"); This helps when writing database-agnostic tools or migration utilities.
Error handling and diagnostics
- Catch specific data provider exceptions when possible (check provider exception types) and fall back to DbException for general handling.
- Enable logging in your application or the provider (if available) to capture executed SQL, timings, and connection issues.
- Validate connection strings and credentials separately from runtime queries during setup to catch configuration errors early.
Migrating an existing app
- Abstract data access through repositories or data access layers.
- Replace database-specific connection/command classes with factory-based creation.
- Centralize connection string management (configuration file, secrets manager).
- Test SQL compatibility — some SQL dialect differences may require conditional SQL or helper methods.
- Use integration tests against each target database.
Troubleshooting common issues
- Connection failures: verify host, port, credentials, and firewall rules.
- Provider not found: ensure NuGet package is installed and the project references the correct assembly; check provider invariant name.
- SQL dialect errors: adjust SQL to avoid engine-specific functions or provide conditional branches.
- Performance problems: analyze query plans on the target DB and optimize indexes; ensure pooling is enabled.
Additional resources
- Official dotConnect Universal Standard documentation and API reference (check the vendor site for the latest).
- ADO.NET DbProviderFactories documentation for using provider-agnostic factories.
- Samples and community forums for provider-specific tips.
To proceed: install the NuGet package for your target framework, pick the provider invariant name for your database, and try the example code against a local test database.
Leave a Reply