/// <summary> /// 更新 /// </summary> /// <param name="id"></param> /// <returns></returns> public ActionResult Edit(int id) { StuCertificate data = stuService.Get(id); ViewBag.AreaCity = City; ViewBag.AreaProvince = Province; if (data != null) { return(View(new StuViewModel { Id = data.Id, StudentName = data.Name, Number = data.Number, City = string.IsNullOrEmpty(data.City) ? 0 : Convert.ToInt32(data.City), Province = string.IsNullOrEmpty(data.Province) ? 0 : Convert.ToInt32(data.Province), State = (int)data.State, Category = data.Category, Gender = data.Gender, HeadImg = data.HeadImg })); } else { return(View()); } }
/// <summary> /// 修改学生证书 /// </summary> /// <param name="stuInfo">学生证书信息</param> /// <returns>修改成功标识</returns> public bool UpdatStu(StuCertificate stuInfo) { DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"Update StuCertificate Set [Name]=@Name ,[UserId]=@UserId ,[HeadImg]=@HeadImg ,[Gender]=@Gender ,[Number]=@Number ,[State]=@State ,[Category]=@Category ,[Province]=@Province ,[City]=@City ,[County]=@County Where ID=@ID"; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@ID", stuInfo.Id)); parametersList.Add(new SqlParameter("@UserId", stuInfo.UserId)); parametersList.Add(new SqlParameter("@Name", stuInfo.Name)); parametersList.Add(new SqlParameter("@HeadImg", stuInfo.HeadImg)); parametersList.Add(new SqlParameter("@Gender", (int)stuInfo.Gender)); parametersList.Add(new SqlParameter("@Number", stuInfo.Number)); parametersList.Add(new SqlParameter("@State", (int)stuInfo.State)); parametersList.Add(new SqlParameter("@Category", (int)stuInfo.Category)); parametersList.Add(new SqlParameter("@Province", stuInfo.Province)); parametersList.Add(new SqlParameter("@City", stuInfo.City)); parametersList.Add(new SqlParameter("@County", stuInfo.County)); return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0); }
private List <StuCertificate> GetSheetData(ISheet sheet) { List <StuCertificate> stuCertList = null; const int minrownum = 1;//最小行数,如果小于1行证明sheet无数据 if (sheet != null && sheet.LastRowNum >= minrownum) { int startrownum = 1;//从第2行开始取数据(row和cell从0开始) int endrownum = sheet.LastRowNum; stuCertList = new List <StuCertificate>(); StuCertificate stuCertInfo = null; for (int index = startrownum; index <= endrownum; index++) { IRow row = sheet.GetRow(index); stuCertInfo = new StuCertificate() { Name = Regex.Replace(CellSwitch(row.GetCell(0)), @"\s", ""), Gender = CellSwitch(row.GetCell(1)).Equals("男") ? Gender.Male : Gender.Female, Number = Regex.Replace(CellSwitch(row.GetCell(2)), @"\s", ""), State = PublishState.Lower, Province = Constant.DEFAULT_PROVINCE, City = string.IsNullOrWhiteSpace(CellSwitch(row.GetCell(3))) ? "" : City.Where(g => g.Value.Equals(CellSwitch(row.GetCell(3)))).FirstOrDefault().Key + "", Category = CellSwitch(row.GetCell(4)).Equals("学员认证") ? CertificateType.Train : CertificateType.Prize }; stuCertList.Add(stuCertInfo); } } return(stuCertList); }
/// <summary> /// 添加学生证书 /// </summary> /// <param name="stuInfo">学生证书信息</param> /// <returns>添加成功标识</returns> public bool AddStu(StuCertificate stuInfo) { DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"if exists(select Id from StuCertificate where Number=@Number) begin update StuCertificate set UserId=@UserId,Name=@Name,Gender=@Gender,HeadImg=@HeadImg,State=@State,Category=@Category,Province=@Province,City=@City,County=@County,CreateDate=getdate(),IsDeleted=0 where Number=@Number end else begin Insert Into StuCertificate(UserId, Name, Gender, HeadImg, Number, State, Category, Province, City, County) Values(@UserId, @Name, @Gender, @HeadImg, @Number, @State, @Category, @Province, @City, @County) end "; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@UserId", stuInfo.UserId)); parametersList.Add(new SqlParameter("@Name", stuInfo.Name)); parametersList.Add(new SqlParameter("@Gender", (int)stuInfo.Gender)); parametersList.Add(new SqlParameter("@HeadImg", stuInfo.HeadImg)); parametersList.Add(new SqlParameter("@Number", stuInfo.Number)); parametersList.Add(new SqlParameter("@State", (int)stuInfo.State)); parametersList.Add(new SqlParameter("@Category", (int)stuInfo.Category)); parametersList.Add(new SqlParameter("@Province", stuInfo.Province)); parametersList.Add(new SqlParameter("@City", stuInfo.City)); parametersList.Add(new SqlParameter("@County", stuInfo.County)); return(dbHelper.ExecuteNonQuery(strSql, parametersList) > 0); }
/// <summary> /// 从游标中读取数据 /// </summary> /// <param name="reader"></param> /// <returns></returns> private StuCertificate GetStuFromReader(DbDataReader reader) { StuCertificate stuInfo = new StuCertificate(); stuInfo.Id = Convert.ToInt32(reader["Id"]); stuInfo.UserId = Convert.ToInt32(reader["UserId"]); stuInfo.Number = reader["Number"].ToString(); stuInfo.Name = reader["Name"].ToString(); stuInfo.Gender = (Gender)Convert.ToInt32(reader["Gender"]); stuInfo.State = (PublishState)reader["State"]; stuInfo.Category = (CertificateType)reader["Category"]; stuInfo.UserName = reader["UserName"].ToString(); if (ReaderExists(reader, "HeadImg") && DBNull.Value != reader["HeadImg"]) { stuInfo.HeadImg = reader["HeadImg"].ToString(); } if (ReaderExists(reader, "Province") && DBNull.Value != reader["Province"]) { stuInfo.Province = reader["Province"].ToString(); } if (ReaderExists(reader, "City") && DBNull.Value != reader["City"]) { stuInfo.City = reader["City"].ToString(); } if (ReaderExists(reader, "County") && DBNull.Value != reader["County"]) { stuInfo.County = reader["County"].ToString(); } stuInfo.CreateDate = Convert.ToDateTime(reader["CreateDate"]); return(stuInfo); }
public bool Add(StuCertificate stuInfo) { //参数验证 if (stuInfo == null) { ErrorMsg = ErrorCode.ParameterNull; return(false); } return(_certificateProvider.AddStu(stuInfo)); }
public StuCertificate Get(int id) { //参数验证 if (id < 1) { ErrorMsg = ErrorCode.ParameterNull; return(null); } //数据获取 StuCertificate stuInfo = _certificateProvider.GetStu(id); return(stuInfo); }
public StuCertificate GetStuByNumber(string number) { StuCertificate stuInfo = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"SELECT sc.Id, sc.UserId, sc.Name, sc.Gender, sc.HeadImg, sc.Number,u.Name as UserName, sc.State, sc.Category, sc.Province, sc.City, sc.County, sc.CreateDate from StuCertificate sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 and sc.Number=@Number"; List <DbParameter> parametersList = new List <DbParameter>(); parametersList.Add(new SqlParameter("@Number", number)); using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parametersList)) { while (reader.Read()) { stuInfo = GetStuFromReader(reader); } } return(stuInfo); }
public JsonResult Edit(StuViewModel model) { ResultRetrun rmodel = new ResultRetrun(); if (ModelState.IsValid) { StuCertificate data = stuService.Get(model.Id); data.Number = model.Number; data.Name = model.StudentName; data.Category = model.Category; data.State = (PublishState)model.State; data.Gender = model.Gender; data.City = model.City.ToString(); data.Province = model.Province < 1 ? Constant.DEFAULT_PROVINCE : model.Province.ToString(); data.HeadImg = model.HeadImg; rmodel.isSuccess = stuService.Update(data); } return(Json(rmodel)); }
/// <summary> /// 获取所有证书信息 /// </summary> /// <param name="condition">筛选条件</param> /// <returns>证书集合</returns> public List <StuCertificate> GetStuAll(FilterEntityModel condition) { string whereSort = string.Empty; if (condition != null) { condition.DefaultSort = SortType.Desc; whereSort = condition.Where + condition.OrderBy; } List <StuCertificate> stuList = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSql = @"SELECT sc.Id, sc.UserId, sc.Name, sc.Gender, sc.HeadImg, sc.Number,u.Name as UserName, sc.State, sc.Category, sc.Province, sc.City, sc.County, sc.CreateDate from StuCertificate sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 " + whereSort; IList <DbParameter> parameList = null; if (condition != null && condition.SqlParList.Count > 0) { parameList = new List <DbParameter>(); foreach (var item in condition.SqlParList) { parameList.Add(new SqlParameter(item.Key, item.Value)); } } using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { stuList = new List <StuCertificate>(); StuCertificate stuInfo = null; while (reader.Read()) { stuInfo = GetStuFromReader(reader); stuList.Add(stuInfo); } } return(stuList); }
public JsonResult Add(StuViewModel model) { ResultRetrun rmodel = new ResultRetrun(); if (ModelState.IsValid) { StuCertificate stuModel = new StuCertificate() { Number = model.Number, Name = model.StudentName, Category = model.Category, State = (PublishState)model.State, Gender = model.Gender, City = model.City.ToString(), Province = model.Province.ToString(), UserId = GetAccount().Id, HeadImg = model.HeadImg }; rmodel.isSuccess = stuService.Add(stuModel); } return(Json(rmodel)); }
public List <StuCertificate> GetStuPage(FilterEntityModel condition, out int totalNum) { totalNum = 0; condition.DefaultSort = SortType.Desc; condition.SortTbAsName = Constant.STU_CERTIFICATE_AS_NAME; string sort = condition.OrderBy; string asSort = condition.AsOrderBy; string where = condition.Where; List <StuCertificate> stuList = null; DBHelper dbHelper = new DBHelper(ConnectionString, DbProviderType.SqlServer); string strSqlQuery = @"select count(sc.ID) from [StuCertificate] sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 " + where;//查询有多少条记录 IList <DbParameter> parameList = new List <DbParameter>(); parameList.Add(new SqlParameter("@pageSize", condition.PageSize)); parameList.Add(new SqlParameter("@pageIndex", condition.PageIndex)); if (condition.SqlParList.Count > 0) { foreach (var item in condition.SqlParList) { parameList.Add(new SqlParameter(item.Key, item.Value)); } } totalNum = Convert.ToInt32(dbHelper.ExecuteScalar(strSqlQuery, parameList)); string strSql = @"SELECT [ID] ,[UserId] ,[Name] ,[Gender] ,[HeadImg] ,[Number] ,[State] ,[Category] ,[Province] ,[City] ,[County] ,[CreateDate] ,[UserName] FROM (select top (@pageSize*@pageIndex) sc.[ID] ,sc.[UserId] ,sc.[Name] ,sc.[Gender] ,sc.[HeadImg] ,sc.[Number] ,sc.[State] ,sc.[Category] ,sc.[Province] ,sc.[City] ,sc.[County] ,sc.[CreateDate] ,u.[Name] as UserName ,ROW_NUMBER() over(" + asSort + ") as num from [StuCertificate] sc inner join [User] u on sc.UserId=u.Id where sc.IsDeleted=0 " + where + ") as t where num between (@pageIndex - 1) * @pageSize + 1 and @pageIndex*@pageSize " + sort; using (DbDataReader reader = dbHelper.ExecuteReader(strSql, parameList)) { stuList = new List <StuCertificate>(); StuCertificate stuInfo = null; while (reader.Read()) { stuInfo = GetStuFromReader(reader); stuList.Add(stuInfo); } } return(stuList); }