A Comprehensive Guide to ADO.NET, Mapping Concepts Usages: Mastering

ADO.net

ADO.NET (ActiveX Data Objects for .NET) is a set of components in the .NET Framework that provides access to relational databases and other data sources. It is a part of the base class library that is included with the Microsoft .NET Framework. ADO.NET serves as the primary data access model for .NET applications, allowing for the creation, reading, updating, and deletion of data within databases. It is designed to be flexible and efficient in managing data from various sources, including SQL Server, Oracle, XML, and more.

Here's a simplified example to illustrate how ADO.NET might be used in the model part of an MVC application:

public class ProductModel
{
    public List<Product> GetAllProducts()
    {
        List<Product> productList = new List<Product>();

        string connectionString = "..."; // Your database connection string
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string sqlQuery = "SELECT * FROM Products";
            SqlCommand command = new SqlCommand(sqlQuery, connection);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Product product = new Product()
                    {
                        ProductId = reader.GetInt32(0),
                        ProductName = reader.GetString(1),
                        Price = reader.GetDecimal(2)
                    };
                    productList.Add(product);
                }
            }
        }

        return productList;
    }
}

Features of ADO.NET:

• Disconnected Model: Unlike its predecessor ADO (ActiveX Data Objects), ADO.NET is designed primarily for a disconnected data architecture. This means that data can be accessed and manipulated without a constant connection to the database, reducing the demand on database resources and improving application performance.
• Data Binding: ADO.NET supports data binding, making it easy to connect UI components to data sources for displaying and editing data.
• XML Integration: ADO.NET datasets can be easily converted to and from XML, facilitating the exchange of data across different platforms and programming environments.
• Scalability and Performance: The disconnected architecture and efficient data access patterns make ADO.NET suitable for both small and large-scale applications.

Core Components of ADO.NET:

• Connection Object: Establishes a connection to a specific data source. Examples include SqlConnection for SQL Server, OleDbConnection for databases accessible through OLE DB, and OracleConnection for Oracle databases.
• Command Object: Executes commands against a data source, allowing for the execution of SQL queries, stored procedures, or commands to insert, update, and delete data. Examples include SqlCommand, OleDbCommand, and OracleCommand.
• DataReader Object: Provides a forward-only, read-only cursor for reading data retrieved by a Command object. It is efficient for accessing data in a firehose manner - quickly and without the overhead of creating a DataSet. Examples include SqlDataReader, OleDbDataReader, and OracleDataReader.
• DataAdapter Object: Acts as a bridge between a DataSet and a data source for retrieving and saving data. The DataAdapter uses Command objects to execute SQL commands at the data source to both load data into the DataSet and update the data source with changes made to the data in the DataSet. Examples include SqlDataAdapter, OleDbDataAdapter, and OracleDataAdapter.
• DataSet Object: A disconnected, in-memory representation of data that can contain one or more DataTables. DataSets are not tied to any specific database or data source. They are designed to be independent of any particular database, allowing for data manipulation and navigation that works across multiple data sources.

In the context of ASP.NET Web API, "mapping" typically refers to several concepts, depending on the specific functionality being discussed. Here are the common uses of mapping in ASP.NET Web API:

• Route Mapping: This involves defining routes that map HTTP requests to specific controller actions. You can configure routing in the Web API via attribute routing on controllers and actions or by defining routes in the WebApiConfig class.

• Data Mapping: In ASP.NET Web API, data mapping often involves translating data between different layers of the application, for example, from domain or entity models (data structures used within the application or ORM layers) to data transfer objects (DTOs) that are sent in HTTP responses. This is important for data encapsulation, hiding certain data that should not be exposed to the API consumers, and customizing the data format or structure as per the client requirements.

• Model Mapping: When you're working with complex data structures that involve nested objects, you might use model mapping tools like AutoMapper. These tools help to automatically map properties from one object type to another, reducing the amount of manual coding required to assign values from one object’s properties to another's.

• Dependency Mapping: In dependency injection frameworks often used together with ASP.NET Web API, such as Microsoft's built-in dependency injection container, mapping refers to configuring how dependencies (like services or repositories) are injected into controllers or other services.

• Object Relation Mapping: ORM is more about data access than Web API specifically, but it's often part of the discussion because Web APIs frequently access databases. ORMs like Entity Framework and dapper are used to map database tables to C# classes. This simplifies data manipulation and allows developers to work with data in a more object-oriented way.

Entity Framework- EF is an Object-Relational Mapping (ORM) framework that simplifies database interactions in ASP.NET applications. It allows developers to work with databases using .NET objects and LINQ queries, abstracting away much of the database-related code.

To use Entity Framework Core with a PostgreSQL database, you'll primarily need to install and configure the appropriate NuGet package for PostgreSQL, namely

Install Required Packages

dotnet new console -n EFCorePostgresExample
cd EFCorePostgresExample
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

Define a Model

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

Create a Database Context
Define a DbContext that includes a DbSet for each model class. Configure it to use PostgreSQL:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace the placeholders with your actual PostgreSQL connection string details
        optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_password");
    }
}

Perform CRUD Operations

using System;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        using (var context = new AppDbContext())
        {
            // Adding a new user
            var user = new User { Name = "Jane Doe", Email = "jane.doe@example.com" };
            context.Users.Add(user);
            context.SaveChanges();

            // Querying for users
            var users = context.Users.ToList();

            foreach (var usr in users)
            {
                Console.WriteLine($"ID: {usr.UserId}, Name: {usr.Name}, Email: {usr.Email}");
            }
        }
    }
}

Managing Database Migrations

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<AppDbContext>(options =>
            options.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_password"));
    }
}

Then, generate and apply migrations

dotnet ef migrations add InitialCreate
dotnet ef database update

This setup guides you through using Entity Framework Core with PostgreSQL in a .NET application. It involves configuring the project with the appropriate EF Core and database provider packages, defining data models, setting up a context to manage instances of your models, and performing basic CRUD operations.

Dapper- Dapper is a simple object mapper for .NET that extends the IDbConnection interface. It is a micro-ORM (Object-Relational Mapper) that is lightweight and fast, designed to abstract and simplify the way you interact with a database by mapping .NET objects to database records.

First, ensure you have Dapper installed in your project. If you're using a .NET project, you can install it via NuGet Package Manager or the Package Manager Console:

Install-Package Dapper

Assume we have a simple User class defined as follows:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

And a SQL table Users defined like this:

CREATE TABLE Users
(
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
)

Here's how you could use Dapper to query this table and map the results to a list of User objects:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main(string[] args)
    {
        // Connection string to your database.
        string connectionString = "Data Source=your_database_server;Initial Catalog=your_database_name;Integrated Security=True";

        // SQL query to fetch all users.
        string sql = "SELECT * FROM Users";

        using (var connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();

            // Execute the query and map the results to a list of User objects
            var users = connection.Query<User>(sql);

            // Iterate over the user list and print details
            foreach (var user in users)
            {
                Console.WriteLine($"ID: {user.Id}, Name: {user.Name}, Email: {user.Email}");
            }
        }
    }
}

In this example, the SqlConnection is a part of the System.Data.SqlClient namespace, which is specific to SQL Server databases. If you're working with a different type of database, you would use the corresponding connection type for that database (e.g., NpgsqlConnection for PostgreSQL).

Language Integrated Query

• LINQ is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, using a syntax reminiscent of SQL but integrated with the programming languages themselves.

Define the Data Models

public class SaleTransaction
{
    public string ProductName { get; set; }
    public string Category { get; set; }
    public string Region { get; set; }
    public decimal Price { get; set; }
    public int Quantity { get; set; }
}

public class SalesSummary
{
    public string Region { get; set; }
    public string Category { get; set; }
    public decimal TotalSales { get; set; }
}

public class BestSellingProduct
{
    public string Region { get; set; }
    public string ProductName { get; set; }
    public decimal TotalSales { get; set; }
}

Sample Data Creation

var transactions = new List<SaleTransaction>
{
    new SaleTransaction { ProductName = "Laptop", Category = "Electronics", Region = "North", Price = 1200, Quantity = 25 },
    new SaleTransaction { ProductName = "Smartphone", Category = "Electronics", Region = "South", Price = 800, Quantity = 50 },
    new SaleTransaction { ProductName = "Keyboard", Category = "Electronics", Region = "North", Price = 100, Quantity = 70 },
    new SaleTransaction { ProductName = "Mouse", Category = "Electronics", Region = "North", Price = 50, Quantity = 100 },
    new SaleTransaction { ProductName = "T-Shirt", Category = "Clothing", Region = "North", Price = 20, Quantity = 200 },
};

LINQ Queries for Analysis

var categorySalesByRegion = transactions
    .GroupBy(t => new { t.Region, t.Category })
    .Select(g => new SalesSummary
    {
        Region = g.Key.Region,
        Category = g.Key.Category,
        TotalSales = g.Sum(x => x.Price * x.Quantity)
    })
    .ToList();

foreach (var summary in categorySalesByRegion)
{
    Console.WriteLine($"Region: {summary.Region}, Category: {summary.Category}, Total Sales: {summary.TotalSales:C}");
}

Best Selling Product in Each Region

var bestSellingProducts = transactions
    .GroupBy(t => t.Region)
    .Select(g => new
    {
        Region = g.Key,
        BestSeller = g
            .GroupBy(x => x.ProductName)
            .Select(x => new { ProductName = x.Key, TotalSales = x.Sum(s => s.Price * s.Quantity) })
            .OrderByDescending(x => x.TotalSales)
            .FirstOrDefault()
    })
    .Select(r => new BestSellingProduct
    {
        Region = r.Region,
        ProductName = r.BestSeller.ProductName,
        TotalSales = r.BestSeller.TotalSales
    })
    .ToList();

foreach (var product in bestSellingProducts)
{
    Console.WriteLine($"Region: {product.Region}, Best Selling Product: {product.ProductName}, Total Sales: {product.TotalSales:C}");
}

Total Sales Calculation: The first query groups transactions by both region and category, then calculates the total sales for each group by multiplying price and quantity.
Best Selling Product: The second query is more complex. It first groups transactions by region. Within each group, it regroups by product and calculates total sales per product. It then selects the best-selling product (highest sales) for each region.

Dependency Injection

• DI is a software design pattern that enables loosely coupled software.
• DI is a great way to reduce tight coupling between software components.
• Enables code to become more manageable.
• Better manage future changes.
• One class depends on another class for a property or method.
• Unit Testing

Types of DI
-> Constructor Injection
--> Property Injection
---> Method Injection

Scenario: E-Commerce Application Services

Imagine we are building an e-commerce system. We need various services like product management, user management, and order processing. These services will require dependencies like data access services and external APIs.

Defining Interfaces and Implementations

public interface IProductService
{
    Task<IEnumerable<Product>> GetAllProductsAsync();
}

public interface IUserService
{
    Task<User> GetUserByIdAsync(int id);
}

public interface IOrderService
{
    Task PlaceOrderAsync(Order order);
}

Now, provide implementations for these interfaces:

public class ProductService : IProductService
{
    private readonly IProductRepository _productRepository; // Dependency

    public ProductService(IProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        return await _productRepository.GetAllAsync();
    }
}

public class UserService : IUserService
{
    private readonly IUserRepository _userRepository; // Dependency

    public UserService(IUserRepository userRepository)
    {
        _userRepository = userRepository;
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        return await _userRepository.GetByIdAsync(id);
    }
}

public class OrderService : IOrderService
{
    private readonly IOrderRepository _orderRepository; // Dependency

    public OrderService(IOrderRepository orderRepository)
    {
        _orderRepository = orderRepository;
    }

    public async Task PlaceOrderAsync(Order order)
    {
        await _orderRepository.SaveOrderAsync(order);
    }
}

Setting Up Dependency Injection
In Startup.cs or Program.cs (depending on your ASP.NET Core version), configure the DI container:

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<IProductService, ProductService>();
    services.AddScoped<IUserService, UserService>();
    services.AddScoped<IOrderService, OrderService>();

    services.AddScoped<IProductRepository, ProductRepository>();
    services.AddScoped<IUserRepository, UserRepository>();
    services.AddScoped<IOrderRepository, OrderRepository>();

    services.AddControllers();
}

Using Injected Services in Controllers

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductService _productService;

    public ProductsController(IProductService productService)
    {
        _productService = productService;
    }

    [HttpGet]
    public async Task<IActionResult> GetAllProducts()
    {
        var products = await _productService.GetAllProductsAsync();
        return Ok(products);
    }
}

Interface-Based Dependency Injection: By defining interfaces for services, we ensure that our controller classes are not tightly coupled to specific implementations of these services.
Service Lifetimes:
AddScoped: The service is created once per client request (connection). This is often used for services that include Entity Framework Core contexts.
AddTransient: The service is created each time it is requested. This works well for lightweight, stateless services.
AddSingleton: The service is created the first time it is requested and then every subsequent request will use the same instance. Use this for services that are thread-safe and can be shared between requests.