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 queryString =
IF OBJECT_ID('#CRM2Oxa_ID_MAPPING') IS NOT NULL
DROP TABLE #CRM2Oxa_ID_MAPPING
CREATE TABLE #CRM2Oxa_ID_MAPPING(
[OxaID][int] NOT NULL,
var listOfSqlCommands = new List<SqlCommand>();
var OxaConnection = new SqlConnection(connectionString);
using (var createTempTableCommand = new SqlCommand(queryString, OxaConnection))
foreach (DataTable dt in ds.Tables)
syncConfig.Root.XPathSelectElement("./entity[@name='" + dt.TableName + "']"),
var OxaCommand = new SqlCommand();
OxaCommand.CommandType = CommandType.StoredProcedure;
OxaCommand.CommandText = "Oxa720_P_" + EntityName + "Sync";
var entityNameParam = new SqlParameter("@EntityName", dt.TableName);
var tblParam = new SqlParameter("@O720_" + EntityName, SqlDbType.Structured);
tblParam.Value = dt;
OxaCommand.Connection = OxaConnection;
foreach (var command in listOfSqlCommands)
using (var da = new SqlDataAdapter(command))
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
OBJECT_ID('#CRM2Oxa_ID_MAPPING') will always be null (unless you are already in the temp database).