Поделиться через


Листинг программного кода C# для пользовательского приложения WSDL

В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется.

ПримечаниеПримечание

Сведения о том, как включить следующий образец кода в проект Visual Studio, см. в разделе Приложение, формирующее пользовательский WSDL-документ.

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;                }        }}