Oracle 使用Update<T>().SetSource(entitys)批量更新报错

kklldog commented 3 years ago

问题描述及重现步骤：

使用fsql.Update().SetSource(entitys) 进行批量更新的时候出现错误。 UpdateTest1 源: DataAccesserTests.cs 行 755 持续时间: 1.2 秒

消息: Test method Medicalsystem.IntegrationPortal.Framework.Data.Tests.DataAccesserTests.UpdateTest1 threw exception: System.Exception: ORA-12704: 字符集不匹配 ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-12704: 字符集不匹配堆栈跟踪: OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, Boolean isFromEF) OracleCommand.ExecuteNonQuery() AdoProvider.ExecuteNonQuery(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) --- End of inner exception stack trace --- AdoProvider.LoggerException(IObjectPool1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException) AdoProvider.ExecuteNonQuery(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) UpdateProvider1.RawExecuteAffrows() UpdateProvider1.SplitExecuteAffrows(Int32 valuesLimit, Int32 parameterLimit) OracleUpdate1.ExecuteAffrows() DataAccesser.Update[T](IEnumerable`1 entitys) 行 147 DataAccesserTests.UpdateTest1() 行 788

追踪的SQL为： UPDATE "TTT_TEST" SET "AGE" = CASE "ID" WHEN N'7e1995f3-b4a8-4166-9bdd-c770c0cbd94a' THEN 11 WHEN N'6a2efe34-bc6e-48a9-80fe-48720c19534e' THEN 11 END, "NAME" = CASE "ID" WHEN N'7e1995f3-b4a8-4166-9bdd-c770c0cbd94a' THEN 'testyyyy' WHEN N'6a2efe34-bc6e-48a9-80fe-48720c19534e' THEN 'testyyyy' END, "BIRTH_DAY" = CASE "ID" WHEN N'7e1995f3-b4a8-4166-9bdd-c770c0cbd94a' THEN to_timestamp('2021-06-07 11:22:05.000000','YYYY-MM-DD HH24:MI:SS.FF6') WHEN N'6a2efe34-bc6e-48a9-80fe-48720c19534e' THEN to_timestamp('2021-06-07 11:22:05.000000','YYYY-MM-DD HH24:MI:SS.FF6') END, "POINT" = CASE "ID" WHEN N'7e1995f3-b4a8-4166-9bdd-c770c0cbd94a' THEN 2.2 WHEN N'6a2efe34-bc6e-48a9-80fe-48720c19534e' THEN 2.2 END, "SEX" = CASE "ID" WHEN N'7e1995f3-b4a8-4166-9bdd-c770c0cbd94a' THEN 1 WHEN N'6a2efe34-bc6e-48a9-80fe-48720c19534e' THEN 1 END WHERE ("ID" IN ('7e1995f3-b4a8-4166-9bdd-c770c0cbd94a','6a2efe34-bc6e-48a9-80fe-48720c19534e'))

实体模型：

    [Table(Name = "TTT_TEST")]
    public class TestClass
    {
        [Column(Name = "ID", IsPrimary = true)]
        public string No { get; set; }

        public int? Age { get; set; }
        public string Name { get; set; }
        [Column(Name = "BIRTH_DAY")]
        public DateTime? Birthday { get; set; }
        public decimal Point { get; set; }
        public Sex? Sex { get; set; }
    }

数据库表：

-- Table structure for TTT_TEST

DROP TABLE "CPCUSER"."TTT_TEST"; CREATE TABLE "CPCUSER"."TTT_TEST" ( "ID" VARCHAR2(36 BYTE) NOT NULL, "NAME" VARCHAR2(255 BYTE), "AGE" NUMBER, "BIRTH_DAY" DATE, "POINT" NUMBER(8,1), "SEX" NUMBER(1,0) ) LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT ) PARALLEL 1 NOCACHE DISABLE ROW MOVEMENT ;

-- Primary Key structure for table TTT_TEST

ALTER TABLE "CPCUSER"."TTT_TEST" ADD CONSTRAINT "SYS_C0098604" PRIMARY KEY ("ID");

测试代码：

        [TestMethod()]
        public void UpdateTest1()
        {
            var test = new TestClass
            {
                No = Guid.NewGuid().ToString(),
                Name = "testxxx",
                Age = 10,
                Sex = Sex.M,
                Birthday = DateTime.Now,
                Point = 1.1M
            };
            var test2 = new TestClass
            {
                No = Guid.NewGuid().ToString(),
                Name = "testxxx",
                Age = 10,
                Sex = Sex.F,
                Birthday = DateTime.Now,
                Point = 1.1M
            };
            var list = new List<TestClass> { test, test2 };
            var result = _fsql.Insert<TestClass>(list).ExecuteAffrows() > 0;
            Assert.IsTrue(result);

            var entitys = _fsql.Select<TestClass>().Where(x => x.Name == "testxxx").ToList();
            //update
            entitys.ForEach(entity => {
                entity.Name = "testyyyy";
                entity.Age = 11;
                entity.Point = 2.2M;
                entity.Sex = Sex.M;
            });
            var acc = new DataAccesser(_fsql);
            **result = acc.Update<TestClass>(entitys); //批量更新报错 ！！！**
            Assert.IsTrue(result);

            var entitys1 = _fsql.Select<TestClass>().Where(x => x.Name == "testyyyy").ToList();
            entitys1.ForEach(item => {
                var oldEntity = entitys.Find(x => x.No == item.No);
                Assert.IsNotNull(oldEntity);
                Assert.AreEqual(item.No, oldEntity.No);
                Assert.AreEqual(item.Name, oldEntity.Name);
                Assert.AreEqual(item.Age, oldEntity.Age);
                Assert.AreEqual(item.Sex, oldEntity.Sex);
                Assert.IsNotNull(item.Birthday);
                Assert.AreEqual(item.Birthday.Value.ToString("yyyy-MM-dd HH:mm:ss"), oldEntity.Birthday.Value.ToString("yyyy-MM-dd HH:mm:ss"));
                Assert.AreEqual(item.Point, oldEntity.Point);
            });
        }

其中DataAccesser的Update(entities)封装为：

        public bool Update<T>(IEnumerable<T> entitys) where T : class
        {
            return  _fsql.Update<T>().SetSource(entitys).ExecuteAffrows() > 0;
        }

数据库的具体版本

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

安装的包

FreeSql 2.5.200
FreeSql.FreeSql.Provider.Oracle 2.5.200

.net framework/. net core？及具体版本

PS C:\Users\mjzhou> dotnet --info
.NET SDK (反映任何 global.json):
 Version:   5.0.202
 Commit:    db7cc87d51

运行时环境:
 OS Name:     Windows
 OS Version:  10.0.19042
 OS Platform: Windows
 RID:         win10-x64
 Base Path:   C:\Program Files\dotnet\sdk\5.0.202\

Host (useful for support):
  Version: 5.0.5
  Commit:  2f740adc14

.NET SDKs installed:
  2.2.207 [C:\Program Files\dotnet\sdk]
  5.0.202 [C:\Program Files\dotnet\sdk]

.NET runtimes installed:
  Microsoft.AspNetCore.All 2.1.27 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.All 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.27 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 3.1.14 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.AspNetCore.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.27 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 2.2.8 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 3.1.14 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.NETCore.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]
  Microsoft.WindowsDesktop.App 3.1.14 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]
  Microsoft.WindowsDesktop.App 5.0.5 [C:\Program Files\dotnet\shared\Microsoft.WindowsDesktop.App]

kklldog commented 3 years ago

补充一下：如果指定主键的DataType = varchar2(36)异常就消失了。

luoyunchong commented 3 years ago

类型不对。string类型在oracle映射的是nvarchar2(36)。因为生成的sql中有N,执行sql时会报错。所以当用string类型时在oracle中换成nvarchar2。可以直接用Guid类型，数据库类型换成char(36)即可。

kklldog commented 3 years ago

收到，谢谢

dotnetcore / FreeSql