Implementing a .NET Application with Multiple Database Connectivity — PostgreSQL, ADO.NET, and Entity Framework Core
In this comprehensive guide, we’ll walk through the steps to create a .NET application that can connect to three different types of databases: PostgreSQL, ADO.NET, and Entity Framework Core. This will allow users to select their preferred database type and perform CRUD operations based on their selection.
Embark on a journey of continuous learning and exploration with DotNet-FullStack-Dev. Uncover more by visiting our https://dotnet-fullstack-dev.blogspot.com reach out for further information.
Understanding Database Connectivity Options
- PostgreSQL: A popular open-source relational database management system known for its robust features and reliability. We’ll use Npgsql for PostgreSQL connectivity.
- ADO.NET: A data access technology in the .NET Framework used for accessing and manipulating data from different data sources. We’ll use it to connect to any relational database that supports ADO.NET.
- Entity Framework Core: A lightweight, extensible, and cross-platform version of the Entity Framework data access technology. It simplifies data access by providing a set of methods for performing CRUD operations. We’ll use it to interact with supported databases.
Step 1: Set Up the .NET Application
Create a new .NET Core Console Application project using Visual Studio or the .NET CLI.
dotnet new console -n DatabaseApp
cd DatabaseAppInstall necessary packages for PostgreSQL, ADO.NET, and Entity Framework Core connectivity.
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQLStep 2: Create Database Contexts
Define database context classes for PostgreSQL and Entity Framework Core.
// PostgreSQL Context
public class PostgresContext : DbContext
{
public DbSet<Customer> Customers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseNpgsql("your_postgres_connection_string");
}
}
// Entity Framework Core Context
public class EFCoreContext : DbContext
{
public DbSet<Product> Products { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("your_sql_server_connection_string");
}
}Step 3: Implement Data Access
ADO.NET Data Access
Here’s how to implement the GetAll(string tableName) method using ADO.NET:
public class AdoNetRepository
{
private readonly string _connectionString;
public AdoNetRepository(string connectionString)
{
_connectionString = connectionString;
}
public IEnumerable<dynamic> GetAll(string tableName)
{
using var connection = new SqlConnection(_connectionString);
connection.Open();
var command = new SqlCommand($"SELECT * FROM {tableName}", connection);
using var reader = command.ExecuteReader();
while (reader.Read())
{
var rowData = new ExpandoObject() as IDictionary<string, object>;
for (var i = 0; i < reader.FieldCount; i++)
{
rowData[reader.GetName(i)] = reader[i];
}
yield return rowData;
}
}
}Entity Framework Core Data Access
Here’s how to implement the GetAll(string tableName) method using Entity Framework Core:
using System;
using System.Collections.Generic;
using System.Linq;
namespace DatabaseApp
{
public class EFCoreRepository
{
private readonly EFCoreContext _context;
public EFCoreRepository(EFCoreContext context)
{
_context = context;
}
public IEnumerable<dynamic> GetAll(string tableName)
{
var query = _context.Set<dynamic>().FromSqlRaw($"SELECT * FROM {tableName}");
return query.ToList();
}
}
}Data Access for PostgreSQL
Here’s how you can implement the GetAll(string tableName) method for PostgreSQL
using System;
using System.Collections.Generic;
using Npgsql;
namespace DatabaseApp
{
public class PostgresRepository
{
private readonly string _connectionString;
public PostgresRepository(string connectionString)
{
_connectionString = connectionString;
}
public IEnumerable<dynamic> GetAll(string tableName)
{
var results = new List<dynamic>();
using (var connection = new NpgsqlConnection(_connectionString))
{
connection.Open();
var query = $"SELECT * FROM {tableName}";
using (var command = new NpgsqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var rowData = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
rowData.Add(reader.GetName(i), reader[i]);
}
results.Add(rowData);
}
}
}
}
return results;
}
}
}Step 4: Implement User Interface
To implement a user interface for the .NET application that connects to multiple databases, we’ll create a simple console-based UI. This UI will allow users to select the database type and perform CRUD operations accordingly. Below are the steps to implement the user interface:
Create Console UI Class
Create a new class ConsoleUI.cs to handle user interactions via the console.
using System;
namespace DatabaseApp
{
public class ConsoleUI
{
public static void ShowMenu()
{
Console.WriteLine("Select Database Type:");
Console.WriteLine("1. PostgreSQL");
Console.WriteLine("2. ADO.NET (Relational)");
Console.WriteLine("3. Entity Framework Core (Relational)");
Console.WriteLine("4. Exit");
Console.Write("Enter your choice: ");
}
public static void ShowOptions(string databaseType)
{
Console.WriteLine($"You selected {databaseType}.");
Console.WriteLine("Choose an operation:");
Console.WriteLine("1. Read data");
Console.WriteLine("2. Add data");
Console.WriteLine("3. Update data");
Console.WriteLine("4. Delete data");
Console.WriteLine("5. Go back");
Console.Write("Enter your choice: ");
}
public static void ShowMessage(string message)
{
Console.WriteLine(message);
}
}
}Integrate UI with Main Program
Modify the Program.cs file to integrate the console UI with the main program logic.
using System;
namespace DatabaseApp
{
class Program
{
static void Main(string[] args)
{
while (true)
{
ConsoleUI.ShowMenu();
var choice = Console.ReadLine();
switch (choice)
{
case "1":
ProcessPostgreSQL();
break;
case "2":
ProcessAdoNet();
break;
case "3":
ProcessEFCore();
break;
case "4":
Environment.Exit(0);
break;
default:
Console.WriteLine("Invalid choice. Please try again.");
break;
}
}
}
static void ProcessPostgreSQL()
{
// Implement PostgreSQL operations
}
static void ProcessAdoNet()
{
// Implement ADO.NET operations
}
static void ProcessEFCore()
{
// Implement EF Core operations
}
}
}Implement Database Operations
Inside the ProcessPostgreSQL(), ProcessAdoNet(), and ProcessEFCore() methods, implement the respective database operations based on the user's selection.
Step 5: Test the Application
Run the application and verify that users can select their preferred database type and perform CRUD operations.
Conclusion
By following these steps and incorporating the provided code snippets, you can create a .NET application that connects to three different types of databases: PostgreSQL, ADO.NET, and Entity Framework Core. The console-based user interface with the main program logic and implementing database operations accordingly, users can interact with the .NET application to connect to multiple databases and perform CRUD operations.
This simple console UI provides a straightforward way for users to interact with the application and select their preferred database type. Adjust the UI and operations as needed to meet the requirements of your application.
