c# - SQL parametrized query for restoring backup file

Question

I want to refactor the following sql query using parametrized query. Is it possible?

 string cmd= $@" IF EXISTS(select * from sys.databases where name='{ dbname}')
                                    ALTER DATABASE[{ dbname}] 
                                    SET OFFLINE WITH ROLLBACK IMMEDIATE;

                                    RESTORE DATABASE[{ dbname}]  
                                    FROM DISK = '{backupFile}'
                                    WITH REPLACE,
                                    MOVE '{mdfFileName}' TO '{sqlDataFolderPath}{dbname}.MDF',   
                                    MOVE '{logFileName}' TO '{sqlDataFolderPath}{dbname}.LDF';

                                    ALTER DATABASE[{ dbname}] 
                                    SET ONLINE ";

            SqlCommand cmd= con.CreateCommand();
            command.CommandText = sqlCommand;
            command.ExecuteNonQuery();

As a first step I used a parametrized query to retrive the information regarding mdf and ldf via RESTORE FILELISTONLY FROM DISK = @backupFilePath. And the next step should be the restore operation. Currently is working only with the above approach but I would like to refactor it in order to solve the security hotspot reported by sonar (sql injection) .

UPDATE:

I've tried to use the same approach as for BACKUP but without success, no error is thrown but the mdf, ldf files are not moved.

string sqlDynamicQuery = "DECLARE @sql nvarchar(max) = (" +
                                         "SELECT 'ALTER DATABASE ' + QUOTENAME(name) + " +
                                         "' SET OFFLINE WITH ROLLBACK IMMEDIATE;'" +
                                         "+ ' RESTORE DATABASE ' + QUOTENAME(name) + " +
                                         "' FROM DISK = @backupfilepath WITH REPLACE, " +
                                         "MOVE @mdfFileName TO @mdfFilePath, " +
                                         "MOVE @logFileName TO @ldfFilePath ' " +
                                         "+ ' ALTER DATABASE ' + QUOTENAME(name) +" +
                                         "'SET ONLINE ' from sys.databases where name= @database); "+
                                         "EXEC sp_executesql "+
                                         "@sql, " +
                                         "N'@backupfilepath nvarchar(128), @mdfFileName nvarchar(128),  @mdfFilePath  nvarchar(128), @logFileName nvarchar(128),  @ldfFilePath  nvarchar(128)', "+
                                         "@backupfilepath = @backupfilepath, "+
                                         "@mdfFileName = @mdfFileName, "+
                                         "@mdfFilePath = @mdfFilePath, "+
                                         "@logFileName = @logFileName, "+
                                         "@ldfFilePath = @ldfFilePath; ";

                using (SqlCommand sqlCmd = new SqlCommand(sqlDynamicQuery, _sqlConnection))
                {
                    sqlCmd.Parameters.Add("@database", SqlDbType.NVarChar, 128).Value = databaseName;
                    sqlCmd.Parameters.Add("@backupfilepath", SqlDbType.NVarChar, 128).Value = backupFile;
                    sqlCmd.Parameters.Add("@mdfFileName", SqlDbType.NVarChar, 128).Value = model.MdfFileName;
                    sqlCmd.Parameters.Add("@mdfFilePath", SqlDbType.NVarChar, 128).Value = Path.Combine(_sqlDataFolderPath, databaseName + ".MDF");
                    sqlCmd.Parameters.Add("@logFileName", SqlDbType.NVarChar, 128).Value = model.LogFileName;
                    sqlCmd.Parameters.Add("@ldfFilePath", SqlDbType.NVarChar, 128).Value = Path.Combine(_sqlDataFolderPath, databaseName + ".LDF");

                    sqlCmd.ExecuteNonQuery();
                }
   

I have also tried in ssms

DECLARE @database nvarchar(128)
DECLARE @backupfilepath nvarchar(128)
DECLARE @mdfFilePath nvarchar(128)
DECLARE @ldfFilePath nvarchar(128)
DECLARE @mdfFileName nvarchar(128)
DECLARE @logFileName nvarchar(128)

SET @database = 'v1'
set @backupfilepath = 'C:\Data\MyDocs\DatabaseBak\v1.bak'
SET @mdfFileName = 'p1'
SET @logFileName = 'p1_log'
SET @mdfFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\v1.MDF'
SET @ldfFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\v1.LDF'



DECLARE @sql nvarchar(max) = (SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET OFFLINE WITH ROLLBACK IMMEDIATE;' +  ' RESTORE DATABASE ' + QUOTENAME(name) + ' FROM DISK = @backupfilepath WITH REPLACE, MOVE @mdfFileName TO @mdfFilePath, MOVE @logFileName TO @ldfFilePath '+ ' ALTER DATABASE ' + QUOTENAME(name) + 'SET ONLINE  ' from sys.databases where name= @database);
print @sql
EXEC sp_executesql
    @sql,
    N'@backupfilepath nvarchar(128), @mdfFileName nvarchar(128),  @mdfFilePath  nvarchar(128), @logFileName nvarchar(128),  @ldfFilePath  nvarchar(128)',
    @backupfilepath = @backupfilepath,
    @mdfFileName = @mdfFileName,
    @mdfFilePath = @mdfFilePath,
    @logFileName = @logFileName,
    @ldfFilePath = @ldfFilePath;

UPDATE:

It works now on SSMS:

DECLARE @database nvarchar(128)
DECLARE @backupfilepath nvarchar(128)
DECLARE @mdfFilePath nvarchar(128)
DECLARE @ldfFilePath nvarchar(128)
DECLARE @mdfFileName nvarchar(128)
DECLARE @logFileName nvarchar(128)

SET @database = 'v1'
set @backupfilepath = 'C:\Data\MyDocs\DatabaseBak\v1.bak'
SET @mdfFileName = 'p1'
SET @logFileName = 'p1_log'
SET @mdfFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\v1.MDF'
SET @ldfFilePath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\v1.LDF'


DECLARE @SQLString nvarchar(max) ='IF EXISTS(select * from sys.databases where name='+'''' + @database +''''+')'+
   'ALTER DATABASE [' + @database + '] ' +
   'SET OFFLINE WITH ROLLBACK IMMEDIATE ' +
   'RESTORE DATABASE ['+@database +
   '] FROM  DISK ='+ '''' + @backupfilepath +''''+
   ' WITH  REPLACE,  '+
   'MOVE '+''''+@mdfFileName+''''+ ' TO '+ '''' + @mdfFilePath +''''+',  '+
   'MOVE '+''''+@logFileName+''''+ ' TO '+ '''' + @ldfFilePath +''''+';  '+
   'ALTER DATABASE [' + @database + '] ' +
   'SET ONLINE '
print @SQLString


EXEC sp_executesql
    @SQLString,
    N'@database nvarchar(128), @backupfilepath nvarchar(128), @mdfFileName nvarchar(128),  @mdfFilePath  nvarchar(128), @logFileName nvarchar(128),  @ldfFilePath  nvarchar(128)',
    @database = @database,
    @backupfilepath = @backupfilepath,
    @mdfFileName = @mdfFileName,
    @mdfFilePath = @mdfFilePath,
    @logFileName = @logFileName,
    @ldfFilePath = @ldfFilePath;

Answer 1

等待大神答复