SQL ParameterDirection.InputOutput / SqlCommand.ExecuteNonQuery（）问题 [英] SQL ParameterDirection.InputOutput / SqlCommand.ExecuteNonQuery() problem
问题描述
帮助！
我有一个非常奇怪的问题，只有当有问题的代码处于高负载情况时才会出现这个问题。我的C＃代码调用一个具有OUTPUT参数的T-SQL存储过程。
在高负载下，返回的数据不会返回到调用的C＃代码。我已将所有相关代码提取到下面的示例中;
存储过程：
CREATE PROCEDURE GetLoginName
@ LoginId BIGINT ，
@ LoginName VARCHAR （ 50 ）输出
AS
SET NOCOUNT ON
SELECT @ LoginName = LoginName
FROM 登录
WHERE Id = @ LoginId
SET NOCOUNT OFF
GO
数据库基类：
public class DatabaseContextBase：IDisposable
{
private SqlConnection _connection;
private string _connectionString;
private SqlInt32 _returnValue;
private int _commandTimeout;
private static int _maxDatabaseExecuteAttempts = 3 ;
private static int s_commandTimeout = 30 ;
protected DBContextBase（）
{
// 首先尝试从标识中获取连接字符串...
string connectionString = GetConnectionStringFromIdentity （）;
if （connectionString！= null ）
{
ConstructionHelper（connectionString，s_commandTimeout）;
}
其他
{
// < span class =code-comment>使用初始化的静态连接字符串，并调用另一个重载
//
ConstructionHelper（s_connectionString，s_commandTimeout）;
}
}
私有 void ConstructionHelper（ string connectionString， int commandTimeout）
{
// 将连接字符串存储在成员var中。
_connectionString = connectionString;
// 将timout存储在成员var中。
_commandTimeout = commandTimeout;
}
public static string GetConnectionStringFromIdentity（）
{
IIdentity identity = Thread.CurrentPrincipal.Identity as wxyz.Security.wxyzIdentityBase;
string connectionString = null ;
if （identity！= null ）
{
connectionString =（（wxyz.Security.wxyzIdentityBase）identity）.ConnectionString;
}
return connectionString;
}
public void Dispose（）
{
if （_connection.State！= ConnectionState.Closed）
{
_connection.Close（）;
}
_connection.Dispose（）;
_connection = null ;
}
protected void ExecuteNonQuery（SqlCommand command）
{
SqlConnection con = this .Connection;
lock （con）
{
if （con.State！= ConnectionState.Open）
{
con.Open（）;
}
// 不需要try catch，因为这只是从这个
// 类中的另一个方法调用，它将包装它。
command.Connection = con;
command.Transaction = _transaction;
command.CommandTimeout = _commandTimeout;
for （ int currentAttempt = 1 ; currentAttempt < = _maxDatabaseExecuteAttempts; currentAttempt ++）
{
try
{
// 执行
命令。的ExecuteNonQuery（）;
// 完成，退出循环
断裂;
}
catch （SqlException sex）
{
HandleDatabaseExceptions（currentAttempt，sex，command.CommandText）;
}
}
}
}
/ // < 摘要 >
/// 获取此数据库拥有的SqlConnection对象（已连接到db）
/// < / summary >
public SqlConnection连接
{
get
{
// 检查我们是否有连接字符串（来自身份或统计信息） ic initialise）
if （_connectionString == null ）
{
throw new ArgumentNullException（ connectionString， 未设置连接字符串）;
}
如果（_connection！= null ）
{
return _connection;
}
其他
{
_connection = 新 SqlConnection（_connectionString）;
return _connection;
}
}
}
/// < 摘要 >
/// 执行存储过程的返回值
/// < /摘要 >
public SqlInt32 ReturnValue
{
get
{
return _returnValue;
}
set
{
_returnValue = value ;
}
}
}
数据库访问类
< pre lang =c＃> /// < 摘要 > 包含数据库的原始数据库命令< / summary >
public class AuthenticationDBCommands
{
/// < 摘要 > 在数据库中返回spiAuthenticateLogin存储过程的命令对象< / summary >
public static SqlCommand GetLoginName（）
{
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand（ GetLoginName）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add（ new SqlParameter（ @ RETURN_VALUE，System.Data.SqlDbType.Int， 0 ，System.Data.ParameterDirection.ReturnValue， false ， 0 ， 0 ， RETURN_VALUE，System.Data.DataRowVersion.Current，SqlInt32.Null））;
cmd.Parameters.Add（ new SqlParameter（ @ LoginId，SqlDbType.BigInt， 8 ，ParameterDirection.Input， false ， 0 ， 0 ， LoginId，DataRowVersion.Current，SqlInt64.Null））;
cmd.Parameters.Add（ new SqlParameter（ @ LoginName，SqlDbType.VarChar， 50 ，ParameterDirection.InputOutput， false ， 0 ， 0 ， LoginName，DataRowVersion.Current，SqlString.Null））;
return cmd;
}
}
public class AuthenticationDBContext：DatabaseContextBase
{
public AuthenticationDBContext（）： base （）
{
}
public void GetLoginName（ SqlInt64 LoginId， ref SqlString LoginName）
{
SqlCommand cmd = AuthenticationDBCommands.GetLoginName（）;
cmd.Parameters [ 1 ]。Value = LoginId;
cmd.Parameters [ 2 ]。Value = LoginName;
base .ExecuteNonQuery（cmd）;
base .ReturnValue =（SqlInt32）cmd.Parameters [ 0 ]。值;
LoginName =（SqlString）（cmd.Parameters [ 2 ]。Value）;
}
}
用法：
受保护 字符串 GetLoginName（ long loginId）
{
SqlString loginName = SqlString.Null;
使用（AuthenticationDBContext dbc = new AuthenticationDBContext（））
{
dbc.GetLogin（loginId， ref loginName）;
}
return loginName.Value;
}
正如你所看到的，这是相当标准的东西。但是当许多不同的用户快速连续调用dbContext.GetLoginName（）方法时，loginName对象有时 null。
我测试了SQL，它总是找到一个值（我已经将@LoginName插入表中，它永远不会为null）。所以问题发生在SqlCommand.ExecuteNonQuery（）之后或之中;
我正在使用.NET 4.5而我的数据库在SQL 2008上。
有没有人见过这样的事情？任何人都可以推荐任何更改吗？
提前致谢，
Matt
< blockquote>你忘了魔法字： RETURN 在 GetLoginName 程序结束时。
从存储过程中返回数据 [ ^ ]
沿关于lock连接的评论，我想提一下，如果你遇到尝试在多个线程之间共享一个连接的性能问题，那么你可能想要支持许多连接。这可以像更改Connection属性一样简单，因此它实例化新的Connection，而不是对共享连接的引用。系统实现了连接池，你可以利用它。
但是......如果你结束了这么多线程（数百个）超过池，那么你可能会开始干扰其他进程（我已经这样做了。
解决方法是实现自己的连接池，限制你一次向内置池发出的请求数量（十几个）。 br />
另外一个注意事项 - 您正在获取属性中的connectionString，如果您做走多条连接的路线，我会得到构造函数中的connectionString。
正如您所提到的，在高负载下您将面临此问题。我认为将commandtimeout属性设置为零可能会有所帮助。试着这样做。在你的代码中，我无法找到_commandtimeout变量的值
Help!
I have a very strange problem that only occurs when the code in question is in a high load situations. My C# code calls a T-SQL stored procedure that has an OUTPUT parameter.
Under high loads, the data being returned does not make it back to the calling C# code. I have extracted all the relevant code into the example below;
Stored Procedure:
CREATE PROCEDURE GetLoginName @LoginId BIGINT, @LoginName VARCHAR(50) OUTPUT AS SET NOCOUNT ON SELECT @LoginName = LoginName FROM Logins WHERE Id = @LoginId SET NOCOUNT OFF GO
Database base class:
public class DatabaseContextBase : IDisposable { private SqlConnection _connection; private string _connectionString; private SqlInt32 _returnValue; private int _commandTimeout; private static int _maxDatabaseExecuteAttempts = 3; private static int s_commandTimeout = 30; protected DBContextBase() { // try and get the connection string off the identity first... string connectionString = GetConnectionStringFromIdentity(); if( connectionString != null ) { ConstructionHelper( connectionString, s_commandTimeout ); } else { // use the initialised static connection string, and call the other overload // of the constructor ConstructionHelper( s_connectionString, s_commandTimeout ); } } private void ConstructionHelper( string connectionString, int commandTimeout ) { // store the connection string in a member var. _connectionString = connectionString; // store the timout in a member var. _commandTimeout = commandTimeout; } public static string GetConnectionStringFromIdentity() { IIdentity identity = Thread.CurrentPrincipal.Identity as wxyz.Security.wxyzIdentityBase; string connectionString = null; if( identity != null ) { connectionString = ( (wxyz.Security.wxyzIdentityBase) identity ).ConnectionString ; } return connectionString; } public void Dispose() { if (_connection.State != ConnectionState.Closed) { _connection.Close(); } _connection.Dispose(); _connection = null; } protected void ExecuteNonQuery(SqlCommand command) { SqlConnection con = this.Connection; lock (con) { if (con.State != ConnectionState.Open) { con.Open(); } // don't need a try catch as this is only ever called from another method in this // class which will wrap it. command.Connection = con; command.Transaction = _transaction; command.CommandTimeout = _commandTimeout; for (int currentAttempt = 1; currentAttempt <= _maxDatabaseExecuteAttempts; currentAttempt++) { try { // do it command.ExecuteNonQuery(); // done, exit loop break; } catch (SqlException sex) { HandleDatabaseExceptions(currentAttempt, sex, command.CommandText); } } } } /// <summary> /// get the SqlConnection object owned by this database (already connected to db) /// </summary> public SqlConnection Connection { get { // check whether we've got a connection string (from either identity or static initialise) if ( _connectionString == null ) { throw new ArgumentNullException( "connectionString", "Connection string not set" ); } if ( _connection != null ) { return _connection; } else { _connection = new SqlConnection( _connectionString ); return _connection; } } } /// <summary> /// Return value from executed stored procedure /// </summary> public SqlInt32 ReturnValue { get { return _returnValue; } set { _returnValue = value; } } }
Database Access Class
/// <summary>Contains raw database commands for the Database</summary> public class AuthenticationDBCommands { /// <summary>Returns command object for spiAuthenticateLogin stored procedure in the Database</summary> public static SqlCommand GetLoginName() { System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetLoginName"); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.ReturnValue, false, 0, 0, "RETURN_VALUE", System.Data.DataRowVersion.Current, SqlInt32.Null)); cmd.Parameters.Add(new SqlParameter("@LoginId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "LoginId", DataRowVersion.Current, SqlInt64.Null)); cmd.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 50, ParameterDirection.InputOutput,false, 0, 0, "LoginName", DataRowVersion.Current, SqlString.Null)); return cmd; } } public class AuthenticationDBContext : DatabaseContextBase { public AuthenticationDBContext() : base() { } public void GetLoginName(SqlInt64 LoginId, ref SqlString LoginName) { SqlCommand cmd = AuthenticationDBCommands.GetLoginName(); cmd.Parameters[1].Value = LoginId; cmd.Parameters[2].Value = LoginName; base.ExecuteNonQuery(cmd); base.ReturnValue = (SqlInt32) cmd.Parameters[0].Value; LoginName= (SqlString)(cmd.Parameters[2].Value); } }
Usage:
protected string GetLoginName(long loginId) { SqlString loginName = SqlString.Null; using (AuthenticationDBContext dbc = new AuthenticationDBContext()) { dbc.GetLogin(loginId, ref loginName); } return loginName.Value; }
As you can see this is fairly standard stuff. But when the dbContext.GetLoginName() method is called by many different users in quick succession the loginName object is sometimes null.
I have tested the SQL and it always finds a value (I've inserted @LoginName into a table and it's never null). So the problem is happening after or in SqlCommand.ExecuteNonQuery();
I'm using .NET 4.5 and my database is on SQL 2008.
Has anyone seen anything like this before? Can anyone recommend any changes?
Thanks in advance,
Matt解决方案You forgot about magic word:RETURNat the end ofGetLoginNameprocedure.
Return Data from a Stored Procedure[^]
Along with my comment aboutlocking the connection, I'd like to mention that if you run into performance issues with trying to share one connection among many threads, then you may want to support many connections. This can be as simple as changing the Connection property so it instantiates a new Connection, rather than a reference to the shared connection. The system implements connection pooling and you would be leveraging that.
But... if you wind up with so many threads (hundreds) that you exceed the pool, then you may start interfereing with other processes (I've done that).
A solution to that is to implement your own connection pool that limits how many requests you make to the built-in pool at a time (a dozen or so).
One further note -- You are getting the connectionString in the property, if you do go the route of many connections, I would get the connectionString in the constructor.
As mentioned by you, "Under high loads" you are facing this issue. I think setting the "commandtimeout" property to zero may be of help. Try doing that. In your code i was not able to find the value of the _commandtimeout variable
这篇关于SQL ParameterDirection.InputOutput / SqlCommand.ExecuteNonQuery（）问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
