Pass datetime parameter in Web API?-CodePudding

I want to ask how to pass datetime parameter in Web API, how to pass datetime parameter in optional date. I want to search in the URL can be optional date without time such as:

localhost:IP/api/values?date=2020-01-01

Expected results:

<ArrayOfTest xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<Test>
<UserId>341</UserId>
<Name>Emily</Name>
<Mobile>386754298</Mobile>
<Age>24</Age>
<Date>2021-11-06T16:04:00</Date>
</Test>
<Test>
<UserId>2555</UserId>
<Name>Peter</Name>
<Mobile>48295729</Mobile>
<Age>45</Age>
<Date>2020-10-12T20:35:00</Date>
</Test>

It can found out date after 2020-01-01. some value are null from SQL Server database, so I used dbnull to make sure the code works and I didn't use Entity Framework to connect the database, I have try to just pass datetime parameter in get method and it is not work as well. Is it possible to pass datatime parameter like this way?

Class code:

    public class TestClass
    {

        public string UserId { get; set; }

        public string Name { get; set; }

        public string Mobile { get; set; }

        public int Age { get; set; }

        public DateTime? Date { get; set; }
    }

controller code:

        public IHttpActionResult Get(DateTime date)
        {
            List<UserClass> Test = new List<UserClass>();
            string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            SqlConnection sqlconn = new SqlConnection(mainconn);
            string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date=" date ";
            sqlconn.Open();
            SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
            SqlDataReader reader = sqlcomm.ExecuteReader();
            while (reader.Read())
                {
                    Test.Add(new UserClass()
                    {
                        UserId = reader.GetValue.ToString(0),
                        Name = reader.GetValue.ToString(1),
                        Mobile = reader.GetValue.ToString(2),
                        Access = Convert.ToInt32(reader.GetValue(3)),
                        Date = (reader.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(reader.GetValue(4)) : (DateTime?)null
                    });
                }
            return Ok(Test);
        }

CodePudding user response：

When using string concatenation to build your query, it is recommended that you use ISO date format for dates:

In C# we can use the u format specifier to obtain an ISO date for SQL, but it appends the value with a Z, the following is a simple way to use this in strings:

date.ToString("u").TrimEnd('Z');

That is equivalent to:

date.ToString("yyyy-MM-dd HH:mm:ss");

But now we have a problem, does the incoming date parameter represent a whole day, or a specific point in time (for the purposes of this query)? Your request is that we want to specify the Date only, without time, for that we can use this format:

date.ToString("yyyy-MM-dd")

Now we have a new problem, in SQL if your data contains time values, then you need to truncate the data to just the date component, to pickup all the values for the provided date.

So now your query looks like this:

string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Cast([Date] as Date) = '" date.ToString("yyyy-MM-dd") "'";

Using a function in the where clause makes this a non-SARGABLE query, which is hard for the database to optimise, you will generally get better performance by usign a BETWEEN and passing in a value for the date and the date of the next day:

string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where [Date] BETWEEN '" date.ToString("yyyy-MM-dd") "' AND '" date.AddDays(1).ToString("yyyy-MM-dd") "'";

But now this query is a bit of a mess, instead we should use parameters to pass through the value, this way we do not need to be aware of ISO dates and formats, the ADO.Net runtime will deal with this for us.

Parameterizing queries offers other benefits, like SQL Injection prevention through input sanitization and other processing optimizations

Lets change the query to use parameters:

string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date BETWEEN @fromDate AND @toDate";

Then you need to pass the dates to the SqlCommand as a parameter,

notice here that we are using the .ToDate() method on the DateTime object to trim the time portion that the user might have provided.

SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.Add("@fromDate ", SqlDbType.Date);
sqlcomm.Parameters["@fromDate "].Value = date.Date;
sqlcomm.Parameters.Add("@toDate ", SqlDbType.Date);
sqlcomm.Parameters["@toDate "].Value = date.AddDays(1).Date;

This is a good post for comparing string concatenated SQL with parameterized queries: C#: SQL Injection Protection Using Parameterized Queries

UPDATE See this fiddle for a live demo: https://dotnetfiddle.net/8zLLrR

Because your controller is pre-sanitizing the input and constraining it to a DateTime typed value, SQL Injection is not likely to be an issue in this specific example, however the string-concatenation approach to build your query is generally a red flag in Web API as this is where your code is most vulnerable to external inputs.

SQL Server has an optimization where it tries to re-use execution plans when queries are re-executed. You will notice in SSMS when you run a query the first time, it is almost always slower than the second time if you re-execute the same query. That is because it has an internal table of stored execution plans and the SQL that you submit is the main key to this index.

When the date filter changes, if you use string concatenation this results in a different SQL so it is harder for the database engine to lookup an existing query execution plan, and in many cases it will generate a new one. When we use parameters, the actual SQL query is the same each time, so the optimizer can simply lookup any previously saved execution plans.

Later versions of SQL Server have additional optimisations to automatically parameterise query expressions for the execution plan index, but it's not 100% and not an excuse to be lazy developers ;)

CodePudding user response：

You should not add a string like this

SELECT UserID, Name, Mobile, Age, Date From tbluser where Date=" date "

This may cause SQL injection. Instead, follow this documentation using SQL parameters with type Date https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-6.0

Here is the example:

        string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date= @YourDate";
        SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
        sqlcomm.Parameters.Add("@YourDate", SqlDbType.Date);
        sqlcomm.Parameters["@YourDate"].Value = date;