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;
}
我从数据库中收到一条消息，提示表 #temptable 不存在。
I'm getting a message back from the database that the table #temptable does not exist.
SqlDataAdapter是否打开自己的连接？也许这就是为什么它不打开的原因
Does SqlDataAdapter open its own connection? Perhaps this is why it does not see the local temp table?
推荐答案
如果您的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"
这篇关于SqlDataAdapter是否打开自己的连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
