public Model.BasicRotaryViewModel GetModelByGP_Students_Rotary_id(string GP_Students_Rotary_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from Basic_Rotary_View "); strSql.Append(" where GP_Students_Rotary_id=@GP_Students_Rotary_id"); SqlParameter[] parameters = { new SqlParameter("@GP_Students_Rotary_id", SqlDbType.NVarChar, 50) }; parameters[0].Value = GP_Students_Rotary_id; Model.BasicRotaryViewModel model = new Model.BasicRotaryViewModel(); DataSet ds = db.RunDataSet(strSql.ToString(), parameters, "tbName"); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
public DataSet GetListByGP_Students_Rotary_id(string GP_Students_Rotary_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from Basic_Rotary_View "); strSql.Append(" where GP_Students_Rotary_id=@GP_Students_Rotary_id"); SqlParameter[] parameters = { new SqlParameter("@GP_Students_Rotary_id", SqlDbType.NVarChar, 50) }; parameters[0].Value = GP_Students_Rotary_id; Model.BasicRotaryViewModel model = new Model.BasicRotaryViewModel(); return(db.RunDataSet(strSql.ToString(), parameters, "tbName")); }
// #region GetPagedListBasic // public List<Model.BasicRotaryViewModel> GetPagedListBasic(string training_base_code, string dept_code, string instructor, string instructor_tag, string name, string sex, string minzu, string high_education, string high_school, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time, // int pageIndex, int pageSize, out int rowCount, out int pageCount) // { // List<Model.BasicRotaryViewModel> list = null; // if (sex == string.Empty || sex == "") sex = null; // if (name == string.Empty || name == "") name = null; // if (minzu == string.Empty || minzu == "") minzu = null; // if (high_education == string.Empty || high_education == "") high_education = null; // if (high_school == string.Empty || high_school == "") high_school = null; // if (identity_type == string.Empty || identity_type == "") identity_type = null; // if (send_unit == string.Empty || send_unit == "") send_unit = null; // if (collaborative_unit == string.Empty || collaborative_unit == "") collaborative_unit = null; // if (training_time == string.Empty || training_time == "") training_time = null; // if (plan_training_time == string.Empty || plan_training_time == "") plan_training_time = null; ////name, sex, minzu, high_education, high_school, identity_type, send_unit, collaborative_unit, training_time, plan_training_time // DataTable dt = db.RunPagedDataPro("GetPageList", " id,name,real_name,sex,age,datebirth,minzu,province,city,area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education,high_school,high_major,high_education_time,identity_type,send_unit,training_base_province_code,training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name,professional_base_code,training_time,plan_training_time,writor,register_date", "Basic_Rotary_view", "training_base_code='" + training_base_code // + "' and rotary_dept_code='" + dept_code + "' and instructor='" + instructor + "' and instructor_tag='" + instructor_tag // + "' and real_name like ISNULL('%"+name+"%',real_name) and sex like ISNULL('%"+sex+"%',sex) and minzu like ISNULL('%"+minzu+"%',minzu)" // + "and high_education like ISNULL('%" + high_education + "%',high_education) and high_school like ISNULL('%" + high_school + "%',high_school)" // + "and identity_type like ISNULL('%" + identity_type + "%',identity_type) and send_unit like isnull('%" + send_unit + "%',send_unit)" // + "and collaborative_unit like isnull('%" + collaborative_unit + "%',collaborative_unit) and training_time like isnull('%" + training_time + "%',training_time) and plan_training_time like isnull('%" + plan_training_time + "%',plan_training_time)" // , "register_date desc", "id", pageIndex, pageSize, out rowCount, out pageCount); // //将数据表转为泛型集合 // if (dt.Rows.Count > 0) // { // list = new List<BasicRotaryViewModel>(); // Model.BasicRotaryViewModel model = null;//声明实体对象 // foreach (DataRow dr in dt.Rows) // { // model = new Model.BasicRotaryViewModel(); // model.id = dr["id"].ToString(); // model.name = dr["name"].ToString(); // model.real_name = dr["real_name"].ToString(); // model.sex = dr["sex"].ToString(); // model.age = dr["age"].ToString(); // model.datebirth = dr["datebirth"].ToString(); // model.minzu = dr["minzu"].ToString(); // model.province = dr["province"].ToString(); // model.city = dr["city"].ToString(); // model.area = dr["area"].ToString(); // model.id_number = dr["id_number"].ToString(); // model.detail_address = dr["detail_address"].ToString(); // model.telephon = dr["telephon"].ToString(); // model.mail = dr["mail"].ToString(); // model.bk_major = dr["bk_major"].ToString(); // model.bk_school = dr["bk_school"].ToString(); // model.graduation_time = dr["graduation_time"].ToString(); // model.high_education = dr["high_education"].ToString(); // model.high_school = dr["high_school"].ToString(); // model.high_major = dr["high_major"].ToString(); // model.high_education_time = dr["high_education_time"].ToString(); // model.identity_type = dr["identity_type"].ToString(); // model.send_unit = dr["send_unit"].ToString(); // model.training_base_province_code = dr["training_base_province_code"].ToString(); // model.training_base_province_name = dr["training_base_province_name"].ToString(); // model.training_base_code = dr["training_base_code"].ToString(); // model.training_base_name = dr["training_base_name"].ToString(); // model.collaborative_unit = dr["collaborative_unit"].ToString(); // model.professional_base_code = dr["professional_base_code"].ToString(); // model.professional_base_name = dr["professional_base_name"].ToString(); // model.training_time = dr["training_time"].ToString(); // model.plan_training_time = dr["plan_training_time"].ToString(); // model.writor = dr["writor"].ToString(); // model.register_date = dr["register_date"].ToString(); // list.Add(model); // } // } // return list; // } // #endregion #region Basic分页 public List <Model.BasicRotaryViewModel> GetPagedListBasic(string training_base_code, string dept_code, string instructor_tag, string name, string sex, string minzu, string high_education, string high_school, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time, int start, int end) { string sql = "select id,name,real_name, sex,age,datebirth,minzu,province,city, area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education, high_school, high_major,high_education_time,identity_type, send_unit,training_base_province_code, training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name, professional_base_code,training_time, plan_training_time,writor,register_date from (select row_number() over(order by register_date desc) as num,* from Basic_Rotary_view where training_base_code='" + training_base_code + "' and rotary_dept_code='" + dept_code + "' and instructor_tag='" + instructor_tag + "'"; if (!string.IsNullOrEmpty(name)) { sql += "and name = '" + name + "'"; } if (!string.IsNullOrEmpty(sex)) { sql += "and sex = '" + sex + "'"; } if (!string.IsNullOrEmpty(minzu)) { sql += "and minzu like '%" + minzu + "%'"; } if (!string.IsNullOrEmpty(high_education)) { sql += "and high_education = '" + high_education + "'"; } if (!string.IsNullOrEmpty(high_school)) { sql += "and high_school like '%" + high_school + "%'"; } if (!string.IsNullOrEmpty(identity_type)) { sql += "and identity_type = '" + identity_type + "'"; } if (!string.IsNullOrEmpty(send_unit)) { sql += "and send_unit like '%" + send_unit + "%'"; } if (!string.IsNullOrEmpty(collaborative_unit)) { sql += "and collaborative_unit like '%" + collaborative_unit + "%'"; } if (!string.IsNullOrEmpty(training_time)) { sql += "and training_time like '%" + training_time + "%'"; } if (!string.IsNullOrEmpty(plan_training_time)) { sql += "and plan_training_time like '%" + plan_training_time + "%'"; } sql += ")as t where t.num>=@start and t.num<=@end"; sql += " group by id,name,real_name, sex,age,datebirth,minzu,province,city, area,detail_address,id_number,telephon,mail,bk_school,bk_major,graduation_time,high_education, high_school, high_major,high_education_time,identity_type, send_unit,training_base_province_code, training_base_province_name,training_base_code,training_base_name,collaborative_unit,professional_base_name, professional_base_code,training_time, plan_training_time,writor,register_date"; SqlParameter[] pars = { new SqlParameter("@start", SqlDbType.Int), new SqlParameter("@end", SqlDbType.Int) }; pars[0].Value = start; pars[1].Value = end; DataTable dt = db.RunDataTable(sql, pars); List <BasicRotaryViewModel> list = null; if (dt.Rows.Count > 0) { list = new List <BasicRotaryViewModel>(); Model.BasicRotaryViewModel model = null;//声明实体对象 foreach (DataRow dr in dt.Rows) { model = new Model.BasicRotaryViewModel(); model.id = dr["id"].ToString(); model.name = dr["name"].ToString(); model.real_name = dr["real_name"].ToString(); model.sex = dr["sex"].ToString(); model.age = dr["age"].ToString(); model.datebirth = dr["datebirth"].ToString(); model.minzu = dr["minzu"].ToString(); model.province = dr["province"].ToString(); model.city = dr["city"].ToString(); model.area = dr["area"].ToString(); model.id_number = dr["id_number"].ToString(); model.detail_address = dr["detail_address"].ToString(); model.telephon = dr["telephon"].ToString(); model.mail = dr["mail"].ToString(); model.bk_major = dr["bk_major"].ToString(); model.bk_school = dr["bk_school"].ToString(); model.graduation_time = dr["graduation_time"].ToString(); model.high_education = dr["high_education"].ToString(); model.high_school = dr["high_school"].ToString(); model.high_major = dr["high_major"].ToString(); model.high_education_time = dr["high_education_time"].ToString(); model.identity_type = dr["identity_type"].ToString(); model.send_unit = dr["send_unit"].ToString(); model.training_base_province_code = dr["training_base_province_code"].ToString(); model.training_base_province_name = dr["training_base_province_name"].ToString(); model.training_base_code = dr["training_base_code"].ToString(); model.training_base_name = dr["training_base_name"].ToString(); model.collaborative_unit = dr["collaborative_unit"].ToString(); model.professional_base_code = dr["professional_base_code"].ToString(); model.professional_base_name = dr["professional_base_name"].ToString(); model.training_time = dr["training_time"].ToString(); model.plan_training_time = dr["plan_training_time"].ToString(); model.writor = dr["writor"].ToString(); model.register_date = dr["register_date"].ToString(); list.Add(model); } } return(list); }
public List <Model.BasicRotaryViewModel> GetPagedListRotary(string training_base_code, string dept_code, string instructor, string instructor_tag, string name, string sex, string high_education, string identity_type, string send_unit, string collaborative_unit, string training_time, string plan_training_time, string rotary_begin_time, string rotary_end_time, string outdept_status, int pageIndex, int pageSize, out int rowCount, out int pageCount) { List <Model.BasicRotaryViewModel> list = null; if (sex == string.Empty || sex == "") { sex = null; } if (name == string.Empty || name == "") { name = null; } if (high_education == string.Empty || high_education == "") { high_education = null; } if (identity_type == string.Empty || identity_type == "") { identity_type = null; } if (send_unit == string.Empty || send_unit == "") { send_unit = null; } if (collaborative_unit == string.Empty || collaborative_unit == "") { collaborative_unit = null; } if (training_time == string.Empty || training_time == "") { training_time = null; } if (plan_training_time == string.Empty || plan_training_time == "") { plan_training_time = null; } if (rotary_begin_time == string.Empty || rotary_begin_time == "") { rotary_begin_time = null; } else { rotary_begin_time = CommonFunc.SafeGetDateTimeStringFromObjectByFormat(rotary_begin_time, "yyyy-MM"); } if (rotary_end_time == string.Empty || rotary_end_time == "") { rotary_end_time = null; } else { rotary_end_time = CommonFunc.SafeGetDateTimeStringFromObjectByFormat(rotary_end_time, "yyyy-MM"); } if (outdept_status == string.Empty || outdept_status == "") { outdept_status = null; } //+"and rotary_begin_time >= isnull('"+rotary_begin_time+"',rotary_begin_time) and rotary_end_time<=isnull('"+rotary_end_time+"',rotary_end_time)" DataTable dt = db.RunPagedDataPro("GetPageList", "id,name,real_name,sex,high_education,high_major,rotary_begin_time,rotary_end_time," + "training_base_code,training_base_name,professional_base_code,professional_base_name,rotary_dept_code,rotary_dept_name," + "instructor,instructor_tag,GP_Students_Rotary_id,GP_Students_Rotary_writor,GP_Students_Rotary_register_date,outdept_status,outdept_application,questionnaire_status", "Basic_Rotary_view", "training_base_code='" + training_base_code + "' and rotary_dept_code='" + dept_code + "' and instructor='" + instructor + "' and instructor_tag='" + instructor_tag + "'and real_name like ISNULL('%" + name + "%',real_name) and sex like ISNULL('%" + sex + "%',sex)" + "and high_education like ISNULL('%" + high_education + "%',high_education)" + "and identity_type like ISNULL('%" + identity_type + "%',identity_type) and send_unit like isnull('%" + send_unit + "%',send_unit)" + "and collaborative_unit like isnull('%" + collaborative_unit + "%',collaborative_unit) and training_time like isnull('%" + training_time + "%',training_time) and plan_training_time like isnull('%" + plan_training_time + "%',plan_training_time)" + "and rotary_begin_time like isnull('%" + rotary_begin_time + "%',rotary_begin_time) and rotary_end_time like isnull('%" + rotary_end_time + "%',rotary_end_time)" + "and outdept_status like isnull('%" + outdept_status + "%',outdept_status)" , "GP_Students_Rotary_register_date desc", "id", pageIndex, pageSize, out rowCount, out pageCount); //将数据表转为泛型集合 if (dt.Rows.Count > 0) { list = new List <BasicRotaryViewModel>(); Model.BasicRotaryViewModel model = null;//声明实体对象 foreach (DataRow dr in dt.Rows) { model = new Model.BasicRotaryViewModel(); model.id = dr["id"].ToString(); model.name = dr["name"].ToString(); model.real_name = dr["real_name"].ToString(); model.sex = dr["sex"].ToString(); model.high_education = dr["high_education"].ToString(); model.high_major = dr["high_major"].ToString(); model.rotary_begin_time = dr["rotary_begin_time"].ToString(); model.rotary_end_time = dr["rotary_end_time"].ToString(); model.training_base_code = dr["training_base_code"].ToString(); model.training_base_name = dr["training_base_name"].ToString(); model.professional_base_code = dr["professional_base_code"].ToString(); model.professional_base_name = dr["professional_base_name"].ToString(); model.GP_Students_Rotary_id = dr["GP_Students_Rotary_id"].ToString(); model.rotary_dept_code = dr["rotary_dept_code"].ToString(); model.rotary_dept_name = dr["rotary_dept_name"].ToString(); model.instructor = dr["instructor"].ToString(); model.instructor_tag = dr["instructor_tag"].ToString(); model.GP_Students_Rotary_writor = dr["GP_Students_Rotary_writor"].ToString(); model.GP_Students_Rotary_register_date = dr["GP_Students_Rotary_register_date"].ToString(); model.outdept_status = dr["outdept_status"].ToString(); model.outdept_application = dr["outdept_application"].ToString(); model.questionnaire_status = dr["questionnaire_status"].ToString(); list.Add(model); } } return(list); }