C＃帮助SqlBulkCopy类 [英] C# help with SqlBulkCopy class

问题描述

请你指导我重写以插入IList< string>使用SqlBulkCopy类将数据转换为SQL Server表。 SQL Server表具有Identity列。

Hi, Could you please guide me re-write to insert IList<string> data into a SQL Server table using SqlBulkCopy class. The SQL Server table has Identity column.

public string toTbl(IList < string > records) {
 const string connectionString = @ "Data Source=sqlserver;Initial Catalog=dbname;User Id=user;Password=password;";

 try {
  var studentData = from record in records
  let srec = record.Split(',')
  select new Student {
   ID = srec[0],
    Student = srec[1],
    Grade = srec[2]
  };

  foreach(var i in studentData) {
   using(SqlConnection sqlConnection = new SqlConnection(connectionString)) {
    sqlConnection.Open();

    using(SqlCommand cmd = new SqlCommand("INSERT INTO [Student] ([ID], [Student], [Grade]) VALUES (@ID, @Student, @Grade)", sqlConnection)) {
     cmd.Parameters.AddWithValue("@ID", i.ID);
     cmd.Parameters.AddWithValue("@Student", i.Student);
     cmd.Parameters.AddWithValue("@Grade", i.Grade);

     cmd.ExecuteNonQuery();
    }
      sqlConnection.Close();
   }
  }
 } catch (Exception ex) {
  message = ex.Message;
 }
}

提前谢谢。

SQLEnthusiast

SQLEnthusiast

推荐答案

SqlBulkCopy使用集合DataRows，DataTable或DataReader作为批量插入的源。我建议您首先将列表复制到DataTable。

SqlBulkCopy uses a collection of DataRows, a DataTable or a DataReader as the source for the bulk insert. I would suggest that you copy your List to a DataTable first.

https://stackoverflow.com/questions/18100783/how-to-convert-a-list-into-data-table

只需记住省略Identity列，因为它将在插入时创建。

Just remember to omit the Identity column as it will be created upon insert.

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