c# - SqlDataAdapter 是否打开自己的连接？

Question

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;

    }

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

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

Answer 1

来自 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.

所以我们在这里看到 SqlDataAdapter 不使用任何特殊的私有(private)连接，但会尝试自动打开您提供的任何连接。

您在这里遇到的问题是对 .Fill() 方法的每次调用都发生在单独的 Execution Context 中。 .