Bulk copy with dynamic created temp table in ADO.NET

Question

I require to create through ADO.NET a temp table, perform a BulkCopy and then a Merge on the server between the temp and the actual table.

Problem is creating the temp table dynamic using pure ADO.NET. The schema should be the same as the existing table, but this table is created using an ORM (NHibernate or Entity Framework, we're not sure yet). This also means that the schema can change in the future.

Is there any way to create a table in the database using plain ADO.NET objects? Such as a DataTable containing the schema of the original table?

Any information pointing me in the right direction is appreciated.

Answer 1

You can create a temporary table using select into #somename.

connection_ = New SqlClient.SqlConnection(connection_string_)
connection_.Open()
If connection_.State = ConnectionState.Open Then

    command_.Connection = connection_
    command_.CommandType = CommandType.Text
    command_.CommandText = "select * into #some_table from some_table where some_id = 0"
    command_.ExecuteNonQuery()

    Dim line_index_ As Integer = 0
    Dim data_table_ As DataTable = New DataTable()
    Using parser_ As FileIO.TextFieldParser = New FileIO.TextFieldParser(path_)
        parser_.SetDelimiters(delimiter_)
        parser_.HasFieldsEnclosedInQuotes = False
        While Not parser_.EndOfData
            If line_index_ = 0 Then
                Dim headers_ As String() = parser_.ReadFields()
                For Each header_ In headers_
                    data_table_.Columns.Add(header_)
                Next
            Else
                Dim row_ As DataRow = data_table_.NewRow()
                row_.ItemArray = parser_.ReadFields()
                data_table_.Rows.Add(row_)
            End If
            line_index_ += 1
        End While
     End Using

     Using bulkCopy_ As SqlBulkCopy = New SqlBulkCopy(connection_)
           bulkCopy_.DestinationTableName = "#some_table"
           bulkCopy_.WriteToServer(data_table_)
     End Using

    ' proof
    command_.CommandText = "select * from #some_table"
    Dim reader_ As SqlDataReader = Nothing
    reader_ = command_.ExecuteReader
    line_index_ = 0
    While reader_.Read
        line_index_ += 0
    End While

 End If

Answer 2

I managed to create a temp table based on an existing schema.

Blogged the solution on my site.