Connect to OceanBase Database by using FreeSQL

FreeSQL is a powerful and flexible .NET ORM framework that supports multiple databases. This topic describes how to connect to OceanBase Database in MySQL mode by using FreeSQL.

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 the 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 FreeSQLOceanBaseDemo
   cd FreeSQLOceanBaseDemo
   

Step 2: Install the required NuGet packages

1. Install FreeSQL and the required dependency packages:

   # ORM core package
   dotnet add package FreeSQL
   dotnet add package FreeSql.Provider.MySqlConnector
   dotnet add package FreeSql.DbContext
   
   # 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:

   using FreeSql.DataAnnotations;
   using System;
   
   namespace FreeSQLOceanBaseDemo.Models;
   
   [Table(Name = "users")]
   public class User
   {
       [Column(IsPrimary = true, IsIdentity = true)]
       public int Id { get; set; }
   
       [Column(StringLength = 50, IsNullable = false)]
       public string Username { get; set; }
   
       [Column(StringLength = 100, IsNullable = false)]
       public string Email { get; set; }
   
       [Column(StringLength = 255, IsNullable = false)]
       public string PasswordHash { get; set; }
   
       [Column(ServerTime = DateTimeKind.Utc, CanUpdate = false)]
       public DateTime CreatedAt { get; set; }
   
       [Column(ServerTime = DateTimeKind.Utc)]
       public DateTime? UpdatedAt { get; set; }
   }
   

Step 5: Implement the data access layer

1. Create a Data folder and add an AppDbContext.cs class:

   using FreeSql;
   using System.Data;
   using FreeSql.DatabaseModel;
   using FreeSQLOceanBaseDemo.Models;
   using Microsoft.Extensions.Configuration;
   using System;
   using System.IO;
   namespace FreeSQLOceanBaseDemo.Data;
   public class AppDbContext : DbContext
   {
   public DbSet Users { get; set; }
   private readonly IConfiguration _configuration;
   private static IFreeSql _fsql;
   public AppDbContext(IConfiguration configuration)
   {
   _configuration = configuration;
   
   if (_fsql == null)
   {
       _fsql = new FreeSqlBuilder()
           .UseConnectionString(DataType.MySql, _configuration.GetConnectionString("DefaultConnection"))
           .UseMonitorCommand(cmd =>
           {
               Console.WriteLine($"SQL: {cmd.CommandText}");
               if (cmd.Parameters != null)
               {
                   foreach (IDataParameter param in cmd.Parameters)
                   {
                       Console.WriteLine($"  -> {param.ParameterName}: {param.Value}");
                   }
               }
           })
           .UseAutoSyncStructure(true)
           .Build();
   
       _fsql.Aop.CurdBefore += (s, e) =>
       {
           Console.WriteLine($"Before execution: {e.Sql}");
       };
   
       _fsql.Aop.CurdAfter += (s, e) =>
       {
           Console.WriteLine($"After execution, affected rows: {e.ElapsedMilliseconds}ms");
       };
   }
   }
   public IFreeSql GetFreeSql()
   {
   return _fsql;
   }
   }
   

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

   using FreeSql;
   using FreeSQLOceanBaseDemo.Models;
   using System;
   using System.Collections.Generic;
   using System.Threading.Tasks;
   
   namespace FreeSQLOceanBaseDemo.Repositories;
   
   public class UserRepository
   {
       private readonly IFreeSql _fsql;
   
       public UserRepository(IFreeSql fsql)
       {
           _fsql = fsql;
       }
   
       public async Task<List<User>> GetAllUsersAsync()
       {
           return await _fsql.Select<User>().ToListAsync();
       }
   
       public async Task<User> GetUserByIdAsync(int id)
       {
           return await _fsql.Select<User>().Where(u => u.Id == id).FirstAsync();
       }
   
       public async Task<User> CreateUserAsync(User user)
       {
           user.CreatedAt = DateTime.UtcNow;
           await _fsql.Insert(user).ExecuteAffrowsAsync();
           return user;
       }
   
       public async Task<bool> UpdateUserAsync(User user)
       {
           user.UpdatedAt = DateTime.UtcNow;
           var result = await _fsql.Update<User>()
               .SetSource(user)
               .ExecuteAffrowsAsync();
   
           return result > 0;
       }
   
       public async Task<bool> DeleteUserAsync(int id)
       {
           var result = await _fsql.Delete<User>()
               .Where(u => u.Id == id)
               .ExecuteAffrowsAsync();
   
           return result > 0;
       }
   }
   

Step 6: Implement the main program

Modify the Program.cs file:

using FreeSQLOceanBaseDemo.Data;
using FreeSQLOceanBaseDemo.Models;
using FreeSQLOceanBaseDemo.Repositories;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;
using System.Threading.Tasks;

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

var dbContext = new AppDbContext(configuration);
var userRepository = new UserRepository(dbContext.GetFreeSql());

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

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

    var user = await userRepository.GetUserByIdAsync(createdUser.Id);
    if (user != null)
    {
        Console.WriteLine($"Retrieved user: {user.Username}, Email: {user.Email}");

        user.Username = "updated_username";
        var updated = await userRepository.UpdateUserAsync(user);
        Console.WriteLine($"User updated: {updated}");

        var users = await userRepository.GetAllUsersAsync();
        Console.WriteLine($"Total users: {users.Count}");

        var deleted = await userRepository.DeleteUserAsync(user.Id);
        Console.WriteLine($"User deleted: {deleted}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"An error occurred: {ex.Message}");
    Console.WriteLine(ex.StackTrace);
}

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

• FreeSQL documentation
• .NET database access best practices
• OceanBase Database documentation