Use SqlSugar|V1.3.1|OceanBase Connector/NET|docs|OceanBase Documentations

The underlying connection driver SqlSugarCore.Oceanbase is OceanBase.ManagedDataAccess, which allows SqlSugar to access OceanBase in Oracle-compatible mode via a .NET driver.

Installation method

The installation method is the same as that for the base driver. For more information, see Package reference and usage instructions.

You also need to reference SqlSugarCore.Oceanbase (which typically includes dependencies like SqlSugarCore):

<ItemGroup>
  <PackageReference Include="SqlSugarCore.OceanBase" />
</ItemGroup>

Connection string

Use the Oracle-compatible mode format supported by OceanBase.ManagedDataAccess. Replace the placeholders with actual environment values; do not directly copy the literals in the example:

server=<host or IP>;port=<port>;user id=<OceanBase Oracle account>;password=<password>;database=<schema name>;

If the session needs to use the GBK character set, you can append Character Set=gbk (aliases CharSet / CharacterSet):

server=<host or IP>;port=<port>;user id=<OceanBase Oracle account>;password=<password>;database=<schema name>;Character Set=gbk;

Note

user id is an OceanBase Oracle-compatible mode account.
We recommend not omitting database. The metadata capabilities of the current provider depend on it to identify the current schema.
The default port is usually 2881.
Starting from V1.3.0, Oracle-compatible mode supports encoding and decoding VARCHAR2, CHAR, and CLOB in GBK using Character Set=gbk. If not set, UTF-8 is used. NCHAR and NVARCHAR2 are not affected by this parameter.
When using GBK with SqlSugar, use Provider V1.3.0 or later. For complete parameter descriptions, see Connection string.

Access method

Parameters such as InstanceFactory.CustomDllName are static global configurations. It is recommended to set them only once when the application starts to avoid loading the wrong assembly due to overwriting in multiple locations.

Method	`ConnectionConfig.DbType`	Description
Method A (recommended)	`DbType.Custom`	Explicitly set`CustomDllName` / `CustomNamespace` / `CustomDbName`, independent of`SqlSugarCore`Built-in or not`DbType.OceanBaseForOracle`
Method B	`DbType.OceanBaseForOracle`	Required`SqlSugarCore`Include this enumeration and set it to`CustomDllName`and others to load the adapter assembly.

Method A (recommended): `DbType.Custom` + `InstanceFactory`

using SqlSugar;
using SqlSugar.OceanBaseForOracle

InstanceFactory.CustomDllName = "SqlSugarCore.OceanBase";
InstanceFactory.CustomNamespace = "SqlSugarCore.OceanBase";
InstanceFactory.CustomDbName = "OceanBaseForOracle";
InstanceFactory.CustomAssemblies = new[] { typeof(OceanBaseForOracleProvider).Assembly };

var db = new SqlSugarClient(new ConnectionConfig
{
    ConnectionString = "server=127.0.0.1;port=2881;user id=...;password=...;database=...;",
    DbType = DbType.Custom,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    MoreSettings = new ConnMoreSettings { IsAutoToUpper = false }
});

Method B: `DbType.OceanBaseForOracle` + `CustomDllName`

This is closer to the enumeration of built-in SqlSugar plugins. The SqlSugarCore used must include DbType.OceanBaseForOracle (otherwise, it cannot be compiled).

using SqlSugar;
using SqlSugar.OceanBaseForOracle;
InstanceFactory.CustomDllName = "SqlSugarCore.OceanBase";
InstanceFactory.CustomAssemblies = new[] { typeof(OceanBaseForOracleProvider).Assembly };

var db = new SqlSugarClient(new ConnectionConfig
{
    ConnectionString = "server=127.0.0.1;port=2881;user id=...;password=...;database=...;",
    DbType = DbType.OceanBaseForOracle,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    MoreSettings = new ConnMoreSettings { IsAutoToUpper = false }
});

In some versions of SqlSugar, InstanceFactory.CustomDllName is automatically set when initializing DbType.OceanBaseForOracle. It is still recommended to explicitly set it once during startup to reduce troubleshooting costs caused by version differences.

Bootstrap encapsulation (`SqlSugarOceanBaseBootstrap`)

If you want to share a piece of startup code between the two methods:

using SqlSugar;
using SqlSugar.OceanBaseForOracle;
public static class SqlSugarOceanBaseBootstrap
{
    private static bool s_registered;

    /// <param name="useCustomDbType">true = Method A (DbType.Custom); false = Method B (DbType.OceanBaseForOracle)</param>
    public static void Register(bool useCustomDbType = true)
    {
        if (s_registered)
            return;

        InstanceFactory.CustomDllName = "SqlSugarCore.OceanBase";
        InstanceFactory.CustomAssemblies = new[] { typeof(OceanBaseForOracleProvider).Assembly };

        if (useCustomDbType)
        {
            InstanceFactory.CustomNamespace = "SqlSugarCore.OceanBase";
            InstanceFactory.CustomDbName = "OceanBaseForOracle";
        }

        s_registered = true;
    }
}

Initialize `SqlSugarScope`

Using SqlSugarScope is recommended in web or multithreaded scenarios. First, call SqlSugarOceanBaseBootstrap.Register(...) (or an equivalent manual assignment to InstanceFactory), then create ConnectionConfig.

Example A:

using SqlSugar;

SqlSugarOceanBaseBootstrap.Register(useCustomDbType: true);

var connectionString = "server=127.0.0.1;port=2881;user id=demo_user@test_tenant#oboracle;password=123456;database=DEMO;";

var db = new SqlSugarScope(new ConnectionConfig
{
    ConfigId = "default",
    DbType = DbType.Custom,
    ConnectionString = connectionString,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    MoreSettings = new ConnMoreSettings { IsAutoToUpper = false }
},
sqlSugarClient =>
{
    sqlSugarClient.Aop.OnLogExecuting = (sql, parameters) => { Console.WriteLine(sql); };
    sqlSugarClient.Aop.OnError = ex => { Console.WriteLine(ex.Message); };
});

Example B: Set Register(useCustomDbType: false) and DbType = DbType.OceanBaseForOracle, with the rest similar to above.

Console applications can also directly use SqlSugarClient, with the same configuration method.

Complete example

The following example demonstrates creating tables, inserting data, querying, updating, paging, deleting, and performing transactions (using Method A by default):

using SqlSugar;
using SqlSugar.OceanBaseForOracle;
SqlSugarOceanBaseBootstrap.Register(useCustomDbType: true);

var db = new SqlSugarScope(new ConnectionConfig
{
    ConfigId = "default",
    DbType = DbType.Custom,
    ConnectionString = "server=127.0.0.1;port=2881;user id=demo_user@test_tenant#oboracle;password=123456;database=DEMO;",
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    MoreSettings = new ConnMoreSettings { IsAutoToUpper = false }
});

if (!db.Ado.IsValidConnection())
    throw new Exception("OceanBase connection failed.");

db.CodeFirst.InitTables<DemoUser>();

var userId = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();

db.Insertable(new DemoUser
{
    Id = userId,
    Name = "Alice",
    Age = 28,
    CreatedAt = DateTime.Now
}).ExecuteCommand();

var user = db.Queryable<DemoUser>()
    .Where(x => x.Id == userId)
    .Single();

Console.WriteLine($"{user.Id} {user.Name} {user.Age}");

db.Updateable<DemoUser>()
    .SetColumns(x => new DemoUser { Name = "Alice-Updated", Age = 29 })
    .Where(x => x.Id == userId)
    .ExecuteCommand();

var page = db.Queryable<DemoUser>()
    .OrderBy(x => x.Id)
    .ToPageList(1, 10, out var totalCount);

Console.WriteLine($"TotalCount={totalCount}");

db.Ado.BeginTran();
try
{
    db.Deleteable<DemoUser>()
        .Where(x => x.Id == userId)
        .ExecuteCommand();

    db.Ado.CommitTran();
}
catch
{
    db.Ado.RollbackTran();
    throw;
}

[SugarTable("T_DEMO_USER")]
public class DemoUser
{
    [SugarColumn(ColumnName = "ID", IsPrimaryKey = true)]
    public long Id { get; set; }

    [SugarColumn(ColumnName = "NAME", Length = 100)]
    public string Name { get; set; } = string.Empty;

    [SugarColumn(ColumnName = "AGE", IsNullable = true)]
    public int? Age { get; set; }

    [SugarColumn(ColumnName = "CREATED_AT")]
    public DateTime CreatedAt { get; set; }
}

If using Method B, set Register(useCustomDbType: false) and change DbType to DbType.OceanBaseForOracle.

ADO and parameterized queries

The @parameter_name in SQL is processed in Oracle-style. It is recommended to use parameterized queries:

var user = db.Ado.SqlQuerySingle<DemoUser>(
    "select ID, NAME, AGE, CREATED_AT from T_DEMO_USER where ID=@id",
    new SugarParameter("@id", 1001));

var users = db.Ado.SqlQuery<DemoUser>(
    "select ID, NAME, AGE, CREATED_AT from T_DEMO_USER where AGE >= @minAge",
    new SugarParameter("@minAge", 18));

CodeFirst / DbFirst

CodeFirst

You can initialize tables directly based on entities:

db.CodeFirst.InitTables<DemoUser>();

Common usage (table names should be modified accordingly):

if (db.DbMaintenance.IsAnyTable("T_DEMO_USER", false))
{
    db.DbMaintenance.DropTable("T_DEMO_USER");
}

db.CodeFirst.InitTables<DemoUser>();

DbFirst/Metadata Reading

var tables = db.DbMaintenance.GetTableInfoList();
var columns = db.DbMaintenance.GetColumnInfosByTableName("T_DEMO_USER");

This capability can also be directly reused for simple database and table inspections or code generation.

Recommended Usage

In business projects, it is recommended to organize the usage as follows:

Call SqlSugarOceanBaseBootstrap.Register(...) (or configure InstanceFactory equivalently) once when the application starts.
Encapsulate SqlSugarScope uniformly as a singleton or manage it via Dependency Injection.
Entities should continue to use the original SqlSugar features and syntax.
The original Queryable, Insertable, Updateable, and Deleteable interfaces generally require no changes.

This way, the business layer can be kept unchanged except for the driver and provider registration code.

Current Limitations and Considerations

1. `DbType` options are `DbType.Custom` or `DbType.OceanBaseForOracle`

Method A: Use DbType.Custom with CustomNamespace and CustomDbName (as described above).
Method B: Use DbType.OceanBaseForOracle. This requires the SqlSugarCore enum and setting CustomDllName to load the extension assembly.

If DbType.OceanBaseForOracle is unavailable (for example, if an older version of SqlSugarCore lacks this enum), use Method A.

2. `BulkCopy` / `FastBuilder` are not implemented yet

The OceanBaseForOracleFastBuilder in the adaptation layer currently throws an exception directly, so do not rely on the BulkCopy capability for now.

3. It is recommended to continue using parameterized SQL

Although the adaptation layer supports automatic conversion from @name to :name, it is still recommended to uniformly use parameterized queries instead of concatenating SQL.

4. Native types are provided by the `OceanBase` driver

If you need to manually specify Oracle types, you can use the types from the underlying driver, for example:

using OceanBase;

var p = new SugarParameter("@p_name", "Alice")
{
    CustomDbType = OracleDbType.NVarchar2
};

Minimum Working Example

Method A:

using SqlSugar;
using SqlSugar.OceanBaseForOracle;
SqlSugarOceanBaseBootstrap.Register(useCustomDbType: true);

var db = new SqlSugarClient(new ConnectionConfig
{
    DbType = DbType.Custom,
    ConnectionString = "<Your connection string>",
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true
});

Console.WriteLine(db.Ado.IsValidConnection());

Method B: Set Register(useCustomDbType: false) and DbType = DbType.OceanBaseForOracle.

An output of True indicates that SqlSugar + OceanBase.ManagedDataAccess + OceanBase Oracle-compatible mode is successfully integrated.