.net c#sql string到date转换错误

我在Visual Studio中使用SQL时数据库存在convserion错误.我使用的数据库是一个普通的sql server数据库.这是我的任务.这是我的查询方法是我的webservice[WebMethod]public bool search(string ddate, string dairpo...

我在Visual Studio中使用SQL时数据库存在convserion错误.

我使用的数据库是一个普通的sql server数据库.
这是我的任务.

这是我的查询方法是我的webservice

 [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

解决方法:

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

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

并添加一个SqlParameter与您想要的DateTime值;那样……没问题.没有解析数据库,没有注入风险.并且查询计划也重复使用.赢得全面.

例如：

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
        }
    }
}