How to pass convert expression in parameter using

问题:

I had an adhoc insert statement created in c# code using string builder.

var str = new StringBuilder(string.Empty);
str.Append(" INSERT INTO Table_01 ( ID,");
        str.Append("Comment,");
        str.Append("DateTimeStamp,");
str.Append(" ) VALUES( ");
        str.Append("'" + Guid.NewGuid() + "',");
        str.Append("'" + CaseComment + "',");
        str.Append("CONVERT(DATETIME, '" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "',120)");

I have converted this code to SqlParameter:-

var str = INSERT INTO Table_01(ID,Comment,DateTimeStamp)     
VALUES(@ID,@Comment,@DateTimeStamp)
using (var cmd = new SqlCommand(str, con))
{
cmd.Parameters.Add(new SqlParameter("@ID", Guid.NewGuid()));
cmd.Parameters.Add(new SqlParameter("@Comment", CaseComment));
cmd.Parameters.Add(new SqlParameter("@DateTimeStamp", ?????));
cmd.ExecuteNonQuery();
}

Before passing DateTime, I have to make sure that format is equal to "convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)" as in TSQL. How will i pass @datetime parameter using TSQL Convert expression?

回答1:

You can use Convert class. Try something like this,

cmd.Parameters.Add(new SqlParameter("@DateTimeStamp", Convert.ToDateTime(DateTime.Now)));

or

cmdItemSearch.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime)); 
cmdItemSearch.Parameters["@EndDate"].Value = DateTime.Now;

回答2:

Why not using String Format for DateTime ?

回答3:

Assuming that the database column is actually a DateTime and not varchar you don't need to convert on insert if using a parameter. Databases don't save dates as strings, but as offsets, and the only reason you had to convert it when using your inline SQL was because you had to make sure the DB recognized the string as a DateTime so it could internally convert it back correctly. This is avoided using parameters.

(In other words, just insert DateTime.Now, don't rip it apart into a string)

回答4:

Honestly, whenever I need to insert the current date/time stamp to a table, I like to use the server date as opposed to the client date. If the app is running on the same box as SQL server then it doesn't matter, but if the app is running on a different box then it's better to have consistent dates from the same timezone. So I would remove the datetime parameter altogether and use the SQL date function GetDate() as follow:

var str = INSERT INTO Table_01(ID,Comment,DateTimeStamp)     
VALUES(@ID,@Comment,GetDate())
using (var cmd = new SqlCommand(str, con))
{
cmd.Parameters.Add(new SqlParameter("@ID", Guid.NewGuid()));
cmd.Parameters.Add(new SqlParameter("@Comment", CaseComment));
cmd.ExecuteNonQuery();
}