我正在考虑创建一个工具来加载SQL Server 2008数据库中的varbinary(max)字段,其中的文件是从打开的文件对话框中选择的 . 我可以使用任何此类工具或等效工具吗?我的SQL服务器位于托管环境中,我无法对服务器进行物理访问,因此无法使用TSQL加载它 .
PowerShell怎么样?
# from: http://sev17.com/2010/05/t-sql-tuesday-006-blobs-filestream-and-powershell/ # $server = "superfly\sqlexpress" $database = "Yak" $query = "INSERT dbo.FileStore VALUES (@FileData, @FileName)" $filepath = "d:\yak.txt" $FileName = get-childitem $filepath | select -ExpandProperty Name $connection=new-object System.Data.SqlClient.SQLConnection $connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database $command=new-object system.Data.SqlClient.SqlCommand($query,$connection) $command.CommandTimeout=120 $connection.Open() $fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Open',[System.IO.FileAccess]'Read') $buffer = new-object byte[] -ArgumentList $fs.Length $fs.Read($buffer, 0, $buffer.Length) $fs.Close() $command.Parameters.Add("@FileData", [System.Data.SqlDbType]"VarBinary", $buffer.Length) $command.Parameters["@FileData"].Value = $buffer $command.Parameters.Add("@FileName", [System.Data.SqlDbType]"NChar", 50) $command.Parameters["@FileName"].Value = $FileName $command.ExecuteNonQuery() $connection.Close()
1 回答
PowerShell怎么样?