What are the potential issues using this static cl

Question

Here is my sample code:

public static class MySqlHelper
{
    private static string constring = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;

    public static int ExecuteNonQuery(string mysqlquery)
    {
        SqlConnection conn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(mysqlquery, conn);
        int result;

        try
        {
            conn.Open();
            result= cmd.ExecuteNonQuery();
        }
        finally
        {
            conn.Close();
        }
        return result;
    }
}

Usage: MySqlHelper.ExecuteNonQuery("select * from customers");

I would like to know the issues using this static class.

I can change my class as mentioned here but I have been using this class in couple of websites and I will need couple days to change it in every place and test it out.

Thanks for any inputs.

Edit: Updated the code. Does that make difference on the answers provided? Sorry, I should have posted in the beginning.

Answer 1

I thought he was pretty clear on his answer.

Writing my own Provider Class in ASP.NET

"remember that the connection will be shared at the same instance for all users, which can cause bad problems..."

Answer 2

Static classes are difficult to test.

Your question mentions that for a change you'll have to change the class on a couple websites. If your websites were coupled to an interface, then swapping out the implementation would be relatively simple.

In my own projects I avoid public static classes. They can get unwieldy very quickly.

Answer 3

I am assuming the connection string doesnt change during execution (you might want to make it readonly). Since there is no other shared state shown in the question, there are no real problems.

However, if you have any shared state you have a huge threading problem. And if you have shared connections you have an even bigger problem.

But as written, with no significant static fields: no problem.

Answer 4

The obvious drawbacks are:

• hardcoded connection string
• without connection pooling in the driver this is very bad: //open conn + //close conn

1) can be ugly solved by using a singleton as the connection string name

2) cannot be solved in a static class without introducing shared data: ie. without planting a knife in your back.

Answer 5

Do you know that the MySQL Connector for .NET now has a MySqlHelper class (you can check it in the source here)?

Answer 6

If you are going to take this route you many want to consider using the Microsoft DAAB SqlHelper v2 offering. It uses the same concept but is much more robust. It also caches SqlParameters. The code came out circa 2001 and has been in constant use by many developers.

http://www.microsoft.com/download/en/details.aspx?id=435