public IList <Bill_Ysmxb> GetYsmxByCode(string billCode) { string sql = @"select * from bill_ysmxb where billCode=@billCode"; SqlParameter[] sps = { new SqlParameter("@billCode", billCode) }; DataTable dt = DataHelper.GetDataTable(sql, sps, false); IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>(); foreach (DataRow dr in dt.Rows) { Bill_Ysmxb ysmx = new Bill_Ysmxb(); ysmx.Gcbh = Convert.ToString(dr["Gcbh"]); ysmx.YsDept = Convert.ToString(dr["YsDept"]); ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]); ysmx.Yskm = Convert.ToString(dr["Yskm"]); ysmx.BillCode = Convert.ToString(dr["BillCode"]); ysmx.YsType = Convert.ToString(dr["YsType"]); list.Add(ysmx); } return(list); }
/// <summary> /// 增加一条数据 /// </summary> public void Add(Bill_Ysmxb model, SqlTransaction tran) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into bill_ysmxb("); strSql.Append("gcbh,billCode,yskm,ysje,ysDept,ysType"); strSql.Append(") values ("); strSql.Append("@gcbh,@billCode,@yskm,@ysje,@ysDept,@ysType"); strSql.Append(") "); SqlParameter[] parameters = { new SqlParameter("@gcbh", SqlDbType.VarChar, 50), new SqlParameter("@billCode", SqlDbType.VarChar, 50), new SqlParameter("@yskm", SqlDbType.VarChar, 50), new SqlParameter("@ysje", SqlDbType.Float, 8), new SqlParameter("@ysDept", SqlDbType.VarChar, 50), new SqlParameter("@ysType", SqlDbType.VarChar, 1), }; parameters[0].Value = SqlNull(model.Gcbh); parameters[1].Value = SqlNull(model.BillCode); parameters[2].Value = SqlNull(model.Yskm); parameters[3].Value = SqlNull(model.Ysje); parameters[4].Value = SqlNull(model.YsDept); parameters[5].Value = SqlNull(model.YsType); DataHelper.ExcuteNonQuery(strSql.ToString(), tran, parameters, false); }
/// <summary> /// 根据科目编号获得各月预算 /// </summary> /// <param name="kmCode"></param> /// <returns></returns> public IList <Bill_Ysmxb> GetYsmxByKm(string kmCode, string dept, string gcbh) { string sql = @" select gcbh,yskm,ysdept,round(sum(isnull(ysje,0)),2) as ysje from bill_ysmxb where ysdept=@dept and yskm=@kmCode and yskm in(" + gcbh + @") group by gcbh,yskm,ysdept order by gcbh "; SqlParameter[] sps = { new SqlParameter("@dept", dept), new SqlParameter("@kmCode", kmCode) }; DataTable dt = DataHelper.GetDataTable(sql, sps, false); IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>(); foreach (DataRow dr in dt.Rows) { Bill_Ysmxb ysmx = new Bill_Ysmxb(); ysmx.Gcbh = Convert.ToString(dr["Gcbh"]); ysmx.YsDept = Convert.ToString(dr["YsDept"]); ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]); ysmx.Yskm = Convert.ToString(dr["Yskm"]); list.Add(ysmx); } return(list); }
/// <summary> /// 根据过程编号,部门编号获得各科目预算 /// </summary> /// <param name="gcbh"></param> /// <param name="depCode"></param> /// <returns></returns> public IList <Bill_Ysmxb> GetYsmxByDeptYue(string gcbh, string depCode) { string sql = @"select gcbh,yskm,round(sum(isnull(ysje,0)),2) as ysje,ysDept from bill_ysmxb a,bill_main b where gcbh=@gcbh and ysDept=@depCode and a.billcode=b.billcode and b.stepid='end' group by gcbh,yskm,ysDept"; SqlParameter[] sps = { new SqlParameter("@gcbh", gcbh), new SqlParameter("@depCode", depCode) }; DataTable dt = DataHelper.GetDataTable(sql, sps, false); IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>(); foreach (DataRow dr in dt.Rows) { Bill_Ysmxb ysmx = new Bill_Ysmxb(); ysmx.Gcbh = Convert.ToString(dr["Gcbh"]); ysmx.YsDept = Convert.ToString(dr["YsDept"]); ysmx.Ysje = Convert.ToDecimal(dr["Ysje"]); ysmx.Yskm = Convert.ToString(dr["Yskm"]); list.Add(ysmx); } return(list); }
public void Add(Bill_Ysmxb model) { using (SqlConnection conn = new SqlConnection(DataHelper.constr)) { conn.Open(); SqlTransaction tran = conn.BeginTransaction(); try { Delete(model.BillCode, tran); Add(model, tran); tran.Commit(); } catch { tran.Rollback(); throw; } } }
/// <summary> /// 按条件获取一个实例 /// </summary> /// <param name="tempsql"></param> /// <param name="sps"></param> /// <returns></returns> public IList <Bill_Ysmxb> ListMaker(string tempsql, SqlParameter[] sps) { DataTable dt = DataHelper.GetDataTable(tempsql, sps, false); IList <Bill_Ysmxb> list = new List <Bill_Ysmxb>(); foreach (DataRow dr in dt.Rows) { Bill_Ysmxb model = new Bill_Ysmxb(); model.Gcbh = dr["gcbh"].ToString(); model.BillCode = dr["billCode"].ToString(); model.Yskm = dr["yskm"].ToString(); if (!DBNull.Value.Equals(dr["ysje"])) { model.Ysje = decimal.Parse(dr["ysje"].ToString()); } model.YsDept = dr["ysDept"].ToString(); model.YsType = dr["ysType"].ToString(); list.Add(model); } return(list); }
/// <summary> /// 得到一个对象实体 /// </summary> public Bill_Ysmxb GetModel(string billcode) { StringBuilder strSql = new StringBuilder(); strSql.Append(sql); strSql.Append(" where billCode=@billCode"); SqlParameter[] parameters = { new SqlParameter("@billCode", SqlDbType.Char, 50) }; parameters[0].Value = billcode; Bill_Ysmxb model = new Bill_Ysmxb(); using (SqlDataReader dr = DataHelper.GetDataReader(strSql.ToString(), parameters)) { if (dr.Read()) { model.Gcbh = dr["gcbh"].ToString(); model.BillCode = dr["billCode"].ToString(); model.Yskm = dr["yskm"].ToString(); if (!DBNull.Value.Equals(dr["ysje"])) { model.Ysje = decimal.Parse(dr["ysje"].ToString()); } model.YsDept = dr["ysDept"].ToString(); model.YsType = dr["ysType"].ToString(); return(model); } else { return(null); } } }