I need to construct a temporary table using ADO.NET and run a
next came a
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 a
contains the original table's schema?
I would be grateful for any information leading me in the proper way.
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