Multi-user concurrent access

[Loony064]

Hey all

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
Regards
Laura

[finito]

Hmm, This is from my limited knowledge that I have of running a similar setup.
So if someone finds errors please point them out.

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?

This should work without any problems, The server is also a machine and can run the program you are making. Did you enable remote access? Here is a nice link to help you do so if you haven't.
http://blogs.msdn.com/b/bethmassi/archive/2008/09/17/enabling-remote-sql-express-2008-network-connections-on-vista.aspx

Using datasets and adapters is fine, but IMO it is sluggish. You may want to use direct sql string commands using SQLCommand and SqlDataReader.

2. How can we handle concurrent access to the same data?

You can lock the data when it is accessed with a boolean column. So when another User accesses the data it will either be shown as Write protected or denied.

There are other ways but this one is the one I like most. An I believe SQL 2008 has some in-built protection for this.

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.

This will be a pain when you code it, It would be easier to make another column and let it keep track of it. Or you can use system Time to keep track of who made the first edit of the day.

Sorry I have no experience with Linq so I can't answer your third question.

[Loony064]

Thanks so much for the response. I'll take a look at the link.

Regards
Laura