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

The underlying connection driver of SqlSugarCore.Oceanbase is OceanBase.ManagedDataAccess, which allows SqlSugar to access OceanBase Database in Oracle mode through the dotnet driver.

Installation

The installation method is the same as the basic driver. For more information, see Package reference and usage.

You also need to reference SqlSugarCore.OceanBase (which usually includes SqlSugarCore and other dependencies):

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

Connection string

Use the Oracle mode format supported by OceanBase.ManagedDataAccess, and replace the placeholders with actual values from your environment. Do not directly copy the literal values from 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 and CharacterSet):

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

Note

user id specifies the OceanBase Oracle account.
database is recommended to be specified. The metadata capabilities of the current provider rely on it to identify the current schema.
The default port is usually 2881.
Starting from V1.3.0, Oracle mode supports encoding and decoding types such as VARCHAR2 and CHAR in GBK using Character Set=gbk. If not specified by default, UTF-8 is used. NCHAR and NVARCHAR2 are not affected by this parameter.
To use GBK with SqlSugar, please upgrade SqlSugarCore.OceanBase to V1.1.0 or later. For complete parameter descriptions, see Connection string.

Access method

InstanceFactory.CustomDllName and similar are static global configurations. We recommend setting them only once during application startup to avoid loading errors caused by multiple overwrites.

Method	`ConnectionConfig.DbType`	Description
Method A (Recommended)	`DbType.Custom`	Explicitly set `CustomDllName`, `CustomNamespace`, and `CustomDbName`. This method does not depend on whether `SqlSugarCore` includes `DbType.OceanBaseForOracle`.
Method B	`DbType.OceanBaseForOracle`	Requires that `SqlSugarCore` includes this enumeration and sets `CustomDllName` 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 method is closer to the built-in plugin enumeration of SqlSugar. It requires that the used SqlSugarCore includes DbType.OceanBaseForOracle (otherwise, the code 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 }
});

Some versions of SqlSugar automatically set InstanceFactory.CustomDllName when initializing DbType.OceanBaseForOracle. We still recommend explicitly setting it once during startup to reduce troubleshooting costs caused by version differences.

Startup encapsulation (`SqlSugarOceanBaseBootstrap`)

If you want to share the same startup code for both 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`

We recommend using SqlSugarScope in web or multi-threaded scenarios. First, call SqlSugarOceanBaseBootstrap.Register(...) (or manually set InstanceFactory), and then create ConnectionConfig.

Method A example:

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); };
});

Method B example: Set Register(useCustomDbType: false) and set DbType = DbType.OceanBaseForOracle. The rest is similar to Method A.

You can also directly use SqlSugarClient in console applications with the same configuration.

Complete example

The following example demonstrates table creation, data insertion, query, update, pagination, deletion, and transactions (default using Method A):

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 you use Method B, set Register(useCustomDbType: false) and change DbType to DbType.OceanBaseForOracle.

ADO and parameterized queries

In SQL, @parameter name will be processed in Oracle style. We recommend using 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 directly initialize tables based on entities:

db.CodeFirst.InitTables<DemoUser>();

Common combination methods (modify the table names as needed):

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");

If you want to perform simple library and table inspections or code generation, you can directly reuse this capability.

Recommended usage

In a business project, it is recommended to organize as follows:

Call SqlSugarOceanBaseBootstrap.Register(...) (or equivalent InstanceFactory configuration) once when the application starts.
Uniformly encapsulate SqlSugarScope as a singleton or managed by DI.
Continue to use the original SqlSugar characteristics and writing methods for entities.
The original Queryable, Insertable, Updateable, and Deleteable methods generally do not need to be modified.

This approach allows the business layer to only replace the driver and register the provider, while keeping the rest of the SqlSugar code unchanged.

Current limitations and considerations

1. `DbType` can be set to `DbType.Custom` or `DbType.OceanBaseForOracle`

Option A: DbType.Custom with CustomNamespace and CustomDbName (as described above).
Option B: DbType.OceanBaseForOracle, which requires the SqlSugarCore to provide this enum and setting CustomDllName to load the extension assembly.

If DbType.OceanBaseForOracle is not available (e.g., in older versions of SqlSugarCore), use Option A.

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

The OceanBaseForOracleFastBuilder in the adapter layer will currently throw an exception, so do not rely on BulkCopy capabilities.

3. It is recommended to continue using parameterized SQL

Although the adapter layer supports automatic conversion from @name to :name, it is still recommended to use parameterized queries and avoid SQL string concatenation.

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

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

using OceanBase;

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

Minimal working example

Option 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());

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

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