SQLServer 批量入库 跨月 不能入库 返回

数据库：SqlServer

Sqlsugar：.Net Framework 5.1.

数据库连接字符串：    "DefaultConnection": "Data Source=10.3.16.59; Initial Catalog=pltest;User Id=sa;Password=Db@877350;Encrypt=True;TrustServerCertificate=True;MultipleActiveResultSets=True;",

跨月的时候会产生这种情况

错误消息：

2024-12-01 00:01:36.041 +01:00 [INF] port[3736] [43014] 2024-12-01 00:00:11 6600000000A80600008501000003000300CBC633

2024-12-01 00:01:36.041 +01:00 [INF] port[3736] [43014] 2024-12-01 00:00:11 6600000000A8060005013F00002C00000004F00300FDB8C0F000ECC0323CFF060000370F3F00003DF2000261DD000A674BA7090000D503300C32B3E633

2024-12-01 00:01:45.447 +01:00 [ERR] The given value of type Int64 from the data source cannot be converted to type datetime of the specified target column.

2024-12-01 00:01:45.447 +01:00 [INF] LightHis total=7604982 batchCount=1253

这个错误信息表明在使用SqlSugar ORM框架将数据插入或更新到数据库时，尝试将一个类型为 Int64（即 long 类型）的值转换为目标表中类型为 datetime 的列时失败了。这通常是因为源数据和目标列的数据类型不兼容

处理逻辑：解包后构造数据 ==>  构造批量可以入库的数据 ==> 执行入库程序，对应的类和处理代码如下：

// 解包后的DTO数据

    public class LightXJ

    {

        #region 附加字段

        public string UID { get; set; }

        public long TunnelLight_ID { get; set; }

        public int AlmLevel_ID { get; set; }

        public string AckSend { get; set; }

        public string PowerModel_TX { get; set; }

       // 时间字段

        public DateTime SampleTime_Value { get; set; }

        public DateTime InspectSuccess_DT { get; set; }

        public string RemoteEndPoint { get; set; }

        public int DataSource { get; set; }

        public DateTime LocalDate { get; set; }

        public int IsDay { get; set; }

        public int? ColorTmpValue { get; set; }

        public int? ColorTmpMinValue { get; set; }

        public int? ColorTmpMaxValue { get; set; }

        public int? ColorTmpD1 { get; set; }

        public string DeviceType { get; set; }

        public int TempTimePlanIsExcute { get; set; }

        public int PowerFreq { get; set; }

        public string ID { get; set; }

        public string Voltage { get; set; }

        public string Current { get; set; }

        public string Power { get; set; }

        public string DirectDimming { get; set; }

        public string DefaultDimming { get; set; }

        public string DimmingTime { get; set; }

        public string GroupNumber { get; set; }

        public string NodeStatus { get; set; }

        public string FirmwareVersion { get; set; }

        public string LuminousIntensity { get; set; }

        public string WorkingTime { get; set; }

        public string PowerConsumption { get; set; }

        public string Temperature { get; set; }

        public string LightningCount { get; set; }

        public int LightningCount_Value { get; set; }

        public string PowerFactor { get; set; }

        public string PositionRec { get; set; }

    }

// 构造批量入库函数

        public static SqlModels.LightHisByP  BuildLightHisByPItem(LightXJ node)

        {

            var itemHis = new SqlModels.LightHisByP();

            itemHis.HisId = SnowFlakeSingle.instance.getID();

            itemHis.PartitionId = DataHelper.GenPartitionId();

            itemHis.LightId = node.TunnelLight_ID;

            itemHis.Address = node.Address;

           // 时间字段

            itemHis.SampTime = node.SampleTime_Value;

            itemHis.LocalDate = node.LocalDate;

            itemHis.DataSource = node.DataSource;

            itemHis.RemoteEndPoint = node.RemoteEndPoint;

            itemHis.AlmLevelId = node.AlmLevel_ID;

            itemHis.IsDay = node.IsDay;

            itemHis.UpUID = node.UID;

            itemHis.ColorTmpD1 = node.ColorTmpD1;

            itemHis.ColorTmpMinValue = node.ColorTmpMinValue;

            itemHis.ColorTmpMaxValue = node.ColorTmpMaxValue;

            itemHis.ColorTmpValue = node.ColorTmpValue;

            itemHis.DeviceType = node.DeviceType;

            itemHis.TempTimePlanIsExcute = node.TempTimePlanIsExcute;

            itemHis.PowerFreq = node.PowerFreq;

            itemHis.UploadData = node.UpLoadData;

            #endregion

            return itemHis;

        }

// 数据库实体类

using SqlSugar;

using System;

namespace LedServer.SqlModels

{

    [SplitTable(SplitType.Month)]

    [SugarTable("LightHis_{year}{month}{day}")]

    [SugarIndex("i_index1",

    nameof(LightHisByP.SampTime), OrderByType.Desc,

    nameof(LightHisByP.LightId), OrderByType.Asc

)]

    public class LightHisByP

    {

        [SugarColumn(ColumnName = "History_ID", IsPrimaryKey = true)]

        public long HisId { get; set; }

        [SugarColumn(ColumnName = "Partition_ID")]

        public long PartitionId { get; set; }

        // 时间字段 都是DateTime类型

        [SugarColumn(ColumnName = "SampTime_DT")]

        [SplitField]

        public DateTime SampTime { get; set; }

        [SugarColumn(ColumnName = "LocalDate", IsNullable = true)]

        public DateTime? LocalDate { get; set; }

        [SugarColumn(ColumnName = "TunnelLight_ID")]

        //[SugarColumn(ColumnName = "TunnelLight_ID", IndexGroupNameList = new string[] { "Index1" })]

        public long LightId { get; set; }

        [SugarColumn(ColumnName = "CurrentFeatureValue_NR", IsNullable = true)]

        public double? Current { get; set; }

        [SugarColumn(ColumnName = "VoltageFeatureValue_NR", IsNullable = true)]

        public double? Voltage { get; set; }

        [SugarColumn(ColumnName = "DimmingFeatureValue_NR", IsNullable = true)]

        public double? DimmingValue { get; set; }

        [SugarColumn(ColumnName = "PowerFeatureValue_NR", IsNullable = true)]

        public double? Power { get; set; }

        [SugarColumn(ColumnName = "LuminousIntensity_NR", IsNullable = true)]

        public double? LuminousIntensity { get; set; }

        [SugarColumn(ColumnName = "Temperature_NR", IsNullable = true)]

        public double? Temperature { get; set; }

        [SugarColumn(ColumnName = "PowerConsumption_NR", IsNullable = true)]

        public double? PowerConsumption { get; set; }

        [SugarColumn(ColumnName = "WorkingTimeInMinute_NR", IsNullable = true)]

        public double? WorkingTimeMins { get; set; }

        [SugarColumn(ColumnName = "VehicleFlow_NR", IsNullable = true)]

        public double? VehicleFlow { get; set; }

        [SugarColumn(ColumnName = "VehicleSpeed_NR", IsNullable = true)]

        public double? VehicleSpeed { get; set; }

        [SugarColumn(ColumnName = "FirmwareVersion_NR", IsNullable = true)]

        public int FirmwareVersion { get; set; }

        [SugarColumn(ColumnName = "AlmLevel_ID", IsNullable = true)]

        public int AlmLevelId { get; set; }

        [SugarColumn(ColumnName = "PowerFactor_NR", IsNullable = true)]

        public double? PowerFactor { get; set; }

        [SugarColumn(ColumnName = "ChannelNumber", IsNullable = true, DefaultValue = "0")]

        public int? ChannelNumber { get; set; }

        [SugarColumn(ColumnName = "Signal_NR", IsNullable = true)]

        public int? Signal { get; set; }

        [SugarColumn(ColumnName = "Version", IsNullable = true, Length = 30)]

        public string Version { get; set; }

        [SugarColumn(ColumnName = "IMEI", IsNullable = true, Length = 64)]

        public string IMEI { get; set; }

        [SugarColumn(ColumnName = "IMSI", IsNullable = true, Length = 64)]

        public string IMSI { get; set; }

        [SugarColumn(ColumnName = "ICCID", IsNullable = true, Length = 64)]

        public string ICCID { get; set; }

        [SugarColumn(ColumnName = "bandNo", IsNullable = true)]

        public int? BandNo { get; set; }

        [SugarColumn(ColumnName = "State", IsNullable = true)]

        public int? State { get; set; }

        [SugarColumn(ColumnName = "GpsInfo", IsNullable = true, Length = 100)]

        public string GpsInfo { get; set; }

        [SugarColumn(ColumnName = "TimeZone_CD", IsNullable = true, Length = 100)]

        public string TimeZone { get; set; }

        [SugarColumn(ColumnName = "LightningCount", IsNullable = true, DefaultValue = "0")]

        public int? LightningCount { get; set; }

        [SugarColumn(ColumnName = "IsDay", IsNullable = true, DefaultValue = "0")]

        public int? IsDay { get; set; }

        [SugarColumn(ColumnName = "DataSource", IsNullable = true, DefaultValue = "0")]

        public int? DataSource { get; set; }

        [SugarColumn(ColumnName = "RemoteEndPoint", IsNullable = true, Length = 60)]

        public string RemoteEndPoint { get; set; }

        [SugarColumn(ColumnName = "Address", IsNullable = true, Length = 30)]

        public string Address { get; set; }

        [SugarColumn(ColumnName = "upUID", IsNullable = true, Length = 20)]

        public string UpUID { get; set; }

        [SugarColumn(ColumnName = "UploadData", IsNullable = true, ColumnDataType = StaticConfig.CodeFirst_BigString)]

        public string UploadData { get; set; }

        [SugarColumn(ColumnName = "LightNet", IsNullable = true, Length = 2)]

        public string LightNet { get; set; }

        [SugarColumn(ColumnName = "CSQ", IsNullable = true, Length = 30)]

        public string CSQ { get; set; }

        [SugarColumn(ColumnName = "ColorTmpMinValue", DefaultValue = "2500", IsNullable = true)]

        public int? ColorTmpMinValue { get; set; }

        [SugarColumn(ColumnName = "ColorTmpMaxValue", DefaultValue = "6500", IsNullable = true)]

        public int? ColorTmpMaxValue { get; set; }

        [SugarColumn(ColumnName = "ColorTmpValue", IsNullable = true)]

        public int? ColorTmpValue { get; set; }

        [SugarColumn(ColumnName = "ColorTmpD1", IsNullable = true)]

        public int? ColorTmpD1 { get; set; }

        [SugarColumn(ColumnName = "DeviceType", IsNullable = true, Length = 10)]

        public string DeviceType { get; set; }

        [SugarColumn(ColumnName = "TempTimePlanIsExcute", DefaultValue = "0", IsNullable = true)]

        public int? TempTimePlanIsExcute { get; set; }

        [SugarColumn(ColumnName = "PowerFreq", DefaultValue = "0", IsNullable = true)]

        public int? PowerFreq { get; set; }

    }

}

入库代码：

        public static async Task<string> BatchInsertLightHisByPAsync(List<LightXJ> nodeList)

        {

            try

            {

                var list = new List<SqlModels.LightHisByP>();

                var count = 0;

                foreach (var m in nodeList)

                {

                    var item = LightXJService.BuildLightHisByPItem(m);

                    list.Add(item);

                }

                if (nodeList.Count >= 800)

                {

                    count = await DbContext.Db.Fastest<SqlModels.LightHisByP>()

                        .SplitTable().BulkCopyAsync(list);

                }

                else

                {

                    count = await DbContext.Db.Insertable<SqlModels.LightHisByP>(list)

                        .SplitTable().ExecuteCommandAsync();

                }

                if (count <= 0)

                {

                    return $"BatchInsert LightHis fail";

                }

                return string.Empty;

            }

            catch(Exception ex)

            {

                return ex.Message;

            }

        }