我怎样才能从SQL Server系统消息的返回值？ [英] How can I get the return value from Sql Server system message?

问题描述

我想验证我在C＃中使用命令针对的SQL Server Express刚刚做备份

I am trying to verify the backup I have just done in c# using command against sql server Express

 string _commandText =  string.Format("RESTORE VERIFYONLY FROM DISK = '{0}'", backupLocation);

 SqlDataReader _sqlDataReader = SqlHelper.ExecuteReader("BookssortedSQLDbConnection", CommandType.Text, _commandText);

如果我在SSMS执行该命令将返回备份的文件1有效设置。但我怎样才能得到这个消息放回我的代码？

If I execute the command in SSMS it returns 'The backup set on file 1 is valid.' but how can I get this message back into my code?

一个读者不会工作，因为没有被返回的行

A reader wont work as there are no rows being returned.

请注意：我已经尝试了SMO。恢复对象，试图验证它，但它不工作，这就是为什么我做这种方式。

NOTE: I have tried the SMO.Restore object to try and verify it but it doesn't work and that is why I am doing it this way.

_restore.SqlVerify(srv, out _errorMessage); //returns false even though bakcup is fine

BTW返回false是要实现什么，我试图做

BTW - Open to suggestions as I don't think this is the ideal way to achieve what I am trying to do

推荐答案

参考消息（严重性低于10），并打印输出是理想的方式返回到客户端，并提出为 InfoMessage 的的SqlConnection 实例事件。每个事件都包含 SQLERROR 对象的集合（这是使用相同的类 SqlException.Errors ）。

Informational messages (with severity less than 10) and PRINT output are returned to the client, and raised as InfoMessage events by the SqlConnection instance. Each event contains a collection of SqlError objects (this is the same class used in SqlException.Errors).

下面是一个完整的例子，显示连接状态的变化，信息消息和异常。请注意，我用的ExecuteReader 而不是的ExecuteNonQuery ，但信息和异常的结果都是一样的。

Here's a complete example that shows connection state changes, info messages and exceptions. Note that I use ExecuteReader instead of ExecuteNonQuery, but the info and exception results are the same.

namespace Test
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class Program
    {
        public static int Main(string[] args)
        {
            if (args.Length != 2)
            {
                Usage();
                return 1;
            }

            var conn = args[0];
            var sqlText = args[1];
            ShowSqlErrorsAndInfo(conn, sqlText);

            return 0;
        }

        private static void Usage()
        {
            Console.WriteLine("Usage: sqlServerConnectionString sqlCommand");
            Console.WriteLine("");
            Console.WriteLine("   example:  \"Data Source=.;Integrated Security=true\" \"DBCC CHECKDB\"");
        }

        public static void ShowSqlErrorsAndInfo(string connectionString, string query)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.StateChange += OnStateChange;
                connection.InfoMessage += OnInfoMessage;

                SqlCommand command = new SqlCommand(query, connection);
                try
                {
                    command.Connection.Open();
                    Console.WriteLine("Command execution starting.");
                    SqlDataReader dr = command.ExecuteReader();
                    if (dr.HasRows)
                    {
                        Console.WriteLine("Rows returned.");
                        while (dr.Read())
                        {
                            for (int idx = 0; idx < dr.FieldCount; idx++)
                            {
                                Console.Write("{0} ", dr[idx].ToString());
                            }

                            Console.WriteLine();
                        }
                    }

                    Console.WriteLine("Command execution complete.");
                }
                catch (SqlException ex)
                {
                    DisplaySqlErrors(ex);
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }

        private static void DisplaySqlErrors(SqlException exception)
        {
            foreach (SqlError err in exception.Errors)
            {
                Console.WriteLine("ERROR: {0}", err.Message);
            }
        }

        private static void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            foreach (SqlError info in e.Errors)
            {
                Console.WriteLine("INFO: {0}", info.Message);
            }
        }

        private static void OnStateChange(object sender, StateChangeEventArgs e)
        {
            Console.WriteLine("Connection state changed: {0} => {1}", e.OriginalState, e.CurrentState);
        }
    }
}

这篇关于我怎样才能从SQL Server系统消息的返回值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！