首页 > SqlSugar

批量插入时，报错“Object cannot be cast from DBNull to other types.” 返回

SqlSugar

 4  58

ColdLight 发布于3天前

悬赏：5 飞吻

程序做数据采集处理，从sqlserver读取数据，往mysql批量插入DataTable类型数据时，报错“Object cannot be cast from DBNull to other types.”请教如何处理？

——SqlSugar版本：5.0.9；

——mysql版本：5.7；

——源表与目标表结构一样。

主要代码如下：

//从源表获取数据（DataTable）

var dataRD = await dbSource.Ado.GetDataTableAsync(pagerSQL)；

//批量写入目标表，这句报错了

await dbTarget.Fastest<System.Data.DataTable>().AS(dataRD.TableName).PageSize(10000).BulkCopyAsync(dataRD.TableName, dataRD);

如果改成使用以下方式则不会出错，但是插入效率太低了。

var x = dbTarget.Storageable(dataRD).WhereColumns(pkeyName).ToStorage()；

x.AsInsertable.ExecuteCommand();

热忱回答（4）

fate sta VIP0
3天前

可以提供一个例子给我，看着像dbnull类型导入引起的

0 回复
ColdLight VIP0
3天前

大佬是需要什么例子？完整代码还是源/目标数据表结构？

0 回复
ColdLight VIP0
3天前

@fate sta：大佬是需要什么例子？完整代码还是源/目标数据表结构？

0 回复
ColdLight VIP0
3天前

CREATE TABLE [dbo].[T_Vehicle] (
[ID_VEHICLE] numeric(16) NOT NULL,
[ID_OWNER] numeric(16) NOT NULL,
[CCERTWORD] varchar(12) COLLATE Chinese_PRC_CI_AS NULL,
[OLDCCERTID] varchar(16) COLLATE Chinese_PRC_CI_AS NULL,
[CCERTID] varchar(16) COLLATE Chinese_PRC_CI_AS NULL,
[MGRSORT] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[VCLSORT] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[VCLTYPE] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[VCLRANK] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[VCLTECRANK] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[VTRDATE] datetime NULL,
[VEHICLEID] varchar(12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[VCLIDCOLOR] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[MTYPE] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[SEAT] int DEFAULT 0 NULL,
[TON] numeric(9,3) DEFAULT 0 NULL,
[CHECKSEAT] int DEFAULT 0 NULL,
[CHECKTON] numeric(9,3) DEFAULT 0 NULL,
[ENGINEID] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CHASSISID] varchar(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[FUELTYPE] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[BODYCOLOR] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[VCLLENGTH] numeric(8) NULL,
[VCLWIDTH] numeric(5) NULL,
[VCLHEIGHT] numeric(5) NULL,
[WHEELBASE] numeric(8) NULL,
[VCLSECTS] numeric(2) NULL,
[MTCTIME] smallint NULL,
[ENGPOWER] numeric(5,1) NULL,
[BUYDATE] datetime NULL,
[SETTLEDATE] datetime NULL,
[OUTMILLDATE] datetime NULL,
[MGRAREA] varchar(1000) COLLATE Chinese_PRC_CI_AS NOT NULL,
[VCLSTATE] varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[STATEDATE] datetime NOT NULL,
[STATEENDDATE] datetime NULL,
[CCERTSTATE] varchar(10) COLLATE Chinese_PRC_CI_AS DEFAULT '有效' NULL,
[FIRSTDATE] datetime NULL,
[EXPIREDATE] datetime NULL,
[DAH] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[REMARK] varchar(500) COLLATE Chinese_PRC_CI_AS NULL,
[DEPARTID] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CARDSTATE] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ZIPAREA] numeric(6) NOT NULL,
[MEMO] varchar(500) COLLATE Chinese_PRC_CI_AS NULL,
[CHKSTUDENTS] smallint NULL,
[F_VCLCENSOR] bit NULL,
[ATTACHEDBY] varchar(80) COLLATE Chinese_PRC_CI_AS NULL,
[PRTVEHICLEID] varchar(12) COLLATE Chinese_PRC_CI_AS NULL,
[PRTVCLIDCOLOR] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[EXTINFO] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[TANKVOLUME] numeric(5,1) NULL,
[TRACTIONMASS] numeric(9,3) NULL,
[VCLACCESSWAYS] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[REGDATE] datetime NULL,
[EXHAUSTEMISSION] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[F_CAMERAINSTALLED] bit NULL,
[VclXKSBH] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[VclXKDate] datetime NULL,
[VclBCPH] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[GPSInstallYH] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CZCJYQZFBID] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[YDJL] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[HxLength] numeric(5) NULL,
[HxWidth] numeric(5) NULL,
[HxHeight] numeric(5) NULL,
[GbNumber] smallint NULL,
[TS] timestamp NOT NULL,
[BiMtcLong] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[ASSESSORGAN] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[attachedbyIDCard] varchar(18) COLLATE Chinese_PRC_CI_AS NULL,
[BEGINDATE] datetime NULL,
[ICID] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[GRANTZIPAREA] decimal(6) NULL,
[ProdPlace] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[Kerbmass] numeric(9,3) NULL,
[LoadedGenquality] numeric(9,3) NULL,
[RunMiles] numeric(8,2) NULL,
[DriveType] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[OilsPerhunkms] numeric(9,3) NULL,
[EngineType] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[VclfileBh] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[GPSInstallBeginTime] datetime NULL,
[GPSInstallEndTime] datetime NULL,
[TEL] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[VCLWEIGHT] numeric(9,3) NULL,
[ICcode] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ICoprstate] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ISONLINE] bit NULL,
[last_online_time] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[BFNX] datetime NULL,
[IsWoPu] bit NULL,
[GPSInstallType] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[GPSServiceBZ] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[AxisType] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[AxisNum] int NULL,
[TireNum] int NULL,
[ISValid] smallint NULL,
[GPSISValid] smallint NULL,
[HighMType] varchar(10) COLLATE Chinese_PRC_CI_AS NULL,
[ISYellowLabel] nvarchar(8) COLLATE Chinese_PRC_CI_AS NULL,
[IsHeiLock] int NULL,
[ATTACHEDBYADDRESS] nvarchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[ONLINEMGRAREA] nvarchar(16) COLLATE Chinese_PRC_CI_AS NULL,
[IDFrame] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[CONTAINERSIZE] decimal(18) NULL,
[SADDLETOTALMASS] decimal(18) NULL,
[TyreModel] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[VEHICLEPFBZ] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[TREAD] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[MBRIEFMT] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[VINNO] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[MANUFACTURER] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[DRVPASSENGER] smallint NULL,
[USEPROPERTY] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[PIC_LEFT] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[PIC_RIGHT] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[PIC_BACK] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[ORIGORGAN] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[MGRORGAN] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[CCERTTYPE] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ICTYPE] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[TRAINPLACE] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[F_GPSINSTALLED] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[F_GPS_VALID] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[ISNEWCAR] bit NULL,
[F_STDRCDINST] int NULL,
[SAFEDEVICES] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[ATTACHEDBYIDTYPE] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ATTACHEDBYTEL] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[REGORGAN] varchar(200) COLLATE Chinese_PRC_CI_AS NULL,
[REGNO] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[DRVCARDDATE] datetime NULL,
[GPSInstallYHNO] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[GRANTORGID] numeric(18) NULL,
[DISPLACEMENT] numeric(5,1) NULL,
[COMPANYNAME] varchar(100) COLLATE Chinese_PRC_CI_AS NULL,
[JTBID] varchar(20) COLLATE Chinese_PRC_CI_AS NULL,
[ISCUSTOMIZEKY] bit NULL,
[ETC] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[GPSServiceNAME] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[GPSServiceLEVEL] varchar(50) COLLATE Chinese_PRC_CI_AS NULL,
[ISDZKY] varchar(4) COLLATE Chinese_PRC_CI_AS NULL,
[ISGSKY] bit NULL
)
GO

0 回复