Pitfalls of calling static method from ASMX

Question

Hello, I would like to know if there are any pitfalls on calling a static method from an ASP.NET web service.

    internal static object SelectScalar(String commandText, DataBaseEnum dataBase)
    {
        SqlConnection sqlc = new SqlConnection(AuthDbConnection.GetDatabaseConnectionString());
        object returnval=null;
        if (sqlc!=null)
        {
            SqlCommand sqlcmd = sqlc.CreateCommand();
            sqlcmd.CommandText = commandText;
            sqlc.Open();
            returnval = sqlcmd.ExecuteScalar();
        }
        return returnval;
    }

So for an example the method above; are there any pitfalls on multiple web methods and multiple clients calling this method at the same time (say, 1000 calls to a web method that calls this function)?

Answer 1

The thing to be aware about is when static members change state that is accessible to other threads in the application domain. In these cases, you have to take the proper measures to make it happen in an orderly way.

Your method does not touch any state out of itself (everything is local) so you're OK.

---

As duffymo and nader noted, you should dispose of your connection as you should dipose of any object that implements IDisposable.

Answer 2

I don't know if C# is like Java, but opening a SQL connection and failing to close it before leaving the method doesn't seem like a good idea for me. The GC will clean it up once it goes out of scope, but that's not the same thing as closing the connection in Java.

The idiom in Java would demand that you close the connection in a finally block. Unless you're certain that the C# class doesn't require such a thing, I'd look into it.

You'll find out soon enough - thousands of web calls will exhaust the number of connections available quickly if they're scarce.

One more thing to check: opening connections this way is expensive in Java, so they're usually pooled. Is connection pooling also done in C#? Is it inefficient to keep opening and closing a database connection? Could you accomplish the same thing with a static, shared connection? If you go that way, perhaps threading issues come into play.

Answer 3

Since you're creating a new SqlConnection, you want to dispose it or the connection won't close. See MSDN for usage guidelines.

The fact that its a static method though... that doesn't seem to be an issue since you're not updating any shared state (global variables).

EDIT: AFAIK, the 'pitfalls' of static methods in webservices are the same as in any other application. The only thing to keep a note of is that a webservice is a server that is expected to run reliably for a long period of time. Thus things that could cause problems over time (memory leaks, db connection exhaustion, etc) are more significant than for other applications that run for a much shorter time period.