I'm currently working on a project for university where multiple users will be able to access the same data concurrently. Up to this point, I've only ever created programs for a single workstation and I have no idea where to start to create a program that multiple users can access simultaneously.
For the most part, users will not be able to update the same row of data from the same table at the same time, although this could happen. Mostly, users will view the same data concurrently. I'm using a C# application front-end and at the moment, MS SQl Server Compact Edition, although I intend to change to Sql Server 2008.
The database will obviously have to be deployed on a server which the clients (running the C# application) will access. This project is also group work. We're using Tortoise SVN as our source control client with Subversion running on my laptop as the server.
My questions are as follows:
1. At the moment, we've added our database as a data source and then access it using datasets and adapters. I assume this is incorrect if we want the database running off the server as this makes the database local to the application. Originally I created a connection to the database using an SQLConnection, opening the connection, running the query and closing the connection when I was finished, but this didn't work off my team-mates computers as it was looking for the database on my laptop. What would be the best/most correct way of creating the application that connects to the DB off the server?
2. How can we handle concurrent access to the same data?
3. I was thinking of using Linq to SQL for this project. Is this a good idea?
My last question, although a bit off topic but I thought I'd throw it in anyway, is as follows:
What is the best way to simulate an autonumber field? For one of our tables, the primary key must consist of the date it was added concatenated with the number of transactions for that day. I.e. the first record added would be 12/07/2010/1, the second would be 12/07/2010/2 and so on. Tomorrow's first record added would be 13/07/2010/1 and so on.
I was thinking of using a public variables for Date and number counter on a class and then using the variables from wherever I need to in the project. Everytime I add a new record, I'd compare the date in the class to the date today; if today's date is greater than the date stored, I'd update the date in the class and set the number counter back to 0. I'm not sure how efficient this method would be though.
I appreciate any help