MS SQL -- Okay for multiple users to connect/read/write to database from a web service (c#)?

Question

Hello all,

This is probably a simple one...

Right now I have a working web service (programmed in c#) and it offers up two webmethods that either read or write data to a MS SQL database. What I want to know is this... Do I have to worry about errors when multiple users try to connect to, reading from, and writing to the database? Furthermore, should I be using threads and locks in my webmethods when they access the database (right now, I'm not using threads or locks)?

Here is some idea of what I have running now (code snippet):

[WebMethod(Description = "Attempts to post a new score to the scoreboard.")]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public TextMessage UpdateScoreboard(string password, string playerName, 
          int level, int gameBoard, string auth)
    {
        SqlDataReader myReader = null;
        SqlCommand myCommand = null;
        SqlConnection myConnection = null;

        /*
        The method then attempts to connect to the DB, 
        look for a duplicate playerName, and if there isn't one,
        data is entered into the DB ... using the INSERT command.
        */
    }

Thanks.

Answer 1

Yes, you have to worry about it. (But not with threads and locks in your webservice - that probably won't help).

I think you can handle a certain amount in the stored proc (locking hints, try/catch exception handling) if you use that through SqlCommand. You still might become have errors so you'll also have to handle them in the web service itself, too.

Answer 2

You don't have to worry about it heavily depending on the situation. As is the nature of databases, you might have situations were folks reading from the database are getting 'old' information, as in they might not get information that was entered recently from when the read operation took place. If this is a big issue for your specific situation then you'll have to think about using locks, however if that isn't a big deal you should be fine.

However, if you start getting a heavy volume of requests either to write or read, you may have to worry about locking or concurrency issues. Based on the simplicity it doesn't really sound like you have to worry much about it though.

Regardless, make sure you handle exceptions and plan for the obvious things that could happen (connection to SQL is down, various error conditions that may be present in your code, etc.)

I generally recommend against locking and threading unless you absolutely have a good reason to go down those paths. Threading should also be far less of an issue here due to the nature of web services, they are designed to handle concurrent usage. That is unless you have a very long running service request, then I'd move that into a thread, but, the less complexity the better.