/// <summary> /// 增加一条数据 /// </summary> public int Add(NCPEP.Model.T_jtnc model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into T_jtnc("); strSql.Append("xh,quyu,mingcheng,dizhi,zhuceshijian,jingyingfanwei,chengyuanshu,nianshouru,lishizhang,lianxidianhua,pinpaiqingkuang,niandujibie,zuzhicode,zuobiaox,zuobiaoy)"); strSql.Append(" values ("); strSql.Append("@xh,@quyu,@mingcheng,@dizhi,@zhuceshijian,@jingyingfanwei,@chengyuanshu,@nianshouru,@lishizhang,@lianxidianhua,@pinpaiqingkuang,@niandujibie,@zuzhicode,@zuobiaox,@zuobiaoy)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@xh", SqlDbType.Int, 4), new SqlParameter("@quyu", SqlDbType.VarChar, 50), new SqlParameter("@mingcheng", SqlDbType.NVarChar, 200), new SqlParameter("@dizhi", SqlDbType.NVarChar, 500), new SqlParameter("@zhuceshijian", SqlDbType.NVarChar, 50), new SqlParameter("@jingyingfanwei", SqlDbType.NVarChar, 500), new SqlParameter("@chengyuanshu", SqlDbType.NVarChar, 50), new SqlParameter("@nianshouru", SqlDbType.NVarChar, 50), new SqlParameter("@lishizhang", SqlDbType.NVarChar, 50), new SqlParameter("@lianxidianhua", SqlDbType.NVarChar, 50), new SqlParameter("@pinpaiqingkuang", SqlDbType.NVarChar, 50), new SqlParameter("@niandujibie", SqlDbType.NVarChar, 500), new SqlParameter("@zuzhicode", SqlDbType.NVarChar, 50), new SqlParameter("@zuobiaox", SqlDbType.NVarChar, 50), new SqlParameter("@zuobiaoy", SqlDbType.NVarChar, 50) }; parameters[0].Value = model.xh; parameters[1].Value = model.quyu; parameters[2].Value = model.mingcheng; parameters[3].Value = model.dizhi; parameters[4].Value = model.zhuceshijian; parameters[5].Value = model.jingyingfanwei; parameters[6].Value = model.chengyuanshu; parameters[7].Value = model.nianshouru; parameters[8].Value = model.lishizhang; parameters[9].Value = model.lianxidianhua; parameters[10].Value = model.pinpaiqingkuang; parameters[11].Value = model.niandujibie; parameters[12].Value = model.zuzhicode; parameters[13].Value = model.zuobiaox; parameters[14].Value = model.zuobiaoy; object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } }
/// <summary> /// 得到一个对象实体 /// </summary> public NCPEP.Model.T_jtnc GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 id,xh,quyu,mingcheng,dizhi,zhuceshijian,jingyingfanwei,chengyuanshu,nianshouru,lishizhang,lianxidianhua,pinpaiqingkuang,niandujibie,zuzhicode,zuobiaox,zuobiaoy from T_jtnc "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; NCPEP.Model.T_jtnc model = new NCPEP.Model.T_jtnc(); DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(NCPEP.Model.T_jtnc model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update T_jtnc set "); strSql.Append("xh=@xh,"); strSql.Append("quyu=@quyu,"); strSql.Append("mingcheng=@mingcheng,"); strSql.Append("dizhi=@dizhi,"); strSql.Append("zhuceshijian=@zhuceshijian,"); strSql.Append("jingyingfanwei=@jingyingfanwei,"); strSql.Append("chengyuanshu=@chengyuanshu,"); strSql.Append("nianshouru=@nianshouru,"); strSql.Append("lishizhang=@lishizhang,"); strSql.Append("lianxidianhua=@lianxidianhua,"); strSql.Append("pinpaiqingkuang=@pinpaiqingkuang,"); strSql.Append("niandujibie=@niandujibie,"); strSql.Append("zuzhicode=@zuzhicode,"); strSql.Append("zuobiaox=@zuobiaox,"); strSql.Append("zuobiaoy=@zuobiaoy"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@xh", SqlDbType.Int, 4), new SqlParameter("@quyu", SqlDbType.VarChar, 50), new SqlParameter("@mingcheng", SqlDbType.NVarChar, 200), new SqlParameter("@dizhi", SqlDbType.NVarChar, 500), new SqlParameter("@zhuceshijian", SqlDbType.NVarChar, 50), new SqlParameter("@jingyingfanwei", SqlDbType.NVarChar, 500), new SqlParameter("@chengyuanshu", SqlDbType.NVarChar, 50), new SqlParameter("@nianshouru", SqlDbType.NVarChar, 50), new SqlParameter("@lishizhang", SqlDbType.NVarChar, 50), new SqlParameter("@lianxidianhua", SqlDbType.NVarChar, 50), new SqlParameter("@pinpaiqingkuang", SqlDbType.NVarChar, 50), new SqlParameter("@niandujibie", SqlDbType.NVarChar, 500), new SqlParameter("@zuzhicode", SqlDbType.NVarChar, 50), new SqlParameter("@zuobiaox", SqlDbType.NVarChar, 50), new SqlParameter("@zuobiaoy", SqlDbType.NVarChar, 50), new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = model.xh; parameters[1].Value = model.quyu; parameters[2].Value = model.mingcheng; parameters[3].Value = model.dizhi; parameters[4].Value = model.zhuceshijian; parameters[5].Value = model.jingyingfanwei; parameters[6].Value = model.chengyuanshu; parameters[7].Value = model.nianshouru; parameters[8].Value = model.lishizhang; parameters[9].Value = model.lianxidianhua; parameters[10].Value = model.pinpaiqingkuang; parameters[11].Value = model.niandujibie; parameters[12].Value = model.zuzhicode; parameters[13].Value = model.zuobiaox; parameters[14].Value = model.zuobiaoy; parameters[15].Value = model.id; int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return(true); } else { return(false); } }
/// <summary> /// 得到一个对象实体 /// </summary> public NCPEP.Model.T_jtnc DataRowToModel(DataRow row) { NCPEP.Model.T_jtnc model = new NCPEP.Model.T_jtnc(); if (row != null) { if (row["id"] != null && row["id"].ToString() != "") { model.id = int.Parse(row["id"].ToString()); } if (row["xh"] != null && row["xh"].ToString() != "") { model.xh = int.Parse(row["xh"].ToString()); } if (row["quyu"] != null) { model.quyu = row["quyu"].ToString(); } if (row["mingcheng"] != null) { model.mingcheng = row["mingcheng"].ToString(); } if (row["dizhi"] != null) { model.dizhi = row["dizhi"].ToString(); } if (row["zhuceshijian"] != null) { model.zhuceshijian = row["zhuceshijian"].ToString(); } if (row["jingyingfanwei"] != null) { model.jingyingfanwei = row["jingyingfanwei"].ToString(); } if (row["chengyuanshu"] != null) { model.chengyuanshu = row["chengyuanshu"].ToString(); } if (row["nianshouru"] != null) { model.nianshouru = row["nianshouru"].ToString(); } if (row["lishizhang"] != null) { model.lishizhang = row["lishizhang"].ToString(); } if (row["lianxidianhua"] != null) { model.lianxidianhua = row["lianxidianhua"].ToString(); } if (row["pinpaiqingkuang"] != null) { model.pinpaiqingkuang = row["pinpaiqingkuang"].ToString(); } if (row["niandujibie"] != null) { model.niandujibie = row["niandujibie"].ToString(); } if (row["zuzhicode"] != null) { model.zuzhicode = row["zuzhicode"].ToString(); } if (row["zuobiaox"] != null) { model.zuobiaox = row["zuobiaox"].ToString(); } if (row["zuobiaoy"] != null) { model.zuobiaoy = row["zuobiaoy"].ToString(); } } return(model); }
protected void btnsave_Click(object sender, EventArgs e) { if (fuhzs.HasFile) { string strf = Path.GetExtension(fuhzs.FileName); if (strf != ".xls") { MessageBox.Show(this, "请按照模板excel文件格式导入!谢谢!"); return; } string filepath = Server.MapPath("../../upload/" + "dr" + Guid.NewGuid().ToString("N") + strf); fuhzs.SaveAs(filepath); //数据导入 // string pathcs = Server.MapPath("moban.xls"); FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); #region 合作社导入 HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet("名单"); DbHelperSQL.ExecuteSql("delete from t_hzs"); for (int t = 3; t < ws.LastRowNum; t++) { NCPEP.Model.T_hzs model = new NCPEP.Model.T_hzs(); try { string str = ws.GetRow(t).GetCell(0).ToString(); model.xh = Convert.ToInt32(str); } catch { } try { string str = ws.GetRow(t).GetCell(1).ToString(); model.quyu = str; } catch { } try { string str = ws.GetRow(t).GetCell(2).ToString(); model.mingcheng = str; } catch { } try { string str = ws.GetRow(t).GetCell(3).ToString(); model.dizhi = str; } catch { } try { string str = ws.GetRow(t).GetCell(4).ToString(); model.zhuceshijian = Convert.ToDateTime(str).ToString("yyyy-MM-dd"); } catch { } try { string str = ws.GetRow(t).GetCell(5).ToString(); model.jingyingfanwei = str; } catch { } try { string str = ws.GetRow(t).GetCell(6).ToString(); model.chengyuanshu = str; } catch { } try { string str = ws.GetRow(t).GetCell(7).ToString(); model.nianshouru = str; } catch { } try { string str = ws.GetRow(t).GetCell(8).ToString(); model.lishizhang = str; } catch { } try { string str = ws.GetRow(t).GetCell(9).ToString(); model.lianxidianhua = str; } catch { } try { string str = ws.GetRow(t).GetCell(10).ToString(); model.pinpaiqingkuang = str; } catch { } try { string str = ws.GetRow(t).GetCell(11).ToString(); model.niandujibie = str; } catch { } try { if (!string.IsNullOrEmpty(ws.GetRow(t).GetCell(0).ToString().Trim())) { NCPEP.Bll.T_hzs bll = new NCPEP.Bll.T_hzs(); bll.Add(model); } } catch { MessageBox.Show(this, "第" + t + "条信息导入失败"); return; } } #endregion } if (this.fujtnc.HasFile) { string strf = Path.GetExtension(fujtnc.FileName); if (strf != ".xls") { MessageBox.Show(this, "请按照模板excel文件格式导入!谢谢!"); return; } string filepath = Server.MapPath("../../upload/" + "dr" + Guid.NewGuid().ToString("N") + strf); fujtnc.SaveAs(filepath); //数据导入 // string pathcs = Server.MapPath("moban.xls"); FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); #region 家庭农场导入 HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet("名单"); DbHelperSQL.ExecuteSql("delete from t_jtnc"); for (int t = 3; t < ws.LastRowNum; t++) { NCPEP.Model.T_jtnc model = new NCPEP.Model.T_jtnc(); try { string str = ws.GetRow(t).GetCell(0).ToString(); model.xh = Convert.ToInt32(str); } catch { } try { string str = ws.GetRow(t).GetCell(1).ToString(); model.quyu = str; } catch { } try { string str = ws.GetRow(t).GetCell(2).ToString(); model.mingcheng = str; } catch { } try { string str = ws.GetRow(t).GetCell(3).ToString(); model.dizhi = str; } catch { } try { string str = ws.GetRow(t).GetCell(4).ToString(); model.zhuceshijian = Convert.ToDateTime(str).ToString("yyyy-MM-dd"); } catch { } try { string str = ws.GetRow(t).GetCell(5).ToString(); model.jingyingfanwei = str; } catch { } try { string str = ws.GetRow(t).GetCell(6).ToString(); model.chengyuanshu = str; } catch { } try { string str = ws.GetRow(t).GetCell(7).ToString(); model.nianshouru = str; } catch { } try { string str = ws.GetRow(t).GetCell(8).ToString(); model.lishizhang = str; } catch { } try { string str = ws.GetRow(t).GetCell(9).ToString(); model.lianxidianhua = str; } catch { } try { string str = ws.GetRow(t).GetCell(10).ToString(); model.pinpaiqingkuang = str; } catch { } try { string str = ws.GetRow(t).GetCell(11).ToString(); model.niandujibie = str; } catch { } try { if (!string.IsNullOrEmpty(ws.GetRow(t).GetCell(0).ToString().Trim())) { NCPEP.Bll.T_jtnc bll = new NCPEP.Bll.T_jtnc(); bll.Add(model); } } catch { MessageBox.Show(this, "第" + t + "条信息导入失败"); return; } } #endregion } }