我应该对这个数据库备份命令使用ExecuteNonQuery吗？ [英] Should I use ExecuteNonQuery for this db backup command

问题描述

我有一种方法可以让我开始备份数据库。我想知道的是我是否应该在这种情况下使用ExecuteNonQuery（）或是否有更好的使用方法。目前这是我的代码：

I have a method that allows me to kick off a back up of a data base. What I am wondering is if I should be using ExecuteNonQuery() in this context or if there is something better to use. Here is my code currently:

    public static void RunBackup(string dbName, string filePath, string backupName, string connString)
    {
        using(SqlConnection objConnection = new SqlConnection(connString))
        {

            string commmandText = "BACKUP DATABASE @DBName TO  DISK = @FilePath WITH NOFORMAT, NOINIT, NAME = @BackUpName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
            SqlCommand objCommand = new SqlCommand(commmandText,objConnection);
            objCommand.Parameters.AddWithValue("@dbName", dbName);
            objCommand.Parameters.AddWithValue("@FilePath", filePath);
            objCommand.Parameters.AddWithValue("@BackUpName", backupName);

            objConnection.Open();
            objCommand.ExecuteNonQuery();
            objConnection.Close();
        }
    }

我担心的一件事是验证备份是否完整且成功，同时处理需要花费较长时间才能完成的备份超时问题。

The one thing I am concerned about is being able to verify that the backup is complete and successful while handling time out issues for backups that take and extended time to complete.

推荐答案

为了处理长期运行的查询，我最终选择了以下方法：

To handle the issue of the long running query I ended up going with this:

    public static void RunBackup(string dbName, string filePath, string backupName, string connString)
    {
        string commmandText = "BACKUP DATABASE @DBName TO  DISK = @FilePath WITH NOFORMAT, NOINIT, NAME = @BackUpName, SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
        SqlConnection objConnection = new SqlConnection(connString);
        try
        {
            SqlCommand objCommand = new SqlCommand(commmandText, objConnection);
            objCommand.Parameters.AddWithValue("@dbName", dbName);
            objCommand.Parameters.AddWithValue("@FilePath", filePath);
            objCommand.Parameters.AddWithValue("@BackUpName", backupName);

            objConnection.Open();

            IAsyncResult result = objCommand.BeginExecuteNonQuery();
            while (!result.IsCompleted)
            {
                System.Threading.Thread.Sleep(100);
            }


            int count = objCommand.EndExecuteNonQuery(result);
         }
        catch (SqlException e)
        {
            throw e;
        }
        finally
        {
            objConnection.Close();

        }

    }

这将允许我可以执行命令而不会出现超时问题。我将在最终代码集中添加一些其他错误处理等。我可能会做一些额外的工作，看看是否可以通过EndExecuteNonQuery或AsyncCallBack获得更好的脚本返回状态。

This will allow me to execute the command without asyncronously without timeout issues. I will be adding some additional error handling etc in my final code set. I may do some additional work to see if I can get a better status returned at the end of the script that I can get via EndExecuteNonQuery or through an AsyncCallBack.

这篇关于我应该对这个数据库备份命令使用ExecuteNonQuery吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！