none
winforms / ADO.net concurrency question RRS feed

  • Question

  • Hi,

    What approach(es) would you recommend regarding a WinForms application that will have both the user interface & a scheduling components regarding sharing configuration data from the database? (exposed in the program via DataTable) Assume both the scheduled task and the user interface can make updates to the shared data. Also assume that the scheduling component is part of the winforms application itself (and spawns a separate thread when it runs)

    What approaches are available in WinForms / ADO.net (DataTables) that would allow one to develop some sort of sharing of data approach? For example:

    a) fully shared - but then how would one side get a notification if the other client had changed the data from under-its-feet so to speak? Would you use the same DataTable in the user interface and the scheduling component and some callback that might be available?

    b) if not shared at all - but then what would be a good approach to lock the database or DataTable from the other client until it's finished, such that the client will block perhaps until its ready. For example, say the scheduling component kicks off and is about to go and get a list of items from the table to work on, however at the same point of time a user is in the maintenance page in the winforms application for this table and is say deleting a couple of items.

    Thanks

    Sunday, January 24, 2010 5:35 AM

Answers

  • Hello Greg,

     

    I am an expert of SQLLite, so I recommend you also consult the problem at some SQLLite support channel to see whether SQLLite has similar features as SQL Server does.   In my opinion, it is really hard to accomplish the target only with the help of ADO.NET. 

     

    For Question a), we may need to query the database to check whether the database has been changed periodically, however, I really don’t think it is a good option.  

     

    For Question b), we can use the Transaction and Isolation level when we update the database to avoid concurrency issues.  However, it is very hard for us to verify only one user is using the database at one time.   One workaround is that we can first retrieve the current connection number of certain database, if another connection exists, the current application will hold.  But if so, we cannot use the connection pool since we need to clear each connection after we don’t need it. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 27, 2010 8:43 AM
    Moderator

All replies

  • Hello Greg,

     

    For Question a), I recommend you consider using a very helpful class SqlDependency.  We will receive a notification if the data source is modified at the database side.  Please note that such a QueryNotificiationService is not included in SQL Server Express versions.   Here are some sample codes about using SqlDependency in a Windows Application, http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx. 

     

     

    For Question b), first we can consider the Transactions and Isolation level, http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx.   Inside one transaction, other access to the data table can be blocked.  Another option would be to set the SQL Server database as SINGLE_USER mode, which only allows one connection one time, http://www.kodyaz.com/articles/alter-single-user-multi-user-mode.aspx.   However, this feature is only set on database instead of any individual data tables.  

     

     

    If you have any questions, please feel free to let me know.  Besides, you can provide us with your detailed scenario, maybe we can figure out some other more appropriate methods.  J

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, January 25, 2010 6:32 AM
    Moderator
  • Hello Greg,

     

    How are you?   Could you please tell me how is the problem at your side? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 27, 2010 12:55 AM
    Moderator
  • Hi Lingzhi,

    My only problem here is that my database is not SQLServer (but rather SQLite) so I was hoping for a non database specific approach...

    thanks
    Greg
    Wednesday, January 27, 2010 1:13 AM
  • Hello Greg,

     

    I am an expert of SQLLite, so I recommend you also consult the problem at some SQLLite support channel to see whether SQLLite has similar features as SQL Server does.   In my opinion, it is really hard to accomplish the target only with the help of ADO.NET. 

     

    For Question a), we may need to query the database to check whether the database has been changed periodically, however, I really don’t think it is a good option.  

     

    For Question b), we can use the Transaction and Isolation level when we update the database to avoid concurrency issues.  However, it is very hard for us to verify only one user is using the database at one time.   One workaround is that we can first retrieve the current connection number of certain database, if another connection exists, the current application will hold.  But if so, we cannot use the connection pool since we need to clear each connection after we don’t need it. 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, January 27, 2010 8:43 AM
    Moderator
  • thanks Lingzhi
    Saturday, January 30, 2010 7:28 AM
  • You are welcome, Greg!

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, February 1, 2010 1:02 AM
    Moderator