Листинг программного кода C# для пользовательского приложения WSDL
В будущей версии Microsoft SQL Server эта возможность будет удалена. Избегайте использования этой возможности в новых разработках и запланируйте изменение существующих приложений, в которых она применяется.
.gif "Примечание") Примечание |
|---|
Сведения о том, как включить следующий образец кода в проект 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; } }}