How can I stop repeating the same code when querying a database

Question

I am writing my first large application in C# and as it grows, I've started repeating the same code in every method that queries the database, more or less like this:

public static bool methodName(string ID, string Name, //etc)
{
    bool success = false;
    DBConnection db = new DBConnection(); //Oracle connection class
    db.Connect(); //Connect to the database

    if (db.GetConnectionState())  //checks the connection
    {
        db.SetSql("//insert, delete, update, etc, each method is different");
        db.addParameter("id", ID);

        if (db.ExecuteTransactions())  //attempts to insert, delete, update, whatever
            success = true;
        else
            success = false;
    }
    else
        success = false;

    db.Dispose();

    return success;    
}

I want only one method like this where the db.SetSql string can be anything. The problem is that every query needs different parameters passed to it from the user interface class, and that the number of db.addParameter calls will be different for a query to one table or another.

I know this is really basic, but I just can't get it. I really want these methods, which are multiplying exponentially every time I add a feature to the application, to be reduced down to one reusable one. Please help

MakePeaceGreatAgain · Accepted Answer · 2016-03-23 12:37:05Z

2

You may use a Dictionary for your parameters;

public static bool methodName(string query, Dictionary<string, string> parameters)
{
    bool success = false;
    DBConnection db = new DBConnection(); //Oracle connection class
    db.Connect(); //Connect to the database

    if (db.GetConnectionState())  //checks the connection
    {
        db.SetSql(query);
        foreach(var param in parameters) db.addParameter(param.Key, param.Value);

        if (db.ExecuteTransactions())  //attempts to insert, delete, update, whatever
            success = true;
        else
            success = false;
    }
    else
        success = false;

    db.Dispose();

    return success;    
}

Now call it as follows:

methodName("Insert...", new Dictionary<string, string> {{"Id", ID }};

edited Mar 23, 2016 at 12:37

answered Mar 23, 2016 at 12:34

MakePeaceGreatAgain

37.4k6 gold badges67 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

diiN__________ Over a year ago

Instead of using KeyValuePair<string, string>[] he could pass a Dictionary.

North Over a year ago

This is great, but sometimes the values are int or DateTime, so Dictionary<string,string> wont work. Not sure if this is good practice (or if it'd even work) but could I do something like : ...methodName(string query, Dictionary<string, string> paramString, Dictionary<string, int> paramInt = 0,...)

MakePeaceGreatAgain Over a year ago

Depending on what db.addParameter expects as datatype for the seond param, you may also use a Dictionary<string, object> where you can put anything into.

North Over a year ago

I just checked, it expects string, object, so I will do that

Daniel van Heerden · Accepted Answer · 2016-03-23 12:31:54Z

1

Consider using an ORM like NHibernate or Entity Framework. It does that stuff for you. Also look at coupling that with an inversion of control framework using something like autofac and you'll be much better off.

answered Mar 23, 2016 at 12:31

Daniel van Heerden

8361 gold badge7 silver badges25 bronze badges

Collectives™ on Stack Overflow

How can I stop repeating the same code when querying a database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related