Microsoft® SQL Server™ 2000支持扩展存储过程,使你可以用其他语言编写组件扩展数据库的功能。以前本地代码VC等可以在做好动态链接库后直接放到System32目录下,然后通过在master库中创建存储过程调用sp_addextendedproc来实现功能。
.NET等托管语言编写的DLL要多做几步工作。首先托管类库要注册COM接口,而要注册COM接口就需要添加一个Interface,声明出要暴露方法,并添加GUID和DispId,例如:
[Guid("200D1D86-E232-4b52-84F1-6D7FED6EA99F")]
public interface COM_Interface
{
[DispId(1)]
void Show(ref long hWndParent, ref U8Login.clsLogin Login, Object mRepManage, Object mRepSysInfo);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
//排产书
[Guid("CD5C1120-FEA4-4abe-AD4D-BF20686A0E05"), ClassInterface(ClassInterfaceType.None)]
public class COM_SoMain : COM_Interface
{
public void Show(ref long hWndParent, ref U8Login.clsLogin Login, Object mRepManage, Object mRepSysInfo)
{
if (Func.cUser_Name == string.Empty)
{
Data.conn_Default = Login.UfDbName.Substring(Login.UfDbName.IndexOf(";") + 1);
Func.cUser_ID = Login.cUserId;
Func.cUser_Name = Login.cUserName;
Func.DataBaseName = Login.cAccName;
MessageBox.Show(Login.cUserId+Login.cUserName+Data.conn_Default);
}
new FormLayer.SaleOrder.SaleOrderList(0,true);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
COM_Interface 成员
}
然后编译并利用Regasm注册(或在VS2005项目属性中直接勾选)。
然后是编写存储过程了,这里我们可以通过sp_OACreate、sp_OADestroy创建和销毁对象,通过sp_OAMethod、sp_OAGetErrorInfo调用对象和捕捉错误,下面是我以前的发送邮件的一段代码:
CREATE PROCEDURE dbo.SendMail
@to varchar(50),
@subject varchar(255),
@body varchar(500)
AS
DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 创建对象实例。
EXEC @hr = sp_OACreate 'ExtendProcC.SendMails', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description='111'
RETURN
END
-- 调用对象方法。
EXEC @hr = sp_OAMethod @object, 'Send',@return OUT,'smtp.163.com','123@163.com','123','123@163.com','某某',@to,'1',@SUBJECT,@body
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
--PRINT @return
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
-- 销毁对象实例。
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
GO
扩展存储过程的DLL将会作为COM对象运行于SQLServer的地址空间,所以在实现类中最好只有方法,慎用全局字段。
.NET等托管语言编写的DLL要多做几步工作。首先托管类库要注册COM接口,而要注册COM接口就需要添加一个Interface,声明出要暴露方法,并添加GUID和DispId,例如:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
然后是编写存储过程了,这里我们可以通过sp_OACreate、sp_OADestroy创建和销毁对象,通过sp_OAMethod、sp_OAGetErrorInfo调用对象和捕捉错误,下面是我以前的发送邮件的一段代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)