Dapper is a lightweight Object-Relational Mapping (ORM) tool for the .NET platform. This topic walks you through connecting to OceanBase Database's MySQL-compatible mode using Dapper.
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-compatible tenant.
Procedure
- Create a new project.
- Install the necessary NuGet packages.
- Configure the database connection.
- Create a data model.
- Implement the data access layer.
- Run the sample program.
Step 1: Create a new project
Open your terminal and run the following commands to create a new console application:
dotnet new console -n DapperOceanBaseDemo
cd DapperOceanBaseDemo
Step 2: Install necessary NuGet packages
Install Dapper, MySQL connector, and related dependencies:
# Core packages
dotnet add package Dapper
dotnet add package MySql.Data
# Configuration-related
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
Create an
appsettings.jsonfile and add your database connection string:{ "ConnectionStrings": { "DefaultConnection": "Server=your_server;Port=2881;Database=your_database;Uid=your_username;Pwd=your_password;" } }Modify the project file (
.csproj) to ensure thatappsettings.jsonis copied to the output directory:<ItemGroup> <None Update="appsettings.json"> <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory> </None> </ItemGroup>
Step 4: Create a data model
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 the data access layer
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 as follows:
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 the database table
Before running 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
Make sure your database connection string in
appsettings.jsonis up to date.In your terminal, run:
dotnet run