数据库更新数据时的传值问题
如果数据库中有多列值时,但是不定个数更新是需要采取“先通过查找数据库中的所有数据保存为一个model类,再给相应要改变的值复制,其他的更新的值保持原有数据不变”
比如:DAL(数据访问层)
/// <summary> /// 更新数据,虽然有的时候只是更新某个值,其他的值可以原样更新。在更新之前先要查询一下原有的数据,以便于不更新没必要的更新 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Updata(TblPerson model) { string sql = "update dbo.TblPerson set uName=@name,age=@age,height=@height,gender=@gender where autoId=@id"; SqlParameter[] sps = new SqlParameter[] { new SqlParameter("@name",model.uName), new SqlParameter("@age",model.age), new SqlParameter("@height",model.height==null?DBNull.Value:(object)model.height), new SqlParameter("@gender",model.gender==null?DBNull.Value:(object)model.gender), new SqlParameter("@id",model.autoId) }; return SqlHelper.ExecureNonQuery(sql,CommandType.Text,sps); } /// <summary> /// 通过autoId查询相关项的数据,并且返回model类 /// </summary> /// <param name="autoId"></param> /// <returns></returns> public TblPerson GetModelById(int autoId) { string sql = "select * from TblPerson where autoId=@id"; TblPerson model = new TblPerson(); using (SqlDataReader sdr = SqlHelper.ExecuteReader(sql, CommandType.Text, new SqlParameter("@id", autoId))) { if (sdr.HasRows) { if (sdr.Read()) { model.autoId = sdr.GetInt32(0); model.uName = sdr.GetString(1); model.age = sdr.GetInt32(2); model.height = sdr.IsDBNull(3) ? null : (int?)sdr.GetInt32(3); model.gender = sdr.IsDBNull(4) ? null : (bool?)sdr.GetBoolean(4); } } else { model = null; } } return model; }
BLL (业务逻辑层)
public int Updata(TblPerson model) { return dal.Updata(model); } public TblPerson GetModelById(int autoId) { return dal.GetModelById(autoId); }
UI(应用层)
private void button1_Click(object sender, EventArgs e) { TblPersonBll bll = new TblPersonBll(); int id =Convert.ToInt32(textBox1.Text.Trim()); TblPerson model = bll.GetModelById(id); model.autoId = id; model.uName = "张三"; int r= bll.Updata(model); MessageBox.Show("更新"+r+"行"); }