locked
How each instance of same C# application can fetch unique set of data from db table RRS feed

  • Question

  • suppose i have a table order where order info stored and i have a console application which is running in 5 pc and this console application will fetch data from table order but in specific way.

    suppose if console application running in PC1 fetch those order data which not fetched by other same console application running in other PC. how could i develop this logic as a result if console app1 fetch records 1 to 5 then console application app2 will not fetch records 1 to 5 rather it may fetch available records which is not fetched by other same console application running in other PC.

    if my scenario is not clear. then please tell me then i will explain it more elaborately with example.

    please give me knowledge to develop this kind of application which will be fetching data from same table but always works with those free data which has not been fetched by other same console application running in other PC.

    do i need to maintain any trick at db end or it can be done fully at application level?

    do i need to lock db records by select statement and if yes then what isolation level should be followed in my SP for fetching data ?

    please share some sample code including C# and sql server relation which help me to design this job.

    thanks 


    • Edited by Sudip_inn Thursday, September 10, 2020 7:28 PM
    Thursday, September 10, 2020 7:09 PM

Answers

  • Hi Sudip_inn,

    I think this question may be more related to the database side. Maybe you can try to ask questions in Microsoft Q&A to see if the engineers there can give you some other opinions.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Tuesday, September 15, 2020 8:44 AM
    Tuesday, September 15, 2020 8:00 AM

All replies

  • Hi, Sudip_inn.  I think your issue is clearly described and well supported in most database engines :-)

    Most database engines will have a RECORD LOCK mechanism of some type or other, so that when your PC1 fetches rows 1-5 it locks them.  Subsequently PC2 can check if the row(s) you want to retrieve are already record locked, or in some DB Engines you can just try to get the 1-5 set and try/catch trap any exceptions, so that if a record-locked exception occurs you can move on to the next dataset.

    Before anyone could provide you with any kind of code sample, you'd have to divulge your database file format or database engine and specific DotNET Connector version.


    Before you can learn anything new you have to learn that there's stuff you don't know.

    Friday, September 11, 2020 12:33 AM
  • If SQL-Server look at implementing SqlDependency. There is setup on the database and restrictions which for what you want will not be an issue.

    Basic example from the docs

    void Initialization()
    {
        // Create a dependency connection.
        SqlDependency.Start(connectionString, queueName);
    }
    
    void SomeMethod()
    {
        // Assume connection is an open SqlConnection.
    
        // Create a new SqlCommand object.
        using (SqlCommand command=new SqlCommand(
            "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers",
            connection))
        {
    
            // Create a dependency and associate it with the SqlCommand.
            SqlDependency dependency=new SqlDependency(command);
            // Maintain the reference in a class member.
    
            // Subscribe to the SqlDependency event.
            dependency.OnChange+=new
               OnChangeEventHandler(OnDependencyChange);
    
            // Execute the command.
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process the DataReader.
            }
        }
    }
    
    // Handler method
    void OnDependencyChange(object sender,
       SqlNotificationEventArgs e )
    {
      // Handle the event (for example, invalidate this cache entry).
    }
    
    void Termination()
    {
        // Release the dependency.
        SqlDependency.Stop(connectionString, queueName);
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, September 11, 2020 2:30 AM
  • Thanks for answer Madam but in my scenario why should i use SqlDependency ?

    how far i know people use SqlDependency just to get notification which records changed but my scenario is different.

    my scenario is my same application instance will run in 5 different pc but each instance will work with db data which another instance will not consider. 

    if my scenario is not clear that what i am trying to do then please let me know then i will elaborate this scenario in more details. thanks

    Friday, September 11, 2020 7:40 AM
  • Hi Sudip_inn,

    I think this question may be more related to the database side. Maybe you can try to ask questions in Microsoft Q&A to see if the engineers there can give you some other opinions.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Tuesday, September 15, 2020 8:44 AM
    Tuesday, September 15, 2020 8:00 AM