我应该为此数据库备份命令使用 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屋！