/// <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); }