Database connection via a single method but using multiple query

Question

Hi just a quick question for my own curiosity. I am trying to not repeat my code. Im still learning how to use parameters and arguments properly so i would imagine it will be through this route here is my code.

public void MultiChoiceLight()
    {
        lCon = new SQLiteConnection(@"Data Source=knowledge.db;Version=3");
        lCon.Open();
        string query1 = $"UPDATE testOrder SET question='{QuestionsFromDb.question}', choice1='{QuestionsFromDb.choice1}" +
            $"', choice2='{QuestionsFromDb.choice2}', choice3='{QuestionsFromDb.choice3}', choice4='{QuestionsFromDb.choice4}' " +
            $"WHERE qid={QuestionsFromDb.b}";
        lCmd = new SQLiteCommand(query1, lCon);
        lCmd.ExecuteNonQuery();
        lDr = lCmd.ExecuteReader();
        lCon.Close();
    }

public void MultiChoiceButtonNext()
    {
        lCon = new SQLiteConnection(@"Data Source=knowledge.db;Version=3");
        lCon.Open();
        string query = $"SELECT * FROM testOrder WHERE qid={qid}";
        lCmd = new SQLiteCommand(query, lCon);
        lCmd.ExecuteNonQuery();
        lDr = lCmd.ExecuteReader();
    }

Ok so what im trying to figure out is to use the lines of code within each method only once and then input the query in afterwards so i can do this as many times as possible via a method. I have quite a few methods like this and would really like to shorten my code. Before you say i am aware of lambda expressions and entity but im not using it within the application i am trying to make. Would be nice if i could save my queries into a class file then call them from there just to make my code look tidy. Thanks for reading.

Yeldar Kurmangaliyev · Accepted Answer · 2017-04-26 04:17:02Z

You can just wrap your SQL connection in some sort of helper class and wherever you need:

// TODO: Parametrized queries?
public class SQLConnectionHelper
{
    private readonly string _connectionString;

    // TODO: Parameterless constructor which gets connection string from config?

    public SQLConnectionHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    private TResult WithConnection<T>(Func<SQLiteConnection, TResult> func)
    {
        // TODO: try-catch-rethrow-finally here

        using (var connection = new SQLiteConnection(_connectionString))
        {
            _sqliteConnection.Open();

            var result = func(_sqliteConnection);

            _sqliteConnection.Close();

            return result;
        }
    }

    public void ConnectExecuteReader(string query, Action<SQLiteDataReader> action)
    {
        WithConnection(conn => {
            var reader = new SQLiteCommand(query, conn).ExecuteReader();
            action(reader);
        });
    }

    public int ConnectExecuteNonQuery(string query)
    {
        return WithConnection(conn => {
            return new SQLiteCommand(query, conn).ExecuteNonQuery();
        });
    }
}

Usage:

public class YourClass
{
    private readonly SQLConnectionHelper _sql = new SQLConnectionHelper(@"Data Source=knowledge.db;Version=3");

    public void MultiChoiceLight()
    {
        string query1 = $"UPDATE testOrder SET question='{QuestionsFromDb.question}', choice1='{QuestionsFromDb.choice1}" +
        $"', choice2='{QuestionsFromDb.choice2}', choice3='{QuestionsFromDb.choice3}', choice4='{QuestionsFromDb.choice4}' " +
        $"WHERE qid={QuestionsFromDb.b}";

        int result = _sql.ConnectExecuteNonQuery(query1);
    }

    public void MultiChoiceButtonNext()
    {
        _sql.ConnectExecuteReader($"SELECT * FROM testOrder WHERE qid={qid}", r => {
            // process your reader here, outside of this lambda connection will be closed
        });
    }
}

Benefits:

SQL connection type is encapsulated into SQLConnectionHelper and can be substituted, the client is almost abstracted (except for SQLiteReader, can be rewritten) from specific type of SQL connection;
SQL connection configuration (connection string) is encapsulated;
SQL connection error handling is encapsulated, you can throw some specific CustomSQLException and you only need to define it in one place;
SQL connection opening / closing is encapsulated. For example, you can have one single connection instead of connecting for every call, and you only need to change it in one place;
Many other OOP benefits for maintainance and future extensibility;
SQLConnectionHelper can implement IDisposable and clean connection resources out.

This solution may seem a little too "functional" because uses high-order functions, you can rewrite it a more OOP-way, it is only written to give the idea, not implementation.

d512 · Accepted Answer · 2017-04-26 04:12:00Z

1

Definitely make the connection string a constant declared somewhere one time.

private const string DB_CONN_STR = @"Data Source=knowledge.db;Version=3";

You could also make a generic method to get a reader:

private SqliteDataReader ExecuteReader(SQLiteConnection lCon, string sql)
{
    lCon.Open();
    lCmd = new SQLiteCommand(sql, lCon);
    return lCmd.ExecuteReader();
}

and use it like this:

using(SQLiteConnection lCon = new SQLiteConnection(DB_CONN_STR))
{
    SqliteDataReader reader = ExecuteReader(lCon, "SELECT * FROM MyTable");

    while(reader.Read())
    {
        // do stuff...
    }
}

answered Apr 26, 2017 at 4:12

d512

34.6k31 gold badges91 silver badges120 bronze badges

1 Comment

PaulD1983 Over a year ago

Thank you for the advice on the private const string for connection string it has helped alot :)

Collectives™ on Stack Overflow

Database connection via a single method but using multiple query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related