C# Code Listing for the Custom WSDL Application

Note

For instructions for including the following sample code in your Visual Studio project, see Building the Custom WSDL Application.

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.Sql;
using SqlDataRecord=Microsoft.SqlServer.Server.SqlDataRecord;

namespace MSSql
{
        /// <summary>
        /// This class provides a sample template of the sequence of actions required to generate a custom WSDL.
        /// The design of this class is as such that different detailed implementations performing the same type of
        /// work can be customized for the specific task.
        /// </summary>
        public class CustomWSDL
        {
                /// <summary>
                /// The parameters listed below is the set of parameters that SQL Server will pass to the WSDL generation stored procedure.
                /// This CLR stored procedure uses the new Visual Studio 2005 System.Data.SqlServer namespace functionalities to
                /// call the original WSDL generation stored procedure.  This method then modifies the WSDL to fit the specific
                /// SOAP client needs.
                /// </summary>
                /// <param name="iEndPointID">The endpoint id stored in the SQL metadata.
                ///                <see cref="int"/>
                /// </param>
                /// <param name="bIsSSL">Specifies whether or not the WSDL GET connection was over SSL or not.
                ///                <see cref="bool"/>
                /// </param>
                /// <param name="strHost">The HTTP Host header value.  Usually it is the name of the machine
                ///                <see cref="char*"/>
                /// </param>
                /// <param name="strQueryString">The URL query string that respresents if it is a WSDL Get.
                ///                <see cref="char*"/>
                /// </param>
                /// <param name="strUserAgent">The HTTP user-agent header value.  Usually it is the name of the application
                /// sending the WSDL request.
                ///  <see cref="char*"/>
                /// </param>
                //[SqlProcedure]
                public static void GenerateWSDL(SqlInt32 iEndPointID, SqlBoolean bIsSSL, SqlString strHost, SqlString strQueryString, SqlString strUserAgent)
                {
                        SqlPipe spPipe = SqlContext.Pipe;        // connection pipe to the client

                        // Retrieve WSDL from SQL server
                        String strWSDL = RetrieveWSDL(iEndPointID, bIsSSL, strHost, strQueryString, strUserAgent, spPipe);
                        if (null == strWSDL)
                        {
                                spPipe.Send("Error retrieving original WSDL.");
                                return;
                        }
                        
                        // customize the WSDL to fit your client application needs
                        strWSDL = UpdateWSDL(strWSDL, strQueryString.Value);
                        if (null == strWSDL)
                        {
                                spPipe.Send("Error while trying to customize WSDL.");
                                return;
                        }

                        // Return the new WSDL to the client.
                        ReturnWSDL(strWSDL, spPipe);
                }

                /// <summary>
                /// Retrieves the original WSDL stored or generated by SQL Server and return the WSDL document as a String or NULL if error occurs.
                /// </summary>
                /// <param name="iEndPointID">The endpoint id stored in the SQL metadata.
                ///                <see cref="SqlInt32"/>
                /// </param>
                /// <param name="bIsSSL">Specifies whether or not the WSDL GET connection was over SSL or not.
                ///                <see cref="SqlBoolean"/>
                /// </param>
                /// <param name="strHost">The HTTP Host header value.  Usually it is the name of the machine
                ///                <see cref="SqlString"/>
                /// </param>
                /// <param name="strQueryString">The URL query string that is specified on the URL.
                ///                <see cref="SqlString"/>
                /// </param>
                /// <param name="strUserAgent">The HTTP user-agent header value.
                ///                <see cref="SqlString"/>
                /// </param>
                /// <param name="spPipe">The connection back to the client.  Used for sending information back to the client.
                ///                <see cref="SqlPipe"/>
                /// </param>
                /// <returns>System.String</returns>
                private static String RetrieveWSDL(SqlInt32 iEndPointID, SqlBoolean bIsSSL, SqlString strHost, SqlString strQueryString, SqlString strUserAgent, SqlPipe spPipe)
                {
                        String strReturnValue = null;
                        SqlConnection sqlConn = new SqlConnection ("context connection = true");
                        sqlConn.Open();
                        SqlCommand myCommand = sqlConn.CreateCommand();
                        if (null == myCommand)
                        {
                                spPipe.Send("Error creating SqlCommand object.");
                                goto ret;
                        }

                        //myPipe.Send("Testing custom wsdl creation.");

                        // example of the call to the default WSDL generation SP
                        //    sp_http_generate_wsdl_defaultsimpleorcomplex 65540, 0, N'server', N'wsdl', N'myApp'
                        myCommand.CommandText = "sys.sp_http_generate_wsdl_defaultsimpleorcomplex";
                        myCommand.CommandType = CommandType.StoredProcedure;

                        //myPipe.Send("SP to be called: " + myCommand.CommandText);

                        if (!strQueryString.Value.StartsWith("wsdl", true, System.Globalization.CultureInfo.InvariantCulture))
                        {
                                spPipe.Send("Error: Not a WSDL request.");
                                goto ret;
                        }
                        // set the parameter values
                        myCommand.Parameters.Add(new SqlParameter("@EndpointID", SqlDbType.Int));
                        myCommand.Parameters[0].Value = iEndPointID;
                        myCommand.Parameters.Add(new SqlParameter("@IsSSL", SqlDbType.Bit));
                        myCommand.Parameters[1].Value = bIsSSL;
                        myCommand.Parameters.Add(new SqlParameter("@Host", SqlDbType.NVarChar, strHost.Value.Length));
                        myCommand.Parameters[2].Value = strHost;
            if (strQueryString.Value.ToLower(System.Globalization.CultureInfo.InvariantCulture).IndexOf("extended") > 0)
                        {
                                //  generated WSDL will use SqlTypes for datatypes
                                myCommand.Parameters.Add(new SqlParameter("@QueryString", SqlDbType.NVarChar, 11));
                                myCommand.Parameters[3].Value = "wsdlcomplex";
                        }
                        else
                        {
                                myCommand.Parameters.Add(new SqlParameter("@QueryString", SqlDbType.NVarChar, 4));
                                myCommand.Parameters[3].Value = "wsdl";
                        }
           myCommand.Parameters.Add(new SqlParameter("@UserAgent", SqlDbType.NVarChar, strUserAgent.Value.Length));
           myCommand.Parameters[4].Value = strUserAgent;

                        // execute query to retrieve WSDL
                        SqlDataReader oReader = myCommand.ExecuteReader();
                        if (null == oReader)
                        {
                                spPipe.Send("Error occurred during execution of SqlCommand.");
                                goto ret;
                        }

                        if (oReader.HasRows)                // make sure there actually data from the server
                        {
                                if (oReader.Read())                // make sure the read doesn't fail
                                {
                                        strReturnValue = oReader.GetSqlValue(0).ToString();
                                }
                        }

                ret:
                        return strReturnValue;
                }

                /// <summary>
                /// Updates/customizes the WSDL document for the specific client platform. Returns NULL if error occurs.
                /// </summary>
                /// <param name="strWsdlOrg">The original WSDL document in String format.
                ///                <see cref="System.String"/>
                /// </param>
                /// <param name="strUserAgent">The application name requesting for WSDL.
                ///                <see cref="System.String"/>
                /// </param>
                /// <returns>System.String</returns>
                private static String UpdateWSDL(String strWsdlOrg, String strUserAgent)
                {
                        // if the original WSDL was not dynamically generated by SQL 2005 in a previous call
                        // make sure that the soap:address information in the WSDL document is correct
                        // <wsdl:port name="my_endpoint" binding="tns:my_endpointSoap">
                        //    <soap:address location="https://servername/sql/myendpoint" /> 
                        // </wsdl:port>

                        //Note that this particular sample uses String manipulation to modify the WSDL
                        // if you want to modify the WSDL document using an XMLDom object, you can use the following
                        //                System.IO.StringReader srReader = new System.IO.StringReader(strWsdlOrg);
                        //                System.Xml.XmlDocument xdWSDL = new System.Xml.XmlDocument();
                        //                xdWSDL.Load(myReader);

                        String strLCUserAgent = strUserAgent.ToLower();
            if (strLCUserAgent.IndexOf("everett") > -1)
                        {
                                // For client applications created using .NET Frameworks 1.1
                                return UpdateWsdlForVS2003(strWsdlOrg);
                        }

            if (strLCUserAgent.IndexOf("jbuilder") > -1)
                        {
                                return UpdateWsdlForJBuilder(strWsdlOrg);
                        }

           if (strLCUserAgent.IndexOf("glue") > -1)
                        {
                                return UpdateWsdlForGLUE(strWsdlOrg);
                        }

                        // default case
                        return strWsdlOrg;
                }

                /// <summary>
                /// Sends the updated WSDL to the client.
                /// </summary>
                /// <param name="strWSDL">The WSDL document to be returned to the client in String format.
                ///                <see cref="String"/>
                /// </param>
                /// <param name="spPipe">The connection to be used to send the WSDL back to the client.
                ///                <see cref="SqlPipe"/>
                /// </param>
                private static void ReturnWSDL(String strWSDL, SqlPipe spPipe)
                {
                        int iMaxLength = 4000;        // The max length supported by SQL Server NVarChar is 4000 characters

                        // setup the required objects to send the new WSDL back to the client.
                        // the MetaData object is used to denote what each of the data records represent
                        SqlMetaData[] oMetaData = new SqlMetaData[1];

                        // XML_F52E2B61-18A1-11d1-B105-00805F49916B is the column name that denotes a FOR XML statement
                        // This is the required column name to return the WSDL document.
                        oMetaData[0] = new SqlMetaData("XML_F52E2B61-18A1-11d1-B105-00805F49916B",
                                                                                        SqlDbType.NVarChar,
                                                                                        iMaxLength,
                                                                                        1033,                                        // note: using US-EN
                                                                                        SqlCompareOptions.None);
                        if (null == oMetaData[0])
                        {
                                spPipe.Send("Error creating the required SqlMetaData object for response.");
                                goto ret;
                        }

                        // making sure that we are not going outside of the range of available characters in the WSDL document
                        if (strWSDL.Length < iMaxLength)
                                iMaxLength = strWSDL.Length;

                        // variable to hold the value that will be set as part of a SqlDataRecord
                        object[] aoResponse = new object[1];
                        aoResponse[0] = new object();
                        if (null == aoResponse[0])
                        {
                                spPipe.Send("Error creating the object to hold the SqlDataRecord value.");
                                goto ret;
                        }

                        aoResponse[0] = strWSDL.Substring(0, iMaxLength);

                        //myPipe.Send("Try creating a SqlDataRecord.");
                        SqlDataRecord oRecord = new SqlDataRecord(oMetaData);
                        if (null == oRecord)
                        {
                                spPipe.Send("Error creating SqlDataRecord.");
                                goto ret;
                        }

                        oRecord.SetValues(aoResponse);
                        // spPipe.SendResultsStart(oRecord);  // send the first block of data
                        spPipe.SendResultsStart(oRecord);  // send the first block of data
                        spPipe.SendResultsRow(oRecord);  // send the first block of data
                        int iccLeft = strWSDL.Length - iMaxLength;
                        int iLength = strWSDL.Length;
                        while (iccLeft > 0)  // check to see if there are more blocks to send
                        {
                                if (iccLeft > iMaxLength)
                                {
                                        oRecord.SetString(0, strWSDL.Substring(iLength - iccLeft, iMaxLength));
                                        spPipe.SendResultsRow(oRecord);
                                        iccLeft = iccLeft - iMaxLength;
                                }
                                else  // iccLeft = iMaxLength or less
                                {
                                        oRecord.SetString(0, strWSDL.Substring(iLength - iccLeft, iccLeft));
                                        spPipe.SendResultsRow(oRecord);
                                        iccLeft = 0;
                                }
                        }
                        spPipe.SendResultsEnd();  // let the client know that there is no more data to send.

                ret:
                        return;
                }

                /// <summary>
                /// Modifies the SQL 2005 generated WSDL for consumption by VS 2003.  Returns NULL if error occurs
                /// </summary>
                /// <param name="strWsdlOrg">The SQL 2005 generated WSDL document in String format
                ///                <see cref="System.String"/>
                /// </param>
                /// <returns>System.String</returns>
                private static String UpdateWsdlForVS2003(String strWsdlOrg)
                {
                        const string strFacet = " maxOccurs=\"unbounded\" minOccurs=\"0\" processContents=\"lax\" ";
                        String strReturn = strWsdlOrg;
                        if (null == strReturn)
                        {
                                goto ret;
                        }

                        // For VS 2003 need to find the following text
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
            //     <xsd:element ref="xsd:schema" /> 
            //     <xsd:any /> 
                        //   </xsd:sequence>
                        // </xsd:complexType>
                        //
                        // Need to change the above section to
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
                        //     <xsd:any maxOccurs="unbounded" minOccurs="0" processContents="lax" /> 
                        //   </xsd:sequence>
                        // </xsd:complexType>

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005
                        string strTemp = "<xsd:element ref=\"xsd:schema\"/>";

                        // first look for the <xsd:complexType name="SqlRowSet"> node
                        int iIndex = strReturn.IndexOf("complexType name=\"SqlRowSet\"");
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now look for the <xsd:element> node from the <xsd:complexType name="SqlRowSet"> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now that we have found what we want, remove it
                        strReturn = strReturn.Remove(iIndex, strTemp.Length);

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005
                        strTemp = "/>";

                        // look for the end of the <xsd:any> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // insert the attributes
           strReturn = strReturn.Insert(iIndex, strFacet);

                ret:
                        return strReturn;
                }

                /// <summary>
                /// Modifies the SQL 2005 generated WSDL for consumption by JBuilder 9.  Returns NULL if error occurs
                /// </summary>
                /// <param name="strWsdlOrg">The SQL 2005 generated WSDL document in String format
                ///                <see cref="System.String"/>
                /// </param>
                /// <returns>System.String</returns>
                private static String UpdateWsdlForJBuilder(String strWsdlOrg)
                {
                        String strReturn = strWsdlOrg;
                        if (null == strReturn)
                        {
                                goto ret;
                        }

                        // For JBuilder need to find the following text
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
                        //     <xsd:element ref="xsd:schema" /> 
                        //     <xsd:any /> 
                        //   </xsd:sequence>
                        // </xsd:complexType>
                        //
                        // Need to change the above section to
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
                        //     <xsd:any minOccurs="0" processContents="lax" /> 
                        //   </xsd:sequence>
                        // </xsd:complexType>

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005
                string strTemp = "<xsd:element ref=\"xsd:schema\"/>";
                string strFacet = " minOccurs=\"0\" processContents=\"lax\" ";

                        // first look for the <xsd:complexType name="SqlRowSet"> node
                        int iIndex = strReturn.IndexOf("complexType name=\"SqlRowSet\"");
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now look for the <xsd:element> node from the <xsd:complexType name="SqlRowSet"> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now that we have found what we want, remove it
                        strReturn = strReturn.Remove(iIndex, strTemp.Length);

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005
                        strTemp = "/>";

                        // look for the end of the <xsd:any> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }
           // insert the attributes
           strReturn = strReturn.Insert(iIndex, strFacet);

                ret:
                        return strReturn;
                }

                /// <summary>
                /// Modifies the SQL 2005 generated WSDL for consumption by GLUE 5.0.1Pro.  Returns NULL if error occurs
                /// </summary>
                /// <param name="strWsdlOrg">The SQL 2005 generated WSDL document in String format
                ///                <see cref="System.String"/>
                /// </param>
                /// <returns>System.String</returns>
                private static String UpdateWsdlForGLUE(String strWsdlOrg)
                {
                        const string strFacet = " maxOccurs=\"unbounded\" minOccurs=\"0\" processContents=\"lax\" ";
                        const string strXsdList = "<xsd:list itemType=\"sqltypes:sqlCompareOptionsEnum\" />";
                        String strReturn = strWsdlOrg;
                        if (null == strReturn)
                        {
                                goto ret;
                        }

                        // For GLUE need to find the following text
                        // <xsd:simpleType name="sqlCompareOptionsList">
                        //   <xsd:list itemType="sqltypes:sqlCompareOptionsEnum">
                        // </xsd:simpleType>
                        //
                        // This needs to be changed to
                        // <xsd:simpleType name="sqlCompareOptionsList">
                        //   <xsd:restriction base="xsd:string" />
                        // </xsd:simpleType>

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005

                        // first look for the <xsd:simpleType name="sqlCompareOptionsList"> node
                        int iIndex = strReturn.IndexOf("simpleType name=\"sqlCompareOptionsList\"");
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now look for the <xsd:list> node from the <xsd:simpleType name="sqlCompareOptionsList"> node
                        iIndex = strReturn.IndexOf(strXsdList, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now that we have found <xsd:list>, remove it
                        strReturn = strReturn.Remove(iIndex, strXsdList.Length);

            // now insert the new xsd:restriction info
            //   <xsd:restriction base="xsd:string" />
                        strReturn = strReturn.Insert(iIndex, "<xsd:restriction base=\"xsd:string\" />");

                        // Need to also find
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
            //     <xsd:element ref="xsd:schema" /> 
            //     <xsd:any /> 
            //   </xsd:sequence>
                        // </xsd:complexType>
                        //
                        // Need to change the above section to
                        // <xsd:complexType name="SqlRowSet">
                        //   <xsd:sequence maxOccurs="unbounded">
                        //     <xsd:any maxOccurs="unbounded" minOccurs="0" processContents="lax" /> 
                        //   </xsd:sequence>
                        // </xsd:complexType>
                        string strTemp = "<xsd:element ref=\"xsd:schema\"/>";

                        // first look for the <xsd:complexType name="SqlRowSet"> node
                        iIndex = strReturn.IndexOf("complexType name=\"SqlRowSet\"");
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now look for the <xsd:element> node from the <xsd:complexType name="SqlRowSet"> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // now that we have found what we want, remove it
                        strReturn = strReturn.Remove(iIndex, strTemp.Length);

                        // hardcoded to match the WSDL generated by SQL 2005
                        // will need to be modified accordingly if the WSDL was not generated by SQL 2005
                        strTemp = "/>";

                        // look for the end of the <xsd:any> node
                        iIndex = strReturn.IndexOf(strTemp, iIndex);
                        if (iIndex <= 0)
                        {
                                strReturn = null;
                                goto ret;
                        }

                        // insert the attributes
                        strReturn = strReturn.Insert(iIndex, strFacet);

                ret:
                        return strReturn;
                }
        }
}

See Also

Reference

Implementing Custom WSDL Support
Building the Custom WSDL Application
Using the Custom WSDL Application

Help and Information

Getting SQL Server 2005 Assistance