Does SqlDataAdapter open its own connection?

Question

Does SqlDataAdapter open its own connection?

        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.

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

Answer 1

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

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.

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.

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

Answer 2

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

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.

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

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

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

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

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

The stored proc looks like this:

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

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