Proper Way To Use .Net DateTime in Parameterized SQL String

Question

There are several questions about using .Net DateTime in Sql statements but none of them solved my problem.

I'm using the following code to query an Oracle database :

private DataTable QueryByIdAndDate(string id, DateTime fdate, DateTime tdate) {
    string query = "SELECT * FROM table WHERE ID = :id AND DATE_TIME BETWEEN :from AND :to"
    DbCommand cmd = db.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = query;

    DbParameter fromDate = CreateDateParameter(cmd, fdate);
    fromDate.ParameterName = "from";
    DbParameter toDate = CreateDateParameter(cmd, tdate);
    toDate.ParameterName = "to"; 
    DbParameter idParam = CreateStringParameter(cmd, id);
    idParam.ParameterName = "id";

    cmd.Parameters.AddRange(new DbParameter[] { fromDate, toDate, idParam });
    return db.ExecuteQuery(cmd);
}

private DbParameter CreateDateParameter(DbCommand cmd, DateTime date) {
    DbParameter param = cmd.CreateParameter();
    param.DbType = DbType.DateTime;
    param.Direction = ParameterDirection.Input;
    param.Value = date;
    return param;
}

But it does not work properly. When trying the code out like so:

DataTable result = QueryByIdAndDate("12345", DateTime.Now, DateTime.Now.AddDays(1));

It gives the following error: ORA-01847: day of month must be between 1 and last day of month

I'm assuming it has to do with the way the DateTime is formatted but I don't know the proper way to fix this in a reliable way.

Answer 1

(As per comments...)

It looks like in this case, the order of the parameters matters... despite the fact that you've given them names. I wouldn't have expected this, and it's the sign of a somewhat broken driver, but changing your code to:

cmd.Parameters.AddRange(new DbParameter[] { idParam, fromDate, toDate });

should fix it. (This isn't necessarily the way you should be constructing your parameters, by the way, but that's somewhat immaterial here.)

Do not start specifying the date/time values as strings. It's a really bad idea to introduce any more string conversions than you need.