如何在异常发生之前检测到导致异常的条件?

简而言之,它看起来不像你可以用任何简单的方式。

我的第一个想法是运行这个SQL:

SELECT CASE WHEN USER = 'MyAppRole' THEN 1 ELSE 0 END

如果您使用SQL Server Management Studio,这是可行的,但在从C代码运行时失败。问题是,当调用sp_setapprole时,您得到的错误不会发生;当连接池调用sp_reset_connection时,它实际上会发生。当您第一次使用连接时,连接池会调用这个,并且在它之前没有办法进入。

所以我想你有四个选择:

1. 通过在连接字符串中添加“pooling=false;”来关闭连接池。
2. 使用其他方法连接到SQL Server。有比ADO.NET低级别的API,但坦率地说,它可能不值得麻烦。
3. 正如Casperone所说,您可以修复代码以正确关闭连接。
4. 捕获异常并重置连接池。不过,我不确定这会对其他开放连接造成什么影响。示例代码如下:

class Program
{
    static void Main(string[] args)
    {
        SqlConnection conn = new SqlConnection("Server=(local);Database=Test;UID=Scrap;PWD=password;");

        setAppRole(conn);
        conn.Close();

        setAppRole(conn);
        conn.Close();
    }

    static void setAppRole(SqlConnection conn) 
    {
        for (int i = 0; i < 2; i++)
        {
            conn.Open();
            try
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "exec sp_setapprole ";
                    cmd.CommandText += string.Format("@rolename='{0}'", "MyAppRole");
                    cmd.CommandText += string.Format(",@password='{0}'", "password1");
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                if (i == 0 && ex.Number == 0)
                {
                    conn.Close();
                    SqlConnection.ClearPool(conn);
                    continue;
                }
                else
                {
                    throw;
                }
            }
            return;
        }
    }
}

它实际上是在连接池调用时发生的 重置连接。连接池在首次使用时调用此 一个连接,在它之前是没有办法进入的。

基于 Martin Brown's answer ,您可以尝试将“connection reset=false”添加到连接字符串中,作为“在”sp_reset_连接之前进入的方法。(见) Working with “soiled” connections “了解此设置的许多缺点。)

你的问题是 known issue with connection pooling . 建议的解决方法是禁用连接池…如果这是一个桌面应用程序,可能值得考虑保持几个连接打开(另请参见 the article linked above )

这些天(SQL 2005+)建议(在 Application Roles and Connection Pooling )是“利用 new security mechanisms 您可以使用它来代替应用程序角色”,如execute as。

我不确定您的问题,但是我认为如果您创建了新的连接对象而不是重用它们,您可以避免这个问题。所以不要这么做

c.Open();
blabla;
c.Close();
c.Open(); 
kaboom...

您可以执行以下操作:

using (new SqlConnection ...)
{
  c.Open();
  blabla;
}

using (new SqlConnection ... )
{
  c.Open();
  no kaboom?
}

(请原谅伪代码…我的eeepc上的键盘无法使用…)

没有办法清除所有连接的池吗?sqlpools.clear之类的。

您可以简单地尝试捕获异常并创建一个新连接,这将强制池创建一个完整的新连接。

我也在回复你之前的问题时发布了这个。调用sp\u setapprole时,完成后应调用sp\u unsetapprole,我在这里提出的解决方案将帮助您:

Detecting unusable pooled SqlConnections

似乎您正在调用sp_setapprole,但没有调用sp_unsetapprole,然后让连接返回池。

我建议在IDisposable实现中使用一个结构(或者一个类,如果必须跨方法使用它的话),它将为您处理这个问题:

public struct ConnectionManager : IDisposable
{
    // The backing for the connection.
    private SqlConnection connection;

    // The connection.
    public SqlConnection Connection { get { return connection; } }

    public void Dispose()
    {
        // If there is no connection, get out.
        if (connection == null)
        {
            // Get out.
            return;
        }

        // Make sure connection is cleaned up.
        using (SqlConnection c = connection)
        {
            // See (1).  Create the command for sp_unsetapprole
            // and then execute.
            using (SqlCommand command = ...)
            {
                // Execute the command.
                command.ExecuteNonQuery();
            }
        }
    }

    public ConnectionManager Release()
    {
        // Create a copy to return.
        ConnectionManager retVal = this;

        // Set the connection to null.
        retVal.connection = null;

        // Return the copy.
        return retVal;        
    }

    public static ConnectionManager Create()
    {
        // Create the return value, use a using statement.
        using (ConnectionManager cm = new ConnectionManager())
        {
            // Create the connection and assign here.
            // See (2).
            cm.connection = ...

            // Create the command to call sp_setapprole here.
            using (SqlCommand command = ...)
            {
                // Execute the command.
                command.ExecuteNonQuery();

                // Return the connection, but call release
                // so the connection is still live on return.
                return cm.Release();
            }
        }
    }
}

1. 您将创建与调用sp_setapprole存储过程相对应的sqlcommand。您可以生成cookie并将其存储在私有成员变量中。
2. 这是您创建连接的地方。

客户端代码如下所示:

using (ConnectionManager cm = ConnectionManager.Create())
{
    // Get the SqlConnection for use.
    // No need for a using statement, when Dispose is
    // called on the connection manager, the connection will be
    // closed.
    SqlConnection connection = cm.Connection;

    // Use connection appropriately.
}

您可以检查C.State(ConnectionState对象),它应该是以下对象之一:

System.Data.ConnectionState.Broken
System.Data.ConnectionState.Closed
System.Data.ConnectionState.Connecting
System.Data.ConnectionState.Executing
System.Data.ConnectionState.Fetching
System.Data.ConnectionState.Open

@EDG:你在评论中说,“…只有当它到达实际服务器时,才会遇到安全问题,如msg-quote中所述”。

这就指出了问题的根源:你 是 遇到安全问题,这似乎是不可避免的,因为调用代码假定的是另一个身份,而不是用来打开连接的身份。这自然会生成一个安全日志条目。

由于身份更改是按设计进行的,所以解决方案可能是过滤安全日志。事件查看器有一个筛选当前日志操作,可以按关键字或事件ID进行筛选。

+汤姆

尝试移动 sp_unsetapprole (它真的是存储过程的名称吗?可能, sp_dropapprole 是正确的吗?)到 setAppRole() 并在添加应用程序角色之前执行。