4
\$\begingroup\$

I write a metric ton of queries for an Oracle environment every day. These are reports that often require multiple separate queries that no end of joins and sub-queries will satisfy, hence lots of separate queries.

I find myself rewriting this bit of code all too often:

try
{
    using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[current_database].ConnectionString))
    {
        conn.Open();
        using (OracleCommand executeQuery = new OracleCommand(sql, conn) { CommandType = CommandType.Text, BindByName = true })
        {
            executeQuery.Parameters.Add( parameter, OracleDbType.SomeType ).Value = paramter_value 
            using (OracleDataReader dr = executeQuery.ExecuteReader())
            {
                while (dr.Read())
                {  
                    //iterate results
                }
            }//end-using-rdr
        }//end-using-cmd
    }//end-using-con
}
catch (Exception ex)
{
   //do stuff
}

I have dumped this code into a class such that I call the class.method and send the SQL string, the parameters + parameter values, and list of columns I'd like returned with their SQL datatype.

The method within the class

public Dictionary<string, Dictionary<string, string>> OracleSelect(string sql, Dictionary<string, string> parameters, List<string> columns)
{
    string current_database;
    int rowCount = 0;
    Dictionary<string, Dictionary<string, string>> result_set = new Dictionary<string, Dictionary<string, string>>();
    Dictionary<string, string> result = new Dictionary<string, string>();
    current_database = getCurrentDB();

    try
    {
        using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[current_database].ConnectionString))
        {
            conn.Open();
            using (OracleCommand executeQuery = new OracleCommand(sql, conn) { CommandType = CommandType.Text, BindByName = true })
            {

                //Iterate through Dictionary to bind parameter name (the dictionary key) and the parameter value (the dictionary value which is typically user input)
                foreach (KeyValuePair<string, string> param in parameters)
                {
                    executeQuery.Parameters.Add(":" + param.Key, OracleDbType.Varchar2).Value = param.Value;
                }
                using (OracleDataReader dr = executeQuery.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        foreach (string col in columns)
                        {
                            //Accepts values "ColumnName" or "ColumnName:DataType"
                            //Data type defaults to string if no datatype specified
                            //Checks for null values instead of having to write queries using nvl(ColumnName,"NullValueReplacement")
                            //Gets column values and assigns them to column name based off column name which causes extra overhead
                            string[] split = col.Split(':');
                            if (split.Length > 1)
                            {
                                if (!dr.IsDBNull(dr.GetOrdinal(split[0])))
                                {
                                    switch (split[1])
                                    {
                                        case "string":
                                                result.Add(split[0], dr.GetString(dr.GetOrdinal(split[0])));
                                                break;
                                        case "date":
                                                result.Add(split[0], dr.GetDateTime(dr.GetOrdinal(split[0])).ToString());
                                                break;
                                        case "int":
                                                result.Add(split[0], dr.GetInt32(dr.GetOrdinal(split[0])).ToString());                                                        
                                                break;
                                        default:
                                                result.Add(split[0], dr.GetString(dr.GetOrdinal(split[0])));
                                                break;
                                    }//end-split
                                }
                                else
                                {
                                    result.Add(split[0], "");
                                }
                            }
                            else
                            {
                                if (!dr.IsDBNull(dr.GetOrdinal(col)))
                                {
                                    result.Add(col, dr.GetString(dr.GetOrdinal(col)));
                                }
                                else
                                {
                                    result.Add(col, "");
                                }
                            }
                        }
                        rowCount = rowCount + 1;
                        var screwit = new Dictionary<string, string>(result);
                        result_set.Add(rowCount.ToString(), screwit);
                        result.Clear();
                    }
                    result.Add("count", rowCount.ToString());
                    result_set.Add("rows", result);
                }//end-using-rdr
            }//end-using-cmd
        }//end-using-con
    }
    catch (Exception ex)
    {
        result.Add("result", ex.ToString());
        result_set.Add("status", result);
        return result_set;
    }
    return result_set;
}

With this query setup:

  • I have row numbers appended to the result set
  • I have the total number of rows
  • If an error occurs I have a sanity check of "status" to let me know that it error'd out
  • I can iterate through the results to mold the data as a I see fit rather than dumping them into a preformed output format (this may be a bad idea on my part)
  • Parameters are bound regardless of how many parameters may have been passed
  • Null values are always handled
  • Varying data types are handled on result iteration (at the moment we only deal with the 3 types listed)

Things I'd love to know

  1. What flaws do you see in the approach?
  2. Is there a more flexible data return method than the strict Dictionary<string,Dictionary<string,string> that also yields the return results I currently list? I am a PHP developer that recently moved to C#.NET so I still work in the mindset of associative arrays, and manipulating them into HTML. I imagine Microsoft has a mechanism for nullifying the usefulness of my chosen return type.

If enough positive comments come in and proper direction I will be adding functionality for MSSQL as I re-use that same basic set of code at the top but specific to that database. I will also be implementing methods for basic CRUD operation. I just don't want to go down that rabbit hole without some constructive criticism and functional insight from the community.

\$\endgroup\$
  • 3
    \$\begingroup\$ Have you considered using an ORM, like Entity Framework? \$\endgroup\$ – svick Dec 11 '13 at 23:38
  • \$\begingroup\$ I have, but I am limited in time and knowledge unfortunately :). From what I've experienced ORM's aren't terribly efficient with the large data-sets and some of the obscenely deep queries we do against the database. I would love to know all the facets of Entity Framework, but for now it is faster for me to write the raw sql and feed it into a method for grabbing the data. The current oracle database is sitting at somewhere near 3000 tables that I regularly pull from, not including stored procedures. My limited knowledge of EF and ORM make me think it may not be a good fit yet. \$\endgroup\$ – Pynt Dec 12 '13 at 18:13
1
\$\begingroup\$

It's very easy to get started with EF, especially for CRUD operations, Plus, you always have the flexibility of executing native SQL queries to accomplish complicated tasks.

Entity Framework has gobs of hours of optimization effort put into it, it's really quite remarkable in that respect, so I would hesitate to discard the notion based on speculative performance issues.

Despite your time crunch, I would invest a few hours in following a tutorial before trying to write your own. I predict you'll save far more time than you'd lose. For example, EF would abstract for you the concern of handling both Oracle and MSSQL.

It's really the first implementation to try for this class of problem in .NET.

\$\endgroup\$
  • \$\begingroup\$ I am not discounting EF by any means. Would it be of any consequence that the Oracle will be Read only, while MSSQL will involve full CRUD when setting up EF? I guess I can simply ask the overlords if I can set aside a few hours a week to learn something that may save time in the long run. \$\endgroup\$ – Pynt Dec 12 '13 at 19:35
  • \$\begingroup\$ No, having a read-only database wouldn't change my answer. In my opinion, it's no more work than setting up ADO.NET. \$\endgroup\$ – neontapir Dec 12 '13 at 19:51
  • 1
    \$\begingroup\$ After much teeth gnashing I have entity framework enabled with the oracle database, using the oracle.net provider. I have managed to mangle together a few simple queries and some of the moderately difficult queries using the Foreign Key setup as well as some explicit joins declared outside of the DB Schema. This is definitely going to cut down on the coding once I learn the LINQ syntax for the insanely complicated queries. I've been meaning to try Entity Framework, but wasn't ready to make the jump. It was certainly a complicated setup, but it should work out great. \$\endgroup\$ – Pynt Dec 23 '13 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.