//导入操作 //读取excel文件 public void ReadExcel(string path) { //流 using (FileStream fsRead=new FileStream(path,FileMode.Open,FileAccess.Read)) { List<MemberInfo> list = new List<MemberInfo>(); //获取工作表 IWorkbook wookbook=WorkbookFactory.Create(fsRead); //获取页 ISheet sheet = wookbook.GetSheetAt(0); //遍历该页的所有行 for (int i = 0; i <= sheet.LastRowNum; i++) { MemberInfo member = new MemberInfo(); //获取行 IRow row=sheet.GetRow(i); //第一行是id =======0 member.MemName = row.GetCell(1).StringCellValue;//名字 member.MemMobilePhone = row.GetCell(2).StringCellValue;//电话 member.DelFlag = Convert.ToInt32(row.GetCell(3).NumericCellValue);//删除标识 注意转换。把表格数据看成string(实际有string和numeric),不是string的member属性全部要转换 list.Add(member); //把Excel文件的数据插入到数据库中 }//end for //循环结束---list集合中有很多会员对象了---传到DAL中 memDal.AddMemberInfo(list); } //end using }
//单例模式代码 public static FrmMemberAddOrModify Single(int temp, MemberInfo member) { if (instance == null || instance.IsDisposed) { instance = new FrmMemberAddOrModify(member); Temp = temp;//标识存起来 } return instance; }
/// <summary> /// 新增或修改的方法 /// </summary> /// <param name="member">会员的对象</param> /// <param name="temp">标识:1---新增:2----修改</param> /// <returns>成功还是失败</returns> public bool AddOrUpdateMemberInfo(MemberInfo member, int temp) { if (temp == 1)//新增 { return memDal.AddMemberInfo(member) > 0; } else if (temp == 2)//修改 { return memDal.UpdateMemberInfo(member) > 0; } return false; }
//窗体构造函数--初始化文本框中的值---下拉框--时间控件 private FrmMemberAddOrModify(MemberInfo member) { InitializeComponent();//设计窗体 LoadMemberTypeByDelFlag(0);//绑定下拉框中的值 //通过会员对象为文本框赋值 if (member != null)//修改 { txtAddress.Text = member.MemAddress;//地址 txtBirstday.Value = member.MemBirthday;//生日 txtDiscount.Text = member.MemDiscount.ToString();//折扣 txtEndTime.Value = member.MemEndTime;//有效期 txtIntegral.Text = member.MemIntegral.ToString();//积分 txtMoney.Text = member.MemMoney.ToString();//金额 txtName.Text = member.MemName; txtNum.Text = member.MemNum;//编号 txtPhone.Text = member.MemMobilePhone;//电话 txtSubTime.Value = member.SubTime;//提交时间 //会员的类型==坑=== cmbType.SelectedValue = member.MemType; rdoMan.Checked = member.MemGender == "男" ? true : false; rdoWoman.Checked = member.MemGender == "女" ? true : false; labId.Text = member.MemberId.ToString();//存储会员的id--为了修改 } else { //为空 新增 //随机生成会员的编号 Random r = new Random(); //int num = r.Next(10000, 100000); //txtNum.Text = "0" + num.ToString(); int num = r.Next(100, 1000); string strDate = System.DateTime.Now.ToString("yyyyMMddHHmmss"); txtNum.Text = strDate + num; } }
//新增 public int AddMemberInfo(MemberInfo member) { string sql = "insert into MemberInfo( MemName, MemMobilePhone, MemAddress, MemType, MemNum, MemGender, MemDiscount, MemMoney, DelFlag, SubTime, MemIntegral, MemEndTime, MemBirthday)values( @MemName, @MemMobilePhone, @MemAddress, @MemType, @MemNum, @MemGender, @MemDiscount, @MemMoney, @DelFlag, @SubTime, @MemIntegral, @MemEndTime, @MemBirthday)"; return AddOrUpdateMemberInfo(member, sql, 1); }
//关系转对象 private MemberInfo RowToMemberInfoByDatarow(DataRow dr) { MemberInfo mem = new MemberInfo(); mem.DelFlag = Convert.ToInt32(dr["DelFlag"]); mem.MemAddress = dr["MemAddress"].ToString(); mem.MemberId = Convert.ToInt32(dr["MemberId"]); mem.MemBirthday = Convert.ToDateTime(dr["MemBirthday"]); mem.MemDiscount = Convert.ToDouble(dr["MemDiscount"]); mem.MemEndTime = Convert.ToDateTime(dr["MemEndTime"]); mem.MemGender = dr["MemGender"].ToString(); mem.MemIntegral = Convert.ToInt32(dr["MemIntegral"]); mem.MemMobilePhone = dr["MemMobilePhone"].ToString(); mem.MemMoney = Convert.ToDouble(dr["MemMoney"]); mem.MemName = dr["MemName"].ToString(); mem.MemNum = dr["MemNum"].ToString(); mem.MemType = Convert.ToInt32(dr["MemType"]); mem.SubTime = Convert.ToDateTime(dr["SubTime"]); return mem; }
//新增和修改公用的一个方法 private int AddOrUpdateMemberInfo(MemberInfo member, string sql, int temp) { List<SqlParameter> list = new List<SqlParameter>(); SqlParameter[] ps ={ new SqlParameter("@MemName",member.MemName), new SqlParameter("@MemMobilePhone",member.MemMobilePhone), new SqlParameter("@MemAddress",member.MemAddress), new SqlParameter("@MemType",member.MemType), new SqlParameter("@MemNum",member.MemNum), new SqlParameter("@MemGender",member.MemGender), new SqlParameter("@MemDiscount",member.MemDiscount), new SqlParameter("@MemMoney",member.MemMoney), new SqlParameter("@MemIntegral",member.MemIntegral), new SqlParameter("@MemEndTime",member.MemEndTime), new SqlParameter("@MemBirthday",member.MemBirthday), }; list.AddRange(ps); if (temp == 1) { //新增 list.Add(new SqlParameter("@DelFlag", member.DelFlag)); list.Add(new SqlParameter("@SubTime", member.SubTime)); } else if (temp == 2) { //修改 list.Add(new SqlParameter("@MemberId", member.MemberId)); } return SqlHelper.ExecuteNonQuery(sql, list.ToArray()); }
//修改 public int UpdateMemberInfo(MemberInfo member) { string sql = "update MemberInfo set MemName=@MemName, MemMobilePhone=@MemMobilePhone, MemAddress=@MemAddress, MemType=@MemType, MemNum=@MemNum, MemGender=@MemGender, MemDiscount=@MemDiscount, MemMoney=@MemMoney, MemIntegral=@MemIntegral, MemEndTime=@MemEndTime, MemBirthday=@MemBirthday where MemberId=@MemberId"; return AddOrUpdateMemberInfo(member, sql, 2); }
private void ShowFrmMemberOrModify(int temp, MemberInfo member) { this.Hide();//当前窗体隐藏 //显示新增或修改窗体 FrmMemberAddOrModify fmam = FrmMemberAddOrModify.Single(temp, member);//单例模式下的new新窗体并且传递了数据,新增就没有对象传过去,修改就有 fmam.FormClosed += new FormClosedEventHandler(fmam_FormClosed);//事件 fmam.Show(); }
//确定 private void btnOk_Click(object sender, EventArgs e) { //判断所有的数据不能为空 if (CheckEmpty()) { MemberInfo member = new MemberInfo(); member.MemAddress = txtAddress.Text; member.MemBirthday = txtBirstday.Value;//生日 member.MemDiscount = Convert.ToDouble(txtDiscount.Text);//折扣 member.MemEndTime = txtEndTime.Value;//结束时间 //性别:member.MemGender==坑 member.MemGender = CheckGender();//性别 member.MemIntegral = Convert.ToInt32(txtIntegral.Text);//积分 member.MemMobilePhone = txtPhone.Text;//电话 member.MemMoney = Convert.ToDouble(txtMoney.Text);//金额 member.MemName = txtName.Text;//会员的名字 member.MemNum = txtNum.Text;//编号 member.MemType = Convert.ToInt32(cmbType.SelectedValue);//会员的类型 member.SubTime = System.DateTime.Now; if (Temp == 1)//新增 { member.DelFlag = 0; } else if (Temp == 2)//修改 { member.MemberId = Convert.ToInt32(labId.Text);//会员的id } MemberInfoBl memBll = new MemberInfoBl(); string msg = memBll.AddOrUpdateMemberInfo(member, Temp) ? "操作成功" : "操作失败"; md.MsgDivShow(msg, 1, Bind); } }