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?
Add a comment
|
4 Answers
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
3 Comments
Ed Harper
It's worth noting that the Native XML webservices are deprecated in SQL 2008.
Daniel Vassallo
Thanks Ed. I wasn't aware of that. Source: msdn.microsoft.com/en-us/library/cc280436.aspx. I updated my answer with this, and with Microsoft's suggestion to use WCF.
Warren Rumak
This feature has been removed entirely in SQL 2012.
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"
2 Comments
Saurabh Rai
Just to Add, you can also write ASP.NET Web APIs and expose the relevant Api(e.g GET data from DB, etc) as per your need.
Seaport
Is it possible to use SSRS? I know SSRS have APIs for reports catalog. I am not sure whether we can create an API expose data from a data source.
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
Comments
I think you'll want to read up on WCF Data Services, available from .net Framework 3.5 and upwards.
