如何在c＃中按日期范围从数据库中搜索记录 [英] How to search a record from database by date range in c#

问题描述

嗨
我在数据库中有一个名为date的列，我的数据库日期列包含2013-02-22，但在前端iam使用2个ajaxdate选择器并搜索记录
$ b $我正在写这样的查询，我想知道它是否正确

  SELECT  * 来自 表 其中  '   ;  

解决方案

第一个问题是日期是SQL中的保留字（和大多数其他数据库，所以你需要更改列名，或者将它括在方括号内。

第二个是你的引号不是匹配！



第三个是 txtodate 是（可能）一个文本框，所以除非你添加.Text对你来说，你将以这样的SQL语句结束：

  SELECT  * 来自 表 其中 [日期]   '  2013-01-01' 和 '  System.Windows.Forms.TextBox，Text：2013-02-18' 

但是，拜托，请那样做！

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

 使用（SqlConnection con = 新的 SqlConnection（strConnect））
 {
 con.Open（）; 
 使用（SqlCommand com =  new  SqlCommand（  从表中选择* * @SD和@ED之间的[日期] ，con））
 {
 com.Parameters .AddWithValue（  @ SD，txtfromDate.Text）; 
 com.Parameters.AddWithValue（  @ ED，txttodate.Text）; 
 使用（SqlDataReader reader = com.ExecuteReader（））
 {
  while （reader.Read（））
 {
  int  id =（ int ）reader [  iD]; 
  string  desc =（ string ）reader [   description]; 
 Console.WriteLine（  ID：{0} \ n {1}， iD，desc）; 
} 
} 
} 
} 
 

如果您使用内联查询，请尝试这个

  string  SQL =   SELECT * from table其中Date为' + txtfromdate.Text +  '和' + txttodate.Text +  '; 

Hi I have one column called date in database and my database datecolumn contain like this 2013-02-22,but in front end iam using 2 ajaxdate pickers and searching for a record
i am writing the query like this,i want to know whether it is correct or not

SELECT * from table where Date between '" + txtfromdate.Text +" "and" "+txttodate +"'";

解决方案

First problem is the Date is a reserved word in SQL (and most other databases, so you either need to change the column name, or enclose it is square brackets.
The second is that your quotes do not match up!

The third is that txtodate is (probably) a text box, so unless you add the ".Text" propery to it you will end with a an SQL statement like this:

SELECT * from table where [Date] between '2013-01-01' and 'System.Windows.Forms.TextBox, Text: 2013-02-18'

But, please, don''t do it like that!
Do not 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.

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("SELECT * from table where [Date] between @SD and @ED", con))
        {
        com.Parameters.AddWithValue("@SD", txtfromDate.Text);
        com.Parameters.AddWithValue("@ED", txttodate.Text);
        using (SqlDataReader reader = com.ExecuteReader())
            {
            while (reader.Read())
                {
                int id = (int) reader["iD"];
                string desc = (string) reader["description"];
                Console.WriteLine("ID: {0}\n    {1}", iD, desc);
                }
            }
        }
    }

if you are using inline queries then try this

string SQL = "SELECT * from table where Date between '" + txtfromdate.Text + "' and '" + txttodate.Text + "'";

这篇关于如何在c＃中按日期范围从数据库中搜索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！