SQLServer 2005中创建Web Services (转)
SQLServer 2005中数据库引擎加入了对Native Web Services的支持。这点让我们可以很方便把 SQLServer 2005中的自定义函数或存储过程等非常迅速的发布为XML Web Services接口公布出来。这点对于一些便捷的数据应用和移动应用提供了非常好的解决方案。
1、在SQLServer 2005示范库AdventureWorks中创建一个根据Color获取Products的存储过程。
2 @Color nvarchar(15)
3 AS
4 BEGIN
5 SET NOCOUNT ON;
6
7 SELECT ProductID, [Name], ProductNumber, Color
8 FROM Production.Product
9 WHERE (Color = @Color)
10 END
2、利用SQLServer 2005新增的 Create EndPoint 创建Web Services。
2 STATE = Started
3 AS HTTP
4 (
5 PATH = '/Products',
6 AUTHENTICATION = (INTEGRATED),
7 PORTS = (CLEAR),
8 SITE = 'localhost',
9 CLEAR_PORT = 2006
10 )
11 FOR SOAP
12 (
13 WEBMETHOD 'GetProductsByColor'
14 (
15 NAME = 'AdventureWorks.dbo.GetProductsByColor',
16 SCHEMA = STANDARD
17 ),
18 WSDL = DEFAULT,
19 DATABASE = 'AdventureWorks',
20 NAMESPACE = DEFAULT
21 )
上面的 STATE = Started,意思是说创建好这个 EndPoint 后自动启动并开始监听外部请求。Path = '/Products' 表示发布的Web Services的路径,AUTHENTICATION = (INTEGRATED)表示验证方式采用集成验证,PORTS = (CLEAR)表示采用HTTP协议,SITE='localhost'表示发布在本机,CLEAR_PORT=2006表示发布的端口号。上面整体意思,表示发布后的Web Services的地址应该是: http://localhost:2006/Products?WSDL。如果不指定CLEAR_PORT,则默认发布到80端口。因为我的Windows XP的80端口被我家的 IIS 占用了,所以只好用其他端口了。接着看下面是WebService的接口声明部分。WEBMETHOD 'GetProductsByColor'()表示发布的WebService的方法名称为 GetProductsByColor,其他都采用默认设置。
完成上面命令后,看看这个地方出现了 AdvProduct 的 EndPoint 了哦。
最后,你只要向用C#写的WebService一样进行引用使用即可。方便吧?!
那么,如果我们不想在SQLServer Management Studio中进行这一系列的操作,而想通过直接在C#中编写代码完成上面,应该如何做呢?答案是利用SQLServer 2005提供的SQLServer Management Object啦。具体再动手下:
1、新建一个WinForm或Console的应用项目,添加三个引用,Microsoft.SqlServer.ConnectionInfo,Microsoft.SqlServer.Smo和 Microsoft.SqlServer.SqlEnum。其中核心的当然就是Microsoft.SqlServer.Smo了,它就是我们说的 SqlServer Management Object核心所在了。
2、写如下C#代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
大家可以理解上面的代码,对照上面的Create Endpoint的SQL语句来理解,那就非常容易理解了。