/// <summary>
/// Prepare a query for execution by replacing parameter names with their binding value
/// </summary>
/// <param name="query"></param>
/// <param name="bindings"></param>
/// <returns></returns>
public IDbCommand PrepareCommand(string query, BindingsDict bindings)
{
// Create the command object for our query
IDbCommand cmd = Connection.CreateCommand();
cmd.Connection = Connection;
cmd.CommandText = query;
// Bind all parameters to the query (if any)
int i = 0;
foreach (KeyValuePair <BindingType, IList <object> > kv in bindings)
{
foreach (object value in kv.Value)
{
IDbDataParameter param = cmd.CreateParameter();
param.DbType = value.GetSqlValueType();
param.Value = value ?? DBNull.Value;
param.ParameterName = DB.BindingNamePrefix + i.ToString(); // Parameter prefix name is constant
cmd.Parameters.Add(param);
++i;
}
}
return(cmd);
}
/// <summary>
/// Execute a select statement and return the results
/// @TODO: Clean up console writelines
/// </summary>
/// <param name="query"></param>
/// <param name="bindings"></param>
/// <returns></returns>
public ResultSet Select(string query, BindingsDict bindings)
{
IDbCommand cmd = PrepareCommand(query, bindings);
ResultSet results = new ResultSet();
try
{
if (_useTimer)
{
_execTimer.Start();
}
IDataReader dataReader = cmd.ExecuteReader();
Console.WriteLine("\nReading dataresult\n----------------------------");
while (dataReader.Read())
{
Row row = new Row();
for (int i = 0; i < dataReader.FieldCount; i++)
{
// Add this column to the current result row
Column newCol = new Column(dataReader.GetName(i), dataReader[i], dataReader[i].GetSqlValueType());
row.Add(newCol);
Console.WriteLine("[{0}]: '{1}'", dataReader.GetName(i), dataReader[i].ToString());
}
// Add the row to our final result set
results.Add(row);
}
#region Debug - Write out command text
// Print out all the parameters we added to the Command object
Console.WriteLine("\nUsing:\n[CommandText]: '{0}'", cmd.CommandText);
foreach (IDbDataParameter param in cmd.Parameters)
{
Console.WriteLine("[" + param.DbType.ToString() + "] Name: '" + param.ParameterName + "', Value: " + param.Value.ToString());
}
Console.WriteLine("-");
#endregion
dataReader.Close();
if (_useTimer)
{
_execTimer.Stop();
}
}
catch (Exception ex)
{
if (_useTimer)
{
_execTimer.Stop();
}
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
throw new DBException("Failed to execute reader on Select query!", ex);
}
return(results);
}
