c# - .net c# sql 字符串到日期转换错误

Question

我在 visual studio 中使用 SQL 时遇到数据库转换错误。

我使用的数据库是普通的sql server数据库。这是我的任务。

这是我的查询方法是我的网络服务

 [WebMethod]
    public bool search(string ddate, string dairport, string aairport, string seat)
    {
        int seat2 = Convert.ToInt32(seat);
        DateTime date = Convert.ToDateTime(ddate);

        String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = '24/09/2011'";

        using (SqlConnection connect = new SqlConnection(conn))
        {
            SqlCommand cmd = new SqlCommand(query1, connect);
            connect.Open();
            SqlDataReader result = cmd.ExecuteReader();
            try
            {
                if (result.Read())
                {
                    return true;
                }

            finally
            {
                result.Close();
                connect.Close();
                connect.Dispose();
            }
            return false;
        }

    }

正常查询没有问题，例如:

"SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + dairport + "'";

错误:

System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.HasMoreRows()
   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
   at System.Data.SqlClient.SqlDataReader.Read()
   at AirportService.AirportServices.search(String ddate, String dairport, String aairport, String seat) in C:\Users\XXXXX\Documents\Visual Studio 2010\Projects\WebService2\AirportService\AirportServices.asmx.cs:line 47

Answer 1

您应该在 C# 中处理日期时间解析逻辑(以您接受的格式)，并将其作为参数向下传递，即

String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate"

并添加一个 SqlParameter 和你想要的 DateTime 值；那样...没问题。无需在数据库中进行解析，也没有注入(inject)风险。查询计划也可以重用。全面获胜。

例如:

DateTime when = DateTime.Parse(ddate); // better to use ParseExact and formally state the format you are using
const string query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate";

using (SqlConnection connect = new SqlConnection(conn))
{
    using (SqlCommand cmd = new SqlCommand(query1, connect))
    {
        cmd.Parameters.AddWithValue("departureDate", when);
        connect.Open();
        using (SqlDataReader result = cmd.ExecuteReader())
        {
           ... etc
        }
    }
}