Expose SQL Server database as web service to get data from

Question

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?

Answer 1

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

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.

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:

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.

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

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
• MSDN - Overview of Native XML Web Services for Microsoft SQL Server 2005
• Developer.com - Native XML Web Services in SQL Server 2005

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

Answer 2

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.

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"

Answer 3

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

http://www.hanselman.com/blog/CreatingAnODataAPIForStackOverflowIncludingXMLAndJSONIn30Minutes.aspx

Answer 4

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