/// <summary> /// 新增一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Add(account model) { StringBuilder sql = new StringBuilder(); sql.Append("insert into [dbo].[account] ([userid],[zid],[uploaddatetime],[provid],[city],[sex],[age],[grade],[subject],[area],[experience],[job],[designation],[record],[suggession],[iden],[classid])"); sql.Append(" values (@userid,@zid,@uploaddatetime,@provid,@city,@sex,@age,@grade,@subject,@area,@experience,@job,@designation,@record,@suggession,@iden,@classid)"); sql.Append(" set @id=@@IDENTITY"); SqlParameter[] cmdParams = new SqlParameter[]{ new SqlParameter("@id", SqlDbType.Int, 4) { Value = model.id, Direction = ParameterDirection.Output }, new SqlParameter("@userid", SqlDbType.Int, 4) { Value = model.userid }, new SqlParameter("@zid", SqlDbType.VarChar, 100) { Value = model.zid }, new SqlParameter("@uploaddatetime", SqlDbType.DateTime, 8) { Value = model.uploaddatetime }, new SqlParameter("@provid", SqlDbType.Int, 4) { Value = model.provid }, new SqlParameter("@city", SqlDbType.VarChar, 100) { Value = model.city }, new SqlParameter("@sex", SqlDbType.Int, 4) { Value = model.sex }, new SqlParameter("@age", SqlDbType.Int, 4) { Value = model.age }, new SqlParameter("@grade", SqlDbType.Int, 4) { Value = model.grade }, new SqlParameter("@subject", SqlDbType.Int, 4) { Value = model.subject }, new SqlParameter("@area", SqlDbType.Int, 4) { Value = model.area }, new SqlParameter("@experience", SqlDbType.Int, 4) { Value = model.experience }, new SqlParameter("@job", SqlDbType.Int, 4) { Value = model.job }, new SqlParameter("@designation", SqlDbType.Int, 4) { Value = model.designation }, new SqlParameter("@record", SqlDbType.Int, 4) { Value = model.record }, new SqlParameter("@suggession", SqlDbType.NVarChar, 8000) { Value = model.suggession }, new SqlParameter("@iden", SqlDbType.Int, 4) { Value = model.iden }, new SqlParameter("@classid", SqlDbType.VarChar, 20) { Value = model.classid } }; int result = Convert.ToInt32(MSEntLibSqlHelper.ExecuteNonQueryBySql(sql.ToString(), cmdParams)); model.id = Convert.ToInt32(cmdParams[0].Value); return result; }
/// <summary> /// 获取数据集 /// </summary> /// <param name="where"></param> /// <param name="orderBy"></param> /// <returns></returns> public List<account> GetList(string where, string orderBy) { StringBuilder sql = new StringBuilder(); sql.Append("select * from [dbo].[account]"); if (!string.IsNullOrEmpty(where)) sql.Append(" where " + where); if (!string.IsNullOrEmpty(orderBy)) sql.Append(" order by " + orderBy); List<account> list = new List<account>(); using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql.ToString())) { while (reader.Read()) { account model = new account(); ConvertToModel(reader, model); list.Add(model); } } return list; }
/// <summary> /// 新增一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Add(account model) { return dal.Add(model) > 0; }
/// <summary> /// 更新一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Update(account model) { return dal.Update(model) > 0; }
/// <summary> /// 获取分页数据集 /// </summary> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="where"></param> /// <param name="orderBy"></param> /// <param name="recordCount"></param> /// <returns></returns> public List<account> GetList(int pageSize, int pageIndex, string where, string orderBy, out int recordCount) { if (string.IsNullOrEmpty(orderBy)) throw new ArgumentNullException(); StringBuilder sb = new StringBuilder(); sb.Append("select count(1) from [dbo].[account]"); if (!string.IsNullOrEmpty(where)) sb.Append(" where " + where); recordCount = Convert.ToInt32(MSEntLibSqlHelper.ExecuteScalarBySql(sb.ToString())); int start = (pageIndex - 1) * pageSize + 1; int end = pageIndex * pageSize; StringBuilder sql = new StringBuilder(); sql.Append("select * from (select *,ROW_NUMBER() over (order by " + orderBy + ") as [RowNum] from [dbo].[account]"); if (!string.IsNullOrEmpty(where)) sql.Append(" where " + where); sql.Append(") as T where [RowNum] between " + start + " and " + end); List<account> list = new List<account>(); using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql.ToString())) { while (reader.Read()) { account model = new account(); ConvertToModel(reader, model); list.Add(model); } } return list; }
private void ConvertToModel(IDataReader reader, account model) { if (reader["id"] != DBNull.Value) model.id = Convert.ToInt32(reader["id"]); if (reader["userid"] != DBNull.Value) model.userid = Convert.ToInt32(reader["userid"]); if (reader["zid"] != DBNull.Value) model.zid = reader["zid"].ToString(); if (reader["uploaddatetime"] != DBNull.Value) model.uploaddatetime = Convert.ToDateTime(reader["uploaddatetime"]); if (reader["provid"] != DBNull.Value) model.provid = Convert.ToInt32(reader["provid"]); if (reader["city"] != DBNull.Value) model.city = reader["city"].ToString(); if (reader["sex"] != DBNull.Value) model.sex = Convert.ToInt32(reader["sex"]); if (reader["age"] != DBNull.Value) model.age = Convert.ToInt32(reader["age"]); if (reader["grade"] != DBNull.Value) model.grade = Convert.ToInt32(reader["grade"]); if (reader["subject"] != DBNull.Value) model.subject = Convert.ToInt32(reader["subject"]); if (reader["area"] != DBNull.Value) model.area = Convert.ToInt32(reader["area"]); if (reader["experience"] != DBNull.Value) model.experience = Convert.ToInt32(reader["experience"]); if (reader["job"] != DBNull.Value) model.job = Convert.ToInt32(reader["job"]); if (reader["designation"] != DBNull.Value) model.designation = Convert.ToInt32(reader["designation"]); if (reader["record"] != DBNull.Value) model.record = Convert.ToInt32(reader["record"]); if (reader["suggession"] != DBNull.Value) model.suggession = reader["suggession"].ToString(); if (reader["iden"] != DBNull.Value) model.iden = Convert.ToInt32(reader["iden"]); if (reader["classid"] != DBNull.Value) model.classid = reader["classid"].ToString(); }
/// <summary> /// 更新一条记录 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Update(account model) { StringBuilder sql = new StringBuilder(); sql.Append("update [dbo].[account] set "); sql.Append("[userid]=@userid,[zid]=@zid,[uploaddatetime]=@uploaddatetime,[provid]=@provid,[city]=@city,[sex]=@sex,[age]=@age,[grade]=@grade,[subject]=@subject,[area]=@area,[experience]=@experience,[job]=@job,[designation]=@designation,[record]=@record,[suggession]=@suggession,[iden]=@iden,[classid]=@classid"); sql.Append(" where [id]=@id"); SqlParameter[] cmdParams = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) { Value = model.id }, new SqlParameter("@userid", SqlDbType.Int, 4) { Value = model.userid }, new SqlParameter("@zid", SqlDbType.VarChar, 100) { Value = model.zid }, new SqlParameter("@uploaddatetime", SqlDbType.DateTime, 8) { Value = model.uploaddatetime }, new SqlParameter("@provid", SqlDbType.Int, 4) { Value = model.provid }, new SqlParameter("@city", SqlDbType.VarChar, 100) { Value = model.city }, new SqlParameter("@sex", SqlDbType.Int, 4) { Value = model.sex }, new SqlParameter("@age", SqlDbType.Int, 4) { Value = model.age }, new SqlParameter("@grade", SqlDbType.Int, 4) { Value = model.grade }, new SqlParameter("@subject", SqlDbType.Int, 4) { Value = model.subject }, new SqlParameter("@area", SqlDbType.Int, 4) { Value = model.area }, new SqlParameter("@experience", SqlDbType.Int, 4) { Value = model.experience }, new SqlParameter("@job", SqlDbType.Int, 4) { Value = model.job }, new SqlParameter("@designation", SqlDbType.Int, 4) { Value = model.designation }, new SqlParameter("@record", SqlDbType.Int, 4) { Value = model.record }, new SqlParameter("@suggession", SqlDbType.NVarChar, 8000) { Value = model.suggession }, new SqlParameter("@iden", SqlDbType.Int, 4) { Value = model.iden }, new SqlParameter("@classid", SqlDbType.VarChar, 20) { Value = model.classid } }; return MSEntLibSqlHelper.ExecuteNonQueryBySql(sql.ToString(), cmdParams); }
/// <summary> /// 取得一条记录 /// </summary> /// <param name="id"></param> /// <param name="where"></param> /// <returns></returns> public account GetModel(int id, string where) { string sql = "select * from [dbo].[account] where [id]=@Id"; if (!string.IsNullOrEmpty(where)) sql += " and " + where; SqlParameter[] cmdParams = new SqlParameter[]{ new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id } }; using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql, cmdParams)) { if (reader.Read()) { account model = new account(); ConvertToModel(reader, model); return model; } else { return null; } } }