将SQL Server数据库公开为Web服务以从中获取数据

Is there any .NET tool to expose the data of my tables in Microsoft SQL Server as web services? Do I have to write the code? Are there any samples? What do your recommend as to how to expose the data?

是否有任何.NET工具将Microsoft SQL Server中的表数据公开为Web服务？我必须编写代码吗？有样品吗？您对如何公开数据有何建议？

4 个解决方案

#1

While to use WCF Data Services can be an option, just like Anton said, you should consider if it's a good idea to provide a direct path to your entire/partial database.

虽然使用WCF数据服务可以是一个选项，就像Anton说的那样，你应该考虑提供一个直接路径到整个/部分数据库是否是个好主意。

Another option is to build a data access layer, which will allow just a small operation set, like: "you can to add a customer, but you're not allowed to delete an invoice"

另一个选择是构建一个数据访问层，它只允许一个小的操作集，例如：“你可以添加一个客户，但你不能删除发票”

#2

As from SQL Server 2005 you can expose native XML web services directly from the database.

从SQL Server 2005开始，您可以直接从数据库公开本机XML Web服务。

SQL Server can be configured to listen natively for HTTP SOAP requests through an HTTP endpoint. In general you would want to expose stored procedures or user-defined functions as HTTP endpoints, so a little coding is required. But it should be very easy to follow from the examples.

可以将SQL Server配置为通过HTTP端点本地侦听HTTP SOAP请求。通常，您希望将存储过程或用户定义的函数公开为HTTP端点，因此需要进行一些编码。但是从例子中可以很容易地理解。

You would normally start by creating a stored procedure as follows:

您通常会从创建存储过程开始，如下所示：

CREATE PROCEDURE [dbo].[getContact]
   @ID [int]       
AS
BEGIN
   SELECT * FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ID   
END;

And then you would define your HTTP endpoint like this:

然后你会像这样定义你的HTTP端点：

CREATE ENDPOINT SQLEP_GetContact
    STATE = STARTED
AS HTTP
(
    PATH = '/Contact',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = 'localhost'
)
FOR SOAP
(
    WEBMETHOD 'ContactInfo' (NAME='AdventureWorks.dbo.getContact'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks',
    NAMESPACE = 'http://AdventureWorks/Contact'
);

After creating the endpoint, you can submit an HTTP request to the server to ensure that the endpoint is responding: http://localhost/Contact?wsdl.

创建端点后，您可以向服务器提交HTTP请求以确保端点正在响应：http：// localhost / Contact？wsdl。

To modify or to stop your endpoint, you can use the ALTER ENDPOINT command:

要修改或停止端点，可以使用ALTER ENDPOINT命令：

ALTER ENDPOINT SQLEP_GetContact
    STATE = STOPPED;

You may want to proceed by checking out the following articles:

您可以查看以下文章：

Devx.com - Easy Web Services with SQL Server 2005 HTTP Endpoints
Devx.com - 使用SQL Server 2005 HTTP端点的简易Web服务
MSDN - Overview of Native XML Web Services for Microsoft SQL Server 2005
MSDN - Microsoft SQL Server 2005的本机XML Web服务概述
Developer.com - Native XML Web Services in SQL Server 2005
Developer.com - SQL Server 2005中的原生XML Web服务

UPDATE: Following Ed Harper's comment below, please note that native XML web services have been deprecated in SQL Server 2008 (November 2009), and this feature will be removed in future version of SQL Server. Microsoft is suggesting using WCF web services instead. Source: MSDN - Native XML Web Services: Deprecated in SQL Server 2008

更新：请参阅下面的Ed Harper的评论，请注意SQL Server 2008（2009年11月）中已弃用本机XML Web服务，并且将在SQL Server的未来版本中删除此功能。 Microsoft建议使用WCF Web服务。来源：MSDN - 原生XML Web服务：在SQL Server 2008中已弃用

#3

Scott Hanselman explains how to create an OData / Open Data / WCF Data Service from a database using Visual Studio 2010:

Scott Hanselman解释了如何使用Visual Studio 2010从数据库创建OData / Open Data / WCF数据服务：

http://www.hanselman.com/blog/CreatingAnODataAPIFor*IncludingXMLAndJSONIn30Minutes.aspx

#4

I think you'll want to read up on WCF Data Services, available from .net Framework 3.5 and upwards.

我想你会想要阅读.net Framework 3.5及更高版本的WCF数据服务。

#1