sqlbulkcopy c＃ [英] sqlbulkcopy c#

问题描述

您好，

我想解析一个csv文件，并通过对象属性赋值将特定列写入不同表中的字段。


csv文件超过50k行和50列，我发现了SqlBulkCopy，但所有例子都包括DataTable的创建。我的解析可以读取列索引，因此我可以通过索引选择 - 创建对象并写入sql server
DB。

I would like to parse a csv file and write specific columns out of it to fields in different tables through object properties assignment.
The csv file is quite big over 50k lines and 50 columns, I found about the SqlBulkCopy however all examples include creation of DataTable. My parses makes available reading the column index therefore I could pick by index - create objects and write to sql server DB.

我遇到的问题是当我执行SqlBulkCopy.WriteToServer时（DATA） - 我需要将数据作为IDataReader传递。我正在使用StreamReader从文件中读取..？有没有办法将streamreader转换为IDataReader ..，或者我应该将什么传递给WriteToServer
方法？谢谢！

The problem I have is when I do SqlBulkCopy.WriteToServer(DATA) - I need to pass the data as IDataReader. I am reading from the file with StreamReader..? Is there a way to convert the streamreader to IDataReader.., or what exactly I should pass to the WriteToServer method? Thank you!

一些示例代码

var connString = @"Integrated Security=SSPI;Persist Security Info=False;MultipleActiveResultSets=true;Initial Catalog=Checker;Data Source=PC\SQLEXPRESS";
            new SqlConnection(connString);


            const string fileName = @"C:\Users\wrk.csv";
            using (var stream = File.OpenRead(fileName))
            using (var reader = new StreamReader(stream))
            {
                var data = Test.ParseIndexAndValues(reader, ',', '"');

                var header = data.Item1;
                var lines = data.Item2;


                using (SqlBulkCopy sbc = new SqlBulkCopy(connString))
                {
                    sbc.BatchSize = 10000;
                    sbc.BulkCopyTimeout = 10000;
                    sbc.ColumnMappings.Add("tx_id", "Transaction_Id");
                    sbc.DestinationTableName = "[Transaction]";
                    sbc.WriteToServer(trn.TransactionId);

                }

                var headerIdx = header.Select((x, i) => new { Index = i, Field = x }).ToDictionary(x => x.Field, x => x.Index);
                foreach (var line in lines)
                {
                    var usr = new User();
                    usr.UserId = Convert.ToInt32(line[headerIdx["Users"]]);
                    tr.AddUsers(use.UserId);
                }

新手

推荐答案

你好y4wn，

Hi y4wn,

的>>有没有办法将streamreader转换为IDataReader ..，或者我应该将什么传递给WriteToServer方法？

您可以使用Oledb来 读取CSV文件。

You can use the Oledb to  read CSV file.

  public static void QueryCSVToOledb()
        {
            string tableName = "test.csv";
            string filePath = AppDomain.CurrentDomain.BaseDirectory;

            string pContent = string.Empty;

            OleDbConnection oledbConn = new OleDbConnection();
            OleDbCommand oledbCmd = new OleDbCommand();
            OleDbDataReader dataReader;
            try
            {
                string strConnOledb = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=";
                //string strConnOledb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
                strConnOledb += filePath;
                strConnOledb += ";Extended Properties='Text;HDR=Yes;IMEX=1;'";

                oledbConn.ConnectionString = strConnOledb;
                oledbConn.Open();
                StringBuilder commandText = new StringBuilder("SELECT ");
                commandText.AppendFormat(" * From [{0}", tableName + "]");
                oledbCmd.Connection = oledbConn;
                oledbCmd.CommandText = commandText.ToString();
                dataReader = oledbCmd.ExecuteReader();

                // Do whatever
                using (SqlBulkCopy bulkCopy =
              new SqlBulkCopy(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True"))
                {
                    bulkCopy.DestinationTableName =
                        "TableCSV";
                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(dataReader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        // Close the SqlDataReader. The SqlBulkCopy
                        // object is automatically closed at the end
                        // of the using block.
                        dataReader.Close();
                    }
                }
            }
            catch (System.Exception ex)
            {
                oledbConn.Close();
            }
            finally
            {
                oledbConn.Close();
            }
        }

以下链接供您参考。

The following links for your reference.

SqlBulkCopy.WriteToServer方法（IDataReader）：

https://msdn.microsoft.com/en-us/library /434atets(v=vs.110).aspx

SqlBulkCopy.WriteToServer Method (IDataReader):
https://msdn.microsoft.com/en-us/library/434atets(v=vs.110).aspx

C＃ - CSV导入导出：

https://www.codeproject.com/articles/30705/c-csv-import-export

C# - CSV Import Export:
https://www.codeproject.com/articles/30705/c-csv-import-export

最好的问候，

Best Regards,

Yohann Lu

Yohann Lu

这篇关于sqlbulkcopy c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！