//导入操作
//读取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);
}
}
