#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