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?

ADyson
  • 57,178
  • 14
  • 51
  • 63
alex4711
  • 21
  • 1
  • 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. – ADyson Jun 17 '20 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? – ADyson Jun 17 '20 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. – ADyson Jun 17 '20 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. – alex4711 Jun 17 '20 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. – ADyson Jun 17 '20 at 11:41
  • Alternatively that are likely to be ways round it such as using global temp tables, or encapsulating this functionality inside a stored procedure which your .NET code executes. But nonetheless this question appears to be purely speculative. And anyway we cannot give you a definitive answer about what .NET will or won't do in future, you'd have to talk to the developers and ask about their roadmap. I just think it's incredibly unlikely they would do this, that's all. – ADyson Jun 17 '20 at 11:48
  • @ADyson: Maybe there's no point in changing behaviour. But, this behavior hidden and not described in documentation. So I'm a little insecure. – alex4711 Jun 17 '20 at 12:50
  • I can see that up to a point. But as I said right at the start, we cannot reassure you here any more than I have already done by arguing that such a change is incredibly unlikely. We don't have any more access to the ADO.NET roadmap than you do. If you want a 100% answer you'd have to ask the developers at Microsoft directly. – ADyson Jun 17 '20 at 13:11

0 Answers0