code_junkie

Parameterized Queries (C#, Oracle): How to produce a more readable representation

Nov 13th, 2011
133
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.73 KB | None | 0 0
  1. INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate)
  2.  
  3. INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (23, ‘Mike’, TO_DATE('2003/07/09', 'yyyy/mm/dd')
  4.  
  5. INSERT INTO PERSON (ID, NAME, BIRTHDATE) VALUES (:id, :name, :birthdate) [:id=23, :name=Mike, birthdate=2004/07/09 00:00:00]
  6.  
  7. using (OracleConnection connection = new OracleConnection(connectionString))
  8. using (OracleCommand command = new OracleCommand(statement, connection))
  9. {
  10. command.Parameters.AddWithValue(":id", id);
  11. command.Parameters.AddWithValue(":name", name);
  12. command.Parameters.AddWithValue(":birthdate", birthdate);
  13. command.Connection.Open();
  14. log.DebugFormat("Executing statement: {0}.", command.CommandText);
  15. // there I would add some more code to iterate over
  16. // the parameters and print them out
  17. command.ExecuteNonQuery();
  18. command.Connection.Close();
  19. }
  20.  
  21. protected string GetCommandLogString(IDbCommand command)
  22. {
  23. string outputText;
  24.  
  25. if (command.Parameters.Count == 0)
  26. {
  27. outputText = command.CommandText;
  28. }
  29. else
  30. {
  31. StringBuilder output = new StringBuilder();
  32. output.Append(command.CommandText);
  33. output.Append("; ");
  34.  
  35. IDataParameter p;
  36. int count = command.Parameters.Count;
  37. for (int i = 0; i < count; i++)
  38. {
  39. p = (IDataParameter) command.Parameters[i];
  40. output.Append(string.Format("{0} = '{1}'", p.ParameterName, p.Value));
  41.  
  42. if (i + 1 < count)
  43. {
  44. output.Append(", ");
  45. }
  46. }
  47. outputText = output.ToString();
  48. }
  49. return outputText;
  50. }
  51.  
  52. INSERT INTO PERSON (ID, NAME, BIRTHDATE)
  53. VALUES ('"+id+"', '"+name+"', '"+birthdate+"')
Add Comment
Please, Sign In to add comment