如何检测条件，导致异常发生之前？ [英] How can I detect condition that causes exception before it happens?
问题描述
我没有运气这个问题所以我制作这个简单，AS-可能的测试情况来说明这个问题。
在code以下，才可能检测到连接不可用尝试使用它之前？
的SqlConnection C =新的SqlConnection（myConnString）;
c.Open（）; //创建池
setAppRole（C）; // 好
c.Close（）; //返回连接池
C =新的SqlConnection（myConnString）; //会从池中连接
c.Open（）; // OK ...但等待它...
// ???如何检测KABOOM它发生过吗？
setAppRole（C）; // KABOOM
在KABOOM表现为在Windows事件日志中的错误;
的连接已断开，因为打开它随后承担了新的安全上下文，然后将主要试图重置其模拟的安全上下文的连接。不支持此方案。请参阅模拟概述联机丛书中。
...加上在code异常。
setAppRole是一个简单的方法来设置连接上的应用程序的作用。它类似于这样...
静态无效setAppRole（SqlConnection的康涅狄格州）{
使用（IDbCommand的CMD = conn.CreateCommand（））
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =EXEC sp_setapprole;
cmd.CommandText + =的String.Format（@角色名称='{0}'，MYUSER）;
cmd.CommandText + =的String.Format（@密码='{0}'，MYPASSWORD）;
cmd.ExecuteNonQuery（）;
}
}
在C尝试真正的$ C $是由使用的 sp_unsetapprole 之前关闭连接，但它不能总是保证（继承越野车多线程应用程序）。在任何情况下，它仍然似乎是合理的期望能够导致其之前检测KABOOM。
总之，它看起来并不像你可以在任何简单的方式。
我首先想到的是运行这个SQL语句：
SELECT CASE WHEN USER ='MyAppRole'，那么1 ELSE 0 END
这工作，如果你使用SQL Server Management Studio中，但是当你从C＃code运行失败。麻烦的是你得到的不是发生时调用sp_setapprole制成，它实际上是存在的，当连接池调用sp_reset_connection的错误。连接池调用此当您第一次使用的连接，也没有办法让在它之前。
所以我猜你有四个选项：
- 在打开连接通过添加池关池=假;到连接字符串。
- 使用一些其他的方式来连接到SQL Server。有较低级别的API比ADO.Net，但坦率地说这可能是不值得的麻烦。
- 当casperOne说，你可以解决您的code正确地关闭连接。
- 捕获异常，并重置连接池。我不知道这是什么会做其他打开的连接，但。下面的例子code：
类节目
{
静态无效的主要（字串[] args）
{
SqlConnection的康恩=新的SqlConnection（服务器=（本地）;数据库=测试; UID =报废; PWD =密码;）;
setAppRole（康涅狄格州）;
conn.Close（）;
setAppRole（康涅狄格州）;
conn.Close（）;
}
静态无效setAppRole（SqlConnection的康涅狄格州）
{
的for（int i = 0;我2;我++）
{
conn.Open（）;
尝试
{
使用（IDbCommand的CMD = conn.CreateCommand（））
{
cmd.CommandType = CommandType.Text;
cmd.CommandText =EXEC sp_setapprole;
cmd.CommandText + =的String.Format（@角色名称='{0}'，MyAppRole）;
cmd.CommandText + =的String.Format（@密码='{0}'，密码1）;
cmd.ExecuteNonQuery（）;
}
}
赶上（SQLEXCEPTION前）
{
如果（我== 0安培;＆安培; ex.Number == 0）
{
conn.Close（）;
SqlConnection.ClearPool（康涅狄格州）;
继续;
}
其他
{
扔;
}
}
返回;
}
}
}
I had no luck with this question so I've produced this simple-as-possible-test-case to demonstrate the problem.
In the code below, is it possible to detect that the connection is unusable before trying to use it?
SqlConnection c = new SqlConnection(myConnString);
c.Open(); // creates pool
setAppRole(c); // OK
c.Close(); // returns connection to pool
c = new SqlConnection(myConnString); // gets connection from pool
c.Open(); // ok... but wait for it...
// ??? How to detect KABOOM before it happens?
setAppRole(c); // KABOOM
The KABOOM manifests as a error in the Windows event log;
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
...plus an exception in code.
setAppRole is a simple method to set an application role on the connection. It is similar to this...
static void setAppRole(SqlConnection conn) {
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "exec sp_setapprole ";
cmd.CommandText += string.Format("@rolename='{0}'",myUser);
cmd.CommandText += string.Format(",@password='{0}'",myPassword);
cmd.ExecuteNonQuery();
}
}
In the real code an attempt is made to use sp_unsetapprole prior to closing the connection but it cannot always be guaranteed (inherited buggy multithreaded app). In any case it still seems reasonable to expect to be able to detect the kaboom before causing it.
In short, it doesn't look like you can in any simple way.
My first thought was to run this SQL:
SELECT CASE WHEN USER = 'MyAppRole' THEN 1 ELSE 0 END
This works if you use SQL Server Management Studio, but fails when you run it from C# code. The trouble is the error you are getting is not occuring when the call to sp_setapprole is made, it is actually occuring when connection pooling calls sp_reset_connection. Connection pooling calls this when you first use a connection and there is no way to get in before it.
So I guess you have four options:
- Turn connection pooling off by adding "Pooling=false;" to your connection string.
- Use some other way to connect to SQL Server. There are lower level APIs than ADO.Net, but frankly it is probably not worth the trouble.
- As casperOne says you could fix your code to close the connection correctly.
- Catch the exception and reset the connection pool. I'm not sure what this will do to other open connections though. Example code below:
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;
}
}
}
这篇关于如何检测条件，导致异常发生之前？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
