# Creating a reusable C#.NET Oracle Query Builder

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
{
{
//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;
}
{
{
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":
break;
case "date":
break;
case "int":
break;
default:
break;
}//end-split
}
else
{
}
}
else
{
if (!dr.IsDBNull(dr.GetOrdinal(col)))
{
}
else
{
}
}
}
rowCount = rowCount + 1;
var screwit = new Dictionary<string, string>(result);
result.Clear();
}
}//end-using-rdr
}//end-using-cmd
}//end-using-con
}
catch (Exception ex)
{
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.

• Have you considered using an ORM, like Entity Framework? – svick Dec 11 '13 at 23:38
• 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. – Pynt Dec 12 '13 at 18:13