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