为什么我的SqlCommand返回时，它应该是一个int一个字符串？ [英] Why is my SqlCommand returning a string when it should be an int?
问题描述
我应该的总是的在返回单个int的查询。现在我已经登录它返回一个字符串完全无关它应该是什么。
I have a query that should always be returning a single int. I have now logged it returning a string entirely unrelated to what it should be.
我们已经收到，我们已经找到了几个数据库查询一些随机FormatExceptions。经过一些额外的日志记录，我发现，今天上午，下面的查询返回的字符串角斗士。 Website.PkID是一个int的列和工作的大部分时间，但有些时候失败了。球队要么返回一个int这是waaaay在那里（大于任何有效WebsiteID）或一个随机字符串。
We've been getting some random FormatExceptions that we've tracked down to several database queries. After some additional logging, I found that, this morning, the query below returned the string "gladiator". Website.PkID is an int column and works most of the time, but some times it fails miserably and returns either an int that's waaaay out there (bigger than any valid WebsiteID) or a random string.
这特定的查询是每一次会话开始打。它不使用共享连接，所以我无法理解怎么会得到这样一个混乱的结果。可能有某种腐败的连接池？
This particular query is hit once per session start. It's not using a shared connection, so I'm having trouble understanding how it could get such a mixed-up result. Could there be some kind of corruption in the connection pools?
我不认为这个问题是独立于该查询。我已经看到了（因为一个意想不到的结果），类似FormatExceptions从LINQ查询以及未来。我们还发现在同一时间一些错误的：
I don't think the problem is isolated to this query. I've seen similar FormatExceptions (because of an unexpected result) coming from LINQ queries as well. We've also spotted some of these errors around the same times:
已经发送请求到服务器时出现了传输级错误。 （provider：TCP提供程序，error：0 - 现有连接被强行关闭远程主机
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.
这可能是一个连接问题？或者，也许我们得到混淆的数据库服务器和Web服务器之间的结果集？这真的让我抓我的头。
Could it be a connection issue? Or maybe we're getting result sets mixed up between the db server and the web server? This has really got me scratching my head.
得罪查询：
public static int GetActiveWebSiteID(string storeID, string statusID)
{
int retval;
string sql = @"SELECT isnull(MAX(PkID),0) FROM WebSite
WHERE StoreID = @StoreID
AND WebSiteStatusID = @WebSiteStatusID";
SqlConnection conn = new SqlConnection(Settings.ConnString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@StoreID", (object)storeID ?? DBNull.Value);
cmd.Parameters.AddWithValue("@WebSiteStatusID", (object)statusID ?? DBNull.Value);
conn.Open();
using(conn)
{
var scalar = cmd.ExecuteScalar(); // <-- This value returned here should only ever be an int, but randomly is a string
retval = Convert.ToInt32(scalar);
}
return retval;
}
上面的查询工作的罚款多年，直到最近。现在，我们有一大堆的应用程序更多的LINQ查询（不知道是否有差别）。我们正在运行.NET 3.5。
The above query has worked fine for years until recently. We now have a bunch of additional LINQ queries in the app (not sure if that makes a difference). We're running .Net 3.5.
推荐答案
在忽视这个问题几个月，它开始达到一个临界质量的流量逐渐增大。我们增加了记录，发现许多明确的情况下，重负载下，完全不同的结果集将得到恢复到不相关的查询。
After a few months of ignoring this issue, it started to reach a critical mass as traffic gradually increased. We increased logging and found numerous definitive instances where, under heavy load, completely different result sets would get returned to unrelated queries.
我们看着探查查询，并能看到糟糕的结果与相同的SPID总是相关联，并且每个坏的结果总是实际的SQL语句背后的一个查询中查询。这就像一个结果集得到了未接来电和任何结果集是在旁边的SPID（从同一池中另一个连接）返回。疯了。
We watched the queries in Profiler and were able to see that the bad results were always associated with the same spid, and that each bad result was always one query behind the actual sql statement being queried. It was like a result set got missed and whatever result set was next in the spid (from another connection in the same pool) was returned. Crazy.
经过反复试验，我们最终找到了一把的SqlCommand或LINQ查询的SqlConnection的是没有使用后立即关闭了。相反，通过一些马虎编程从LINQ连接的误解始发，在DataContext物体设置（和连接闭合）仅在一个请求而不是立即的末端。
Through trial and error, we eventually tracked down a handful of SqlCommand or LINQ queries whose SqlConnection wasn't closed immediately after use. Instead, through some sloppy programming originating from a misunderstanding of LINQ connections, the DataContext objects were disposed (and connections closed) only at the end of a request rather than immediately.
在我们重构这些方法来立即关闭与C＃使用块（释放该池的下一个请求）的连接，我们没有得到更多的错误。虽然我们仍然不知道背后的根本原因是连接池会得到这么混在一起，我们能够停止这种类型的所有错误。与其他类似的错误我一起发布此问题已得到解决，在这里找到：是什么原因导致内部连接致命错误？
Once we refactored these methods to immediately close the connection with a C# "using" block (freeing up that pool for the next request), we received no more errors. While we still don't know the underlying reason that a connection pool would get so mixed up, we were able to cease all errors of this type. This problem was resolved in conjunction with another similar error I posted, found here: What Causes "Internal Connection Fatal Errors"?
这篇关于为什么我的SqlCommand返回时，它应该是一个int一个字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
