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
– Frederik Gheysels, Commented Nov 27, 2012 at 6: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
– ankur, Commented Nov 27, 2012 at 6:10

Community · Accepted Answer · 2017-05-23 11:43:11Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered Nov 27, 2012 at 5:59

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

ankur Over a year ago

it gives an compile time error "Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DBNull'"

Pranay Rana Over a year ago

@ankur - so for this thing better you use parameter query ...which support DBNull.Value

Habib Over a year ago

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

ankur Over a year ago

@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:)

Habib Over a year ago

@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

|

Damith · Accepted Answer · 2012-11-27 08:04:32Z

0

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 + "))";

answered Nov 27, 2012 at 8:04

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related