C# 如何将 RESTORE HEADERONLY 的结果集放入一个 DataSet中？

Question

如题，我现在的方法有问题：preparedFile = "D:\temp.bak"
string sql = "RESTORE HEADERONLY FROM DISK = '" + preparedFile + "'";
try
{
    DataSet dsFiles = Query(sql);
}public DataSet Query(string commondText)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
             DataSet dataSet = new DataSet();
             connection.Open();
             cmd = connection.CreateCommand();
             cmd.CommandText = commondText;
             SqlDataAdapter SqlDA = new SqlDataAdapter();
             SqlDA.SelectCommand = cmd;
             SqlDA.Fill(dataSet);
             connection.Close();
             return dataSet;
         }
     catch (Exception ex)
     {
         throw (ex);
     }
}
不知道应该如何实现。

Answer 1

建个临时表 列的数目和restore headeronly返回的相同
用insert...exec

Answer 2

是用一个存储过程吧，完了怎么放到DataSet里面？能写具体些不，我对存储过程不太熟。

Answer 3

可以存储过程,以前有人写过 帮你copy了一个
IF OBJECT_ID(N'tempdb..#') IS NOT NULL
    DROP TABLE #
CREATE TABLE #(
    BackupName nvarchar(128) ,
    BackupDescription nvarchar(255) ,
    BackupType smallint ,
    ExpirationDate datetime ,
    Compressed tinyint ,
    Position smallint ,
    DeviceType tinyint ,
    UserName nvarchar(128) ,
    ServerName nvarchar(128) ,
    DatabaseName nvarchar(128) ,
    DatabaseVersion int ,
    DatabaseCreationDate datetime ,
    BackupSize numeric(20,0) ,
    FirstLSN numeric(25,0) ,
    LastLSN numeric(25,0) ,
    CheckpointLSN numeric(25,0) ,
    DatabaseBackupLSN numeric(25,0) ,
    BackupStartDate datetime ,
    BackupFinishDate datetime ,
    SortOrder smallint ,
    CodePage smallint ,
    UnicodeLocaleId int ,
    UnicodeComparisonStyle int ,
    CompatibilityLevel tinyint ,
    SoftwareVendorId int ,
    SoftwareVersionMajor int ,
    SoftwareVersionMinor int ,
    SoftwareVersionBuild int ,
    MachineName nvarchar(128) ,
    Flags int ,
    BindingID uniqueidentifier ,
    RecoveryForkID uniqueidentifier ,
    Collation nvarchar(128) ,
    FamilyGUID uniqueidentifier ,
    HasBulkLoggedData bit ,
    IsSnapshot bit ,
    IsReadOnly bit ,
    IsSingleUser bit ,
    HasBackupChecksums bit ,
    IsDamaged bit ,
    BeginsLogChain bit ,
    HasIncompleteMetaData bit ,
    IsForceOffline bit ,
    IsCopyOnly bit ,
    FirstRecoveryForkID uniqueidentifier ,
    ForkPointLSN numeric(25,0) NULL,
    RecoveryModel nvarchar(60) ,
    DifferentialBaseLSN numeric(25,0) NULL,
    DifferentialBaseGUID uniqueidentifier ,
    BackupTypeDescription nvarchar(60) ,
    BackupSetGUID uniqueidentifier NULL
)
--建表结束
--调用方法
INSERT # EXEC(N'
RESTORE HEADERONLY
FROM DISK = ''c:\db_test.bak''
WITH FILE = 1
RESTORE HEADERONLY
FROM DISK = ''c:\db_test_log.bak''
WITH FILE = 1
')

Answer 4

是INSERT INTO # EXEC(N'
RESTORE HEADERONLY
FROM DISK = ''c:\db_test.bak''
WITH FILE = 1
RESTORE HEADERONLY
FROM DISK = ''c:\db_test_log.bak''
WITH FILE = 1
')么？
提示对名象名 '#' 无效。

Answer 5

快搞差不多了，现在有这样一个问题：
INSERT INTO  #HEADERONLY  EXEC(N'RESTORE HEADERONLY FROM DISK = ''F:\Temp\DBBackup\新建文件夹\backupTest.bak''')
select * from #HEADERONLY
这两句在查询分析器中执行没问题，在代码中执行就报：对象名"#HEADERONLY”无效，不知什么原因。
 
       public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }
        public static DataSet Query(string commondText)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    DataSet dataSet = new DataSet();
                    connection.Open();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = commondText;
                    SqlDataAdapter SqlDA = new SqlDataAdapter();
                    SqlDA.SelectCommand = cmd;
                    SqlDA.Fill(dataSet);
                    connection.Close();
                    return dataSet;
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
            }
        }

Answer 6

搞定了。
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string sql = "";
                SqlCommand cmd = null;
                try
                {
                    //连接数据库
                    connection.Open();                    //删除临时表
                    sql =
                        @"IF OBJECT_ID(N'tempdb..#HEADERONLY') IS NOT NULL
                        DROP TABLE #HEADERONLY";
                    cmd = new SqlCommand(sql, connection);
                    cmd.ExecuteNonQuery();                    //创建临时表
                    sql =
                        @"CREATE TABLE #HEADERONLY (
                        [BackUpName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
                        [BackUpDescription] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
                        [BackUpType] [int] NULL ,
                        [ExpirationDate] [datetime] NULL ,
                        [Compressed] [int] NULL ,
                        [position] [int] NULL ,
                        [DeviceType] [int] NULL ,
                        [UserName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
                        [ServerName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
                        [DatabaseName] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
                        [DatabaseVersion] [int] NULL ,
                        [DatabaseCreationDate] [datetime] NULL ,
                        [BackupSize] [int] NULL,
                        [FirstLsn] [nvarchar] (128),
                        [LastLsn] [nvarchar] (128),
                        [CheckpointLsn] [nvarchar] (128),
                        [DifferentialBaseLsn] [nvarchar] (128),
                        [BackupStartDate] [datetime] NULL ,
                        [BackupFinishDate] [datetime] NULL ,
                        [SortOrder] [nvarchar] (128),
                        [CodePage] [int],
                        [UnicodeLocaleId] [int],
                        [UnicodeComparisonStyle] [int],
                        compatibilityLevel int,
                        SoftwareVendorid int,
                        softwareVersionMajor int,
                        softwareVersionMinor int,
                        softwareVersionBuild int,
                        [MachineName] [nvarchar] (128),
                        Flags int,
                        [BindingId] [nvarchar] (128),
                        [RecoveryForkId] [nvarchar] (128),
                        [Collation] [nvarchar] (128)
                        ) ON [PRIMARY]";
                    cmd = new SqlCommand(sql, connection);
                    cmd.ExecuteNonQuery();                    //将数据库信息插入临时表
                    sql = string.Format(
                        @"INSERT INTO #HEADERONLY EXEC(N'RESTORE HEADERONLY FROM DISK = ''{0}''')", backupFile);
                    cmd = new SqlCommand(sql, connection);
                    cmd.ExecuteNonQuery();                    //从临时表获取数据库信息
                    sql = @"SELECT * FROM #HEADERONLY";
                    DataSet dataSet = new DataSet();
                    cmd = connection.CreateCommand();
                    cmd.CommandText = sql;
                    SqlDataAdapter SqlDA = new SqlDataAdapter();
                    SqlDA.SelectCommand = cmd;
                    SqlDA.Fill(dataSet);
                    connection.Close();
                    return dataSet;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    connection.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }