/// <summary>
/// 更新一条记录（根据Id）
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int UpdateById(TblPerson model)
{
string sql = "update TblPerson set Name =@Name,Age=@Age,Height=@Height,Gender=@Gender where Id = @Id";
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@Id", SqlDbType.Int)
{
Value = model.Id
},
new SqlParameter("@Name", SqlDbType.NVarChar, 50)
{
Value = model.Name
}, //如果这里不写数据类型，它内部会帮我们推断一个类型，我们显式的写了，就不需要内部推断了。
new SqlParameter("@Age", SqlDbType.Int)
{
Value = model.Age
},
new SqlParameter("@Height", SqlDbType.Int)
{
Value = model.Height
},
new SqlParameter("@Gender", SqlDbType.Bit)
{
Value = model.Gender == null ? DBNull.Value:(object)model.Gender
} //【C#中的 null值 转换为 数据库中的 DBNull.Value,if-else返回类型不统一，任意一个转为object类型】
};
return(SqlHelper.ExcuteNonQuery(sql, CommandType.Text, pms));
}
public async Task <IActionResult> Reservation(ReservationModel reservationModel)
{
if (ModelState.IsValid)
{
TblPerson tblPerson = new TblPerson
{
FirstName = reservationModel.FirstName,
LastName = reservationModel.LastName,
EmailAddress = reservationModel.EmailAddress,
PhoneNumber = reservationModel.PhoneNumber,
StreetNumber = reservationModel.StreetNumber,
StreetName = reservationModel.StreetName,
City = reservationModel.City,
PostalCode = reservationModel.PostalCode,
Country = reservationModel.Country,
};
await _context.TblPerson.AddAsync(tblPerson);
TblReservation tblReservation = new TblReservation
{
PersonId = tblPerson.PersonId,
RoomNumber = reservationModel.RoomNumber,
ExpectedArriveDate = reservationModel.ExpectedArrivalDate,
ExpectedLeaveDate = reservationModel.ExpectedLeaveDate,
ReservationNotes = reservationModel.Notes,
};
await _context.TblReservation.AddAsync(tblReservation);
await _context.SaveChangesAsync();
return(Redirect("/"));
}
return(View());
}
public async Task <IActionResult> PutTblPerson(int id, TblPerson tblPerson)
{
if (id != tblPerson.PersonId)
{
return(BadRequest());
}
_context.Entry(tblPerson).State = EntityState.Modified;
try
{
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!TblPersonExists(id))
{
return(NotFound());
}
else
{
throw;
}
}
return(NoContent());
}
public JsonResult ChangeInfo([FromBody] TblPerson person)
{
var cookie = Request.Cookies["User"];
var personId = Convert.ToInt32(cookie);
if (ModelState.IsValid)
{
var findPerson = _ctx.TblPerson.FirstOrDefault(x => x.PersonId == personId);
findPerson.FirstName = person.FirstName;
findPerson.LastName = person.LastName;
findPerson.Address = person.Address;
findPerson.Email = person.Email;
findPerson.Telephone = person.Telephone;
findPerson.Pass = person.Pass;
_ctx.TblPerson.Update(findPerson);
_ctx.SaveChanges();
return(Json(new { result = "Successed" }));
}
return(Json(new { result = "Fail" }));
}
public JsonResult Login([FromBody] TblPerson person)
{
switch (SessionHandler.Login(person.Email, person.Pass))
{
case ReturnValue.Successful:
var number = _ctx.TblPerson.FirstOrDefault(x => x.Email == person.Email);
_user = _ctx.TblPerson.FirstOrDefault(x => x.PersonId == number.PersonId);
ViewCookies(_user.PersonId);
return(Json(new { result = "Success", user = _user.PersonId })); //användarens inloggning sparas i en cookie
case ReturnValue.WrongUserOrPassword:
if (_user == null)
{
_user = _ctx.TblPerson.FirstOrDefault(x => x.PersonId == 2);
}
return(Json(new { result = "Fail", user = _user.PersonId }));
}
return(Json(new { result = "Fail" }));
}
public async Task <ActionResult <TblPerson> > PostTblPerson(TblPerson tblPerson)
{
_context.TblPeople.Add(tblPerson);
await _context.SaveChangesAsync();
return(CreatedAtAction("GetTblPerson", new { id = tblPerson.PersonId }, tblPerson));
}
//当前行获取焦点事件
private void dgvDataList_RowEnter(object sender, DataGridViewCellEventArgs e)
{
//1、获取当前选中行【e.RowIndex获取当前事件触发，所在的行索引】
DataGridViewRow row = this.dgvDataList.Rows[e.RowIndex];
//2、获取当前行 绑定的数据对象，将其转换为 TblPerson 类型对象
TblPerson person = row.DataBoundItem as TblPerson;
//3、把数据 显示到“编辑”框中
if (person != null)
{
tbxName.Text = person.Name;
tbxAge.Text = person.Age.ToString();
tbxHeight.Text = person.Height.ToString();
if (person.Gender == null)
{
cbxGender.SelectedIndex = 0;
}
else if (person.Gender.Value)
{
cbxGender.SelectedIndex = 1;
}
else
{
cbxGender.SelectedIndex = 2;
}
lblId.Text = person.Id.ToString();
}
}
public ActionResult About()
{
ViewBag.About = "active";
TblPerson tblPerson = new TblPerson();
var person = tblPerson.GetAll().OrderBy(a => a.ID).FirstOrDefault();
return View(person);
}
private void LoadPersons(bool forCustomer, int selectedIndex = -1)
{
_costs = _costs.OrderBy(m => m.Id).ToList();
if (forCustomer)
{
var table = new TblPerson(_groupDets, _customers);
this.gridPerson.DataSource = table;
this.SetUpGridGroupDetail();
}
else
{
var table = new TblPerson(_attendants, _employees, _jobs);
this.gridPerson.DataSource = table;
this.SetUpGridAttendant();
}
if (selectedIndex < 0)
{
this.gridPerson.ClearSelection();
}
else
{
this.gridPerson.Rows[selectedIndex].Selected = true;
}
}
/// <summary>
/// 插入一条记录并返回刚插入的自动编号
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public object Insert(TblPerson model)
{
string sql = "insert into TblPerson output inserted.autoId values(@name,@age,@height,@gender)";
SqlParameter[] pms = 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),
};
return(SqlHelper.ExecuteScalar(sql, CommandType.Text, pms));
}
public IActionResult ViewCookies(int personId)
{
_user = _ctx.TblPerson.FirstOrDefault(x => x.PersonId == personId);
CookieOptions options = new CookieOptions();
options.HttpOnly = true;
options.Expires = DateTime.Now.AddDays(1);
Response.Cookies.Append("User", personId.ToString(), options);
return(View());
}
/// <summary>
/// 更新行，返回所影响的行数
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(TblPerson model)
{
string sql = "update TblPerson set uname=@name,age=@age,height=@height,gender=@gender where autoId=@id";
SqlParameter[] pms = 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.ExecuteNonQuery(sql, CommandType.Text, pms));
}
private void LoadGridView(int selectedIndex = -1)
{
TblPerson table = new TblPerson(_arr.Cast <Person>().ToList(), true);
this.gridView.DataSource = table;
if (selectedIndex < 0)
{
this.gridView.ClearSelection();
}
else
{
this.gridView.Rows[selectedIndex].Selected = true;
}
}
private void LoadGridView(int selectedIndex = -1)
{
Search();
TblPerson table = new TblPerson(_arr);
this.gridView.DataSource = table;
if (selectedIndex < 0)
{
this.gridView.ClearSelection();
}
else
{
this.gridView.Rows[selectedIndex].Selected = true;
}
}
private void button1_Click(object sender, EventArgs e)
{
int id = Convert.ToInt32(txtId.Text.Trim());
TblPersonBll bll = new TblPersonBll();
//由于更新语句是更新表中的列，这时用户实际可能只更新部分列，那么对于那些其他的用户没有要更新的列，则还是更新成原来的值，所以这时需要先将数据库中的原来这行中德数据查询出来赋值给model，然后再更新。
TblPerson model = bll.GetModelById(id);
model.AutoId = id;
model.Uname = "蒋坤";
int r = bll.Update(model);
MessageBox.Show("成功更新" + r + "行");
}
public ActionResult Create(TblPerson tblPerson)
{
if (ModelState.IsValid)
{
//db.Fun_InsertPerson(null, tblPerson.Name, tblPerson.Email, tblPerson.Mobile, tblPerson.Course, tblPerson.Percentage, tblPerson.Address, tblPerson.City);
db.TblPersons.Add(tblPerson);
db.SaveChanges();
return(RedirectToAction("Index", "Home"));
}
List <TblCity> list = new List <TblCity>();
list = db.Fun_ViewCity().ToList();
ViewBag.City = new SelectList(list, "CityId", "CityName");
return(View());
}
//增加
private void btnAdd_Click(object sender, EventArgs e)
{
//1、采集数据
TblPerson person = new TblPerson();
person.Name = tbxName.Text.Trim();
person.Age = Convert.ToInt32(tbxAge.Text.Trim());
person.Height = Convert.ToInt32(tbxHeight.Text.Trim());
person.Gender = cbxGender.SelectedIndex == 0 ? null : (bool?)(cbxGender.SelectedIndex == 1 ? true : false);
//2、实例化业务逻辑层，调用方法
TblPersonBll bll = new TblPersonBll();
int r = bll.AddPerson(person);
if (r > 0)
{
MessageBox.Show("增加成功！");
LoadPersonData();
}
}
private void BtnAdd_Click(object sender, EventArgs e)
{
string name = txtName.Text.Trim();
int age = Convert.ToInt32(txtAge.Text.Trim());
int? height = string.IsNullOrEmpty(txtHeight.Text.Trim()) ? null : (int?)Convert.ToInt32(txtHeight.Text.Trim());
bool? gender = string.IsNullOrEmpty(cboGender.Text.Trim()) ? null : (bool?)(cboGender.Text == "男" ? true : false);
TblPerson model = new TblPerson();
model.Uname = name;
model.Age = age;
model.Height = height;
model.Gender = gender;
TblPersonBll bll = new TblPersonBll();
int r = bll.Insert(model);
MessageBox.Show("插入成功！自动编号" + r);
}
public JsonResult CreateUser([FromBody] TblPerson person)
{
var findEmail = _ctx.TblPerson.FirstOrDefault(x => x.Email == person.Email);
if (findEmail == null)
{
int?newId = _ctx.TblPerson.Max(x => (int?)x.PersonId);
if (newId == null)
{
newId = 1;
}
else
{
newId++;
}
var createUser = new TblPerson
{
PersonId = (int?)newId ?? 1,
Email = person.Email,
Pass = person.Pass,
Address = person.Address,
FirstName = person.FirstName,
LastName = person.LastName,
LastLoggedIn = null,
Telephone = person.Telephone
};
_ctx.TblPerson.Add(createUser);
_ctx.SaveChanges();
ViewCookies(createUser.PersonId);
_user = createUser;
return(Json(new { result = "Successed" }));
}
return(Json(new { result = "Fail" }));
}
private void btnUpdate_Click(object sender, EventArgs e)
{
//1、采集数据
TblPerson person = new TblPerson();
person.Id = Convert.ToInt32(lblId.Text);
person.Name = tbxName.Text.Trim();
person.Age = Convert.ToInt32(tbxAge.Text.Trim());
person.Height = Convert.ToInt32(tbxHeight.Text.Trim());
person.Gender = cbxGender.SelectedIndex == 0 ? null : (bool?)(cbxGender.SelectedIndex == 1 ? true : false);
//2、实例化业务逻辑层，调用方法
TblPersonBll bll = new TblPersonBll();
if (bll.UpdatePerson(person))
{
//重新加载数据
LoadPersonData();
MessageBox.Show("修改成功！");
}
}
public ActionResult PersonelInfoUpdate(Models.Person person)
{
TblPerson tblPerson = new TblPerson();
string sqlStr = String.Format("update People set Name='{0}',Surname='{1}',Address='{2}',Mail='{3}',Description='{4}',Linkedin='{5}',Github='{6}',Twitter='{7}',Facebook='{8}',Youtube='{9}',Instagram='{10}' where ID='{11}'",
HttpUtility.UrlDecode(person.Name),
HttpUtility.UrlDecode(person.Surname),
HttpUtility.UrlDecode(person.Address),
HttpUtility.UrlDecode(person.Mail),
HttpUtility.UrlDecode(person.Description),
HttpUtility.UrlDecode(person.Linkedin),
HttpUtility.UrlDecode(person.Github),
HttpUtility.UrlDecode(person.Twitter),
HttpUtility.UrlDecode(person.Facebook),
HttpUtility.UrlDecode(person.Youtube),
HttpUtility.UrlDecode(person.Instagram),
person.ID
);
int status = tblPerson.ExecuteQuery(sqlStr);
return RedirectToAction("About");
}
/// <summary>
/// 更新行的内容，为了在Upadate数据的时候，不至于用户只更新一列的时候，其他列为空
/// </summary>
/// <param name="autoId"></param>
/// <returns></returns>
public TblPerson GetModelById(int autoId)
{
string sql = "select * from TblPerson where autoId=@id";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text, new SqlParameter("@id", autoId)))
{
if (reader.HasRows)
{
if (reader.Read())
{
TblPerson model = new TblPerson();
model.Age = reader.GetInt32(2);
model.AutoId = reader.GetInt32(0);
model.Gender = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
model.Height = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3);
model.Uname = reader.GetString(1);
return(model);
}
}
return(null);
}
}
public async Task <IActionResult> EditReservation(ReservationModel reservationModel)
{
if (ModelState.IsValid)
{
TblPerson tblPerson = new TblPerson
{
PersonId = reservationModel.PersonID,
FirstName = reservationModel.FirstName,
LastName = reservationModel.LastName,
City = reservationModel.City,
Country = reservationModel.Country,
EmailAddress = reservationModel.EmailAddress,
PhoneNumber = reservationModel.PhoneNumber,
PostalCode = reservationModel.PostalCode,
StreetName = reservationModel.StreetName,
StreetNumber = reservationModel.StreetNumber,
};
TblReservation tblReservation = new TblReservation
{
ReservationId = reservationModel.ReservationID,
PersonId = tblPerson.PersonId,
ExpectedArriveDate = reservationModel.ExpectedArrivalDate,
ExpectedLeaveDate = reservationModel.ExpectedLeaveDate,
RoomNumber = reservationModel.RoomNumber,
ReservationNotes = reservationModel.Notes
};
_context.Update(tblPerson);
_context.Update(tblReservation);
await _context.SaveChangesAsync();
return(Redirect("~/Concierge/Reservations"));
}
ViewData["RoomNumber"] = new SelectList(_context.TblRooms.Where(s => s.RoomStatus == 0), "RoomNumber", "RoomNumber");
return(View(reservationModel));
}
/// <summary>
/// 查询所有的数据，返回一个集合
/// </summary>
/// <returns></returns>
public List <TblPerson> SelectAll()
{
List <TblPerson> list = new List <TblPerson>();
string sql = "select * from TblPerson";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
while (reader.Read())
{
TblPerson model = new TblPerson();
model.Age = reader.GetInt32(2);
model.AutoId = reader.GetInt32(0);
model.Gender = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
model.Height = reader.IsDBNull(3) ? null : (int?)reader.GetInt32(3);
model.Uname = reader.GetString(1);
list.Add(model);
}
}
}
return(list);
}
public static ReturnValue Login(string username, string password = null)
{
if (password == null)
{
return(ReturnValue.WrongUserOrPassword);
}
HashAlgorithm hash = new SHA256Managed();
byte[] passwordBytes = Encoding.UTF8.GetBytes(password);
byte[] hashBytes = hash.ComputeHash(passwordBytes);
string passwordSHA256 = Convert.ToBase64String(hashBytes);
string passwordSHA2562 = GetSHA256Password(password);
TblPerson user = ContextMethods.GetUserFromLogin(username, password, passwordSHA256, passwordSHA2562);
if (user != null)
{
return(ReturnValue.Successful);
}
return(ReturnValue.WrongUserOrPassword);
}
/// <summary>
/// 查找所有记录
/// </summary>
/// <returns></returns>
public List <TblPerson> FindAll()
{
List <TblPerson> list = new List <TblPerson>();
string sql = "select * from TblPerson";
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql))
{
if (reader.HasRows)
{
//根据列名来获取索引【放在循环外边，这样可以提高效率。因为这个检索是非常耗时的】
#region 获取列名的索引
int IdIndex = reader.GetOrdinal("Id");
int NameIndex = reader.GetOrdinal("Name");
int AgeIndex = reader.GetOrdinal("Age");
int HeightIndex = reader.GetOrdinal("Height");
int GenderIndex = reader.GetOrdinal("Gender");
#endregion
#region 循环取数据
while (reader.Read())
{
TblPerson person = new TblPerson()
{
Id = reader.GetInt32(IdIndex),
Name = reader.GetString(NameIndex),
Age = reader.GetInt32(AgeIndex),
Height = reader.GetInt32(HeightIndex),
Gender = reader.IsDBNull(GenderIndex) ? null : (bool?)reader.GetBoolean(GenderIndex)//这个可能为空，取出来的时候要转换【注意，存和取，对空值的处理】
};
list.Add(person);
}
#endregion
}
}
return(list);
}
/// <summary>
/// 插入一条记录
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(TblPerson model)
{
string sql = "insert into TblPerson values(@Name,@Age,@Height,@Gender)";
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@Name", SqlDbType.NVarChar, 50)
{
Value = model.Name
}, //如果这里不写数据类型，它内部会帮我们推断一个类型，我们显式的写了，就不需要内部推断了。
new SqlParameter("@Age", SqlDbType.Int)
{
Value = model.Age
},
new SqlParameter("@Height", SqlDbType.Int)
{
Value = model.Height
},
new SqlParameter("@Gender", SqlDbType.Bit)
{
Value = model.Gender == null ? DBNull.Value:(object)model.Gender
} //【C#中的 null值 转换为 数据库中的 DBNull.Value,if-else返回类型不统一，任意一个转为object类型】
};
return(SqlHelper.ExcuteNonQuery(sql, CommandType.Text, pms));
}
public int Insert(TblPerson model)
{
return((int)dal.Insert(model));
}
public int Update(TblPerson model)
{
return(dal.Update(model));
}
public void PhotoPathUpdate(string photoPath)
{
TblPerson tblPerson = new TblPerson();
string sqlStr = String.Format("update People set Photo='{0}' where ID= (select top 1 ID from People order by ID)", photoPath);
int status = tblPerson.ExecuteQuery(sqlStr);
}
