是否可以使用参数化查询执行BACKUP语句? [英] Is it possible to execute a BACKUP statement using a parameterised query?

问题描述

我使用上面的sql命令.但是参数没有添加.我有2个文本框来指定位置和名称.但它不会传递给查询.有解决办法吗?

Iam using above sql command. But the parameters are not adding. I have 2 textboxes to specify location and name. but its not passing to the query. Any solution?

SqlConnection con = new SqlConnection(CS);
con.Open();
SqlCommand cmd= new SqlCommand("BACKUP DATABASE BillingSoftware TO DISK = '@Loc:\\@Name.BAK'",con);

cmd.Parameters.AddWithValue("@Loc", textBox_BackupDatabaseLocation.Text);
cmd.Parameters.AddWithValue("@Name", textBox_BackupDatabaseName.Text);
cmd.ExecuteNonQuery();

推荐答案

与其传递两个单独的参数并尝试在SQL脚本中串联它们，不如将完整路径作为单个参数传递.您可以使用 System.IO.Path.Combine :

Instead of passing two separate parameters and trying to concatenate them in the SQL script, just pass the complete path as a single parameter. You can create a valid path from a folder and filename with System.IO.Path.Combine:

var folder=textBox_BackupDatabaseLocation.Text;
var filename = textBox_BackupDatabaseName.Text;
var fullPath=Path.Combine(folder,filename);

//...
var cmd= new SqlCommand("BACKUP DATABASE BillingSoftware TO DISK = @fullPath",con);
cmd.Parameters.AddWithValue("@fullPath", fullPath);
cmd.ExecuteNonQuery();

Path.Combine 会生成正确的文件路径，即使文件夹名称中存在尾随或缺少斜杠的情况，也会使字符串连接中断.

Path.Combine generates a correct file path even if there are eg trailing or missing slashes in the folder name, which would make string concatenation break.

另一种选择是避免 SQL语句，并使用

Another option is to avoid SQL statements and use the SQL Server Management Objects library. In the end, SMO generates SQL commands but it exposes all functionality in an API that should make finding and specifying additional options very easy.

我说应该，因为文档不符合SQL Server标准.类引用在那里，但是示例......检查备份和还原数据库和事务日志.也许有一个更好的"SO文档"页面.

I say should because the documentation isn't up to SQL Server standards. The class reference is there but the examples ... Check Backing Up and Restoring Databases and Transaction Logs. Perhaps there is a better SO Documentation page for this.

无论如何，示例代码可以简化为此:

Anyway, the example code can be distilled to this:

//Local server
var srv = new Server();  
var db = srv.Databases["AdventureWorks2012"];    
var bk = new Backup
         {
             Action = BackupActionType.Database,
             BackupSetDescription = "Full backup of Adventureworks2012",
             BackupSetName = "AdventureWorks2012 Backup",
             Database = "AdventureWorks2012",
             Checksum = true
         };

var bdi = new BackupDeviceItem(fullDestinationPath,DeviceType.File);  
bk.Devices.Add(bdi);  
bk.SqlBackup(srv);  

优点是您可以将多个数据库作为目标，例如循环执行，获取进度通知等.

The advantage is that you can target multiple databases eg in a loop, get progress notifications etc.

更新

路径类包含可用于验证路径的方法，例如目录.存在.检查文件夹是否存在.

The Path class contains methods that can be used to validate paths, eg Path.IsPathRooted can be used to check whether the path is a full path. Other System.IO classes can be used for validation, eg Directory.Exists check if a folder exists.

这篇关于是否可以使用参数化查询执行BACKUP语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！