从SQL Server 2005中提取.NET程序集 [英] Extracting a .NET Assembly from SQL Server 2005
问题描述
我想帮助一个私人朋友（谁现在也是一个客户机）与SQL CLR
相关的问题。他有一个具有3 .NET程序集
在其内嵌数据库的SQL Server中。他要我帮他从
数据库中提取组件，并将其保存为磁盘上的.dll文件。这甚至可能？
I am trying to help a personal friend (who now also is a client) with a SQL CLR related problem. He has a SQL Server with a database that has 3 .NET assemblies embeded in it. He asked me to help him extract the assemblies from within the database and save them as .dll files on the disk. Is this even possible?
推荐答案
是的，这是可能的。该组件的实际二进制表示过
在SQL目录服务器。也就是说，如果您运行
sys.assembly_files和sys.assemblies之间的连接，你可以得到的所有信息，你
的需求。该组件的二进制文件是sys.assembly_files
视图的内容列。
Yes, this is possible. The actual binary representation of the assemblies live in the SQL catalog for your server. Namely, if you run a join between sys.assembly_files and sys.assemblies you can get to all the information you need. The assemblies binary is in the content column of the sys.assembly_files view.
但为了从SQL Server和到磁盘上的
档，你将不得不编写需要在运行一些.NET代码的二进制表示在那里你指的程序集的
同一个数据库所在了。在Visual
工作室启动SQL CLR项目并添加一个类来它用下面的代码：
But in order to extract the binary representation from SQL Server and into a file on disk you will have to write some .NET code that needs to run on the same database where the assemblies you refer to are located now. In Visual Studio start a SQL CLR project and add a class to it with the following code:
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;
namespace ExtractSqlAssembly {
[PermissionSet(SecurityAction.Demand, Unrestricted = true, Name = "FullTrust")]
public partial class SaveSqlAssembly {
[SqlProcedure]
public static void SaveAssembly(string assemblyName, string path) {
string sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname";
using (SqlConnection conn = new SqlConnection("context connection=true")) {
using (SqlCommand cmd = new SqlCommand(sql, conn)) {
SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
param.Value = assemblyName;
cmd.Parameters.Add(param);
cmd.Connection.Open(); // Read in the assembly byte stream
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
SqlBytes bytes = reader.GetSqlBytes(0);
// write the byte stream out to disk
FileStream bytestream = new FileStream(path, FileMode.CreateNew);
bytestream.Write(bytes.Value, 0, (int)bytes.Length);
bytestream.Close();
}
}
}
}
}
然后生成该项目并将其部署到你的数据库。确保CLR
启用配置选项在SQL Server上启用。这大概是
已经启用了，因为你有它集。如果CLR执行不是
激活，您可以运行在SSMS下面的代码来启用它：
Then build the project and deploy it to your database. Make sure that the CLR Enabled configuration option is enabled on the SQL Server. This is probably already enabled, since you have assemblies on it. In case clr execution is not enabled you can run the following code on SSMS to enable it:
sp_configure 'clr enabled', 1
go
reconfigure
go
，你需要知道的一件事是在默认情况下SQL Server可能
不允许你写从.NET代码的磁盘。如果当您通过调用
SSMS存储过程运行上面的代码获得FileIO专注
安全错误，你将需要配置为
总成设置适当的权限。您可以通过SSMS这样做：右击新的装配，并期待在
的权限设置在属性对话框。将其设置为外部访问。现在你
应该能够在SSMS运行下面的代码来导出组件：
One more thing that you need to be aware of is the by default SQL server may not allow you to write to the disk from the .NET code. If you get a FileIO security error when you run the code above by calling the stored procedure in SSMS, you will need to configure the proper permission set for the assembly. You can do this via SSMS: right-click the new assembly and look at the Permission Set in the Properties dialog. Set it to External Access. Now you should be able to export your assemblies by running the following code in SSMS:
exec SaveAssembly 'AssemblyName', 'f:\path\to\assemblyname.dll'
希望这对你的作品...
Hope this works for you...
这篇关于从SQL Server 2005中提取.NET程序集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
