none
I can't make .NET 2.0 SqlBulkCopy work RRS feed

  • Question

  • I have a scenario whereby I'd like to insert multiple rows into a table on a SQL server database as efficiently and easily as possible.

    After some research, it looked like .NET 3.0's SqlBulkCopy class would do what I want. I've tried to set something up, but it's not working. It's not even throwing an error. The code executes but it simply hasn't done the insert by the end of it!

     My table structure is simple. The name of the table is LPSTUnavailableDate. It has just two columns, one of them an auto-populated ID field:

    • LPSTUnavailableDateId, INT, PRIMARY KEY, IDENTITY(1,1)
    • UnavailableDate, DATETIME(8) UNIQUE

    My function accepts an array of .NET DateTime objects as a parameter. I want to insert all of those DateTimes into the UnavailableDate column in the table.

    It builds a .NET DataTable with a single column called "UnavailableDate" with the DateTime data-type. Then it loops through each of the DateTime objects in the array and them as a new row to the single column table. Then it passes the DataTable as a parameter to the WriteToServer method of the SqlBulkCopy class.

     No errors thrown, but it doesn't do anything! What's wrong, please?

    I've pasted it below:

     

    1        Public Shared Sub SetUnavailableDates(ByVal unavailableDates() As DateTime)
    2    
    3            CancelAppointmentsOnDates(unavailableDates) 'This line isn't relevant, and works fine.
    4    
    5            Dim bulk As New SqlBulkCopy(ConfigurationManager.AppSettings("ConnectionString"))
    6            bulk.DestinationTableName = "dbo.LPSTUnavailableDate"
    7    
    8            Dim unavailableDatesTable As New DataTable()
    9            unavailableDatesTable.Columns.Add("UnavailableDate", GetType(DateTime))
    10   
    11           For Each unavailableDate As DateTime In unavailableDates
    12               Dim newRow As DataRow = unavailableDatesTable.NewRow()
    13               newRow(0) = unavailableDate
    14               unavailableDatesTable.Rows.Add(newRow)
    15           Next
    16   
    17           bulk.WriteToServer(unavailableDatesTable)
    18   
    19       End Sub
    

    Many thanks

    Ben

    Monday, February 11, 2008 9:21 AM

Answers

  • Ben, I think if you add column mappings, you will be good to go. Try the code below:

     

    Code Snippet

    1 Public Shared Sub SetUnavailableDates(ByVal unavailableDates() As DateTime)

    2

    3 CancelAppointmentsOnDates(unavailableDates) 'This line isn't relevant, and works fine.

    4

    5 Dim bulk As New SqlBulkCopy(ConfigurationManager.AppSettings("ConnectionString"))

    6 bulk.DestinationTableName = "dbo.LPSTUnavailableDate"

    7 bulk.ColumnMappings.Add("UnavailableDate", "UnavailableDate");

    8 Dim unavailableDatesTable As New DataTable()

    9 unavailableDatesTable.Columns.Add("UnavailableDate", GetType(DateTime))

    10

    11 For Each unavailableDate As DateTime In unavailableDates

    12 Dim newRow As DataRow = unavailableDatesTable.NewRow()

    13 newRow(0) = unavailableDate

    14 unavailableDatesTable.Rows.Add(newRow)

    15 Next

    16

    17 bulk.WriteToServer(unavailableDatesTable)

    18

    19 End Sub

     

     

    Monday, February 11, 2008 2:46 PM

All replies

  • Ben, I think if you add column mappings, you will be good to go. Try the code below:

     

    Code Snippet

    1 Public Shared Sub SetUnavailableDates(ByVal unavailableDates() As DateTime)

    2

    3 CancelAppointmentsOnDates(unavailableDates) 'This line isn't relevant, and works fine.

    4

    5 Dim bulk As New SqlBulkCopy(ConfigurationManager.AppSettings("ConnectionString"))

    6 bulk.DestinationTableName = "dbo.LPSTUnavailableDate"

    7 bulk.ColumnMappings.Add("UnavailableDate", "UnavailableDate");

    8 Dim unavailableDatesTable As New DataTable()

    9 unavailableDatesTable.Columns.Add("UnavailableDate", GetType(DateTime))

    10

    11 For Each unavailableDate As DateTime In unavailableDates

    12 Dim newRow As DataRow = unavailableDatesTable.NewRow()

    13 newRow(0) = unavailableDate

    14 unavailableDatesTable.Rows.Add(newRow)

    15 Next

    16

    17 bulk.WriteToServer(unavailableDatesTable)

    18

    19 End Sub

     

     

    Monday, February 11, 2008 2:46 PM
  • Thanks, Dennis,

     

    That's solved the problem.

    Tuesday, February 12, 2008 11:55 AM