Bulk copy with dynamic created temp table in ADO.NET

ado.net sqlbulkcopy

Question

I need to construct a temporary table using ADO.NET and run aBulkCopy next came aMerge between the temporary table and the real table on the server.

The issue is utilizing pure ADO.NET to dynamically create the temp table. Although this table was made using an ORM (NHibernate or Entity Framework, we're not sure which yet), the schema should be the same as the one for the current table. This implies that the schema could change later.

Can a table be created in the database using just standard ADO.NET objects? Including aDataTable contains the original table's schema?

I would be grateful for any information leading me in the proper way.

1
6
4/10/2013 9:44:33 AM

Accepted Answer

I succeeded in building a temporary table from an existing schema.

I posted the answer on my website as a blog..

2
4/25/2013 8:37:03 AM

Popular Answer

Using select into #somename, a temporary table may be created.

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


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow