C# FAQ: How express dates and times in SQL statements

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

How express dates and times in SQL statements?

The best answer answer is not to put escaped data into SQL statements in the first place: Use parameters instead. Virtually, all database providers provide a method of specifying parameters in SQL statements.

The parameters may be either named—e.g. SELECT NAME FROM PEOPLE WHERE ID=@ID—or positional—e.g. SELECT NAME FROM PEOPLE WHERE ID=?. Whereas some providers support positional parameters only, others support named parameters only, and, still others support both.

Parameters are a mechanism for creating a SQL statement which can accept variable values. Use a SqlCommand—or the equivalent for the database in use—to specify the query text, the command type—stored procedure, text, etc.—and the parameters themselves. Create the command once with parameters; and, from then on, reuse the command by setting the desired parameter values for each request. When using a DataAdapter to update the database, parameter values for the relevant command—delete, insert, or update—are filled in automatically.

Parameters enable writing SQL without having to escape values or format dates, times, etc. And, SQL injection attacks are not a concern; because, parameter values are not be used as SQL itself. Further, parameters are simpler for the database software to handle. For example, the database can cache the query for faster execution with the actual parameter values provided.

For more information on parameter usage, consult the documentation for the database in use.

Visual C# Best Practices

• Parameters should be used whenever values may change with each request.

• Whether named or positional, use parameters in a consistent manner. Always use sensible names. Always specify parameters in the same order in which they appear in SQL statements.