General Query Method

Question

I find that my C# apps do a lot of queries with a lot of boilerplate that clutters up my code space. I also want to avoid repetition, but I'm not sure how I could write a method to do this generically.

I am accessing an Oracle database using ODP. I can't use Linq because our data warehouse people refuse to designate primary keys, and ODP support for Linq appears to be, well ... they'd rather have you use their platform.

I can't really return a List because every query returns different numbers of different types.

string gufcode = String.Empty;
double cost = 0.0;
OracleCommand GUFCommand2 = thisConnection.CreateCommand();
String GUFQuery2 = "SELECT GUF_ID, COST_RATE FROM SIMPLE_TABLE";
GUFCommand2.CommandText = GUFQuery2;
OracleDataReader GUFReader2 = GUFCommand2.ExecuteReader();
while (GUFReader2.Read())
{
    if (GUFReader2[0/**GUF_CODE**/] != DBNull.Value)
    {
        gufcode = Convert.ToString(BUFReader2[0]);
    }
    if (GUFReader2[1/**COST_RATE**/] != DBNull.Value)
    {
        cost = Convert.ToDouble(GUFReader2[1]);
    }

    effortRatioDictionary.Add(bufcode, percentageOfEffort);
}
GUFReader2.Close();

But there's really a lot more terms and a lot more queries like this. I'd say 15 or so queries -some with as many as 15 or so fields returned.

Copy/pasting this boilerplate everywhere leads to a lot of fires: for example if I don't update everything in the copy paste I'll close the wrong reader (or worse) send a different query string to the database.

I'd like to be able to do something like this:

string gufQuery = "SELECT GUF_ID, COST_RATE FROM SIMPLE_TABLE";
List<something> gufResponse = miracleProcedure(gufQuery, thisConnection);

And so most of the boilerplate goes away.

I'm looking for something simple.

Answer 1

Linq was the right answer. I give credit to David M, above, but I can't mark it as the correct answer since he only left a comment.

I was able to do a semi-generalized method using ArrayLists:

    public static ArrayList GeneralQuery(string thisQuery, OracleConnection myConnection)
    {
        ArrayList outerAL = new ArrayList();

        OracleCommand GeneralCommand = myConnection.CreateCommand();
        GeneralCommand.CommandText = thisQuery;
        OracleDataReader GeneralReader = GeneralCommand.ExecuteReader();
        while (GeneralReader.Read())
        {
            for (int i = 0; i < GeneralReader.FieldCount; i++)
            {
                ArrayList innerAL = new ArrayList();

                if (GeneralReader[i] != DBNull.Value)
                {
                    innerAL.Add(GeneralReader[i]);
                }
                else
                {
                    innerAL.Add(0);
                }
                outerAL.Add(innerAL);
            }
        }
        GeneralReader.Close();

        return outerAL;
    }

And the code that calls this method looks like this:

                thisConnection.Open();

                List<ProjectWrapper> liProjectCOs = new List<ProjectWrapper>();

                String ProjectQuery = "SELECT SF_CLIENT_PROJECT.ID, SF_CLIENT_PROJECT.NAMEX, SF_CHANGE_ORDER.ID, SF_CHANGE_ORDER.END_DATE, ";
                ProjectQuery += "SF_CLIENT_PROJECT.CONTRACTED_START_DATE, SF_CHANGE_ORDER.STATUS, SF_CHANGE_ORDER.TYPE, SF_CLIENT_PROJECT.ESTIMATED_END_DATE, SF_CLIENT_PROJECT.CONTRACTED_END_DATE ";
                ProjectQuery += "FROM SF_CLIENT_PROJECT, SF_CHANGE_ORDER ";
                ProjectQuery += "WHERE SF_CHANGE_ORDER.TYPE = 'New' ";
                ProjectQuery += "AND SF_CLIENT_PROJECT.ID = SF_CHANGE_ORDER.PROJECT";
                ArrayList alProjects = GeneralQuery(ProjectQuery, thisConnection);
                foreach( ArrayList proj in alProjects ) {
                    ProjectWrapper pw = new ProjectWrapper();
                    pw.projectId = Convert.ToString( proj[0] );
                    pw.projectId = Convert.ToString(proj[0]);
                    pw.projectName = Convert.ToString(proj[1]);
                    pw.changeOrderId = Convert.ToString(proj[2]);
                    pw.newEndDate = Convert.ToDateTime(proj[3]);
                    pw.startDate = Convert.ToDateTime(proj[4]);
                    pw.status = Convert.ToString(proj[5]);
                    pw.type = Convert.ToString(proj[6]);
                    if ( Convert.ToString(proj[7]) != "0" ) // 0 returned by generalquery if null
                        pw.oldEndDate = Convert.ToDateTime(proj[7]);
                    else
                        pw.oldEndDate = Convert.ToDateTime(proj[8]);
                    liProjectCOs.Add(pw);

There's a lot of obvious disadvantages here (although it is a lot better than what I was trying to do earlier). It is so much worse than Linq I renegotiated with our data warehouse people. There's a new guy there, and he was a lot more helpful.

Linq reduces the lines of code from above by a factor of 2. It is a factor of 4 from the non-encapsulated way I was doing it before that.