1
votes

I'm trying to use Dapper to insert a date range into my Postgres database, using code like this:

string INSERT_DATE_RANGE = @"INSERT INTO datetable (daterange) VALUES('[@dateRangeStart, @dateRangeBegin]')";

.... (standard connection stuff)

var startDate = DateTime.Now;
var endDate = DateTime.MaxValue;

connection.Execute(INSERT_DATE_RANGE, new { @dateRangeStart = startDate, @dateRangeBegin = endDate });

This gives me the following error:

22007: invalid input syntax for type date: "@dateRangeStart"

I suspect this is because the parameters I'm trying to replace are inside the single quotes. If I remove the quotes however, I get a different error message:

42601: syntax error at or near "["

Any suggestions? I could just concatenate the string but I would prefer not to use that approach as it opens things up to a possible SQL injection.

1
@mcNets Does Dapper provide a way to do this? From what I've read it's better to pass in parameters along with the SQL statement rather than use something like String.Format - quietkatalyst
Hmm, one quick idea: why not try to insert the range directly, i.e. provide a single NpgsqlRange<DateTime> parameter which would contain the start and end? Even if this works it's worth understanding why your example doesn't, though. - Shay Rojansky
@ShayRojansky I tried this out as well, and got this: The member effectiveDateRange of type NpgsqlTypes.NpgsqlRange`1[[NpgsqlTypes.NpgsqlDate, Npgsql, Version=3.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7]] cannot be used as a parameter value - quietkatalyst

1 Answers

3
votes

Use the constructor function for the daterange type, i.e.:

INSERT INTO datetable (daterange) VALUES (daterange(@dateRangeStart, @dateRangeBegin, '[]'))