Silverterra 3d blog

Goal: integrate an external application data to my database

I had a small desktop application which is asking a web page about information.
The application itself is a simple web robot. Login with credentials, passing through some pages to get the correct information.
Using GET, POST methods as well in the process and at the end of the line dig the data from an incorrect structured web page.

First I did a model with an integrated WebBrowser control to see how it working. Good because:

1. I can see the process itself
2. I can forget all of cookies and credentials

This is fine to test, but bad idea to production because:

1. the webBrowser rendering the hole page (all elements on it such as: images, stylesheets, js files) – time factor
2. the webBrowser core itself is Internet Explorer – this is enough to die
3. the webBrowser will process all the javascript codes on the page

The second step is to do it with low level webRequests. Passing cookies correctly, doing credentials is a new to do, but I can ignore everything else easily I can focus on the process and the pure data and in time!

The sturcture is the following
WebDB based web site -> my oracle — (tcp link) –> a c# application

the process is (typically 1-2 sec)
watching process (to check the external program is running or not)

1. ping the external application on the specified tcp port

asking process (get the data)

1. push the asking button on the web site
2. if the external application is available then do the next (if not -> stop)
3. prepare the data for the question into a table
4. send the request to the external application
5. wait for the answer
6. read the answer
7. show the answer on the page

on the oracle side
watching process (to check the external program is running or not)

...
port number := 12456; -- this is the lister port
...
function fu_ping_external return varchar2 is
l_req varchar2(32000);
conn utl_tcp.connection;
l_ret varchar2(100);
begin
  begin
    l_ret := null;    
    conn := utl_tcp.open_connection('localhost',port);
    l_ret := utl_tcp.write_line(conn,'DBF'||'|'||a_sys.session_user);
    utl_tcp.flush(conn);
   BEGIN
     LOOP
       l_ret := l_ret ||(utl_tcp.get_line(conn, TRUE));  -- read result
     END LOOP;
   EXCEPTION
     WHEN utl_tcp.end_of_input THEN
       NULL; -- end of input
   END;
  end;
  
  utl_tcp.close_connection(conn);
  return l_ret;

end;

The real answering procedure is similar but with a small wait cycle to keep the control on it.

...
  l_ret := fu_start_listener('STOREINFO'); -- ping the listener (start the process)

  FOR i IN 1 .. 30 -- wait for 30 sec, then return
  LOOP
    dbms_lock.sleep(1);
    select answre from ... -- check the answer
    IF answer = 1 THEN     -- we have a measurable answer
      ... -- do what you want
      return;
    END IF;
  END LOOP;

  -- we are over 30 sec and we have no answer 
  ... -- do what you want

...    

on the c# side
We need to create a simple web server with TcpListener.

public class TcpTimeServer
{

  public string Listen(int portNum)
  {
    string ret = "";
    bool done = false;

    TcpListener listener = new TcpListener(portNum);

    listener.Start();

    while (!done)
    {
      // Waiting for connection...
      TcpClient client = listener.AcceptTcpClient();

      // Connection accepted.
      Application.DoEvents();

      NetworkStream ns = client.GetStream();

      byte[] byteTime = Encoding.ASCII.GetBytes(DateTime.Now.ToString());

      try
      {
        ret = ReadStream(ns);
        ns.Write(byteTime, 0, byteTime.Length);
        ns.Close();
        client.Close();
        done = true;
      }
      catch (Exception e)
      {
        Console.WriteLine(e.ToString());
      }
    }

    listener.Stop();

    return ret;
  }

  public string ReadStream(NetworkStream myNetworkStream)
  {
    // Check to see if this NetworkStream is readable.
    if (myNetworkStream.CanRead)
    {
      byte[] myReadBuffer = new byte[1024];
      StringBuilder myCompleteMessage = new StringBuilder();
      int numberOfBytesRead = 0;
      
      do // incoming message may be larger than the buffer size
      {
        numberOfBytesRead = myNetworkStream.Read(myReadBuffer, 0, myReadBuffer.Length);
        myCompleteMessage.AppendFormat("{0}", Encoding.ASCII.GetString(myReadBuffer, 0, numberOfBytesRead));
      }
      while (myNetworkStream.DataAvailable);

      // Print out the received message to the console.
      return myCompleteMessage.ToString();
    }
    else
    {
      return "Sorry.  You cannot read from this NetworkStream.";
    }

  }

}

We need to create a new thread for this listener.
The simpliest way is to do with a BackgroundWorker like this:

...
  // bindable event
  public event delCommandReceived CommandReceived;
...
  private System.ComponentModel.BackgroundWorker bgw1;
...
  this.bgw1.DoWork += new DoWorkEventHandler(bgw1_DoWork);
  this.bgw1.ProgressChanged += new ProgressChangedEventHandler(bgw1_ProgressChanged);
...
  // here is the worker process
  void bgw1_DoWork(object sender, DoWorkEventArgs e)
  {
    BackgroundWorker bg = sender as BackgroundWorker;
    do
    {
      TcpTimeServer ts = new TcpTimeServer();
      string ret = ts.Listen((int)e.Argument);
      bg.ReportProgress(1, ret);
    } while (true);
    e.Cancel = true;
  }
  void bgw1_ProgressChanged(object sender, ProgressChangedEventArgs e)
  {
    if (this.CommandReceived != null)
      this.CommandReceived((string)e.UserState);
  }

  // and the links to manage the class
  public void Start(int portListen)
  {
    this.port = portListen;
    if (!bgw1.IsBusy)
      bgw1.RunWorkerAsync((object)port);
  }

  public void Stop()
  {
    bgw1.CancelAsync();
  }

in the host application

...
  private ucTcpListener ucTcpListener1; // my tcp server
...
  this.ucTcpListener1.CommandReceived += new delCommandReceived(ucTcpListener1_CommandReceived);
...
  void ucTcpListener1_CommandReceived(string command)
  {
    if (command.StartsWith("DBF"))
      CreateFiles(); // here is the real trigger
  }
...

simple…

Saturday, July 26th, 2014 at 11:23.
Filed under .net, oracle.