Insert date-time from c# in sql server without parametrized query's

Question

I am working on a quite old application in which there were no parametrized query's that were used at that time.

I have to insert date time value in an column of sql table with date-time as data type, null value is not allowed in this column.

My code.

var expires = dtpExpires.Enabled ? dtpExpires.Value.ToString() : "'1/1/1900 12:00:00 AM'";
string query = "INSERT INTO route (expires) Values ("+ expires +")";

The problem with this is, When the date picker is disabled then a default value must be passed since null are not allowed. So for that I have to include an extra '' to wrap around the date and it works correctly.

But when date picker is enabled and valid date time is trying to get inserted into database it fails due to lack of '' this wrapped around the expires variable.

Is there any clean approach to do this without parametrized query. the same problem will come while updating the code. Can there be clean approach for this to work on both the cases rather than adding just if-else clause .

ON a sidenote, what is the reason of not wanting to use parameterized queries ? — Frederik Gheysels, Nov 27 '12 at 06:07
@FrederikGheysels this is an old application for the sake of uniformity can't use parameterized queries in only one place. Either we completely migrate the whole application to it or carry this thing as it is:(. — ankur, Nov 27 '12 at 06:10

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

EDIT

To avoid "Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DBNull'"

SqlCommand command = new SqlCommand("INSERT INTO route (expires) 
                                   Values (@dtpExpires)", connections);
SqlParameter dtpExpires= new SqlParameter("@dtpExpires", SqlDbType.DateTime, 10);
dtpExpires.Value = dtpExpires.Enabled ? dtpExpires.Value : DBNull.Value;
command.Parameters.Add(dtpExpires);

For you info OP@ankur

Benefits of use parameters instead of concatenation

Safety. Concatenation opens you up to SQL-injection, especially when TB stands for Textbox. (Obligatory XKCD cartoon)
Type safety. You solve a lot of DateTime and number formatting issues.
Speed. The query does not change all the time, the system(s) may be able to re-use a query handle.

Note

It's better you make use of pram query to avoid sql Injection attack.

edited May 23 '17 at 11:43

Community

1
1

answered Nov 27 '12 at 05:59

Pranay Rana

175,020
35
237
263

1

it gives an compile time error "Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DBNull'" – ankur Nov 27 '12 at 06:04
@ankur - so for this thing better you use parameter query ...which support DBNull.Value – Pranay Rana Nov 27 '12 at 06:09
1

this line `dtpExpires.Enabled ? dtpExpires.Value.ToString() : DBNull.Value` shouldn't even compile. Returning two different types from condition operator is a compile time error – Habib Nov 27 '12 at 06:13
@PranayRana Dude read the question correctly no scope for parameterized queries. btw what is the caseID you added in command.Parameters.AddWithValue("@dtpExpires", caseid); LOL:) – ankur Nov 27 '12 at 06:18
@Habib - its having one solution that make use of param query..with DBNull.Value resolve issue....otherwise in plan query i think its difficult to do it – Pranay Rana Nov 27 '12 at 06:19
@ankur - i updated befor you8r commpen just have look to it...I think as programmer you can just remove that thing because i m not writing code in VS studio...lol.. – Pranay Rana Nov 27 '12 at 06:20
1

@PranayRana, I believe your edit `dtpExpires.Enabled ? dtpExpires.Value : DBNull.Value` would cause the same error. You may remove that from your answer, or you may pass Date as string and `NULL` instead of DBNull.Value – Habib Nov 27 '12 at 06:24
@PranayRana You don't correctly read the question and description do you. – ankur Nov 27 '12 at 06:34
@ankur - i read that but i dont think the way you want is possible ...so i come up with alternet solution buddy.. – Pranay Rana Nov 27 '12 at 06:35

score 0 · Accepted Answer · answered Nov 27 '12 at 08:04

since you send both datetime and null data as string, let the convertion from string to datetime handle by the sql server by using CONVERT function

  var expires = dtpExpires.Enabled ? "'" + tpExpires.Value.ToString() + "'" : "null";

  string query = "INSERT INTO route (expires) Values (CONVERT(datetime, " + expires + "))";

Insert date-time from c# in sql server without parametrized query's

2 Answers2