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>;
Note
user idspecifies the OceanBase Oracle account.databaseis 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.
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 equivalentInstanceFactoryconfiguration) once when the application starts. - Uniformly encapsulate
SqlSugarScopeas a singleton or managed by DI. - Continue to use the original
SqlSugarcharacteristics and writing methods for entities. - The original
Queryable,Insertable,Updateable, andDeleteablemethods 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.CustomwithCustomNamespaceandCustomDbName(as described above). - Option B:
DbType.OceanBaseForOracle, which requires theSqlSugarCoreto provide this enum and settingCustomDllNameto 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.
