4.7 Database

Introduction

In distributed computing, databases play a crucial role in managing and storing data across multiple nodes or servers in a network.

Distributed databases are designed to provide scalability, fault tolerance, and improved performance compared to centralized databases.

Data Distribution: In a distributed database system, data is distributed across multiple nodes or servers. This distribution can be done in various ways, such as sharding, partitioning, or replication. The goal is to ensure that data is available and accessible even if some nodes fail

Replication: Replicating data involves maintaining multiple copies of the same data on different nodes. This provides fault tolerance and high availability. Changes to one copy of the data are propagated to other copies to keep them synchronized. However, managing consistency and avoiding conflicts is a challenge in replication-based systems.

Scalability: One of the primary reasons for using distributed databases is scalability. As data grows, you can add more nodes to the database cluster to distribute the load and maintain acceptable performance levels. Horizontal scaling, where you add more machines, is a common approach

Security and Authorization: Distributed databases must implement robust security measures to protect data from unauthorized access. Access control, encryption, and auditing mechanisms are essential components of distributed database security.

Data Consistency and Conflict Resolution: In a distributed environment, conflicts can occur when multiple nodes attempt to update the same data simultaneously. Conflict resolution mechanisms must be in place to handle such situations and maintain data integrity

Load Balancing: To ensure even distribution of query and transaction loads across nodes, load balancing mechanisms are employed. Load balancers distribute incoming requests to database nodes based on various algorithms like round-robin, least connections, or weighted distribution.

Commercial Database Servers

These are software products provided by various companies for managing and storing structured data efficiently and securely. Here are some well-known commercial database servers:

• Oracle Database
• Microsoft SQL Server
• Amazon RDS (Relational Database Service)
• Google Cloud SQL
• Microsoft Azure SQL Database

Portable Database

• A portable database refers to a database system that can be easily moved or transferred from one computing environment to another with minimal effort or modification.

• Portability in the context of databases is important for scenarios where data needs to be moved between different systems, platforms, or locations without significant changes to the database structure or code.

• Open source database systems like MySQL, PostgreSQL, and SQLite are known for their portability. They have versions available for various operating systems and architectures, making it easier to transfer data between systems.

• Technologies like virtual machines (VMs) and containerization (e.g., Docker) can encapsulate an entire database system, including its dependencies and configuration, into a portable package. This package can be moved to different environments without modification.

SQL-Lite

• Self-Contained: SQLite is a serverless, self-contained database engine, meaning it doesn’t require a separate server process to operate. The entire database is contained within a single file on disk, making it highly portable and easy to distribute.

• File-Based: SQLite databases are stored as single files on the file system. These files have a “.db” or “.sqlite” extension and contain all the necessary data and schema information.

• Cross-Platform: SQLite is cross-platform and runs on various operating systems, including Windows, macOS, Linux, Android, and iOS.

• SQLite is commonly used for prototyping and development due to its ease of use and portability.

• However, for high-concurrency or large-scale applications, you may want to consider more robust RDBMS solutions like MySQL, PostgreSQL, or Microsoft SQL Server, which offer features like multi- user support and advanced management capabilities.

• Our main focus is the universal database connectivity.

• A universal approach to database connectivity aims to provide a standardized way to connect to different types of database management systems (DBMS) regardless of the programming language or platform you are using.

Universal database connectivity strategy

• ODBC (Open Database Connectivity): ODBC is a standard API for connecting to databases. It provides a common interface for various programming languages, including C/C++, Python, Java, and more, to access different DBMSs. ODBC drivers are available for many popular databases, making it a universal choice for cross-DBMS connectivity.

• JDBC (Java Database Connectivity): JDBC is the Java equivalent of ODBC and is specifically designed for Java applications. It offers a standardized way to connect to and interact with relational databases using Java.

• ADO.NET: For .NET-based applications, ADO.NET provides a set of classes and libraries for database connectivity. It supports various database providers through Data Providers, which are specific to the target DBMS

1. ASP.NET Database Connectivity

In your project cretae a Student Model class and DBManager class

• …
• DirectoryModels

  • Student.cs
• DirectoryViews

  • …
• DirectoryControllers

  • HomeController.cs
  • StudentsController.cs
• DirectoryData

  • DBManager.cs
• …

IMPORTANT

Depending on whether you are using ADO.NET low level access or Entity Framework, the content of the DBManager.cs will change.

Student.cs

public class Student
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int Age { get; set; }

}

1.1 ADO.NET

• ADO.NET is a low-level data access framework that provides direct access to the database using data providers such as SqlConnection and SqlCommand for SQL Server.

• ADO.NET provides maximum flexibility, making it suitable for scenarios where you need to execute highly optimized or complex database operations.

1. DDL Queries

DDL (Data Definition Language) is used to define and manage database structures or schema. It includes commands to create, modify, and delete database objects like tables, indexes, views, etc.

DBManager class | CreateTable() method

public class DBManager
{
    // In this example, the connection string specifies that the SQLite database is
    // located in a file named mydatabase.db, and it's an SQLite version 3 database
    private static string connectionString = "Data Source=mydatabase.db;Version=3;";

    public static bool CreateTable()
    {
        try
        {
            // SQLiteConnection object represents a connection to an SQLite database, and
            // the connection string specifies various connection details, such as the database
            // file location, version, and other parameters needed to establish the connection.
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // SQL command to create a table named "StudentTable"
                    command.CommandText = @"
                CREATE TABLE StudentTable (
                    ID INTEGER,
                    Name TEXT,
                    Age INTEGER
                )";
                // When you prefix a string with @, it tells the compiler to
                // interpret the string as a verbatim string, which means
                // that escape sequences (like \n for a newline or \" for a double
                // quote) are treated as literal character

                    // Execute the SQL command to create the table
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
            Console.WriteLine("Table created successfully.");
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }

        return false; // Create table failed

    }
    ...
}

2. DML Queries

DML (Data Manipulation Language) is used to manipulate or manage the data inside the database tables. It includes commands to insert, update, and delete data.

DBManager class | Insert(), Update(), Delete() methods

    public static bool Insert(Student student)
    {
        try
        {
            // Create a new SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // SQL command to insert data into the "StudentTable" table
                    command.CommandText = @"
                    INSERT INTO StudentTable (ID, Name, Age)
                    VALUES (@ID, @Name, @Age)";

                    // Define parameters for the query
                    command.Parameters.AddWithValue("@ID", student.Id);
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);

                    // Execute the SQL command to insert data
                    int rowsInserted = command.ExecuteNonQuery();

                    // Check if any rows were inserted
                    connection.Close();
                    if (rowsInserted > 0)
                    {
                        return true; // Insertion was successful
                    }
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }

        return false; // Insertion failed
    }

    public static bool Delete(int id)
    {
        try
        {
            // Create a new SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // Build the SQL command to delete data by ID
                    command.CommandText = $"DELETE FROM StudentTable WHERE ID = @ID";
                    command.Parameters.AddWithValue("@ID", id);

                    // Execute the SQL command to delete data
                    int rowsDeleted = command.ExecuteNonQuery();

                    // Check if any rows were deleted
                    connection.Close();
                    if (rowsDeleted > 0)
                    {
                        return true; // Deletion was successful
                    }
                }
                connection.Close();
            }

            return false; // No rows were deleted
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
            return false; // Deletion failed
        }
    }
    public static bool Update(Student student)
    {
        try
        {
            // Create a new SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // Build the SQL command to update data by ID
                    command.CommandText = $"UPDATE StudentTable SET Name = @Name, Age = @Age WHERE ID = @ID";
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@ID", student.Id);

                    // Execute the SQL command to update data
                    int rowsUpdated = command.ExecuteNonQuery();
                    connection.Close();
                    // Check if any rows were updated
                    if (rowsUpdated > 0)
                    {
                        return true; // Update was successful
                    }
                }
                connection.Close();
            }

            return false; // No rows were updated
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
            return false; // Update failed
        }
    }

    ...

3. DQL Queries

DQL (Data Query Language) is used to query and fetch data from the database. The primary command for DQL is SELECT.

DBManager class | GetAll(), GetById() methods

    public static List<Student> GetAll()
    {
        List<Student> studentList = new List<Student>();

        try
        {
            // Create a new SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // Build the SQL command to select all student data
                    command.CommandText = "SELECT * FROM StudentTable";

                    // Execute the SQL command and retrieve data
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Student student = new Student();
                            student.Id = Convert.ToInt32(reader["ID"]);
                            student.Name = reader["Name"].ToString();
                            student.Age = Convert.ToInt32(reader["Age"]);

                            // Create a Student object and add it to the list
                            studentList.Add(student);
                        }
                    }
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }

        return studentList;
    }

    public static Student GetById(int id)
    {
        Student student = null;

        try
        {
            // Create a new SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // Create a new SQLite command to execute SQL
                using (SQLiteCommand command = connection.CreateCommand())
                {
                    // Build the SQL command to select a student by ID
                    command.CommandText = "SELECT * FROM StudentTable WHERE ID = @ID";
                    command.Parameters.AddWithValue("@ID", id);

                    // Execute the SQL command and retrieve data
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            student = new Student();
                            student.Id = Convert.ToInt32(reader["ID"]);
                            student.Name = reader["Name"].ToString();
                            student.Age = Convert.ToInt32(reader["Age"]);

                        }
                    }
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }

        return student;
    }

    public static void DBInitialize()
    {
        if (CreateTable())
        {
            Student student = new Student();
            student.Id = 1;
            student.Name = "Sajib";
            student.Age = 20;
            Insert(student);

            student = new Student();
            student.Id = 2;
            student.Name = "Mistry";
            student.Age = 30;
            Insert(student);

            student = new Student();
            student.Id = 3;
            student.Name = "Mike";
            student.Age = 40;
            Insert(student);

        }
    }
}

StudentController.cs

[Route("api/[controller]")]
[ApiController]
public class StudentController : ControllerBase
{
    [HttpGet]
    public IEnumerable<Student> getStudents()
    {
        List<Student> students = DBManager.GetAll();
        return students;
    }
    [HttpGet]
    [Route("{id}")]
    public IActionResult Get(int id)
    {
        Student student = DBManager.GetById(id);
        if(student == null)
        {
            return NotFound();
        }
        return Ok(student);
    }
    [HttpPost]
    public IActionResult Post([FromBody] Student student)
    {
        if (DBManager.Insert(student))
        {
            return Ok("Successfully inserted");
        }
        return BadRequest("Error in data insertion");
    }
    [HttpDelete]
    [Route("{id}")]
    public IActionResult Delete(int id)
    {
        if(DBManager.Delete(id))
        {
            return Ok("Successfully Deleted");
        }
        return BadRequest("Could not delete");
    }
    [HttpPut]
    public IActionResult Update(Student student)
    {
        if (DBManager.Update(student))
        {
            return Ok("Successfully updated");
        }
        return BadRequest("Could not update");
    }
}

1.2 Entity Framework (EF)

Play

Entity Framework

• Entity Framework is a high-level Object-Relational Mapping (ORM) framework that allows developers to work with databases using .NET objects and classes, abstracting much of the database-specific code.

• EF can work with various database systems, not just SQL Server, with providers like Entity Framework Core supporting multiple database platforms.

• EF provides built-in change tracking, making it easier to manage updates, inserts, and deletes. It automatically generates the necessary SQL statements.

Caution

While EF is excellent for most common database operations, it may not offer the same level of control and optimization as handcrafted SQL for complex queries.

1. Installation

If nuPackage Manager does not work, follow the steps below:

 go to this path in visual studio: tools->nuget package manager -> package manager console add below code: Install-Package SQLite -Version 3.13.0

2. Data Context

In ASP.NET with Entity Framework, the DbContext (or DataContext) is a class that serves as a bridge between your application and the database. It manages the database operations and represents a session with the database, allowing you to query and save data.

Key features of DbContext:

1. Database Connection: Manages the connection to the database.
2. CRUD Operations: Provides methods to create, read, update, and delete (CRUD) data.
3. Change Tracking: Tracks changes made to entities during the session.
4. LINQ Queries: Supports querying the database using LINQ.
5. Migrations: Helps in managing database schema changes over time.

Example

using Microsoft.EntityFrameworkCore;
using System;

public class MyDbContext : DbContext
{
    // Define your DbSets (representing database tables)
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    // Constructor that takes DbContextOptions
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }

    // Override OnConfiguring to configure the database connection
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("YourConnectionStringHere");
        }
    }

    // Override OnModelCreating to customize the model building process
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Customize table names or relationships, apply configurations
        modelBuilder.Entity<Customer>().ToTable("tbl_Customers");

        // ToTable("tbl_Customers"): This maps the Customer entity to a database table called tbl_Customers instead of the default Customers.

        // After running migrations and applying the schema to the database, the Customer entity will be stored in the tbl_Customers table and the Order entity will be stored in tbl_Orders.

        // Example: Configure a one-to-many relationship
        modelBuilder.Entity<Customer>()
            .HasMany(c => c.Orders)  // Configures the one-to-many relationship between Customer and Order.
            .WithOne(o => o.Customer) // Specifies that each Order belongs to one Customer.
            .HasForeignKey(o => o.CustomerId); // Sets up the foreign key in the Orders table to reference the CustomerId.

        // Add any other configurations or custom conventions here
    }

    // Override SaveChanges to add custom logic during saving entities
    public override int SaveChanges()
    {
        // Add any pre-save logic here (e.g., auditing, validation)
        foreach (var entry in ChangeTracker.Entries())
        {
            if (entry.State == EntityState.Added)
            {
                // Example: Set created date for new entries
                if (entry.Entity is IAuditable auditableEntity)
                {
                    auditableEntity.CreatedDate = DateTime.Now;
                }
            }
        }

        // Call the base SaveChanges method to save the changes to the database
        return base.SaveChanges();
    }
}

// Example entity classes
public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public string Product { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

// Optional interface for auditing
public interface IAuditable
{
    DateTime CreatedDate { get; set; }
}

Tip

Here, MyDbContext is a DbContext that defines two entities (Customer and Order) that will be mapped to database tables.

Key Parts

• OnConfiguring: Specifies how the database connection is configured, such as setting the connection string.

• OnModelCreating: Defines how the model (entity relationships, table names, constraints) is built. This is useful for applying configurations like table mappings or relationships.

• SaveChanges: Allows custom logic to be executed before changes are persisted to the database, such as adding auditing information.

Using IAuditable Interface

To implement and use the IAuditable interface in Entity Framework, you can follow these steps:

Step 1: Define the IAuditable Interface

This interface will be used for auditing entities by tracking fields like CreatedDate and ModifiedDate.

public interface IAuditable
{
    DateTime CreatedDate { get; set; }
    DateTime? ModifiedDate { get; set; }
}

• CreatedDate: Stores the timestamp of when the entity was created.
• ModifiedDate: Stores the timestamp of when the entity was last updated. It can be nullable because the entity might not be modified after creation.

Step 2: Implement IAuditable in Your Entities

Now, let’s apply this interface to entities that you want to track for audit purposes.

public class Customer : IAuditable
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }

    // Auditable properties
    public DateTime CreatedDate { get; set; }
    public DateTime? ModifiedDate { get; set; }
}

public class Order : IAuditable
{
    public int OrderId { get; set; }
    public string Product { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }

    // Auditable properties
    public DateTime CreatedDate { get; set; }
    public DateTime? ModifiedDate { get; set; }
}

Here, Customer and Order now implement the IAuditable interface, which means they will have the CreatedDate and ModifiedDate properties.

Step 3: Modify SaveChanges in DbContext to Set Audit Fields

Override the SaveChanges method in your DbContext to automatically populate the CreatedDate and ModifiedDate fields whenever an entity is added or updated.

public class MyDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }

    public override int SaveChanges()
    {
        var entries = ChangeTracker.Entries<IAuditable>();

        foreach (var entry in entries)
        {
            if (entry.State == EntityState.Added)
            {
                entry.Entity.CreatedDate = DateTime.Now;
            }
            else if (entry.State == EntityState.Modified)
            {
                entry.Entity.ModifiedDate = DateTime.Now;
            }
        }

        return base.SaveChanges();
    }
}

Explanation:

• ChangeTracker: This is used to track changes made to entities. We filter the entries to those that implement IAuditable.
• EntityState.Added: When the entity is added to the context, the CreatedDate is set to the current date and time.
• EntityState.Modified: When the entity is updated, the ModifiedDate is set to the current date and time.

Step 4: Use DbContext with Auditable Entities

Now, when you add or update Customer or Order entities, the CreatedDate and ModifiedDate will automatically be managed by Entity Framework.

Example Usage:

var customer = new Customer { Name = "John Doe" };
context.Customers.Add(customer);
context.SaveChanges(); // CreatedDate is automatically set

customer.Name = "John Updated";
context.SaveChanges(); // ModifiedDate is automatically set

This approach simplifies auditing by automatically managing CreatedDate and ModifiedDate fields without manually setting them in your application logic.

4. DBManger Class

• …
• DirectoryModels

  • Student.cs
• DirectoryViews

  • …
• DirectoryControllers

  • HomeController.cs
  • StudentsController.cs
• DirectoryData

  • DBManager.cs
• …

Here is the DBManger class for student example

public class DBManager : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source = Students.db;");
    }

    public DbSet<Student> Students { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        List<Student> students = new List<Student>();
        Student student = new Student();
        student.Id = 1;
        student.Name = "Sajib";
        student.Age = 20;
        students.Add(student);

        student = new Student();
        student.Id = 2;
        student.Name = "Mistry";
        student.Age = 30;
        students.Add(student);

        student = new Student();
        student.Id = 3;
        student.Name = "Mike";
        student.Age = 40;
        students.Add(student);
        modelBuilder.Entity<Student>().HasData(students);
    }
}