SQL message listener in C#

Question

Hi,

I have a C# Windows service that listens to a HTTP port and fires an action upon receiving a HTTP GET request. The action is typically the execution of an application that resides in the same machine (running Windows XP Professional or above).

I now have the requirement for the C# Windows service to listen to incoming SQL queries from a JDBC client and trigger an action, once again, the execution of an application. I am looking for some help to implement a simple 'listener' to receive SQL queries from the JDBC client and trigger the action, after returning a response to the JDBC client. Is there any resource/sample code I can refer to? I do not have the option to install any database server (SQL Express, MySQL, etc) on the target machine.

The XML configuration needs to look like -

<XmlConfiguration>
   <SQLListenerPort>18781</SQLListenerPort>
   <HttpRootUrl>http://127.0.0.1:8731/&lt;/HttpRootUrl&gt;
   <HttpsRootUrl>https://127.0.0.1:8732/&lt;/HttpsRootUrl&gt;
   <ServeHttpsRequests>true</ServeHttpsRequests>
   <Triggers>
      <!-- Sample triggers for testing, please update for production deployment -->
      <Trigger>
         <URL>http://127.0.0.1:8731/CustomerID&lt;/URL&gt;
         <Action>MyExecutable.exe CustomerID</Action>
      </Trigger>
      <Trigger>
         <SQL>SELECT * FROM A/SQL>
         <Action>MyExecutable.exe A</Action>
      </Trigger>
      <Trigger>
         <SQL>SELECT * FROM B</SQL>
         <Action>MyExecutable.exe B</Action>
      </Trigger>
      <Trigger>
         <URL>https://127.0.0.1:8732/CASE1/CustomerID/MachineID&lt;/URL&gt;
         <Action>TEST.exe CustomerID MachineID</Action>
      </Trigger>
   </Triggers>
</XmlConfiguration>

Behaviour -

The C# Windows service receives SQL query 'SELECT * FROM A', looks up the triggers map and fires the executable with an argument (that is, 'A').

Thank you for your time.

indyfromoz

Answer 1

JDBC is an API that unifies the various database APIs under a generic manager, with a single API to interface with. There is no JDBC protcol, each particular driver loaded by JDBC will use its own, native, protocol to intercat with the database. As such, you cannot listen in a service for 'JDBC' call, since there is no such thing as a 'JDBC call'. If you want to receive a SQL request and return a result set, you will have to implement one of the protocols of a JDBC driver, like TDS for example for a SQL Server driver. To implement a TDS 'service' means you have to write a TDS gateway, a completely useless exercise since you can achieve the very same by simply exposing the TDS listenning port of the server itself (ignoring security details...).

What you can look into is to receive ordinary HTTP web service calls (SOAP or REST) and process them as ordinary web service requests, returning the results as a SOAP response or a RESTful HTTP response. Or you could simply expose an OData service, which can be created straight from an EF project. But the client would have to interact with this service just as any WWW service, there is no JDBC nor other database technology stack involve don the client side.

Answer 2

What you are looking for is MS SQL Service Broker. Here is its homepage.

Here are some starters / examples:

• msdn
• Pinal Dave
• Sql Server Central