/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.zxkc model)
{
using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update t_zxkc set ");
strSql.Append("year=@year,");
strSql.Append("month=@month,");
strSql.Append("empid=@empid,");
strSql.Append("name=@name,");
strSql.Append("znjy=@znjy,");
strSql.Append("jxjy=@jxjy,");
strSql.Append("dbyl=@dbyl,");
strSql.Append("zfdk=@zfdk,");
strSql.Append("zfzj=@zfzj,");
strSql.Append("sylr=@sylr");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@year", SqlDbType.VarChar, 50),
new SqlParameter("@month", SqlDbType.VarChar, 50),
new SqlParameter("@empid", SqlDbType.VarChar, 50),
new SqlParameter("@name", SqlDbType.VarChar, 50),
new SqlParameter("@znjy", SqlDbType.Decimal),
new SqlParameter("@jxjy", SqlDbType.Decimal),
new SqlParameter("@dbyl", SqlDbType.Decimal),
new SqlParameter("@zfdk", SqlDbType.Decimal),
new SqlParameter("@zfzj", SqlDbType.Decimal),
new SqlParameter("@sylr", SqlDbType.Decimal),
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = model.year;
parameters[1].Value = model.month;
parameters[2].Value = model.empid;
parameters[3].Value = model.name;
parameters[4].Value = model.znjy;
parameters[5].Value = model.jxjy;
parameters[6].Value = model.dbyl;
parameters[7].Value = model.zfdk;
parameters[8].Value = model.zfzj;
parameters[9].Value = model.sylr;
parameters[10].Value = model.id;
DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
trans.Commit();
}
catch
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.zxkc model)
{
int newId;
using (SqlConnection conn = new SqlConnection(DbHelperSql.connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into t_zxkc(");
strSql.Append("year,month,empid,name,znjy,jxjy,dbyl,zfdk,zfzj,sylr)");
strSql.Append(" values (");
strSql.Append("@year,@month,@empid,@name,@znjy,@jxjy,@dbyl,@zfdk,@zfzj,@sylr)");
SqlParameter[] parameters =
{
new SqlParameter("@year", SqlDbType.VarChar, 50),
new SqlParameter("@month", SqlDbType.VarChar, 50),
new SqlParameter("@empid", SqlDbType.VarChar, 50),
new SqlParameter("@name", SqlDbType.VarChar, 50),
new SqlParameter("@znjy", SqlDbType.Decimal),
new SqlParameter("@jxjy", SqlDbType.Decimal),
new SqlParameter("@dbyl", SqlDbType.Decimal),
new SqlParameter("@zfdk", SqlDbType.Decimal),
new SqlParameter("@zfzj", SqlDbType.Decimal),
new SqlParameter("@sylr", SqlDbType.Decimal)
};
parameters[0].Value = model.year;
parameters[1].Value = model.month;
parameters[2].Value = model.empid;
parameters[3].Value = model.name;
parameters[4].Value = model.znjy;
parameters[5].Value = model.jxjy;
parameters[6].Value = model.dbyl;
parameters[7].Value = model.zfdk;
parameters[8].Value = model.zfzj;
parameters[9].Value = model.sylr;
DbHelperSql.ExecuteSql(conn, trans, strSql.ToString(), parameters);
//取得新插入的ID
newId = GetMaxId(conn, trans);
trans.Commit();
}
catch
{
trans.Rollback();
return(-1);
}
}
}
return(newId);
}
/// <summary>
/// excel文件导入
/// </summary>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="fileName">文件名</param>
public void ExcelFileUpload(string excelFilePath, string fileName)
{
IWorkbook workbook = null; //新建IWorkbook对象
FileStream fileStream = new FileStream(excelFilePath + fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fileStream); //xlsx数据读入workbook
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xls数据读入workbook
}
for (int s = 0; s < workbook.NumberOfSheets; s++) //遍历所有工作簿
{
ISheet sheet = workbook.GetSheetAt(s); //获取工作表
IRow row; // = sheet.GetRow(0); //新建当前工作表行数据
for (int i = 3; i < sheet.LastRowNum; i++) //从第3行开始读取EXCEL工作簿表格每行
{
row = sheet.GetRow(i); //row读入第i行数据
if (row == null) //如果A列数据为空，退出循环
{
break;
}
else
{
if (row.GetCell(0).ToString() == "")//如果A列数据为空，退出循环
{
break;
}
}
if (row != null) // 行不为空时，对数据进行处理
{
try
{
Model.zxkc model = new Model.zxkc(); //新建zxkc数据模型对像
model.year = slt_year.Value;
model.month = slt_month.Value;
model.empid = GetStringValue(row.GetCell(1));
model.name = GetStringValue(row.GetCell(2));
model.znjy = row.GetCell(3).NumericCellValue; //GetDoubleValue(row.GetCell(3).StringCellValue);
model.jxjy = row.GetCell(4).NumericCellValue; //GetDoubleValue(row.GetCell(4));
model.zfdk = row.GetCell(5).NumericCellValue; //GetDoubleValue(row.GetCell(5));
model.zfzj = row.GetCell(6).NumericCellValue; //GetDoubleValue(row.GetCell(6));
model.sylr = row.GetCell(7).NumericCellValue; //GetDoubleValue(row.GetCell(7));
model.dbyl = row.GetCell(8).NumericCellValue; //GetDoubleValue(row.GetCell(8));
bll.Add(model);
}
catch (Exception e)
{
strStatus.InnerText = "上传的文件： " + fileName + " 第" + (i + 1).ToString() + "行格式错误！请检查后重新上传";
}
}
}
}
fileStream.Close();
workbook.Close();
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.zxkc model)
{
return(dal.Update(model));
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.zxkc model)
{
return(dal.Add(model));
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Model.zxkc GetModel(int id)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 id,year,month,empid,name,znjy,jxjy,dbyl,zfdk,zfzj,sylr from t_zxkc ");
strSql.Append(" where id=@id");
SqlParameter[] parameters =
{
new SqlParameter("@id", SqlDbType.Int, 4)
};
parameters[0].Value = id;
Model.zxkc model = new Model.zxkc();
DataSet ds = DbHelperSql.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
{
model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
}
if (ds.Tables[0].Rows[0]["year"] != null && ds.Tables[0].Rows[0]["year"].ToString() != "")
{
model.year = ds.Tables[0].Rows[0]["year"].ToString();
}
if (ds.Tables[0].Rows[0]["month"] != null && ds.Tables[0].Rows[0]["month"].ToString() != "")
{
model.month = ds.Tables[0].Rows[0]["month"].ToString();
}
if (ds.Tables[0].Rows[0]["empid"] != null && ds.Tables[0].Rows[0]["empid"].ToString() != "")
{
model.empid = ds.Tables[0].Rows[0]["empid"].ToString();
}
if (ds.Tables[0].Rows[0]["name"] != null && ds.Tables[0].Rows[0]["name"].ToString() != "")
{
model.name = ds.Tables[0].Rows[0]["name"].ToString();
}
if (ds.Tables[0].Rows[0]["znjy"] != null && ds.Tables[0].Rows[0]["znjy"].ToString() != "")
{
model.znjy = double.Parse(ds.Tables[0].Rows[0]["znjy"].ToString());
}
if (ds.Tables[0].Rows[0]["jxjy"] != null && ds.Tables[0].Rows[0]["jxjy"].ToString() != "")
{
model.jxjy = double.Parse(ds.Tables[0].Rows[0]["jxjy"].ToString());
}
if (ds.Tables[0].Rows[0]["dbyl"] != null && ds.Tables[0].Rows[0]["dbyl"].ToString() != "")
{
model.dbyl = double.Parse(ds.Tables[0].Rows[0]["dbyl"].ToString());
}
if (ds.Tables[0].Rows[0]["zfdk"] != null && ds.Tables[0].Rows[0]["zfdk"].ToString() != "")
{
model.zfdk = double.Parse(ds.Tables[0].Rows[0]["zfdk"].ToString());
}
if (ds.Tables[0].Rows[0]["zfzj"] != null && ds.Tables[0].Rows[0]["zfzj"].ToString() != "")
{
model.zfzj = double.Parse(ds.Tables[0].Rows[0]["zfzj"].ToString());
}
if (ds.Tables[0].Rows[0]["sylr"] != null && ds.Tables[0].Rows[0]["sylr"].ToString() != "")
{
model.sylr = double.Parse(ds.Tables[0].Rows[0]["sylr"].ToString());
}
return(model);
}
else
{
return(null);
}
}
