locked
How to write a C# listener to check and retreive last inserted row in a sql server table? RRS feed

  • Question

  • Hi All,

    I have two C#-wpf apps, where app1 inserts data into a sql server table.

    From App 2, I need to get the newly inserted row whenever the table is inserted with a new row.

    Can anyone provide me code help or point to any resources/tutorials, for such an listener in C#.

    Thanks,

    Bharat.

    Thursday, October 4, 2012 1:36 PM

Answers

All replies

  • What if you poll with app2 storing the id retrieved last time?

    Thursday, October 4, 2012 1:42 PM
  • Maybe you can retrieve all data at the moment in app2, and store in a DataTable, then when you need to get only newly inserted, you can use LINQ to get the rows that are in the new table and not in the old like this:

                DataTable firstData = new DataTable();
                firstData.Columns.Add("id");
                firstData.Columns.Add("name");
    
                // populating the first table.
                DataRow row = firstData.NewRow();
                row["id"] = "1";
                row["name"] = "a";
                firstData.Rows.Add(row);
                row = firstData.NewRow();
                row["id"] = "2";
                row["name"] = "b";
                firstData.Rows.Add(row);
    
                DataTable dtNewRows = new DataTable();
                dtNewRows.Columns.Add("id");
                dtNewRows.Columns.Add("name");
    
                // populating the second table
                row = dtNewRows.NewRow();
                row["id"] = "1";
                row["name"] = "a";
                dtNewRows.Rows.Add(row);
                row = dtNewRows.NewRow();
                row["id"] = "2";
                row["name"] = "b";
                dtNewRows.Rows.Add(row);
                row = dtNewRows.NewRow();
                row["id"] = "3";
                row["name"] = "c";
                dtNewRows.Rows.Add(row);
    
                var query = dtNewRows.AsEnumerable().Except(firstData.AsEnumerable(), DataRowComparer.Default); 
                // query will have as values id = 3 and name = c


    Web Developer


    • Edited by Norkk Thursday, October 4, 2012 1:52 PM
    Thursday, October 4, 2012 1:52 PM
  • I think either SqlDependency or SqlCacheDependency should help you.

    However pay attention to the advice mentioned in the Note section since your application seems to be a client app (WPF)?, and if it is likely to be used by thousands of users in turn leading to those many number of registrationscations via the OnChangeEventHandler event handler.

    HTH

    Sameer


    If this answers your question, please Mark it as Answer. If this post is helpful, please vote as helpful.

    Thursday, October 4, 2012 6:01 PM
  • Yes try using SQLDependency as shown in the below link

    http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events

    Also try below link if this works

    http://lab.technet.microsoft.com/en-us/library/ms162170(v=sql.90).aspx



    Mark it as helpful if so!!! thanks, Mithilesh

    • Marked as answer by ykbharat Friday, October 5, 2012 12:19 PM
    Thursday, October 4, 2012 10:37 PM