从数据库中获取字符串数据，并在查询中将其转换为日期对象

本文关键字：转换 日期 对象 查询 获取 数据库 字符串 数据 | 更新日期: 2023-09-27 18:19:15

我正在开发一个ATM软件，我想通过输入开始日期和结束日期来获得报告。在我的表中保存的日期是字符串dd/MM/yyyy的形式。我正在尝试下面的代码，并得到语法错误的异常。

public DataTable getReportByDate(DateTime startDate, DateTime endDate)
{
   try
   {
      DataTable table = new DataTable();
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
         SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from [Transaction] Where CAST(CurrDate AS Date) >=" + startDate + " AND CAST(CurrDate AS Date) <=" + endDate + ";", connectionString);
         // Create a command builder to generate SQL update, insert, and
         // delete commands based on selectCommand. These are used to
         // update the database.
         SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
         // Populate a new data table and bind it to the BindingSource.
         table.Locale = System.Globalization.CultureInfo.InvariantCulture;
         dataAdapter.Fill(table);
      }
      return table;
   }
   catch (Exception e)
   {
       return null;
   }
}

请帮帮我。

对

从数据库中获取字符串数据，并在查询中将其转换为日期对象

好的，首先，不要将异常转换为返回null

catch(Exception e)
{
   return null;
}

这是不好的做法，因为你吸收每一个可能的例外。相反，您应该只捕获sql适配器应该抛出的异常，或者更好:不捕获它们，而是记录它们并进一步向外捕获它们，因为如果在此方法中出现问题，则意味着您的sql连接或代码损坏。如果你让它保持原样，你只会隐藏问题，使调试更加困难。

第二，你应该在你的查询中使用参数。

现在是语法错误:startDate和endDate是DateTime类型，所以你应该先用.ToString("dd/MM/yyyy")将它们转换为字符串-这将减少参数的麻烦。

变化

SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from [Transaction] Where CAST(CurrDate AS Date) >=" + startDate + " AND CAST(CurrDate AS Date) <=" + endDate + ";", connectionString);


SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from [Transaction] Where CAST(CurrDate AS Date) >='" + startDate.ToString("yyyy-MM-dd HH:mm:ss") + "' AND CAST(CurrDate AS Date) <='" + endDate.ToString("yyyy-MM-dd HH:mm:ss") + "';", connectionString);

更新:

SqlDataAdapter dataAdapter = new SqlDataAdapter("Select * from [Transaction] Where CAST(CurrDate AS Date) >='" + startDate.ToString("dd/MM/yyyy") + "' AND CAST(CurrDate AS Date) <='" + endDate.ToString("dd/MM/yyyy") + "';", connectionString);

一定要在查询中使用参数——既可以避免SQL注入攻击，也可以提高性能(通过重用执行计划)。到目前为止还没有人展示过它，所以这里是:

public DataTable getReportByDate(DateTime startDate, DateTime endDate)
{
    DataTable table = new DataTable();
    string sqlStmt =  
        "SELECT * FROM [dbo].[Transaction] " + 
        "WHERE CAST(CurrDate AS DATE) >= @startDate " + 
        "AND CAST(CurrDate AS DATE) <= @endDate";
      using (SqlConnection connection = new SqlConnection(connectionString))
      using (SqlCommand cmd = new SqlCommand(sqlStmt, connection))
      {
         cmd.Parameters.Add("@startDate", SqlDbType.Date).Value = startDate.Date;
         cmd.Parameters.Add("@endDate", SqlDbType.Date).Value = endDate.Date;
         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
         adapter.Fill(table);
      }
      return table;
   }
}

我确实尝试过，sql在查询编辑器中正常工作，但是，它似乎只能在参数化时工作。所以我重新发布了代码，我注意到一个版主把我最初的回复变成了评论。

public DataTable getReportByDate(DateTime startDate, DateTime endDate)
{
DataTable table = new DataTable();
            string query = "select * from [transaction] where cast(currdate as date) >= @startdate and cast(currdate as date) <= @enddate";
            using (SqlConnection connection = new SqlConnection("server=(local);database=quicksilver;integrated security=true"))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(query);
                command.Parameters.AddWithValue("@startdate", startdate);
                command.Parameters.AddWithValue("@enddate", enddate);
                command.Connection = connection;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                //
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
                dataAdapter.Fill(table); 
            }
return table;
}
相关文章：