本文介绍了如何将.NET ORM框架业务从Oracle数据库迁移到KES数据库，包含EF6、EFCORE、NHIBERNATE、SqlSugar、FreeSql、Dapper、Chloe等框架的方言包说明和框架迁移说明。
1 EF6迁移
对于EF6而言，需要在项目中引用Kdbndp.dll和EntityFramework6.Kdbndp.dll（金仓提供的EF6方言包），然后在App.config中修改工程配置文件配置信息，如下所示：
Oracle配置信息 |
<entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" /> <providers> <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" /> </providers> </entityFramework> <system.data> <DbProviderFactories> < add name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler,Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" /> </DbProviderFactories> </system.data> <connectionStrings> <add name="TestKbContext" connectionString="User Id=your_username;Password=your_password;Data Source=your_datasource" providerName="oracle.manageddataaccess.client" /> </connectionStrings> |
KES配置信息 |
<entityFramework> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" /> <providers> <provider invariantName="Kdbndp" type="Kdbndp.KdbndpServices, EntityFramework6.Kdbndp"/> </providers> </entityFramework> <system.data> <DbProviderFactories> <remove invariant="Kdbndp"></remove> <add name="Kdbndp Data Provider" invariant="Kdbndp" description=".Net Framework Data Provider for KingbaseES Server" type="Kdbndp.KdbndpFactory, Kdbndp" /> </DbProviderFactories> </system.data> <connectionStrings> <add name="TestKbContext" connectionString="Server=10.12.1.30;Database=test; User Id=system;Password=123456;Port=52233;Pooling=True;Maximum Pool Size=3;Timeout=1024" providerName="Kdbndp" /> </connectionStrings> |
EF6例程不用做迁移修改，直接可以运行
例程如下： //模型 public class Modules : DbContext { public Modules(): base("name=TestKbContext") { } public DbSet<Students> KB_Students { get; set; } } public class Students { [Key] public int Id { get; set; } public string name { get; set; } public int addressId { get; set; } } //查询 static void Test_EF6_with_Module_Seldata() { using (var db = new Modules()) { var a = db.KB_Address.AsQueryable().ToList(); var b = db.KB_Students.AsQueryable().ToList(); var query = (from S in db.KB_Students join A in db.KB_Address on S.Id equals A.Id where S.Id > 5 select new { Stu_id = S.Id, Stu_name = S.name, Stu_address = A.address, Stu_City = A.city }).ToArray(); foreach (var q in query) { Console.WriteLine("条件筛选：{0}, {1}, {2}, {3}", q.Stu_id, q.Stu_name, q.Stu_address, q.Stu_City); } } } |
2 EFCore迁移
对于EFCore而言，需要在项目中引用Kdbndp.EntityFrameworkCore.KingbaseES.dll（金仓对外提供的EFCore方言包）和Kdbndp.dll，然后修改DbContext中的配置信息即可。
主要修改：UseOracle改为UseKdbndp。
namespace ConsoleApp_Efcode { public class Modules : DbContext { public DbSet<Author> Authors { get; set; } //oracle /* protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseOracle(@"Data Source=(DESCRIPTION=(ADDRESS= (PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1111))(CONNECT_DATA=(SID=orclcdb)));User Id=system;Password=123456;"); */ //KES protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder.UseKdbndp(@"Server=10.12.1.30;User ID=system;Password=123456;Database = test; Port=52220"); } public class Author { /// <summary> /// 主键 /// </summary> [Key] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] [Column("Id")] public int Id { get; set; } /// <summary> /// 姓名 /// </summary> [StringLength(20)] [Column("Name")] public string Name { get; set; } /// <summary> /// 性别 /// </summary> [Column("Gender")] public bool? Gender { get; set; } /// <summary> /// 时间 /// </summary> [Column("Dt")] public DateTime Dt { get; set; } /// <summary> /// GUID /// </summary> [Column("Uuid")] public Guid Uuid { get; set; } public Author() { Id = 0; Name = String.Empty; Gender = false; Dt = DateTime.MinValue; Uuid = Guid.Empty; } } } static void Print(Modules db, String strShowInfo) { Console.WriteLine("查询数据"); foreach (var item in db.Authors) { Console.WriteLine("数据行："+item.Id + " " + item.Name + " " + item.Dt); } Console.WriteLine("data search over...."); } //查询数据 static void Select() { using (var dbContext = new Modules()) { string[] a = { "a", "ABC" }; Print(dbContext, "select * from Authors"); } } |
3 NHibernate迁移
对于NHibernate而言，需要在项目中引用KdbndpDialect.dll（金仓对外提供的NHibernate方言包）和Kdbndp.dll，然后修改hibernate.cfg.xml中的配置信息即可。
Oracle配置信息 |
<?xml version="1.0" encoding="utf-8" ?> <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2"> <session-factory> <!-- 数据库提供程序设置 --> <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property> <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property> <!-- 数据库连接字符串 --> <property name="connection.connection_string"> User ID=your_username;Password=your_password;Data Source=your_datasource; </property> </hibernate-configuration> |
KES配置信息 |
<property name="connection.driver_class">NHibernate.Driver.KdbndpDriver, KdbndpDialect, Version=1.0.0.0, Culture=neutral, PublicKeyToken=50a10c7fb0162091</property> <property name="dialect">NHibernate.Dialect.KdbndpDialect, KdbndpDialect, Version=1.0.0.0, Culture=neutral, PublicKeyToken=50a10c7fb0162091</property> <property name="connection.connection_string">Server=10.10.10.10;Database=test;User ID=system;Password=123456;Port=54321;</property> |
例程如下： static int ExecuteStatement(string sql) { string hibernateConfigFile = "hibernate.cfg.xml"; Console.WriteLine(sql); Configuration result = new Configuration(); if (hibernateConfigFile != null) result.Configure(hibernateConfigFile); using (IConnectionProvider prov = ConnectionProviderFactory.NewConnectionProvider(result.Properties)) { var conn = prov.GetConnection(); try { using (var tran = conn.BeginTransaction()) using (var comm = conn.CreateCommand()) { comm.CommandText = sql; comm.Transaction = tran; comm.CommandType = System.Data.CommandType.Text; int res = comm.ExecuteNonQuery(); tran.Commit(); DataTable dt = conn.GetSchema("TABLES"); return res; } } Finally { prov.CloseConnection(conn); } } } |
4 SqlSugar迁移
SqlSugar 是比较成熟的.NET 开源ORM框架，由糖果大数据科技团队维护和更新，开箱即用最易上手的ORM。SqlSugar和人大金仓有深度整合，支持人大金仓多个数据库模式，还将支持存储过程中返回游标等功能。
SqlSugar对外发布Nuget包，SqlSugarCore和SqlSugarCore.Kdbndp（金仓驱动）。在迁移数据库过程中，只需要修改SqlSugar的配置信息即可，其他代码不用修改。
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { //Oracle /* DbType = DbType.Oracle, */ //KES DbType = DbType.Kdbndp, ConnectionString = "Server=127.0.0.1;port=54321;UID=system;PWD=123456;database=test", IsAutoCloseConnection = true, MoreSetting = new ConMoreSettings() { //金仓数据库不同的模式在这里设置 DataBaseModel = DbType.Oracle } }) |
5 FreeSql迁移
对于FreeSql而言要迁移金仓数据库，需要安装FreeSql.Provider.KingbaseES.dll（金仓对外的FreeSql方言包）和Kdbndp.dll。
对于FreeSql迁移，只需要初始化FreeSql对象时加载金仓驱动即可。
//初始化对象 static IFreeSql InitObject() { //Oracle /* var connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=10.10.10.10)(PORT=1111))(CONNECT_DATA=(SID=orclcdb)));User Id=system; Password=123456;"; IFreeSql fSql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.Oracle, connectionString) .UseAutoSyncStructure(true) .Build(); */ //KES var connectionString = "User ID=system;Password=123;Host=10.12.1.30;Port=54321; Database=test;"; IFreeSql fSql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.KingbaseES, connectionString) .UseAutoSyncStructure(true) .Build(); return fSql; } //模型 class Student { [Column(IsIdentity = true, IsPrimary = true)] public int Id { get; set; } public string Name { get; set; } public DateTime Entrance { get; set; } public int ClassId { get; set; } } //创建表 static void CreateTable() { IFreeSql fSql = InitObject(); fSql.CodeFirst.SyncStructure<Student>(); } |
6 Dapper迁移
Dapper是一款轻量级的ORM，责数据库和编程语言之间的映射，Dapper本身支持多种数据库，其使用基类方法如DBConnection。
对于Dapper的迁移，只需要安装Kdbndp.dll，然后修改其连接的方法即可。
例程如下 //模型 public class Cat { public int Id { get; set; } public string Breed { get; set; } public string Name { get; set; } } //连接对象 //Oracle public static OracleConnection GetOpenOracleConnection() { const string ConnectionString ="Data Source=(DESCRIPTION=(ADDRESS= (PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1111))(CONNECT_DATA=(SID=orclcdb)));User Id=system;Password=123456;"; var conn = new OracleConnection (ConnectionString); conn.Open(); return conn; } //KES public static KdbndpConnection GetOpenKdbndpConnection() { const string ConnectionString = "Server=10.10.10.10;User ID=system;Password=123; Database=test;Port=54321"; var conn = new KdbndpConnection(ConnectionString); conn.Open(); return conn; } //执行SQL using (IDbConnection conn = GetOpenKdbndpConnection()) { conn.Execute(“create table tcat ( id serial not null, breed character varying(20) not null, name character varying (20) not null);"); var result = conn.Execute(“select * from tcat where id=@Id", new { id = 5 }); } |
7 Chloe迁移
Chloe 一款轻量、高效的.NET C# 数据库访问框架(ORM)，迁移Chloe需要在项目中安装Chloe.Kingbase.dll（金仓对外提供的Chloe方言包）和Kdbndp.dll，然后调用工厂类来构建连接。
Oracle例程 |
//定义连接创建工厂类 public class OracleConnectionFactory : IDbConnectionFactory { string _connString = null; public OracleConnectionFactory(string connString) { this._connString = connString; } public IDbConnection CreateConnection() { OracleConnection conn = new OracleConnection(this._connString); return conn; } } //定义模型 public class cf_p_user { public int id { get; set; } public string name { get; set; } public DateTime createtime { get; set; } } //创建连接 OracleContext context = new OracleContext(new OracleConnectionFactory(ConnString)); //插入数据 public static void InsertData(OracleContext context) { int id = (int)context.Insert<cf_p_user>(() => new cf_p_user() { id = 10, name = "name10", createtime = DateTime.Now }); } |
KES例程 |
//定义连接创建工厂类 public class KingbaseConnectionFactory : IDbConnectionFactory { string _connString = null; public KingbaseConnectionFactory(string connString) { this._connString = connString; } public IDbConnection CreateConnection() { KdbndpConnection conn = new KdbndpConnection(this._connString); return conn; } } //定义模型 public class cf_p_user { public int id { get; set; } public string name { get; set; } public DateTime createtime { get; set; } } //创建连接 KingbaseContext context = new KingbaseContext(new KingbaseConnectionFactory(ConnString)); //插入数据 public static void InsertData(KingbaseContext context) { int id = (int)context.Insert<cf_p_user>(() => new cf_p_user() { id = 10, name = "name10", createtime = DateTime.Now }); } |
