C# Code Listing for the Custom WSDL Application
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
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;
}
}
}