将CSV转换为System.data.common.dbdatareader，以将CSV批量导入SQL [英] Convert a CSV to System.data.common.dbdatareader for bulk importing CSV into SQL

问题描述

我正在创建一个简单的.NET Core应用程序，该应用程序读取CSV文件，然后将行批量插入SQL Server.我有56列，所以，我不想创建一个实体类.我看到了很多示例，它们在其中使用SqlBulkCopy，但由于软件包问题，它们对我都不起作用.

I am creating a simple .NET Core app which reads the CSV file and bulk inserts the rows into SQL server. I have 56 columns, So, I dont want to create an entity class. I saw a lot of examples, where they use the SqlBulkCopy but none of them are working for me due to package issues.

有人可以帮助我将CSV导入SQL而不用硬编码代码中的列名吗?我的CSV与db表结构完全同步.

Can anyone help me in importing the CSV into SQL without hardcoding the column names in the code? My CSV is exactly in sync with the db table structure.

CSVHelper.CsvReader rdr = new CSVHelper.CsvReader(new StreamReader(new FileStream(@"/Users/selva/Downloads/test.csv", FileMode.Open)));
System.Data.SqlClient.SqlBulkCopy bcp = new SqlBulkCopy(builder.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction);
bcp.BatchSize = 500;
bcp.DestinationTableName = "test_table";
bcp.NotifyAfter = 500;
bcp.SqlRowsCopied += (sender, e) =>
{
    Console.WriteLine("Written: " + e.RowsCopied.ToString());
};
bcp.WriteToServer(rdr);

提前谢谢.

推荐答案

CSVReader 类未实现 IDataReader .因此，不能与一起使用SQLBulkCopy .但是，在nuget LumenWorkCsvReader 上还有另一个CsvReader可用，它实现了 IDataReader

CSVReader class you are using does not implement IDataReader.So it cannot be used with SQLBulkCopy .However there is another CsvReader available on nuget LumenWorkCsvReader which implements IDataReader

用法会像这样.(在下面的代码中测试了一个小的CSV文件)

Usage would be somthing like this.(Tested below code for a small CSV file)

using (var bcp = new SqlBulkCopy("connectionString", SqlBulkCopyOptions.UseInternalTransaction))
using (var rdr = new CsvReader(new StreamReader("data.csv"), false))
{
    bcp.BatchSize = 500;
    bcp.DestinationTableName = "test_table";
    bcp.NotifyAfter = 500;
    bcp.SqlRowsCopied += (sender, e) =>
    {
        Console.WriteLine("Written: " + e.RowsCopied.ToString());
    };
    bcp.WriteToServer(rdr);

}

您必须在顶部添加以下内容

you have to add following using on top

using LumenWorks.Framework.IO.Csv;

GitHub页面

免责声明:我与上述项目没有任何关系

Disclaimer: I have no affiliation with the projects mentioned above

这篇关于将CSV转换为System.data.common.dbdatareader，以将CSV批量导入SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！