CODEWITHSUNDEEP
sql-serverado.nettemp-tablessqlcommand
alex4711
alex4711

Reputation: 21

Temp tables via SqlCommand from .NET application

I use following pattern to work with SQL Server temp tables from .NET applications:

  1. Open the connection
  2. Create a temp table (only with ad hoc command!!!)
  3. Write a lot of data into it via SqlBulkCopy
  4. Perform a select/update with join to this temp table (this request can already be parameterized)
  5. Close the connection
Using cn As New SqlConnection("Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;") : cn.Open()

    Using cm As New SqlCommand("create table #T1(C1 int primary key, C2 int)", cn)
        cm.ExecuteNonQuery()
    End Using

    Using bk As New SqlBulkCopy(cn)
        bk.DestinationTableName = "#T1"
        bk.WriteToServer(dataToWrite)
    End Using

    Using cm As New SqlCommand("update a set a.C2=b.C2 from SomeTable a join #T1 b on a.C1=b.C1 where a.C3=@PC3", cn)
        cm.Parameters.Add("C2", SqlDbType.Int).Value = c3Value
        cm.ExecuteNonQuery()
    End Using
End Using

Unfortunately, I can't explicitly influence whether the “create temp table” as ad-hoc command runs.

Although the SqlCommand source code describes this behavior in comments (// Send over SQL Batch command if we are not a stored proc and have no parameters), but nothing is specified in the documentation.

Theoretically, the behavior of SqlCommand in future .NET versions can be changed, so that "create table #T..." will be packed into sp_executesql. Using temporary tables from .NET code will become impossible and the application will become inoperable.

Do I understand the problem correctly?

Can I remain assured that in future versions of .NET will still not package parameterless queries in sp_executesql?

Upvotes: 2

Views: 491

Answers (0)

Related Questions