在SQL数据库中使用日期搜索 [英] Searching with date in SQL database

问题描述

我有一个c＃代码，用于在文本框中输入用户的输入。有2个测试框可以输入日期和来自。用户需要以dd / mm / YYYY格式输入日期并搜索在此期间发生的事件。但是我发现搜索不正确。我猜这是由于我的数据库中的时间字段的格式是由我在进行搜索的SQL表中的一些外部应用程序给我的。



我的代码是

I have one c# code that takes input from user in text box.There are 2 test boxes to enter date from and to.User needs to put date in the dd/mm/YYYY format and search for the events occurred during the time interval.But I found that the searching is not correct.I guess it is due to the format of the time field in my database which is given to me by some external application in SQL table from which I am doing the search.

My code is

using (SqlCommand cmd2 = new SqlCommand("SELECT " + "SELECT * FROM [FAULT_SUMMARY] where STRIGGERTIME between '" + textBox1.Text + "' AND '" + textBox2.Text + "' AND [STATION_NAME] like '%" + textBox3.Text + "%'"))

数据库中的字段为Var char，看起来像

The field in the database is Var char and look like

13/10/2016 12:25:06.749016

。请注意，字段STRIGGERTIME包含具有毫秒精度的实际日期和时间，并且需要在搜索后显示。



有人可以帮我解决问题。



我尝试了什么：



尝试在同一个Sql Command查询中将STRIGGERTIME从verchar转换为日期和时间格式。但是没有成功。

.Please note that the field STRIGGERTIME contains actually date and time with milisecond precision and it is required to show after the search.

Can someone help me to resolve the same.

What I have tried:

Tried converting the STRIGGERTIME from verchar to date and time format in the same Sql Command query. but did not succeed.

推荐答案

永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



使用DateTime.TryParse将用户输入转换为DateTime值，并报告任何错误：

Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Convert your user input into a DateTime value by using DateTime.TryParse, and report any error:

DateTime dtStart;
if (!DateTime.tryParse(textBox1.text, out dtStart))
   {
   ... report a problem to the user ...
   return;
   }

对结束值执行相同操作，然后将值作为参数传递给SQL：

Do the same with the end value, and then pass the values to SQL as parameters:

using (SqlCommand cmd = new SqlCommand("SELECT * FROM [FAULT_SUMMARY] WHERE STRIGGERTIME BETWEEN @ST AND @END AND [STATION_NAME] LIKE '%' + @SN + '%'"))
   {
   cmd.Parameters.AddWithValue("@ST", dtStart);
   cmd.Parameters.AddWithValue("@END", dtEnd);
   cmd.Parameters.AddWithValue("@SN", textBox3.text);
   ...

还有其他两件事：

1）停止使用所有大写字母列名：cammelCase更易读，就像C＃变量名一样。

2）帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得TextBox8 今天是手机号码，但是当你必须在三周内修改它时，你会这样吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键......

And two other things:
1) Stop using all capitals for column names: cammelCase is a lot more readable, just as it is for C# variable names.
2) Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

这篇关于在SQL数据库中使用日期搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！