SQL Server 2005 Native XML Web Services : Exposing SQL Programmability as Web Services (part 2) - Calling Native XML Web Service Endpoints from Client Applications

5. Granting Endpoint Permissions

By default, only the creator has access to an endpoint. Access for other users must be explicitly granted using the GRANT ON ENDPOINT statement:

GRANT permission ON ENDPOINT :: endpoint_name TO < server_principal >

The permissions that can be set are ALTER, CONNECT, CONTROL, TAKE OWNERSHIP, and VIEW DEFINITION.

6. Calling Native XML Web Service Endpoints from Client Applications

Because Native XML Web Services fully supports SOAP 1.1 and SOAP 1.2 and returns WSDL, programming against an endpoint is pretty much the same as programming against any Web service. If you are programming from Visual Studio .NET, you take the following steps:

1.	Add a Web reference to the project. The wizard allows you to enter the URL for the Web service, and it then downloads the WSDL and creates a proxy class in your project for calling the Web service.
2.	Create an instance of the proxy class.
3.	Invoke a Web method on the proxy class.
4.	If the Web method is configured to use the ALL_RESULTS FORMAT, you must loop through the returned object array, checking the types of each entry and casting them to the appropriate .NET type.

Listing 1 demonstrates how to invoke a Web method and consume the returned object array, in this case simply displaying a MessageBox for each type to show the returned value(s). When the Web method is configured to use the ALL_RESULTS FORMAT, six primary types can be returned by the server in the object array, which we need to be prepared to handle in the client code:

• Primitive Scalar value For scalar UDFs, the return value can be consumed directly. In the upcoming code, it is converted to a string and displayed.

• System.Data.DataSet The expected return from a stored procedure that returns a row set.

• NativeSOAPApp1.server.SqlRowCount Returns the row count of the returned row set.

• System.Xml.XmlElement The return type for a stored procedure that uses XML AUTO to format the return data as XML.

• NativeSOAPApp1.server.SqlMessage If any errors occurred during execution of the stored procedure or UDF, the error is returned as an SQLMessage.

• NativeSOAPApp1.server.SqlParameter If there is an out parameter, it is returned as an SQLParameter.

Note

Notice that the types used for SqlRowCount, SqlMessage, and SqlParameter are defined as classes within the namespace of the generated client-side Web service proxy, rather than the corresponding types defined in the System.Data.SQLClient namespace. The reason for this is that one of the goals of Native XML Web Services is for client applications not to have any dependency on the SQL Server client software. To accomplish this, the WSDL defines types that match the corresponding types in the System.Data.SQLClient namespace, which causes these classes to be generated in the proxy.

Listing 1. Sample client code

private void invokeWebMethod(string inParam) {

System.Data.SqlTypes.SqlString outParam = "";
server.sql_endpoint proxy = new server.sql_endpoint();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
object[] results;
results = proxy.GetCustomerInfo(inParam, ref outParam);
for (int j = 0; j < results.Length; j++)
   {
     object r;
     server.SqlMessage errorMessage;
     System.Xml.XmlElement xmlResult;
     System.Data.DataSet resultDS;

     r = results[j];

     //return value from SP is an int
     if (r.GetType().IsPrimitive)
     {
       MessageBox.Show(r.ToString());
     }

     switch (r.ToString())
     {
        case "System.Data.DataSet":
             resultDS = (System.Data.DataSet)r;
             MessageBox.Show(resultDS.GetXml());
             break;

        case "NativeSOAPApp1.server.SqlRowCount":
             MessageBox.Show(((NativeSOAPApp1.server.SqlRowCount)r).Count);
             break;

        case "System.Xml.XmlElement":
             xmlResult = (System.Xml.XmlElement)r;
             MessageBox.Show(xmlResult.OuterXml);
             break;

        case "NativeSOAPApp1.server.SqlMessage":
          errorMessage = (server.SqlMessage)r;
             MessageBox.Show(errorMessage.Message + ", " + errorMessage.Source);
             break;

        case "NativeSOAPApp1.server.SqlParameter":
             string outParamMsg= "Outparam name is :" +
                                 ((server.SqlParameter)r).name + ", " +
                                 "Outparam value is :" +
                                 ((server.SqlParameter)r).Value;
             MessageBox.Show(outParamMsg);
             break;
     }
   }
   // output param value
   MessageBox.Show("Output Param: " + outParam.ToString());
}