CODEWITHSUNDEEP
c#
BOOnZ
BOOnZ

Reputation: 848

C# refine method code for query

i am trying to create a method to access an access2010 .accdb database simply by calling the method with the SQL statement. this method currently works after my many hours of "trial and error". Are there any ways to refine this piece of code to make it more robust yet simpler because of the many steps involved. (new connection, then new command, then new reader etc seems to be too many of a steps in just executing one SQL command?)

btw this code is to query a database and return a string.

public static string getString(string SQL)
{   
    using (var connection = new OleDbConnection(connectionString))
    using (var command = connection.CreateCommand())
    {
        command.CommandText = SQL;
        command.CommandType = CommandType.Text;

        connection.Open();

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                return reader.GetString(0).ToString();
            }
        }

        return null;
        }
    }
}

Upvotes: 0

Views: 198

Answers (2)

Davide Piras
Davide Piras

Reputation: 44605

in my opinion this method looks very good, there is no much to add if not exception logging with a log of the SQL statement on a logging system.

mind also that you are actually returning a scalar string only, for that you could use the ExecuteScalar and avoid the reader completely.

Upvotes: 0

Bryan Watts
Bryan Watts

Reputation: 45445

Since you will at most read one value, you could use the ExecuteScalar method:

using(var connection = new OleDbConnection(connectionString))
using(var command = connection.CreateCommand())
{
    command.CommandText = SQL;
    command.CommandType = CommandType.Text;

    connection.Open();

    var value = command.ExecuteScalar();

    return value == DBNull.Value ? null : value.ToString();
}

Upvotes: 2

Related Questions