Aquí vengo de nuevo ahora con un artículo interesante. Habla de los EndPoints en SQL Server 2005
¿que es esto?, bueno pues es el manejo que se puede dar a los Web Services directamente desde SQL Server 2005
vale la pena implementar algo así.
Saludos y hasta la próxima!
Fuente: http://www.developer.com/db/article.php/10920_3390241_1
New HTTP Endpoints Create SQL Server 2005 Web Services
By Peter DeBetta
Go to page: 1 2 3 Next
by Peter DeBetta of Wintellect
SQL Server 2000 offers some capabilities for returning XML output via HTTP using SQLXML—and, of course, SQLXML supports Web services creation. Although not rocket science, setting up, configuring, and using Web services in SQL Server 2000 does require a little effort (see the SQLXML documentation about Web services in SQL Server 2000).
When .NET came about, people began writing Web services in C# or VB.NET. They simply connected into SQL Server (or any data store, for that matter) to retrieve the data. .NET made creating Web services as simple as 1-2-3:
- Create some stored procedures.
- Write some WebMethods to return the data from said stored procedures.
- Presto! You have a Web service.
But now, the next generation of Web services is hitting the streets. SQL Server 2005 supports native HTTP SOAP via a feature known as HTTP Endpoints. For those who don't know, Simple Object Access Protocol (affectionately known as SOAP) is a lightweight messaging protocol that Webopedia defines as follows:
"Short for Simple Object Access Protocol, a lightweight XML-based messaging protocol used to encode the information in Web service request and response messages before sending them over a network. SOAP messages are independent of any operating system or protocol and may be transported using a variety of Internet protocols, including SMTP, MIME, and HTTP ."
This new HTTP Endpoints feature in SQL Server 2005 is the subject of this article.
HTTP Endpoints
So what is an HTTP Endpoint? You may have heard that it is SQL Server 2005's means for creating Web services, but it actually is much more. An HTTP Endpoint also is a means of creating interfaces via HTTP or TCP for SOAP, T?SQL, Service Broker, and even database mirroring. Although these other functions are very intriguing, this discussion concerns only the ability to create Web services in SQL Server—Web services that can return rowset data, scalar values, messages, and even errors, all of which are serialized into XML automatically. And, an HTTP Endpoint does all of this without requiring you to install IIS (it uses the Windows 2003 kernel module http.sys).
Because I like to learn from examples, I am going to teach by example. I demonstrate how to use HTTP Endpoints by walking through an example from start to finish. Before starting, here are a few notes to consider:
- This demonstration uses the new demo database called AdventureWorks.
- Native HTTP SOAP in SQL Server 2005 is not supported on Windows XP. If you want to try the examples, you need Windows Server 2003.
- SQL Server 2005 Express Edition (the new MSDE) does not support HTTP Endpoints, so be sure to install the Developer Edition.
- I do not explain how to use SQL Server Management Studio to execute SQL scripts. I trust you know how to do this.
Security
Because HTTP Endpoints are a server-level feature, security of endpoints is also on the server level. Of course, the serveradmin system role can create, alter, and drop endpoints from the SQL Server instance, but how do you allow developers to manage these endpoints without giving them the excessive permissions of this system role?
The answer lies in SQL Server 2005's new ability to assign server-level permissions to logins, as shown here:
GRANT ALTER ANY HTTP ENDPOINT TO peter
With this command, you can allow the peter login to manage HTTP Endpoints without giving other unnecessary permissions. The following is a list of permissions that you can modify via GRANT, DENY, or REVOKE:
{GRANT|DENY|REVOKE} ALTER ANY ENDPOINT TO Login Controls the ability to alter any HTTP Endpoint; also allows permission to transfer ownership and connect to any endpoint {GRANT|DENY|REVOKE} ALTER ON ENDPOINT:: EndPointName TO Login Controls whether a login can alter a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint {GRANT|DENY|REVOKE} CONTROL ON ENDPOINT:: EndPointName TO LoginControls whether a login can alter or drop a specific HTTP Endpoint; also allows permission to transfer ownership and connect to the specified endpoint {GRANT|DENY|REVOKE} CONNECT ON ENDPOINT:: EndPointName TO Login Controls whether or not a login can connect to (execute requests against) an HTTP Endpoint -
{GRANT|DENY|REVOKE} TAKE OWNERSHIP ON ENDPOINT:: EndPointName
TO Login Controls whether a login can take ownership of the HTTP Endpoint -
{GRANT|DENY|REVOKE} VIEW DEFINITION ON ENDPOINT:: EndPointName
TO Login Controls the ability for a login to see the metadata (definition) of the HTTP Endpoint
Consider one last example. If I want to deny the peter login the ability to use an HTTP Endpoint named SQLEP_Financial, I would execute the following statement:
DENY CONNECT ON ENDPOINT::SQLEP_Financial TO peter
Providing a Source for Data
The first order of business is to create the code components that will be the source of information for the endpoint. I create two stored procedures and one user-defined function (UDF), as shown in Listing 1:
Listing 1
USE AdventureWorks
GO
CREATE PROCEDURE prProductList
AS
SELECT Product.ProductID, Product.ProductNumber, Product.Name
FROM Production.Product AS Product
INNER JOIN Production.ProductInventory AS Inventory
ON Product.ProductID = Inventory.ProductID
INNER JOIN Production.Location AS Location
ON Inventory.LocationID = Location.LocationID
WHERE Product.ListPrice > 0
AND Location.LocationID = 6 --Products from Misc Storage only
ORDER BY Product.Name
GO
CREATE PROCEDURE prProductStockInfo
@ProductID int
AS
IF (SELECT ListPrice FROM Production.Product
WHERE ProductID = @ProductID) = 0
BEGIN
RAISERROR ('Product not available for retail sale', 11, 1)
END
ELSE
BEGIN
SELECT Product.ProductID , Product.ProductNumber,
Product.Name, Product.Weight,
Product.WeightUnitMeasureCode,
Product.ListPrice, Inventory.Quantity
FROM Production.Product AS Product
INNER JOIN Production.ProductInventory AS Inventory
ON Product.ProductID = Inventory.ProductID
INNER JOIN Production.Location AS Location
ON Inventory.LocationID = Location.LocationID
WHERE Product.ProductID = @ProductID
AND Location.LocationID = 6 --Misc Storage
END
GO
CREATE FUNCTION dbo.fnProductPhoto (@ProductID int)
RETURNS varbinary(MAX)
AS
BEGIN
DECLARE @largePhoto varbinary(max)
SELECT @largePhoto = ProdPhoto.LargePhoto
FROM Production.ProductPhoto AS ProdPhoto
INNER JOIN Production.ProductProductPhoto ProdProdPhoto
ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID
WHERE ProdProdPhoto.ProductID = @ProductID
RETURN (@largePhoto)
END
The first procedure returns a list of products (product ID, name, and number) from the AdventureWorks database. The second procedure returns more details about a product, including inventory levels. The UDF returns a single varbinary value containing the product photo.
Creating the Endpoint
Once you have your procedures and functions set up, you can create the endpoint (Web service). The following code creates the endpoint on my server:
CREATE ENDPOINT SQLEP_AWProducts
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'win2k301'
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
WEBMETHOD 'ProductStockInfo'
(NAME=' AdventureWorks.dbo.prProductStockInfo'),
WEBMETHOD 'ProductPhoto'
(NAME='AdventureWorks.dbo.fnProductPhoto'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
Believe it or not, you are now ready to consume this Web service. Before doing that, though, examine the CREATE ENDPOINT statement. Starting from the top, the first thing you will notice is the name of the endpoint, SQLEP_AWProducts, which is how you refer to the Web service within your client code (see Figure 1). PATH is the virtual path on the server for the Web service. So, for this example, you would access the Web service at http://win2k301/AWproducts, but it would be called SQLEP_AWProducts in your .NET application code. Table 1 provides explanations for this and other arguments used in the AS HTTP clause.
Click here for a larger image.
Figure 1: Object Browsing the Web Service in Visual Studio.NET 2005
Table 1: Arguments Used in the AS HTTP Clause
| Argument | Description |
| PATH | The virtual URL path on the server where the Web service will reside |
| AUTHENTICATION | The method of authentication; can be BASIC, DIGEST, or INTEGRATED You should try to use INTEGRATED when possible, since it is the most secure. It will try to use Kerberos-based authentication if possible (otherwise, NTLM). DIGEST is not as secure as INTEGRATED. You should use it only if INTEGRATED authentication is not possible. BASIC authentication is the least secure. You should use it only if you can't implement either INTEGRATED or DIGEST authentication methods. BASIC requires SSL (see PORTS below). |
| PORTS | CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default) |
| SITE | The name of the server on which the Web service is running |
The STATE argument can have one of three values:
- STARTED—listening and responding
- DISABLED—neither listening nor responding
- STOPPED—listening, but returns errors to client requests
You can change the state of an existing Web service using the ALTER ENDPOINT statement. For example, to disable the endpoint, you can execute:
ALTER ENDPOINT SQLEP_AWProducts STATE = DISABLED
The FOR SOAP clause determines which methods the Web service will expose, which other features are exposed (dynamic SQL, for example), and other characteristics of the Web service. In this example, three methods are exposed: the two stored procedures and the UDF shown in Listing 1. WEBMETHOD requires the name of the exposed method as its first argument. As you can see in the code, it does not have to match the name of the actual stored procedure or UDF that it is exposing. The next argument of WEBMETHOD is the fully qualified NAME of the object you are exposing.
The BATCHES argument specifies whether or not the Web service supports ad hoc queries. WSDL indicates how the WSDL document generation will occur; DEFAULT indicates that the WSDL document will be created automatically. SQL Server 2005 allows you to implement custom WSDL generators using the CLR-based abilities of SQL Server 2005 (see Books Online for more details). DATABASE is the name of the database that will be accessed from the endpoint (endpoints are defined at the server level). And finally, NAMESPACE is, well, the namespace for the endpoint.
You have now officially created a Web service using SQL Server 2005. Congratulations.
The Client to Consume Your Web Service
Consuming a Web service created in SQL Server 2005 as an HTTP Endpoint is similar to consuming a Web service created in C#. That's the beauty of Web services and SOAP. Begin a new Windows application in Visual Studio.NET and add the following controls to the form (ControlType: name):
- Button: btnExecSP
- DataGridView: dgvProduct
- ListBox: lstProducts
- PictureBox: picProduct
You will need to add the Web reference before writing any code. Again, you can browse to http://server/awproducts?wsdl to create the Web reference. Keep in mind that your Web reference object will be named server_AWProducts (my machine is named win2k301 so I browse to http://win2k301/awproducts?wsdl, and my Web reference object is win2k301_AWProducts). You will have to tweak the code to the Web reference name that you create.
Once the Web reference is in place, you can double-click the Button control and add the code in Listing 2.
Listing 2
private void BtnExecSP_Click(System.Object sender, System.EventArgs e)
{
win2k301_AWProducts.SQLEP_AWProducts proxy =
new win2k301_AWProducts.SQLEP_AWProducts();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials ;
object[] products = proxy.ProductList();
System.Data.DataSet resultDS;
if (products[0].ToString() == "System.Data.DataSet")
{
resultDS = (System.Data.DataSet )products[0];
DataTable dt = resultDS.Tables[0];
lstProducts.DataSource = dt;
lstProducts.DisplayMember = "Name";
lstProducts.ValueMember = "ProductID";
}
}
The first thing you will notice is that the Web method returns an object array. Because SQL Server can actually send back not only result sets, but also messages, errors, and other types (see Table 2 for more details), you should validate that you received what you expected. Listing 2 does a check by using the ToString method on the first element in the returned object array and comparing it to "System.Data.DataSet". If true, you know you can safely cast the object to a DataSet type and work with it from there.
Table 2: Object Return Types
| Item from SQL Server | Corresponding .NET Object |
| Results of SELECT statement | System.Data.DataSet |
| Results of a SELECT statement with FOR XML | System.Xml.XmlElement |
| Raised error | SqlMessage (from WSDL) |
| Message | SqlMessage (from WSDL) |
| Output parameter | SqlParameter (from WSDL) |
| Rows affected | SqlRowCount (from WSDL) |
| RETURN statement value | System.Int32 |
Now double-click the ListBox control and add the following code:
private void lstProducts_SelectedIndexChanged(object sender,
System.EventArgs e)
{
Int32 i = 0;
try
{i = System.Convert.ToInt32(lstProducts.SelectedValue.ToString ());}
catch
{return;} //prevents issue when loading listbox
object e1;
win2k301_AWProducts.SqlMessage errorMessage;
System.Data.DataSet resultDS;
win2k301_AWProducts.SQLEP_AWProducts proxy =
new win2k301_AWProducts.SQLEP_AWProducts();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] products = proxy.ProductStockInfo(i);
e1 = products[0];
switch ( e1.ToString())
{
case "System.Data.DataSet":
resultDS = (System.Data.DataSet)products[0];
DataTable dt = resultDS.Tables[0];
dgvProduct.DataSource = dt;
dgvProduct.Refresh();
break;
case "Yukon_Http_v1.win2k301_AWProducts.SqlMessage":
errorMessage = (win2k301_AWProducts.SqlMessage)products[0];
MessageBox.Show("Error fetching product", "'"
+ errorMessage.Message + " occurred at '"
+ errorMessage.Source + "'");
return;
}
Byte[] photo = proxy.ProductPhoto(i);
picProduct.Image = new Bitmap(new MemoryStream(photo));
}
You should pay attention to a couple of details here. First, this code snippet checks for another type of returned object: SqlMessage. Notice the type name (from the ToString method) is Yukon_Http_v1.server_virtualDirectory.SqlMessage. This type is defined in the WSDL document and is unique to each Web service. The following listing shows a snippet from the WSDL of the SqlMessage complexType:
<xsd:complexType name="SqlMessage">
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="Class" type="sqlmessage:nonNegativeInteger" />
<xsd:element name="LineNumber"
type="sqlmessage:nonNegativeInteger" />
<xsd:element name="Message" type="xsd:string" />
<xsd:element name="Number" type="sqlmessage:nonNegativeInteger" />
<xsd:element name="Procedure" type="xsd:string" minOccurs="0" />
<xsd:element name="Server" type="xsd:string" minOccurs="0" />
<xsd:element name="Source" type="xsd:string" />
<xsd:element name="State" type="sqlmessage:nonNegativeInteger" />
</xsd:sequence>
</xsd:complexType>
The last tidbit for this discussion is the Byte array that is returned from the UDF. Because this UDF returned varbinary data, its XSD type is xsd:base64Binary. Thus, it is a Byte array in .NET. SQL Server's int data type is the XSD type xsd:int and Int32 in .NET. Books Online has more information about the mapping of types.
Easy Web Service Creation
HTTP Endpoints provide a way to create various interfaces into SQL Server, including features such as Service Broker, database mirroring, and of course, Web services. It makes the creation of a Web service (that exposes SQL Server data) a snap for experienced programmers and database administrators alike. Enjoy!
About the Author
Peter DeBetta is an independent software consultant who trains exclusively for Wintellect. Peter regularly publishes on the topic of SQL Server and recently wrote Introducing SQL Server 2005 for Developers for Microsoft Press. He also speaks at conferences such as VSLive!, WinSummit, WinDev, and Devscovery.
ENTENDIDOS LOS CONCEPTOS, 
