![31bba9ff3b6ebb77d61dcb20d6724472.png](https://img-blog.csdnimg.cn/img_convert/31bba9ff3b6ebb77d61dcb20d6724472.png)
一、数据表设计
CREATE TABLE [dbo].[pastelist]([id] [int] IDENTITY(1,1) NOT NULL,[listid] [nvarchar](50) NULL,[dwmc] [nvarchar](50) NULL,[rq] [date] NULL,[dw] [nvarchar](10) NULL,[zy] [nvarchar](50) NULL,[yt] [nvarchar](50) NULL,[sj] [date] NULL,[je] [decimal](18, 2) NULL,[pzs] [int] NULL,[fkfs] [nvarchar](50) NULL)
二、Model实体类
public class pastelist { public int id { get; set; } public string listid { get; set; } public string dwmc { get; set; } public DateTime rq { get; set; } public string dw { get; set; } public string zy { get; set; } public string yt { get; set; } public DateTime sj { get; set; } public decimal je { get; set; } public int pzs { get; set; } public string fkfs { get; set; } }
三、DAL数据访问
1、增加数据
//增加数据 public int Add(Model.pastelist model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [pastelist]("); strSql.Append("[listid],[dwmc],[rq],[dw],[zy],[yt],[sj],[je],[pzs],[fkfs],[fgld],[csfzr],[cwfzr],[jbr])"); strSql.Append(" values ("); strSql.Append("@listid,@dwmc,@rq,@dw,@zy,@yt,@sj,@je,@pzs,@fkfs,@fgld,@csfzr,@cwfzr,@jbr)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@listid", SqlDbType.NVarChar,50), new SqlParameter("@dwmc", SqlDbType.NVarChar,50), new SqlParameter("@rq", SqlDbType.Date), new SqlParameter("@dw", SqlDbType.NVarChar,50), new SqlParameter("@zy", SqlDbType.NVarChar,50), new SqlParameter("@yt", SqlDbType.NVarChar,50), new SqlParameter("@sj", SqlDbType.Date), new SqlParameter("@je", SqlDbType.Decimal), new SqlParameter("@pzs", SqlDbType.Int), new SqlParameter("@fkfs", SqlDbType.NVarChar,50) }; parameters[0].Value = model.listid; parameters[1].Value = model.dwmc; parameters[2].Value = model.rq; parameters[3].Value = model.dw; parameters[4].Value = model.zy; parameters[5].Value = model.yt; parameters[6].Value = model.sj; parameters[7].Value = model.je; parameters[8].Value = model.pzs; parameters[9].Value = model.fkfs; //添加主表数据 object obj = new SqlHelper().ExecuteScalar(strSql.ToString(), parameters, CommandType.Text); model.id = Convert.ToInt32(obj); return model.id; }
2、删除数据
(1)前台代码
/*记录-删除*/ function rec_del(strid) { if (prompt("请输入密码:") == 'ok') { layer.confirm('确认要将补贴申报记录删除吗?', { title: "删除确认" }, function () { //发异步删除数据 $.ajax({ type: 'get', url: 'btsb_del.ashx', contentType: "application/json;charset=utf-8", dataType: "text", data: {id: strid}, success: function (data) { alert(data); layer.msg('已删除!', { icon: 1, time: 1000 }); location.reload(); }, error: function () { alert("出错了!请稍候再试!"); } }); }); } else { alert("密码错误!"); } }
(2)后台处理
int id = int.Parse(context.Request.QueryString["id"]); bool result = new DAL.butiesb().Delete(id); if (result) { context.Response.Write("删除成功!" + context.Request.QueryString["id"]); } else { context.Response.Write("false"); }
(3)DAL
//删除数据 public bool Delete(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("delete from [butiesb]"); strSql.Append(" where id=@id"); SqlParameter[] parameters = {new SqlParameter("@id", SqlDbType.Int,4)}; parameters[0].Value = id; int rowsAffected = new SqlHelper().ExecuteNonQuery(strSql.ToString(), parameters, CommandType.Text); if (rowsAffected > 0) { return true; } else { return false; } }
3、查询数据
// 组合SQL查询语句========================== public string CombSqlTxt(Model.butiesb btsb) { StringBuilder strTemp = new StringBuilder(); if (!string.IsNullOrEmpty(btsb.bqid)) { strTemp.Append(" and [bqid] ='" + btsb.bqid + "'"); } if (!string.IsNullOrEmpty(btsb.bqmc)) { strTemp.Append(" and [bqmc] like '%" + btsb.bqmc + "%'"); } return strTemp.ToString(); } //得到条件查询后的记录行数 public int GetCount(Model.butiesb btsb) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*) from [peixun].[dbo].[butiesb] where 1=1"); strSql.Append(CombSqlTxt(btsb)); return Convert.ToInt32(new SqlHelper().ExecuteScalar(strSql.ToString(), CommandType.Text)); } //带有查询条件的分页 public DataTable SelectbyPage(Model.butiesb btsb, string startIndex, string endIndex) //当前页的首条页码参数starIndex和最后页码参数endIndex { StringBuilder strSql = new StringBuilder(); //下面语句中的ORDER BY 可以设置排序字段 strSql.Append("with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY id desc )AS Row, * from [peixun].[dbo].[butiesb] where 1=1"); strSql.Append(CombSqlTxt(btsb)); //添加条件语句 strSql.Append(") SELECT * FROM temptbl where Row between @startIndex and @endIndex"); SqlParameter[] sqlParameters = { new SqlParameter("@startIndex", startIndex), new SqlParameter("@endIndex",endIndex)}; DataTable dt = new SqlHelper().ExecuteQuery(strSql.ToString(), sqlParameters, CommandType.Text); return dt; }
4、修改数据
//更新一条记录 public bool Update(Model.butiesb model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update [butiesb] set "); strSql.Append("bqid=@bqid,"); strSql.Append("bqmc=@bqmc,"); strSql.Append("xxdw=@xxdw,"); strSql.Append("qdb=@qdb,"); strSql.Append("kqb=@kqb,"); strSql.Append("jssk=@jssk,"); strSql.Append("jcff=@jcff,"); strSql.Append("jycj=@jycj,"); strSql.Append("gwxx=@gwxx,"); strSql.Append("gwtj=@gwtj,"); strSql.Append("jksp=@jksp,"); strSql.Append("jyzs=@jyzs,"); strSql.Append("zgzs=@zgzs,"); strSql.Append("pxbtsq=@pxbtsq,"); strSql.Append("jdbtsq=@jdbtsq,"); strSql.Append("pxbtdlxy=@pxbtdlxy,"); strSql.Append("jdbtdlxy=@jdbtdlxy,"); strSql.Append("sfzpx=@sfzpx,"); strSql.Append("sfzjd=@sfzjd,"); strSql.Append("create_time=@create_time,"); strSql.Append("jbr=@jbr,"); strSql.Append("jdfpj=@jdfpj,"); strSql.Append("fp=@fp,"); strSql.Append("bfcns=@bfcns,"); strSql.Append("khfz=@khfz,"); strSql.Append("yxzl=@yxzl,"); strSql.Append("hfjl=@hfjl"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@bqid", SqlDbType.NVarChar,20), new SqlParameter("@bqmc", SqlDbType.NVarChar,50), new SqlParameter("@xxdw", SqlDbType.NVarChar,50), new SqlParameter("@qdb", SqlDbType.NVarChar,50), new SqlParameter("@kqb", SqlDbType.NVarChar,50), new SqlParameter("@jssk", SqlDbType.NVarChar,50), new SqlParameter("@jcff", SqlDbType.NVarChar,50), new SqlParameter("@jycj", SqlDbType.NVarChar,50), new SqlParameter("@gwxx", SqlDbType.NVarChar,50), new SqlParameter("@gwtj", SqlDbType.NVarChar,50), new SqlParameter("@jksp", SqlDbType.NVarChar,50), new SqlParameter("@jyzs", SqlDbType.NVarChar,50), new SqlParameter("@zgzs", SqlDbType.NVarChar,50), new SqlParameter("@pxbtsq", SqlDbType.NVarChar,50), new SqlParameter("@jdbtsq", SqlDbType.NVarChar,50), new SqlParameter("@pxbtdlxy", SqlDbType.NVarChar,50), new SqlParameter("@jdbtdlxy", SqlDbType.NVarChar,50), new SqlParameter("@sfzpx", SqlDbType.NVarChar,50), new SqlParameter("@sfzjd", SqlDbType.NVarChar,50), new SqlParameter("@create_time", SqlDbType.DateTime), new SqlParameter("@jbr", SqlDbType.NVarChar,50), new SqlParameter("@jdfpj", SqlDbType.NVarChar,50), new SqlParameter("@fp", SqlDbType.NVarChar,50), new SqlParameter("@bfcns", SqlDbType.NVarChar,50), new SqlParameter("@khfz", SqlDbType.NVarChar,50), new SqlParameter("@yxzl", SqlDbType.NVarChar,50), new SqlParameter("@hfjl", SqlDbType.NVarChar,50), new SqlParameter("@id", SqlDbType.Int,4) }; parameters[0].Value = model.bqid; parameters[1].Value = model.bqmc; parameters[2].Value = model.xxdw; parameters[3].Value = model.qdb; parameters[4].Value = model.kqb; parameters[5].Value = model.jssk; parameters[6].Value = model.jcff; parameters[7].Value = model.jycj; parameters[8].Value = model.gwxx; parameters[9].Value = model.gwtj; parameters[10].Value = model.jksp; parameters[11].Value = model.jyzs; parameters[12].Value = model.zgzs; parameters[13].Value = model.pxbtsq; parameters[14].Value = model.jdbtsq; parameters[15].Value = model.pxbtdlxy; parameters[16].Value = model.jdbtdlxy; parameters[17].Value = model.sfzpx; parameters[18].Value = model.sfzjd; parameters[19].Value = model.create_time; parameters[20].Value = model.jbr; parameters[21].Value = model.jdfpj; parameters[22].Value = model.fp; parameters[23].Value = model.bfcns; parameters[24].Value = model.khfz; parameters[25].Value = model.yxzl; parameters[26].Value = model.hfjl; parameters[27].Value = model.id; int res = new SqlHelper().ExecuteNonQuery(strSql.ToString(), parameters, CommandType.Text); if (res >= 1) //更新一条就是=1 { return true; } else { return false; } }