将. csv导入SQL数据库时,将空字段设置为null,而不是默认值
本文关键字:null 默认值 设置 导入 csv SQL 数据库 字段 | 更新日期: 2023-09-27 18:05:17
我正在创建一个可以将.csv文件导入SQL数据库的应用程序。csv文件被读取为7列:Id、InvoiceAmount、InvoiceDate、InvoiceNumber、DeniedDate、DeniedReasonId和DeniedNotes。.csv中的每一行都有一个Id,但是接下来的六个字段中只有三个将被填充。或 DeniedDate, DeniedReasonId, DeniedNotes.
[Table("TemporaryCsvUpload")]
public class OutstandingCredit
{
[CsvColumn(FieldIndex = 1, CanBeNull = false)]
public string Id { get; set; }
[CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]
public DateTime InvoiceDate { get; set; }
[CsvColumn(FieldIndex = 3)]
public string InvoiceNumber { get; set; }
[CsvColumn(FieldIndex = 4, OutputFormat = "C")]
public decimal InvoiceAmount { get; set; }
[CsvColumn(FieldIndex = 5, OutputFormat = "dd MMM HH:mm:ss")]
public DateTime DeniedDate { get; set; }
[CsvColumn(FieldIndex = 6)]
public int DeniedReasonId { get; set; }
[CsvColumn(FieldIndex = 7)]
public string DeniedNotes { get; set; }
}
那么问题来了:在.csv的每一行中,InvoiceDate或DeniedDate将为空。当这种情况发生时,我希望数据库中相应的字段保持为空。但是,它的默认值是1/1/0001 12:00。此外,当.csv中的DeniedReasonId为空时,它默认为0。我试图保持这个空值,而不是默认值。
这就是我使用LinqToCsv来循环。csv格式中的值的地方:
var cc = new CsvContext();
var filePath = uploadFile(csvFile.InputStream);
var model = cc.Read<OutstandingCredit>(filePath, inputFileDescription);
try
{
var entity = new ManagementEntities();
foreach (var item in model)
{
var tc = new TemporaryCsvUpload
{
Id = item.Id,
InvoiceAmount = item.InvoiceAmount,
InvoiceDate = item.InvoiceDate,
InvoiceNumber = item.InvoiceNumber,
DeniedDate = item.DeniedDate,
DeniedReasonId = item.DeniedReasonId,
DeniedNotes = item.DeniedNotes
};
entity.TemporaryCsvUploads.Add(tc);
任何帮助都会很感激。谢谢!
如果您希望CSV文件中的空值转换为null
,则必须将属性更改为Nullable
类型(DateTime?
, decimal?
和int?
)。您当前看到1/1/0001 12:00 AM和0的原因是因为它们是DateTime
和int
的默认值。通过将它们更改为Nullable
类型,默认值将更改为null
(或者更准确地说,更改为Nullable<T>
,其中HasValue
为假,但c#基本上将其视为null
)。
[Table("TemporaryCsvUpload")]
public class OutstandingCredit
{
[CsvColumn(FieldIndex = 1, CanBeNull = false)]
public string Id { get; set; }
[CsvColumn(FieldIndex = 2, OutputFormat = "dd MMM HH:mm:ss")]
public DateTime? InvoiceDate { get; set; }
[CsvColumn(FieldIndex = 3)]
public string InvoiceNumber { get; set; }
[CsvColumn(FieldIndex = 4, OutputFormat = "C")]
public decimal? InvoiceAmount { get; set; }
[CsvColumn(FieldIndex = 5, OutputFormat = "dd MMM HH:mm:ss")]
public DateTime? DeniedDate { get; set; }
[CsvColumn(FieldIndex = 6)]
public int? DeniedReasonId { get; set; }
[CsvColumn(FieldIndex = 7)]
public string DeniedNotes { get; set; }
}
您还必须确保TemporaryCsvUpload
中相应的类型也是Nullable
,如果它们还没有。