4.Coach汽车信息表： 5.Purchase车票购买表： 6.Line线路表 ： （二）代码 登录操作代码： （1）SqlHelper helper = new SqlHelper(); SqlDataReader rdr; （2）if (string.IsNullOrEmpty(name) || string.IsNullOrEmpty(password) || string.IsNullOrEmpty(identity)) { MessageBox.Show(\姓名,密码,身份框均不能为空！\); } rdr = helper.ExecuteReader(\, CommandType.StoredProcedure); while (rdr.Read()) { if (rdr[\].ToString() == name && rdr[\].ToString() == password && rdr[\].ToString() == identity) { this.Hide(); string iden = rdr[\].ToString(); MainForm main = new MainForm(iden); main.ShowDialog();
11
rdr.Close(); return; } } MessageBox.Show(\错误的姓名或密码或与身份不匹配，请重新输入！\); rdr.Close(); （3）存储过程： ALTER PROCEDURE dbo.SelectUserinfo /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ BEGIN select * from Userinfo END 修改/删除操作代码： （1） try { SqlCommandBuilder scb = new SqlCommandBuilder(sd); sd.Update(ds, \); ds.AcceptChanges(); MessageBox.Show(\修改成功！\); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } （2） try { SqlCommandBuilder scb = new SqlCommandBuilder(sd); int a = dataGridView1.CurrentRow.Index; ds.Tables[0].Rows[a].Delete(); sd.Update(ds, \); ds.AcceptChanges(); MessageBox.Show(\删除成功！\); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
12
添加操作代码： （1）DAL.CoachDAO coach = new Coach.DAL.CoachDAO(); SqlHelper helper = new SqlHelper(); (2) if (string.IsNullOrEmpty(coachnumber) || string.IsNullOrEmpty(seatnumber1) || string.IsNullOrEmpty(coachholder)) { MessageBox.Show(\车牌号，座位数量，负责人均不能为空！\); } else { if (coach.Add(coachtype,coachnumber,date2,seatnumber,ishavetv,ishavecon,coachholder)) MessageBox.Show(\添加成功！\); else MessageBox.Show(\添加失败！\); } （3）实体类CoachDAO.cs中： public bool Add(string coachtype,string coachnumber,DateTime date2, int seatnumber,string ishavetv,string ishavecon,string coachholder) { bool flag = false; DataTable dt = new DataTable(); string cmdText = \; SqlParameter[] paras = new SqlParameter[]{ new SqlParameter(\,coachtype), new SqlParameter(\,coachnumber), new SqlParameter(\,date2), new SqlParameter(\,seatnumber), new SqlParameter(\,ishavetv), new SqlParameter(\,ishavecon), new SqlParameter(\,coachholder) }; int res = helper.ExecuteNonQuery(cmdText, paras, CommandType.StoredProcedure); if (res > 0) { flag = true; } return flag; } (4)存储过程： ALTER PROCEDURE dbo.AddCoach @coachtype nvarchar(20), @coachnumber nvarchar(20), @date2 datetime, @seatnumber int,
13
@ishavetv char(10), @ishavecon char(10), @coachholder nvarchar(20) AS /* SET NOCOUNT ON */ BEGIN /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ insert into Coach (Ctype,Coachnumber,Ctime,Cnum,Ctv,Ccondition,Coachholder) values (@coachtype,@coachnumber,@date2,@seatnumber,@ishavetv,@ishavecon,@coachholder) END 搜索操作代码： （1） SqlHelper helper = new SqlHelper(); SqlConnection conn = new SqlConnection(\Security=True\); SqlDataAdapter sd; //定义一个数据适配器 DataSet ds; //定义一个数据集 （2） string str1 = \编号,Ldepart as 起始城市,Larrival as 到达城市,convert(varchar(10),Ltime,108) as 路程时间 from Line where Lno =\ + lineid + \; dataBind(str1); （3） private void dataBind(string str) { try { sd = new SqlDataAdapter(str, conn); ds = new DataSet(); sd.Fill(ds, \); dataGridView1.DataSource = ds; dataGridView1.DataMember = \; } catch (Exception ex) { MessageBox.Show(ex.Message); } } 查询操作代码： （1） DAL.PurchaseDAO purchase = new Coach.DAL.PurchaseDAO(); SqlHelper helper = new SqlHelper(); （2） dataGridView1.DataSource = purchase.Select1(departcity,arrivalcity,date2);
14
（3） 实体类PurchaseDAO.cs中： public DataTable Select1(string departcity, string arrivalcity, DateTime date2) { DataTable dt = new DataTable(); string cmdText = \; SqlParameter[] paras = new SqlParameter[]{ new SqlParameter(\,departcity), new SqlParameter(\,arrivalcity), new SqlParameter(\,date2) }; dt = helper.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure); return dt; } （4）存储过程 ALTER PROCEDURE dbo.SelectPurchase @departcity nvarchar(20), @arrivalcity nvarchar(20), @date2 datetime /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ AS /* SET NOCOUNT ON */ BEGIN select Ldepart as 起始城市,Larrival as 到达城市,convert(varchar(10),Tdate,120) as 出发日期,convert(varchar(10),Ttime,108) as 出发时间,Ctype as 车型,Coachnumber as 车牌号,Ctv as 有无电视,Ccondition as 有无空调,Price as 票价,Cnum as 总票数,LastTicket as 剩余票数from Coach,Line,Ticket where Coach.Cno = Ticket.Cno and Line.Lno = Ticket.Lno and Ldepart = @departcity and Larrival = @arrivalcity and Tdate = @date2 END 预订/购买操作代码： （1）tno = purchase.Yuding(depart, arrival, coachnum); （2）实体类PurchaseDAO.cs中 public int Yuding(string depart, string arrival, string coachnum) { int tno; DataTable dt = new DataTable(); string cmdText = \; SqlParameter[] paras = new SqlParameter[]{ new SqlParameter(\,depart),
15