SqlDataAdapter是否打开自己的连接？

如果您的SqlConnection已经打开，则SqlDataAdapter应该按原样使用它（即，不关闭/打开）。

If your SqlConnection was already open, then SqlDataAdapter should use it as is (i.e. without closing/opening it).

为什么存储的proc无法看到临时表的一种可能是ADO.NET使用sp_executesql执行了您的第一个SqlCommand（用于创建临时表）呼叫。这意味着将在存储的proc sp_executesql的范围内创建临时表，即使使用相同的连接，后续命令也看不到该临时表。要进行检查，您可以运行Sql Profiler跟踪-如果看到sp_executesql用于您的第一个SqlCommand，则将遇到问题。

One possibility as to why your stored procs cannot see the temp table, is that ADO.NET executed your first SqlCommand (used to create the temp table), with a sp_executesql call. That would mean that the temp table gets created within the scope of the stored proc sp_executesql, and would not be visible to subsequent commands, even though you are using the same connection. To check, you could run a Sql Profiler trace - if you see sp_executesql being used for your first SqlCommand, then you'll have a problem.

此评论位于：
Sql Server临时表消失可能是相关的：

This comment at: Sql Server temporary table disappears may be relevant:

老实说，我认为这与SqlCommand文本的方式有关
结构化。如果它是一个简单的没有参数的select into，则
可以作为一个简单的select语句运行，因此不会像$ sp $ execsql这样包装在
SqlProcedure中，因此它将保持可见到
使用相同的SqlCommand和SqlConnection
对象的后续查询。另一方面，如果它是一个复杂的语句，则可以在诸如 sp_executesql之类的存储过程中创建临时表
，并且
将在命令完成时超出范围。 – Triynko
15年2月25日在21:10

I honestly think it has to do with the way the SqlCommand text is structured. If it's a simple select into, with no parameters, then it may be run as a simple select statement, so it won't be wrapped in an SqlProcedure like 'sp_executesql', so it will remain visible to subsequent queries using that same SqlCommand and SqlConnection object. On the other hand, if it's a complex statement, the temp table may be created within a stored procedure like 'sp_executesql', and will go out of scope by the time the command is finished. – Triynko Feb 25 '15 at 21:10

如果ADO.NET确实在使用sp_executesql作为表创建命令，则可以通过将命令分成2个SqlCommands来诱使它不使用它：一个删除temp表（如果存在），另一个删除temp表。

If ADO.NET is indeed using sp_executesql for your table creation command, then you might be able to coax it into not using it, by breaking up the command into 2 SqlCommands: one to drop the temp table if it exists, and another to just create the temp table.

编辑：在旁注中，此代码：

EDIT : on a side note, this code:

IF OBJECT_ID('#CRM2Oxa_ID_MAPPING') IS NOT NULL
       DROP TABLE #CRM2Oxa_ID_MAPPING

可能是：

IF OBJECT_ID('tempdb..#CRM2Oxa_ID_MAPPING') IS NOT NULL
       DROP TABLE #CRM2Oxa_ID_MAPPING

否则 OBJECT_ID（'＃CRM2Oxa_ID_MAPPING'）始终为null（除非您已经在临时数据库中）。

otherwise OBJECT_ID('#CRM2Oxa_ID_MAPPING') will always be null (unless you are already in the temp database).

编辑2 ：这是一些简单的代码对我来说：

EDIT 2 : here's some simple code which works for me:

        DataSet ds = new DataSet();

        using(SqlConnection conn = new SqlConnection("YourConnectionString"))
        {
            conn.Open();

            string str = "if object_id('tempdb..#mytest') is not null drop table #mytest; create table #mytest (id int)";

            // create temp table
            using(SqlCommand cmdc = new SqlCommand(str, conn))
            {
                cmdc.ExecuteNonQuery(); 
            }

            // insert row
            using (SqlCommand cmdi = new SqlCommand("insert #mytest (id) values (1)", conn))
            {
                cmdi.ExecuteNonQuery();
            }

            // use it
            using (SqlCommand cmds = new SqlCommand("dbo.mytestproc", conn))
            {
                cmds.CommandType = CommandType.StoredProcedure;
                cmds.Parameters.Add("@id", SqlDbType.Int).Value = 1;
                cmds.Connection = conn;

                using (SqlDataAdapter da = new SqlDataAdapter(cmds))
                {
                    da.Fill(ds);
                }
            } 

            // clean up - drop temp table
            string strd = "if object_id('tempdb..#mytest') is not null drop table #mytest";
            using (SqlCommand cmdd = new SqlCommand(strd, conn))
            {
                cmdd.ExecuteNonQuery();
            }
        }

        MessageBox.Show("done, num rows " + ds.Tables[0].Rows.Count);

存储的过程如下：

create proc dbo.mytestproc(@id int)
as
select * from #mytest where id = @id
GO

最后，它显示： done，num rows 1

At the end, it displays : "done, num rows 1"