problem in retrieving data from DB

msgiribabu

1. this is test code which i modified, but its seems not working, let me know the proper solution to retrieve data from DB.
2. Is that enough to write only DataAdapter and client, else i have to write MetaDataAdapter, DataAdapter and client.

public class MyDataAdapter : IDataProvider
    {
        private IItemEventListener listener;
        private volatile bool go;
        
        private DataTable dt;
        private SqlConnection conn;
        private SqlCommand comm;

        public MyDataAdapter()
        {
            try
            {
                conn = new SqlConnection("Data Source=(local);Database=Northwind; Integrated Security=true;");
                comm = new SqlCommand("Select * from Orders", conn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex);
                throw new DataProviderException(ex.Message.ToString());
            }
        }

        public void Init(IDictionary parameters, string configFile)
        {
            
        }

        public bool IsSnapshotAvailable(string itemName)
        {
            return false;
        }

        /// <summary>
        /// Used to receive a reference to the server's listener
        /// that will be used to inject events.
        /// </summary>
        /// <param name="eventListener"></param>
        public void SetListener(IItemEventListener eventListener)
        {
            listener = eventListener;
        }

        public void Subscribe(string itemName)
        {
            if (itemName.Equals("myData"))
            {
                Thread t = new Thread(new ThreadStart(Run));
                t.Start();
            }
        }

        public void Unsubscribe(string itemName)
        {
            if (itemName.Equals("myData"))
            {
                go = false;
            }
        }

        public void Run()
        {
            go = true;

            while (go)
            {
                try
                {
                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        IDictionary eventData = new Hashtable();
                        eventData.Add("OrderID", dr[0]);
                        eventData.Add("UnitPrice", dr[1]);
                        eventData.Add("Quantity", dr[2]);
                        eventData.Add("Discount", dr[3]);

                        listener.Update("myData", eventData, false);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

msgiribabu

when i tried to run the application i got this error, when init method is called that time its giving error

I/O Exception caught while reading/writing from/to streams: Connection reset, aborting..

let me know what i am missing

Dario Crivelli

You can see this thread page for a starting point on fetching data from a DB and this thread for a further discussion.

In order to feed LS Server with updates from a DB table, you can ask the DB for notifications; otherwise, the simplest way is to inquiry the table at regular intervals (but your code has no wait in the loop, which may be infeasible).
By the way, you close the connection to the DB inside the "while" loop, which compromises the loop behavior.

I confirm that you have to code the client and Data Adapter in parallel, because the item and field names involved have to be the same.
On the other hand, in the first phase, you can stick to the same Metadata Adapter used by the provided samples.

msgiribabu

i tried the sample program but its not working at my end, it compiles properly and runs but when i run the client its not working
http://www.mydemos.com/SimpleDBFeed/index.html
UNABLE TO CONNECT message is coming.

i have created the folder SimpleDBFeed and copied the .exe, dll's
and i have created adapter.xml in Adapter/SimpleDBFeed folder

its not working
check the attached sample which i have created

Dario Crivelli

We couldn't find any issue in the Lightstreamer-related code (apart from the loop with no wait).
I just suggest you should move any initialization code from the Data Adapter constructor to the Init method, but this is not an issue.
You should also ensure that the connection to the DB is open upon subsequent invocations of the Run method, but this should not be a problem at the current stage.

Can you confirm that your concerns are only on DB-related stuff?
Unfortunately, we are not familiar with that.

Where do you get the "UNABLE TO CONNECT" message?
The Data Adapter is supposed to connect in its constructor/Init method, hence, the message could be issued before you ever open the page.
Can you confirm that?

When you run your .NET project from Visual Studio, can you see the constructor and the Init method being invoked?
And after you open the page, can you see the Subscribe method being invoked?

msgiribabu

i have modified the code and executed, when i run the application from visual studio its properly without any errors. this is the flow of my code 1st my dataadapter constructor execute
server.Start()method will execute in the DataAdapterLauncher class next init() method from MyDataAdapter class is called there i opened the connection and next its executes SetListener() method after that it execute console.Writeline method next to server.Start() method will be called, this is the i wrote for testing check.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Lightstreamer.Interfaces.Data;
using System.Collections;
using System.Data;
using System.Threading;
using System.Data.SqlClient;

namespace DataAdapterDemo
{
    public class MyDataAdapter : IDataProvider, IExternalFeedListener
    {
        private IItemEventListener listener;
        private volatile bool go;
        
        private DataTable dt;
        public SqlConnection conn;
        private SqlCommand comm;

        private IDictionary subscribedItems;

        public MyDataAdapter()
        {
            //dt = new DataTable();
            //subscribedItems = new Hashtable();
        }

        public void Init(IDictionary parameters, string configFile)
        {
            try
            {
                conn = new SqlConnection("Data Source=(local);Database=Northwind; Integrated Security=true;");
                comm = new SqlCommand("Select * from Orders", conn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex);
                throw new DataProviderException(ex.Message.ToString());
            }
        }
        public void SetListener(IItemEventListener eventListener)
        {
            listener = eventListener;
        }
        public void Subscribe(string itemName)
        {
            if (itemName.Equals("item"))
            {
                Thread t = new Thread(new ThreadStart(Run));
                t.Start();
            }
        }

        public void Unsubscribe(string itemName)
        { 
            if (itemName.Equals("item"))
            {
                go = false;
            }
        }

        public bool IsSnapshotAvailable(string itemName)
        {
            if (!itemName.StartsWith("item"))
                throw new SubscriptionException("Unexpected item: " + itemName);

            return true;
        }

        public void Run()
        {
            go = true;
            try
            {
                while (go)
                {

                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        IDictionary eventData = new Hashtable();
                        eventData.Add("OrderID", dr[0]);
                        eventData.Add("UnitPrice", dr[1]);
                        eventData.Add("Quantity", dr[2]);
                        eventData.Add("Discount", dr[3]);

                        listener.Update("item", eventData, false);
                    }

                }
            }
            catch (Exception ex) { Console.WriteLine(ex); }
            try { Thread.Sleep(1000); }
            catch (ThreadInterruptedException ex) { Console.WriteLine(ex); }
            finally { conn.Close(); }
        }

        public void onEvent(string itemName, IDictionary currentValues, bool isSnapshot)
        {
            lock (subscribedItems)
            {
                if (!subscribedItems.Contains(itemName)) return;

                bool started = (bool)subscribedItems[itemName];
                if (!started)
                {
                    if (!isSnapshot)
                        return;

                    subscribedItems[itemName] = true;
                }
                else
                {
                    if (isSnapshot)
                    {
                        isSnapshot = false;
                    }
                }
            }

            if (listener != null)
                listener.Update(itemName, currentValues, isSnapshot);
        }
    }
}
public interface IExternalFeedListener
    {
        void onEvent(string itemName, IDictionary currentValues, bool isSnapshot);
    }

DataAdapterLauncher.cs code

public class DataAdapterLauncher
    {
        public static void Main(string[] args)
        {
            string host = ConfigurationManager.AppSettings[0].ToString();
            int reqRepPort = Convert.ToInt32(ConfigurationManager.AppSettings[1].ToString());
            int notifPort = Convert.ToInt32(ConfigurationManager.AppSettings[2].ToString());
            try
            {
                DataProviderServer server = new DataProviderServer();
                server.Adapter = new MyDataAdapter();

                TcpClient reqrepSocket = new TcpClient(host,reqRepPort);
                server.RequestStream = reqrepSocket.GetStream();
                server.ReplyStream = reqrepSocket.GetStream();

                TcpClient notifSocket = new TcpClient(host, notifPort);
                server.NotifyStream = notifSocket.GetStream();

                server.Start();

                Console.WriteLine("Remote Adapter connected to LightStreamer Server...");
                Console.WriteLine("Ready to publish data...");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Could not connect to Lightstreamer Server.");
                Console.WriteLine("Make sure Lightstreamer Server is started before this Adapter.");
                Console.WriteLine(ex);
            }
        }
    }

when i call client code then i get the "UNABLE TO CONNECT" message

Dario Crivelli

You should have your Subscribe, Unsubscribeand Run methods also log something.
If you find out that, after you run the client, Subscribeand Run get called and then "UNABLE TO CONNECT" appears, then the problem should be entirely in the DB access part (in fact, the reported message is not originated by Lightstreamer code).

In that case, we could not help you.
You should setup a simple .NET application not involving Lightstreamer in any way and try to log the DB table contents from there at regular intervals.
Once succeeded, we could help you porting the code in a Data Adapter for Lightstreamer.

msgiribabu

Now i am able to display the data in webclient and if i change value in db that automatically reflecting in webclient. In INIT method i am retrieving all the record in 1shot and storing in dataTable, and then showing in webclient, if change any value from backend its also reflecting the page with updated value, now if i added new record instead of modifying/updating existing record that should also be subscribed in webclient.

What are the changes that need to done and where exactly i have to write the code in which event.

Dario Crivelli

Until now, you have been associating your subscription to a table containing a single record.
You could see that in a more general way, by associating your subscription to the record in your table that is identified by a predetermined key (and the key value should be in correspondance with the item name).

Now, in order to subscribe to multiple lines, you should use multiple items and setup multiple lines on your page for displaying the results.
Otherwise, if you want to subscribe to the entire table contents, you could leverage COMMAND mode.
Please see this thread, which explores the former case, first.
Do you think you will need COMMAND mode?

msgiribabu

Thanks for your reply Dario, this is code which i am using for retrieve the updated value, which i have written in ExternalFeedProducer class.

public void ComputeNewValues() {
            lock (this) {
                try
                {
                    conn = new SqlConnection("Data Source=(local);Database=TestDB;Integrated Security=true;");

                    comm = new SqlCommand("Select TotalQuantity, UnitPrice from Products WHERE ProductID=" + _productID + ";", conn);
                    conn.Open();

                    SqlDataReader dr = comm.ExecuteReader();
                    while (dr.Read())
                    {
                        int totQty = Convert.ToInt32(dr[0]);
                        double unitP = Convert.ToDouble(dr[1]);
                        if (_totalQuantity != totQty)
                            _totalQuantity = totQty;
                        if (_unitPrice != unitP)
                            _unitPrice = unitP;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message.ToString());
                }
                finally
                {
                    conn.Close();
                }
            }
        }

Actually i have a table in SQL SERVER with multiple records [say 10 records with multiple columns], when i run the DataAdapter class in INIT method i am retrieving all the records from SQL SERVER and storing in DataTable.

Now the DataTable contains 10records stored internally, when i called the webclient i am getting all the records displayed in the page [total 10 records are displaying].

If i change any value in the DataBase [SQL SERVER table], its reflecting in the web client. My code work perfectly for only those records which is retrieved in INIT method when i run the DataAdapter. BUT if i add extra record into the database SQL SERVER [say i have added 11th record], now my table record count will be 11 instead of 10 and in my webclient its already showing 10records.

My Issue: when i add new record in db it should also reflect/show in the webclient, that mean whenever any new record is added in DB that should be reflected in my PAGE.

If i restart DataAdapter and access my webpage then its showing 11 records, instead of restarting dataadapter to check the updated record my dataadapter should automatically check if any new records is added in the database if added then it should display in my web page.

Hope you got the point what exactly i need. If you want more clarification then let me know i will explain with code and screen shots

Dario Crivelli

If you need that all your DB table is shown on the screen, then you also need a variable-size table on the screen too.
Please, see our Basic Portfolio Demo, where you can have lines added or removed by buying or selling stocks.

Is this a case similar to yours?
The demo leverages the COMMAND mode for items (a single item is needed for the whole table) and a DynaMetapushTable to display tabular data on the page.
This demo may be a better starting point for your use case.
However, the demo Data Adapter is not based on an external database,
so all the adapting work from database queries to the updates in COMMAND mode has to be made from scratch.
We can provide you with hints, if needed.

msgiribabu

Thankyou Dario, Out requirement matches similarly with BASIC PORTFOLIO DEMO.
Your Valuable Hints are always welcome DARIO.

let me know how to use these two [COMMAND mode, DynaMetapushTable ].

Suggestion: if you include samples in HELP file for each method & Property [how to use it] in java are any language [it could be not complete code but 3 to 4 lines of code is also enough to understand] then it could help the programmer to easily understand where exactly he has to use the method and how to use it instead of posting in forum and waiting for reply, and that too it will reduce burden for both, for replying/ clarifying doubts raised/posted by the developer in forum.

its just a suggestion from my side.

msgiribabu

Dario Crivelli

We chose to provide suggestions on how to use the various API methods through complete yet short examples.
This is because most methods have to be used in combination with others and it would be difficult for us to clarify the correct context (or the various possible contexts) for each call in a few lines of code.
Could you point us to a public API documentation that you feel as exemplary?

In your case, you have now identified the Basic Portfolio Demo as a starting point and you should now analyze the demo code
to see how data is transferred from the Data Adapter through the Server to the client page
and how the client uses a DynaMetapushTable to display the data.
Then you can refer to the documentation of DynaMetapushTable for the usage details, though for the example usage you have to stick to the demo.
You can also find a general introduction to COMMAND mode in the DOCS-SDKs/General Concepts.pdf document, particularly in paragraph 3.1
For any further aspects that have to be clarified, we are available via forum.

I remind you that, once you have grasped the logics of the COMMAND mode, you can no longer take advantage of the provided demo,
as adapting code from a DB table to COMMAND mode has to be written from scratch.
We may provide you with pseudocode, if needed.

msgiribabu

Thank you for responding Dario. I am very much satisfied with the support how you people are giving,

check the below link how they provided help with example's [ABCPdf7 doc],
http://www.websupergoo.com/helppdf7net/default.html

I am not saying that we have create full example for each method, but we can use single example where most of the method are used and divide that example and link it to those method which are used in the example.

Its just a suggestion to make your product more helpful to users and less burden on support team.

Dario Crivelli

Thank you for the suggestion.
Though I could find method-related examples only for the "Doc" class,
it is a big piece of work, that cannot be replicated in short time.
We could try to keep that into consideration.

Referring to full code snippets from inside a javadoc may not be easy.
A simple cross-reference can be found in the examples introduction page.