c# 和 SMO - 获取消息输出，例如“表已存在”用于记录
我正在使用 SQL 管理对象连接到 SQL 服务器。目前，对于我的示例，它们包含简单的“创建表”命令。
我故意运行此代码两次，以导致“表已存在”错误。
但是我下面的事件没有被触发。
任何人都有任何想法，我如何在我的代码中获取此消息，然后更改 ExecutionType 以在导致异常的错误上停止（我不想这样做，我想继续）
我的代码：
public void executeSomeSQL() {
FileInfo file = new FileInfo(@"\c:\sqlcommands.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
conn.InfoMessage +=new SqlInfoMessageEventHandler(conn_InfoMessage);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
server.ConnectionContext.ExecuteNonQuery(script,ExecutionTypes.ContinueOnError);
MessageBox.Show("All Done");
}
事件：-
public void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e) {
textBox3.Text += "1:"+DateTime.Now.ToString();
}
public void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e) {
textBox3.Text += "2:" + DateTime.Now.ToString();
}
I am using SQL Management objects to connect to SQL server. At the moment they contain simple "create table" commands for my example.
I run this code twice on purpose to cause an error for "table already exists".
However my events below are not getting triggered.
Anyone got any ideas, how I can get hold of this message in my code other then changing the ExecutionType to stop on errors causing exceptions ( which I dont want to do, I want to continue)
My Code:
public void executeSomeSQL() {
FileInfo file = new FileInfo(@"\c:\sqlcommands.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
conn.InfoMessage +=new SqlInfoMessageEventHandler(conn_InfoMessage);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
server.ConnectionContext.ExecuteNonQuery(script,ExecutionTypes.ContinueOnError);
MessageBox.Show("All Done");
}
Events:-
public void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e) {
textBox3.Text += "1:"+DateTime.Now.ToString();
}
public void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e) {
textBox3.Text += "2:" + DateTime.Now.ToString();
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据 MSDN：
在已存在的表上执行 CreateTable 时，错误严重性为 16，这将绕过 InfoMessage 事件。
您可能希望将 TSQL 包装在 Try...Catch 块中并使用 RAISEERROR。 TRY…CATCH 构造捕获所有严重性大于 10 且不会终止数据库连接的执行错误。
或者，您可能希望在 TSQL 中添加检查表是否存在，并执行打印操作，该打印操作将引发您的 InfoMessage 事件。
According to MSDN:
The error severity for a CreateTable on a table that already exists is 16, which bypasses the InfoMessage event.
You might want to wrap your TSQL in a Try...Catch block and use RAISEERROR. A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
Or, you might want to add a check in your TSQL for the existence of the table and do a Print which will be raised to your InfoMessage event.
我不是程序员（DBA），因此我不确定。
我认为以下凸轮会有帮助
conn.FireInfoMessageEventOnUserErrors = true;
I'm not programmer (DBA), therefore I'm not sure.
I assume following cam be helpfull
conn.FireInfoMessageEventOnUserErrors = true;