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
- What flaws do you see in the approach?
- 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.
