none
C# help with SqlBulkCopy class RRS feed

  • Question

  • 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;
     }
    }

    Thank you in advance.


    SQLEnthusiast



    Tuesday, February 13, 2018 6:21 AM

All replies

  • 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

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


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 13, 2018 4:08 PM
  • Hi Paul, I modified my script accordingly. I get the below error at this "values[i] = Props[i].GetValue(item, null);" 

    System.Reflection.TargetParameterCountException

    Parameter count mismatch

    		public void main()
            {
                BulkCopy(ToDataTable(records), "Student", 10000);
            }
    
            public static DataTable ToDataTable<T>(List<T> records)
            {
                DataTable dataTable = new DataTable(typeof(T).Name);
    
                PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                foreach (PropertyInfo prop in Props)
                {
                    var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                    dataTable.Columns.Add(prop.Name, type);
                }
                foreach (T item in records)
                {
                    var values = new object[Props.Length];
                    for (int i = 0; i < Props.Length; i++)
                    {
                        values[i] = Props[i].GetValue(item, null);
                    }
                    dataTable.Rows.Add(values);
                }
                return dataTable;
            }
    
            public void BulkCopy(DataTable dataTable, string DestinationTable, int batchSize)
            {
                DataTable dtInsertRows = dataTable;
    
                using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
                {
                    sbc.DestinationTableName = DestinationTable;
                    sbc.BatchSize = batchSize;
    				
                    sbc.ColumnMappings.Add("Student", "Student");
                    sbc.ColumnMappings.Add("Grade", "Grade");
    
                    sbc.WriteToServer(dtInsertRows);
                }
            }

    Thanks in advance.


    SQLEnthusiast

    Tuesday, February 13, 2018 7:46 PM
  • You can't use a List. Needs to be a primitive ADO.NET type such as a DataTable. You have to convert your list to a DataTable and use the WriteToServer method. In my example, I'm using the async versions of the methods which is recommended.

    SqlBulkCopy.WriteToServer Method (DataTable)

    Example of creating DataTable.

                try
                {
                    var someTataTable = new DataTable(); // convert your list to a DataTable
                    
                    using (var conn = new SqlConnection(connectionString))
                    {
                        await conn.OpenAsync();
                        using (var bulkCopy = new SqlBulkCopy(conn))
                        {
                            bulkCopy.DestinationTableName = "Student";
    
                            try
                            {
                                // Write from the source to the destination.
                                await bulkCopy.WriteToServerAsync(someTataTable);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.Message);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    // ignored
                }




    william xifaras





    Tuesday, February 13, 2018 7:51 PM
  • I would create the DataTable manually. Loop through your list, and create a DataRow for each item. You will have to set your DataTable structure beforehand by creating the DataColumns.

    Example of creating a DataTable.


    william xifaras


    Tuesday, February 13, 2018 8:27 PM
  • Give this one a try instead so you don't have to rely on the enumerating index (from https://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable):

    public static DataTable ToDataTable<T>(this IList<T> data)
    {
        PropertyDescriptorCollection properties = 
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                 row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 14, 2018 4:58 PM