2

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?

Improve this question
8
  • You'd have to ask the ADO.NET team that question. But if you're talking about .NET Framework specifically, there won't be any future versions, at least not major enough to introduce breaking changes anyway I would expect. .NET Framework is not really being actively developed any more. If you're talking about .NET Core, then I suppose it's possible that such a change could be introduced. Commented Jun 17, 2020 at 10:44
  • But anyway I don't think I quite follow what your concern is. You say "I can't explicitly influence whether the “create temp table” as ad-hoc command runs"...why not? If you send the command, why would you think it wouldn't be executed? I can't see anything in the source code you pointed to which would cause the command not to be executed. And why do you think anyone would want to pack things into sp_executesql? I can't think what advantage it would bring. It's not clear whether this question is based on some hard information, or just the product of some speculative thinking? Commented Jun 17, 2020 at 10:45
  • Overall, I can't really see why anyone would want to make it so you couldn't use temporary tables from .NET code, and it's unclear what other advantage could be gained from the change you're worrying about - why do you think someone might do that, and do you really think it's likely? It would probably break a lot more applications than just yours. Major libraries tend not to break significant things unless they really really need to. Commented Jun 17, 2020 at 10:52
  • 1
    @ADyson When you execute an SQL in SqlCommand and trace what actually arrives on SQL Server, you can see the difference depending on whether the SqlCommand includes the parameters or not. If SqlCommand is executed without parameters, comes SQL to Server as "ad hoc" - in plain text. If SqlCommand contains parameters, SQL command will come to SQL Server packed in sp_executesql - as dynamic sql. sp_executesql opens an internal connection therefore creating the temporary table inside sp_executesql is useless - the temporary table disappears as soon as this internal connection is closed. Commented Jun 17, 2020 at 11:11
  • I see. It wasn't clear what you meant by "ad-hoc" up to that point. Ok well again I'd ask - what benefit would be gained by changing the non-parameterised version to do that? It works fine as it is. And changing it would probably break things. Generally, library makers try to guarantee not to break things, at least not within major versions. And then when they do break them, the breakages (or at least, the things they intended to break ;-)) are documented. So you'd have time and information to prepare for such a a change if it occurred, and the choice not to upgrade as well. Commented Jun 17, 2020 at 11:41
Related questions
11
Using temporary table in c#
4
How do I use a temp table across multiple c# calls
16
'Invalid object name' for temporary table when using command with parameters
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.