Creating a SOAP sqlSession supported client application

Article
03/21/2006

SQL 2005 Native Web Services enables support for login environment settings through the use of SOAP Headers. The set of environment settings include:
   initialLanugage
   initialDatabase
   applicationName
   clientInterface
   clientPID
   hostName
   networkID
   enableNotifications
   sqlSession

Some of these such as:
   initialLanguage
   initialDataBase
   applicationName
   clientPID
   clientInterface
are input values only (ie. SQL server does not return these SOAP Headers as part of a response)

sqlSessions on the other hand can be returned as a SOAP header as part of a response. As the name suggests, sqlSessions is used when you want to establish a session that spans multiple SOAP requests/connections. You may be wondering how do I make use of this functionality. Please refer to https://msdn2.microsoft.com/en-us/library/ms179186(SQL.90).aspx for additional information on how SOAP sessions work.

The standard method of exposing a SQL function and/or stored procedure as a webmethod applies here.
1) Create the function and/or stored procedure
2) Create an SOAP endpoint to expose the function and/or stored procedure; please remember to enable session support on the endpoint
eg.

 CREATE ENDPOINT sql_endpoint
 STATE=STARTED
AS HTTP (
 PATH='/sql/session',
 AUTHENTICATION=(INTEGRATED),
 PORTS=(CLEAR)
 )
FOR SOAP (
  BATCHES=ENABLED,
  SCHEMA = NONE,
  WSDL = DEFAULT,
  SESSIONS = ENABLED,
  SESSION_TIMEOUT = 3600 -- in seconds
 )
 go

   Please refer to https://msdn2.microsoft.com/en-us/library/ms181591(SQL.90).aspx for additional information on CREATE ENDPOINT.
3) Create the client application; for Visual Studio 2005, you can simply add "Web Reference" to the project.
4) Add a new code file item to add the SOAP extension header sample library to the project; Copy and paste the contents of the C# Code Listing for the SQL SOAP Header Sample Class Library and save it with the name "SqlSoapHeader.cs".
   Please refer to https://msdn2.microsoft.com/en-us/library/ms186386(SQL.90).aspx for the C# code listing of SQL supported SOAP Header class library.
5) Select Show All Files and expand the Web References node and select the Reference.cs file.
6) In the Code Editor window, update the Reference.cs file to add a public variable to the endpoint definition class. The variable must be of the same SqlSoapHeader class as the type of SOAP optional header.
   For example, to add a public property variable called "sqlSession" of the SqlSoapHeader.sqlSession class to the Reference.cs file under the endpoint class definition ("sql_endpoint"), you would add the following line of code:

  public class sql_endpoint : System.Web.Services.Protocols.SoapHttpClientProtocol {
 public SqlSoapHeaders.sqlSession sqlSession;

7) Add the appropriate SoapHeaderAttribute property to the Web method that you want to enable the SOAP header for.
For sessions, you would add the following line of code:

      [System.Web.Services.Protocols.SoapHeaderAttribute("sqlSession", Direction=SoapHeaderDirection.InOut)]
     public object[] sqlbatch(string BatchCommands, ...

Note: Since the sqlSession SOAP Header is sent in both request and respons SOAP messages, the "Direction" property must be set to "InOut".
8) Utilize the sqlSession support in the application:
For example, to call SqlBatch across different requests but sharing the same session:

      sql_endpoint proxy = new sql_endpoint();
     proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
     proxy.sqlSession = new SqlSoapHeader.sqlSession();
     proxy.sqlSession.initiate = true;
     proxy.sqlSession.MustUnderstand = true; 

     SqlParameter[] myParams = null;
     object[] res = proxy.sqlbatch("SET language Italian; SELECT DATENAME(month, GETDATE()) AS 'Month Name'", ref myParams);
     proxy.sqlSession.timeoutSpecified = false;
     res = proxy.sqlbatch("select @@language", ref myParams);
     proxy.sqlSession.terminate = true;
     proxy.sqlSession.timeoutSpecified = false;
     res = proxy.sqlbatch("SELECT DATENAME(month, GETDATE()) AS 'Month Name'", ref myParams); That's it.Jimmy WuSQL Server ProtocolsDisclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Creating a SOAP sqlSession supported client application

Additional resources