Connect to OceanBase Database by using Dapper

Dapper is a lightweight object-relational mapping (ORM) tool for the .NET platform. This topic describes how to use Dapper to connect to OceanBase Database in MySQL mode.

Prerequisites

• You have installed .NET SDK 6.0 or later.
• You have installed Visual Studio or VS Code.
• You have deployed OceanBase Database and created a MySQL tenant.

Procedure

1. Create a new project.
2. Install the required NuGet packages.
3. Configure the database connection.
4. Create a data model.
5. Implement a data access layer.
6. Run the sample program.

Step 1: Create a new project

1. Run the following command in the terminal to create a new console application:

   dotnet new console -n DapperOceanBaseDemo
   cd DapperOceanBaseDemo
   

Step 2: Install the required NuGet packages

1. Install the Dapper, MySQL connector, and related dependency packages:

   # Core packages
   dotnet add package Dapper
   dotnet add package MySql.Data
   
   # Configuration-related packages
   dotnet add package Microsoft.Extensions.Configuration --version 8.0.0
   dotnet add package Microsoft.Extensions.Configuration.FileExtensions --version 8.0.0
   dotnet add package Microsoft.Extensions.Configuration.Json --version 8.0.0
   
   # Password encryption
   dotnet add package BCrypt.Net-Next --version 4.0.3
   

Step 3: Configure the database connection

1. Create an appsettings.json file and add the database connection string:

   {
     "ConnectionStrings": {
       "DefaultConnection": "Server=your_server;Port=2881;Database=your_database;Uid=your_username;Pwd=your_password;"
     }
   }
   

2. Modify the project file (.csproj) to ensure that appsettings.json is copied to the output directory:

   <ItemGroup>
     <None Update="appsettings.json">
       <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
     </None>
   </ItemGroup>
   

Step 4: Create a data model

1. Create a Models folder and add a User.cs class:

   namespace DapperOceanBaseDemo.Models;
   
   public class User
   {
       public int Id { get; set; }
       public string Username { get; set; }
       public string Email { get; set; }
       public string PasswordHash { get; set; }
       public DateTime CreatedAt { get; set; }
       public DateTime? UpdatedAt { get; set; }
   }
   

Step 5: Implement a data access layer

1. Create a Repositories folder and add a UserRepository.cs class:

   using System.Data;
   using Dapper;
   using DapperOceanBaseDemo.Models;
   using Microsoft.Extensions.Configuration;
   using MySql.Data.MySqlClient;
   
   namespace DapperOceanBaseDemo.Repositories;
   
   public class UserRepository
   {
       private readonly string _connectionString;
   
       public UserRepository(IConfiguration configuration)
       {
           _connectionString = configuration.GetConnectionString("DefaultConnection");
       }
   
       public async Task<User> GetUserByIdAsync(int id)
       {
           using IDbConnection db = new MySqlConnection(_connectionString);
           return await db.QueryFirstOrDefaultAsync<User>(
               "SELECT * FROM users WHERE Id = @Id LIMIT 1", new { Id = id });
       }
   
       public async Task<int> CreateUserAsync(User user)
       {
           const string sql = @"
               INSERT INTO users (Username, Email, PasswordHash, CreatedAt, UpdatedAt)
               VALUES (@Username, @Email, @PasswordHash, @CreatedAt, @UpdatedAt);
               SELECT LAST_INSERT_ID();";
   
           using IDbConnection db = new MySqlConnection(_connectionString);
           return await db.ExecuteScalarAsync<int>(sql, new
           {
               user.Username,
               user.Email,
               user.PasswordHash,
               CreatedAt = DateTime.UtcNow,
               UpdatedAt = (DateTime?)null
           });
       }
   }
   

Step 6: Implement the main program

Modify the Program.cs file:

using DapperOceanBaseDemo.Models;
using DapperOceanBaseDemo.Repositories;
using Microsoft.Extensions.Configuration;
using System.Text;

var configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
    .Build();

var userRepository = new UserRepository(configuration);

// Create a new user
var newUser = new User
{
    Username = "testuser",
    Email = "test@example.com",
    PasswordHash = BCrypt.Net.BCrypt.HashPassword("your_secure_password")
};

try
{
    var userId = await userRepository.CreateUserAsync(newUser);
    Console.WriteLine($"User created with ID: {userId}");

    // Query the user
    var user = await userRepository.GetUserByIdAsync(userId);
    if (user != null)
    {
        Console.WriteLine($"Retrieved user: {user.Username}, Email: {user.Email}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"An error occurred: {ex.Message}");
}

Create a database table

Before you run the program, make sure that you have created the corresponding table in OceanBase Database:

CREATE TABLE IF NOT EXISTS users (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) NOT NULL UNIQUE,
    PasswordHash VARCHAR(255) NOT NULL,
    CreatedAt DATETIME NOT NULL,
    UpdatedAt DATETIME NULL,
    INDEX idx_email (Email)
);

Run the program

1. Make sure that the database connection string in the appsettings.json file is updated.

2. Run the following command in the terminal:

   dotnet run
   

References

• Dapper official documentation
• .NET database access best practices
• OceanBase official documentation