SqlDateTime溢出问题

#region IBus_Orders 成员
        /// <summary>
        /// 插入订单和客户信息
        /// </summary>
        /// <param name="order">订单实体</param>
        /// <param name="customer">客户实体</param>
        /// <returns></returns>
        public bool InsertOrderAndCustomer(AirBtc.Entity.BusTic.Bus_Orders order, AirBtc.Entity.BusTic.Bus_P_assenger customer)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.SQLConString);
            if (ConnectionState.Open != conn.State)
            {
                conn.Open();
            }
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand com = new SqlCommand();
            try
            {
                //字段封装
                string insertOrder = "declare @count int;declare @order varchar(8);declare @zero int;declare @temp varchar(8);declare @num int;select @count=count(*) from dbo.Bus_Orders;set @order='8'+convert(varchar(2),@count+1);set @zero=8-len(@order);if(@zero>0)begin  set @num=0; set @temp=''; while @num<@zero  begin set @temp=@temp+'0'; set @num=@num+1;end set @order='8'+@temp+convert(varchar(2),@count+1) end;insert into Bus_Orders(BusOrderNo, CreateTime, DepCity, ArrCity, WaitName, BusDate, DepTime, ArrTime, BuyNum, BusPrice, PeopleNum, IsBuyInstrance, InstrancePrice, InstranceNum, InstranceTotalPrice, BusTotalPrice, BusOrderStatus, PayType, PayStatus, PayTime, IsLock, LockLoginID, OrderNo, Remark)values(@order,@CreateTime,@DepCity,@ArrCity,@WaitName,@BusDate,@DepTime,@ArrTime,@BuyNum,@BusPrice,@PeopleNum,@IsBuyInstrance,@InstrancePrice,@InstranceNum,@InstranceTotalPrice,@BusTotalPrice,@BusOrderStatus,@PayType,@PayStatus,@PayTime,@IsLock,@LockLoginID,@OrderNo,@Remark);";

                com.Connection = conn;
                com.Transaction = trans;
                com.CommandText = insertOrder;
                com.Parameters.Add("@CreateTime", SqlDbType.DateTime).Value=order.CreateTime;
                com.Parameters.Add("@DepCity", SqlDbType.VarChar).Value = order.DepCity;
                com.Parameters.Add("@ArrCity", SqlDbType.VarChar).Value = order.ArrCity;
                com.Parameters.Add("@WaitName", SqlDbType.VarChar).Value = order.WaitName;
                com.Parameters.Add("@BusDate", SqlDbType.VarChar).Value = order.BusDate;
                com.Parameters.Add("@DepTime", SqlDbType.VarChar).Value = order.DepTime;
                com.Parameters.Add("@ArrTime", SqlDbType.VarChar).Value = order.ArrTime;
                com.Parameters.Add("@BuyNum", SqlDbType.Int).Value = order.BuyNum;
                com.Parameters.Add("@BusPrice", SqlDbType.Decimal).Value = order.BusPrice;
                com.Parameters.Add("@PeopleNum", SqlDbType.Int).Value = order.PeopleNum;
                com.Parameters.Add("@IsBuyInstrance", SqlDbType.Int).Value = order.IsBuyInstrance;
                com.Parameters.Add("@InstrancePrice", SqlDbType.Decimal).Value = order.InstrancePrice;
                com.Parameters.Add("@InstranceNum", SqlDbType.Int).Value = order.InstranceNum;
                com.Parameters.Add("@InstranceTotalPrice", SqlDbType.Decimal).Value = order.InstranceTotalPrice;
                com.Parameters.Add("@BusTotalPrice", SqlDbType.Decimal).Value = order.BusTotalPrice;
                com.Parameters.Add("@BusOrderStatus", SqlDbType.Int).Value = order.BusOrderStatus;
                com.Parameters.Add("@PayType", SqlDbType.Int).Value = order.PayType;
                com.Parameters.Add("@PayStatus", SqlDbType.Int).Value = order.PayStatus;
                com.Parameters.Add("@PayTime", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
                com.Parameters.Add("@IsLock", SqlDbType.Int).Value = order.IsLock;
                com.Parameters.Add("@LockLoginID", SqlDbType.Int).Value = order.LockLoginID;
                com.Parameters.Add("@OrderNo", SqlDbType.VarChar).Value = order.OrderNo;
                com.Parameters.Add("@Remark", SqlDbType.VarChar).Value = DBNull.Value;

                object result = com.ExecuteNonQuery();
                if (Convert.ToInt32(result)<1)
                {
                    trans.Rollback();
                    return false;   
                }

                com.CommandText = "select max(convert(int,BusOrderNo)) from Bus_Orders";
                SqlDataReader reader = com.ExecuteReader();
                while (reader.Read())
                {
                    customer.BusOrderNo = reader[0].ToString();
                }
                reader.Close();

                
                string insertCustomer = string.Format("insert into Bus_Passenger(PassName, PassPhone, TelPhone, Birthday, [Certificate], CertNo, Remark, InstrancePrice, InstranceNo, BusOrderNo, PassStatus)values('{0}','{1}','{2}','{3}',{4},'{5}','{6}',{7},'{8}','{9}','{10}')", customer.PassName, customer.PassPhone, customer.TelPhone, customer.Birthday, customer.Certificate, customer.CertNo, customer.Remark, customer.InstrancePrice, customer.InstranceNo, customer.BusOrderNo, customer.PassStatus);

                com.CommandText = insertCustomer;
                object result2 = com.ExecuteNonQuery();
                if (Convert.ToInt32(result2)<1)
                {
                    trans.Rollback();
                    return false;
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                return false;
            }
            trans.Commit();
            return true;
        }

        #endregion

做上面这段插入，用到一个事务，所以用不到封装的sqlHelper,所有的只能用最原始的做法了。

之前sql语句的拼接是用string.Format()，遇到的问题“从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界.”,原因是自己的sql语句Convert成datetime类型的时候出错，即字符串不符合sql的转换规定，字符串长度大于时间格式的长度。

百度后使用参数化拼接。参数化拼接也引发“SqlDateTime 溢出。必须介于 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间”，这里的参数CreateTime为NULL，这里的NULL指的是程序代码里面的null，大多数出现这种情况的情景是：在程序里面定义了一个时间类型的变量，没有给赋值，就传给数据库（或存储过程了）。这时这个变量的值默认是赋成了01年01月01日。由于在数据库中DateTime类型字段，最小值1/1/1753 12:00:00，而.NET Framework中，DateTime类型，最小值为1/1/0001 0:00:00，显然，超出了Sql的值的最小范围，导致数据溢出的错误。

C#的NULL和sqlserver的NULL是不同类型所以DBNull.Value