user2571670 user2571670 - 25 days ago 6
C# Question

C# Database static or not

I am developing a tool which gets some information from a MSSQL database. There is an own database
user for the access. I know how static works an when I should use them but in this case I am not sure.

I have an own Database class:

class Database
{
private const String connString = "the connection string";
private const String query1 = "";

public Database()
{
// some initialization
}

// some different methods which calls CreateCommand and a queryString and
// returns the result

private static void CreateCommand(string queryString, string connectionString)
{
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(queryString, sqlConnection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader != null)
{
while (dataReader.Read())
{

}
}
}
}
}
}
}


There are just select queries where I get data from the server. A own thread will call some database methods for example every hour an displays the result.
Should I use a static class and static methods for this or a normal class with only the createCommand method static or nothing static?

Kind regards

Answer

Except in specific cases, static methods should be avoided as they reduce testability and cause your design to become tightly coupled. A full discussion is outside the scope of this site, but here are a couple of places to start:

http://objcsharp.wordpress.com/2013/07/08/why-static-code-is-bad/ http://misko.hevery.com/2008/12/15/static-methods-are-death-to-testability/

In your case, however, your static method is private, which as pointed out by BG100, doesn't seem to make sense in the context of your instantiated class. Why not just declare it an instance (non-static) method, and keep your code flexible by passing in the connection string in the constructor:

class Database
{
    private string connString;

    public Database(string connString)
    {
        this.connString = connString;
    }

    private void CreateCommand(string queryString)
    {  
        using (SqlConnection sqlConnection = new SqlConnection(this.connString))
        {
            // etc.
        }
    }
}

But now you have the problem that your CreateCommand is returning void. Once you read the data from your reader, how are you returning it to the calling method? Perhaps you should take a look at using a DataSet instead:

http://stackoverflow.com/a/4099694/289319