SqlDataAdapter是否打开它自己的连接?

Does SqlDataAdapter open its own connection?

SqlDataAdapter是否打开它自己的连接?

        private DataTable UpdateOxa(ProductCatalogSyncData syncDataModel, string connectionString)
    {
        var ds = syncDataModel.SyncDataSet;
        var dtResults = new DataTable("BillingIds");
        var syncConfig = syncDataModel.XDataMapping;
        string EntityName;


        string queryString =
                    @"         
                                IF OBJECT_ID('#CRM2Oxa_ID_MAPPING') IS NOT NULL
                               DROP TABLE #CRM2Oxa_ID_MAPPING


                               CREATE TABLE #CRM2Oxa_ID_MAPPING(
                                [EntityName][nvarchar](1000) NULL,

                                   [TableName][nvarchar](1000) NULL,

                                   [CRMID][uniqueidentifier] NULL,

                                   [OxaID][int] NOT NULL,

                                   [CRMColumnName][nvarchar](1000) NULL
                               ) ";
        var listOfSqlCommands = new List<SqlCommand>();
        var OxaConnection = new SqlConnection(connectionString);

        try
        { 


            OxaConnection.Open();

            using (var createTempTableCommand = new SqlCommand(queryString, OxaConnection))
            {
                createTempTableCommand.ExecuteNonQuery();
            }

                foreach (DataTable dt in ds.Tables)
                {
                    EntityName =
                        StringDefaultIfNull(
                            syncConfig.Root.XPathSelectElement("./entity[@name='" + dt.TableName + "']"),
                            "OxaTableName").Substring(3);

                    var OxaCommand = new SqlCommand();

                    OxaCommand.CommandType = CommandType.StoredProcedure;
                    OxaCommand.CommandText = "Oxa720_P_" + EntityName + "Sync";

                    var entityNameParam = new SqlParameter("@EntityName", dt.TableName);
                    OxaCommand.Parameters.Clear();
                    OxaCommand.Parameters.Add(entityNameParam);
                    var tblParam = new SqlParameter("@O720_" + EntityName, SqlDbType.Structured);
                    tblParam.Value = dt;
                    OxaCommand.Parameters.Add(tblParam);
                    OxaCommand.Connection = OxaConnection;

                    listOfSqlCommands.Add(OxaCommand);
                }



                foreach (var command in listOfSqlCommands)
                {
                    using (var da = new SqlDataAdapter(command))
                    {
                        da.Fill(dtResults);
                    }

                }
        }
        finally
        {
            OxaConnection.Close();
        }
        return dtResults;

    }

I'm getting a message back from the database that the table #temptable does not exist.

我从数据库中得到一条消息，表#temptable不存在。

Does SqlDataAdapter open its own connection? Perhaps this is why it does not see the local temp table?

SqlDataAdapter是否打开它自己的连接?也许这就是为什么它看不到本地临时表的原因?

2 个解决方案

#1

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

如果您的SqlConnection已经打开，那么SqlDataAdapter应该按原样使用它(即不关闭/打开它)。

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.

关于为什么存储的procs不能看到临时表，一种可能是ADO。NET使用sp_executesql调用执行您的第一个SqlCommand(用于创建临时表)。这意味着在存储的proc sp_executesql范围内创建临时表，即使您正在使用相同的连接，也不会对后续命令可见。要检查，您可以运行Sql分析器跟踪—如果您看到sp_executesql用于第一个SqlCommand，那么就会出现问题。

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

此注释:Sql Server临时表消失可能相关:

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

老实说，我认为这与SqlCommand文本的结构有关。如果它是一个简单的select into，没有参数，那么它可能会作为一个简单的select语句运行，因此它不会被包装在像“sp_executesql”这样的SqlProcedure中，因此它对于使用相同的SqlCommand和SqlConnection对象的后续查询仍然可见。另一方面，如果这是一个复杂的语句，那么可以在“sp_executesql”这样的存储过程中创建临时表，并在命令完成时超出范围。- 2月25日21:10

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.

如果ADO。NET确实在为表创建命令使用sp_executesql，然后您可以通过将命令分解为两个sqlcommand:一个用于删除临时表(如果存在的话)，另一个用于创建临时表。

EDIT : on a side note, this code:

编辑:在附注中，这段代码:

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

should probably be:

应该是:

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

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

否则OBJECT_ID('#CRM2Oxa_ID_MAPPING')将始终为null(除非您已经在temp数据库中)。

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

编辑2:这里有一些简单的代码可以帮助我:

        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);

The stored proc looks like this:

存储的proc是这样的:

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

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

最后，它显示:“完成，num第1行”

#2

From the documentation on the SqlDataAdapter.Fill() method:

从SqlDataAdapter.Fill()方法的文档中可以看到:

The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

与select命令相关联的IDbConnection对象必须是有效的，但不需要打开它。如果在调用Fill之前关闭IDbConnection，则打开它以检索数据，然后关闭。如果在调用Fill之前连接是打开的，那么它仍然是打开的。

So we see here that that SqlDataAdapter does not use any special private connection, but will try to automatically open whatever connection you give it.

因此，我们在这里看到SqlDataAdapter不使用任何特殊的私有连接，而是试图自动打开您提供的任何连接。

The problem you're having here is that each call to the .Fill() method happens in a separate Execution Context.

这里的问题是，对. fill()方法的每个调用都发生在单独的执行上下文中。