/// <summary>
/// 增加一条数据
/// </summary>
public int Add(NCPEP.Model.T_hzs model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_hzs(");
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_hzs 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_hzs ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
NCPEP.Model.T_hzs model = new NCPEP.Model.T_hzs();
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_hzs model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update T_hzs 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_hzs DataRowToModel(DataRow row)
{
NCPEP.Model.T_hzs model = new NCPEP.Model.T_hzs();
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
}
}
