SQL Server 2005 XML Web Services - Create HTTP Endpoint Using SMO
by David Hayden ( C# .NET Developer )
As mentioned in an earlier post, Native XML Web Services in SQL Server 2005 - Creating HTTP Endpoints - Native HTTP, SQL Server 2005 has this new feature that allows one to expose XML Web Services from the database. This is really cool to play with, because using T-SQL you can expose a stored procedure as a web service that you can then consume in your client applications without the need for IIS.
Here is the T-SQL mentioned in the previous post:
CREATE ENDPOINT AW_Contacts
STATE = Started
AS HTTP
(
PATH = '/Contacts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'GetContacts'
(NAME = 'AdventureWorks.dbo.GetContacts'),
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = DEFAULT
)
But how about doing the same thing using SQL Server Management Objects?
No problem. Give this code a spin:
// Connect to Local Instance
Server server = new Server();
// Create New Endpoint
Endpoint newEndpoint = new Endpoint(server,"AW_Contacts");
newEndpoint.ProtocolType = ProtocolType.Http;
newEndpoint.Protocol.Http.HttpAuthenticationModes
= HttpAuthenticationModes.Integrated;
newEndpoint.Protocol.Http.HttpPortTypes
= HttpPortTypes.Clear;
newEndpoint.Protocol.Http.WebSite = "*";
newEndpoint.Protocol.Http.WebSiteUrlPath = "/Contacts";
newEndpoint.EndpointType = EndpointType.Soap;
newEndpoint.Payload.Soap.DefaultDatabase
= "AdventureWorks";
newEndPoint.Payload.Soap.XmlFormatOption
= XmlFormatOption.XmlFormat;
newEndPoint.Payload.Soap.XsdSchemaOption
= XsdSchemaOption.Standard;
newEndPoint.Payload.Soap.WsdlGeneratorOption
= WsdlGeneratorOption.Procedure;
newEndPoint.Payload.Soap.WsdlGeneratorProcedure
= "[master].[sys].[sp_http_generate_wsdl_
defaultcomplexorsimple]";
// Create new Soap Method
SoapPayloadMethod method = new SoapPayloadMethod
(newEndpoint.Payload.Soap, "GetContacts");
method.MethodXsdSchemaOption
= MethodXsdSchemaOption.Standard;
method.ResultFormat = ResultFormat.RowSets;
method.SetSqlMethod("AdventureWorks", "dbo", "GetContacts");
method.MethodLoginType = MethodLoginType.Windows;
newEndpoint.Payload.Soap.SoapPayloadMethods.Add(method);
newEndpoint.Create();
newEndpoint.Start();
Conclusion
SQL Server 2005 XML Web Services and SQL Server Management Objects are just plain cool.
Source: David Hayden ( C# .NET Developer )
SQL Server Management Objects Tutorials