/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Notice> GetList()
{
string sql = "select * from Notice";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Notice> GetList = new List <Notice>();
foreach (DataRow item in dt.Rows)
{
Notice entity = new Notice();
entity.NoticeID = int.Parse(item["NoticeID"].ToString());
entity.NoticeType = int.Parse(item["NoticeType"].ToString());
entity.NoticeTitle = item["NoticeTitle"].ToString();
entity.NoticeContent = item["NoticeContent"].ToString();
entity.UserID = int.Parse(item["UserID"].ToString());
entity.NoticeStateTime = DateTime.Parse(item["NoticeStateTime"].ToString());
entity.NoticeEndTime = DateTime.Parse(item["NoticeEndTime"].ToString());
entity.NoticeState = int.Parse(item["NoticeState"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Assessment> GetList()
{
string sql = "select * from Assessment";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Assessment> GetList = new List <Assessment>();
foreach (DataRow item in dt.Rows)
{
Assessment entity = new Assessment();
entity.AssessmentID = int.Parse(item["AssessmentID"].ToString());
entity.PerformanceTime = DateTime.Parse(item["PerformanceTime"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.WorkSummary = item["WorkSummary"].ToString();
entity.UpperGoal = item["UpperGoal"].ToString();
entity.CompletionDegree = int.Parse(item["CompletionDegree"].ToString());
entity.ExaminationItems = item["ExaminationItems"].ToString();
entity.NextStageObjectives = item["NextStageObjectives"].ToString();
entity.PerformanceScore = int.Parse(item["PerformanceScore"].ToString());
entity.comments = item["comments"].ToString();
entity.perstate = int.Parse(item["perstate"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Leave> GetList()
{
string sql = " select * from Leave";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Leave> GetList = new List <Leave>();
foreach (DataRow item in dt.Rows)
{
Leave entity = new Leave();
entity.LeaveID = int.Parse(item["LeaveID"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.LeaveState = int.Parse(item["LeaveState"].ToString());
entity.LeaveTime = DateTime.Parse(item["LeaveTime"].ToString());
entity.LeaveStartTime = DateTime.Parse(item["LeaveStartTime"].ToString());
entity.LeaveEndTime = DateTime.Parse(item["LeaveEndTime"].ToString());
entity.LeaveHalfDay = item["LeaveHalfDay"].ToString();
entity.LeaveDays = int.Parse(item["LeaveDays"].ToString());
entity.LeaveReason = item["LeaveReason"].ToString();
entity.ApproverID = int.Parse(item["ApproverID"].ToString());
entity.ApprovalTime = DateTime.Parse(item["ApprovalTime"].ToString());
entity.ApproverReason = item["ApproverReason"].ToString();
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <PaySlipID> GetList()
{
string sql = "select * from PaySlipID";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <PaySlipID> GetList = new List <PaySlipID>();
foreach (DataRow item in dt.Rows)
{
PaySlipID entity = new PaySlipID();
entity.id = int.Parse(item["id"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.BasicSalary = decimal.Parse(item["BasicSalary"].ToString());
entity.AttendanceBonus = decimal.Parse(item["AttendanceBonus"].ToString());
entity.Fine = decimal.Parse(item["Fine"].ToString());
entity.SalaryTime = DateTime.Parse(item["SalaryTime"].ToString());
entity.SalarySum = decimal.Parse(item["SalarySum"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <CategoryItems> GetList()
{
string sql = "select * from CategoryItems";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <CategoryItems> GetList = new List <CategoryItems>();
foreach (DataRow item in dt.Rows)
{
CategoryItems entity = new CategoryItems();
entity.CID = int.Parse(item["CID"].ToString());
entity.C_Category = item["C_Category"].ToString();
entity.CI_ID = int.Parse(item["CI_ID"].ToString());
entity.CI_Name = item["CI_Name"].ToString();
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Overtime> GetList()
{
string sql = "select * from Overtime";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Overtime> GetList = new List <Overtime>();
foreach (DataRow item in dt.Rows)
{
Overtime entity = new Overtime();
entity.OvertimeID = int.Parse(item["OvertimeID"].ToString());
entity.OvertimeStateTime = DateTime.Parse(item["OvertimeStateTime"].ToString());
entity.OvertimeEndTime = DateTime.Parse(item["OvertimeEndTime"].ToString());
entity.OvertimeDuration = int.Parse(item["OvertimeDuration"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.ApplyTime = DateTime.Parse(item["ApplyTime"].ToString());
entity.OvertimeState = int.Parse(item["OvertimeState"].ToString());
entity.ApproverReason = item["ApproverReason"].ToString();
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <AttendanceSheet> GetList()
{
string sql = "select * from AttendanceSheet";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <AttendanceSheet> GetList = new List <AttendanceSheet>();
foreach (DataRow item in dt.Rows)
{
AttendanceSheet entity = new AttendanceSheet();
entity.AttendanceID = int.Parse(item["AttendanceID"].ToString());
entity.AttendanceStartTime = DateTime.Parse(item["AttendanceStartTime"].ToString());
entity.AttendanceType = int.Parse(item["AttendanceType"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.ClockTime = DateTime.Parse(item["ClockTime"].ToString());
entity.ClockOutTime = DateTime.Parse(item["ClockOutTime"].ToString());
entity.Workinghours = int.Parse(item["Workinghours"].ToString());
entity.remake = item["remake"].ToString();
entity.Late = int.Parse(item["Late"].ToString());
entity.Absenteeism = int.Parse(item["Absenteeism"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <PayRise> GetList()
{
string sql = "select * from PayRise";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <PayRise> GetList = new List <PayRise>();
foreach (DataRow item in dt.Rows)
{
PayRise entity = new PayRise();
entity.PayRiseID = int.Parse(item["PayRiseID"].ToString());
entity.UserID = int.Parse(item["UserID"].ToString());
entity.PayRiseMoney = decimal.Parse(item["PayRiseMoney"].ToString());
entity.Reason = item["Reason"].ToString();
entity.ApplicationTime = DateTime.Parse(item["ApplicationTime"].ToString());
entity.ApprovalContent = item["ApprovalContent"].ToString();
entity.ApprovalState = int.Parse(item["ApprovalState"].ToString());
entity.ApprovalTime = DateTime.Parse(item["ApprovalTime"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <SystemLog> GetList()
{
string sql = "select * from SystemLog";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <SystemLog> GetList = new List <SystemLog>();
foreach (DataRow item in dt.Rows)
{
SystemLog entity = new SystemLog();
entity.LogID = int.Parse(item["LogID"].ToString());
entity.userID = int.Parse(item["userID"].ToString());
entity.LogTime = DateTime.Parse(item["LogTime"].ToString());
entity.LogOperation = item["LogOperation"].ToString();
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 分页
/// </summary>
/// <param name="entity"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <Entity.UserInfo> GetPage(Entity.UserInfo entity, int PageIndex, int PageSize, out int count)
{
string sqlwhere = "";
if (entity.DepartmentID != 0)
{
sqlwhere += " and UserInfo.DepartmentID=" + entity.DepartmentID;
}
if (entity.UserName != null && !entity.UserName.Equals(""))
{
sqlwhere += " and UserInfo.UserName like '%" + entity.UserName + "%'";
}
string sql = "select count(*) from UserInfo where 1=1 " + sqlwhere;
count = (int)db.ExecScalar();
sql = @"select * from (
select UserInfo.*,ROW_NUMBER() over(order by UserInfo.UserID) rowid,Department.DepartmentName
from UserInfo inner join Department on UserInfo.DepartmentID=Department.DepartmentID
where 1=1 " + sqlwhere + ") tamp where rowid between @PageIndex and @PageSize";
db.PrepareSql(sql);
db.SetParameter("PageIndex", (PageIndex - 1) * PageSize + 1);
db.SetParameter("PageSize", PageIndex * PageSize);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Entity.UserInfo> GetList = new List <UserInfo>();
foreach (DataRow item in dt.Rows)
{
entity.UserID = int.Parse(item["UserID"].ToString());
entity.DepartmentID = int.Parse(item["DepartmentID"].ToString());
entity.RoleID = int.Parse(item["RoleID"].ToString());
entity.UserNumber = item["UserNumber"].ToString();
entity.LoginName = item["LoginName"].ToString();
entity.LoginPwd = item["LoginPwd"].ToString();
entity.UserName = item["UserName"].ToString();
entity.UserAge = int.Parse(item["UserAge"].ToString());
entity.UserSex = int.Parse(item["UserSex"].ToString());
entity.UserTel = item["UserTel"].ToString();
entity.UserAddress = item["UserAddress"].ToString();
entity.UserIphone = item["UserIphone"].ToString();
entity.UserRemarks = item["UserRemarks"].ToString();
entity.UserStatr = int.Parse(item["UserStatr"].ToString());
entity.EntryTime = DateTime.Parse(item["EntryTime"].ToString());
entity.DimissionTime = DateTime.Parse(item["DimissionTime"].ToString());
entity.BasePay = decimal.Parse(item["BasePay"].ToString());
entity.department = new Department();
entity.department.DepartmentName = item["DepartmentName"].ToString();
GetList.Add(entity);
}
return(GetList);
}
//更新管理员信息
public int UpdateAdministrator(Model.Administrator admin)
{
string sql = "Update [Administrator] set [State]=0";
if (!string.IsNullOrEmpty(admin.APassword))
{
sql += ",[APassword]='" + admin.APassword + "'";
}
if (!string.IsNullOrEmpty(admin.AdminName))
{
sql += ",[AdminName]='" + admin.AdminName + "'";
}
if (!string.IsNullOrEmpty(admin.Account))
{
sql += ",[Account]='" + admin.Account + "'";
}
if (!string.IsNullOrEmpty(admin.Explains))
{
sql += ",[Explains]='" + admin.Explains + "'";
}
sql += " where [AdminID]='" + admin.AdminID + "'";
return(DBHelper.ExecQuery(sql));
}
public User GetUserByUserNameAndPassword(string username, string password)
{
if ((username == null) || (password == null))
{
return(null);
}
Regex regex = new Regex("[a-zA-Z0-9_]");
MatchCollection matchCollectionUserName = regex.Matches(username);
MatchCollection matchCollectionPassword = regex.Matches(password);
if (matchCollectionUserName.Count < username.Length || matchCollectionPassword.Count < password.Length)
{
return(null);
}
query = $@"select * from Users where userName = '******' and userPassWord = '******'";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("Không thể kết nối tới cơ sở dữ liệu");
return(null);
}
User user = null;
if (reader.Read())
{
user = GetUser(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(user);
}
//更新宿舍日常管理信息
public int UpdateManagementDAL(Model.Management ma)
{
string sql = "update [Management] set [State]=0";
if (!string.IsNullOrEmpty(ma.dormitoryID.ToString()))
{
sql += ",[dormitoryID]='" + ma.dormitoryID + "'";
}
if (!string.IsNullOrEmpty(ma.Hygiene))
{
sql += ",[Hygiene]='" + ma.Hygiene + "'";
}
if (!string.IsNullOrEmpty(ma.Inspectors))
{
sql += ",[Inspectors]='" + ma.Inspectors + "'";
}
if (!string.IsNullOrEmpty(ma.ManagementTime.ToString()))
{
sql += ",[ManagementTime]='" + ma.ManagementTime + "'";
}
if (!string.IsNullOrEmpty(ma.Explain))
{
sql += ",[Explain]='" + ma.Explain + "'";
}
sql += " where [ManagementID]=" + ma.ManagementID;
return(DBHelper.ExecQuery(sql));
}
public Customer GetCustomerByUserNameAndPassWord(string username, string password)
{
if ((username == null) || (password == null))
{
return(null);
}
Regex regex = new Regex("[a-zA-Z0-9_]");
MatchCollection matchCollectionUserName = regex.Matches(username);
MatchCollection matchCollectionPassWord = regex.Matches(password);
if (matchCollectionUserName.Count < username.Length || matchCollectionPassWord.Count < password.Length)
{
return(null);
}
query = $@"select * from Customers where customer_account = '{username}' and customer_password = '******'";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("Can not connect to database!");
return(null);
}
Customer customer = null;
if (reader.Read())
{
customer = GetCustomer(reader);
}
reader.Close();
DBHelper.CloseConnection();
return(customer);
}
public List <Shoes> GetListShoes(int PageIndex = 1)
{
int PageSize = 10;
DBHelper.OpenConnection();
var index = PageSize * (PageIndex - 1);
query = @"select it.item_id,it.item_name,it.item_price,it.item_color,it.item_material,it.item_trademark,itd.item_size,itd.item_quantity from items it, itemdetails itd where it.item_id = itd.item_id order by it.item_id limit " + PageSize + " offset " + index + "";
List <Shoes> shoes = new List <Shoes>();
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception Exception)
{
Console.WriteLine(Exception);
Console.WriteLine("Can not connect database!");
return(null);
}
while (reader.Read())
{
shoes.Add(GetShoesInfo(reader));
}
reader.Close();
DBHelper.CloseConnection();
return(shoes);
}
public List <Shoes> ShowShopingCartByUserId(int?userId)
{
if (userId == null)
{
return(null);
}
List <Shoes> listShoes = new List <Shoes>();
query = @"select it.item_id, it.item_name, it.item_price, ordt.amount from
Orders ord inner join Orderdetails ordt on ord.order_id = ordt.order_id
inner join Items it on ordt.item_id = it.item_id where ord.order_customer = " + userId + " and ord.order_status = 0 ;";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("can not connect ok");
return(null);
}
while (reader.Read())
{
listShoes.Add(GetItemShoppingCart(reader));
}
DBHelper.CloseConnection();
return(listShoes);
}
public Orders GetOrderDetailByOrderID(int?orderId)
{
query = @"Select Orders.OrderID ,Customers.CusID,Customers.CusName,Customers.Address,Orders.OrderDate,Items.ItemID,Items.ItemName,Items.ItemPrice,OrderDetail.Size,OrderDetail.ItemCount,Orders.OrderStatus
from Orders inner join Customers on Orders.CusID = Customers.CusID inner join OrderDetail on Orders.OrderID = OrderDetail.OrderID inner join Items on Orderdetail.ItemID = Items.ItemID where Orders.OrderId = " + orderId + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
Orders or = new Orders();
or.Items = new List <Items>();
while (reader.Read())
{
or.OrderID = reader.GetInt32("OrderID");
or.Customer = new Customer();
or.Customer.CusID = reader.GetInt32("CusID");
or.Customer.CusName = reader.GetString("CusName");
or.Customer.Address = reader.GetString("Address");
or.OrderDate = reader.GetDateTime("OrderDate");
or.Status = reader.GetString("OrderStatus");
Items item = new Items();
item.ItemID = reader.GetInt32("ItemID");
item.ItemName = reader.GetString("ItemName");
item.ItemCount = reader.GetInt32("ItemCount");
item.ItemPrice = reader.GetDecimal("ItemPrice");
item.Size = reader.GetString("Size");
or.Items.Add(item);
}
DBHelper.CloseConnection();
return(or);
}
public List <Order> ShowAllItemOrdered(int?userId)
{
if (userId == null)
{
return(null);
}
List <Order> listOrders = new List <Order>();
query = $@"select it.item_id, it.item_name, ord.order_date from
Orders ord inner join Orderdetails ordt on ord.order_id = ordt.order_id
inner join Items it on ordt.item_id = it.item_id
where ord.order_customer = {userId} and ord.order_status = 1 group by it.item_name";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("can not connect 1");
return(null);
}
while (reader.Read())
{
listOrders.Add(GetOrder(reader));
}
DBHelper.CloseConnection();
return(listOrders);
}
public List <Order> ShowAllItemOrder(int?userID)
{
if (userID == null)
{
return(null);
}
List <Order> listOrders = new List <Order>();
query = $@"select it.itemID, it.itemName, ord.orderDate from Orders ord inner join OrderDetails ordls on ord.orderID = ordls.orderID
inner join Items it on ordls.itemID = it.itemID where ord.orderUser = {userID} and ord.cartStatus = 1 group by it.itemName ;";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("Không thể kết nối với database");
return(null);
}
while (reader.Read())
{
listOrders.Add(GetOrder(reader));
}
DBHelper.CloseConnection();
return(listOrders);
}
public List <Order> ShowOrderUserPaySucess(int?userID)
{
if (userID == null)
{
return(null);
}
List <Order> orders = new List <Order>();
query = $@"select ord.orderID as orderID, ord.orderDate, it.itemID, it.itemName, itdls.itemSize, itdls.itemPrice, us.userAccount, us.userEmail from Users us
inner join Orders ord on ord.orderUser = us.userID
inner join orderDetails ordls on ord.orderID = ordls.orderID
inner join Items it on ordls.itemID = it.itemID
inner join ItemDetails itdls on itdls.itemID = it.itemID
where ord.orderUser = {userID} and ord.orderID = {GetLastInsertOrderID(userID)} ;";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
while (reader.Read())
{
orders.Add(GetOrderPurchaseSucess(reader));
}
reader.Close();
DBHelper.CloseConnection();
return(orders);
}
public List <Item> ShowShoppingCartByUserID(int?userID)
{
if (userID == null)
{
return(null);
}
List <Item> listItems = new List <Item>();
query = $@"select it.itemID, it.itemName, itdls.itemPrice, itdls.itemSize from Orders ord
inner join OrderDetails ordls on ord.orderID = ordls.orderID
inner join Items it on ordls.itemID = it.itemID
inner join ItemDetails itdls on itdls.itemID = it.itemID
where ord.orderUser = {userID} and ord.cartStatus = 0 ;";
try
{
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
}
catch (System.Exception)
{
Console.WriteLine("Không thể kết nối với cơ sở dữ liệu !!!.");
return(null);
}
while (reader.Read())
{
listItems.Add(GetItemShoppingCart(reader));
}
DBHelper.CloseConnection();
return(listItems);
}
//更新来访信息
public int UpdateVisitingDAL(Model.Visiting vis)
{
string sql = "update [Visiting] set [State]=0";
if (!string.IsNullOrEmpty(vis.dormitoryID.ToString()))
{
sql += ",[dormitoryID]='" + vis.dormitoryID + "'";
}
if (!string.IsNullOrEmpty(vis.VisitingName))
{
sql += ",[VisitingName]='" + vis.VisitingName + "'";
}
if (!string.IsNullOrEmpty(vis.VisitingTime.ToString()))
{
sql += ",[VisitingTime]='" + vis.VisitingTime + "'";
}
if (!string.IsNullOrEmpty(vis.LikaiTime.ToString()))
{
sql += ",[LikaiTime]='" + vis.LikaiTime + "'";
}
sql += " where [VisitingID]=" + vis.VisitingID;
return(DBHelper.ExecQuery(sql));
}
//更新学员信息
public int UpdateStudentDAL(Model.Student stu)
{
string sql = "Update [Student] set [State]=0";
if (!string.IsNullOrEmpty(stu.StudentName))
{
sql += ",[StudentName]='" + stu.StudentName + "'";
}
if (!string.IsNullOrEmpty(stu.Age.ToString()))
{
sql += ",[Age]='" + stu.Age + "'";
}
if (!string.IsNullOrEmpty(stu.Gender))
{
sql += ",[Gender]='" + stu.Gender + "'";
}
if (!string.IsNullOrEmpty(stu.dormitoryID.ToString()))
{
sql += ",[dormitoryID]='" + stu.dormitoryID + "'";
}
if (!string.IsNullOrEmpty(stu.Place))
{
sql += ",[Place]='" + stu.Place + "'";
}
if (!string.IsNullOrEmpty(stu.Accommodationtime.ToString()))
{
sql += ",[Accommodationtime]='" + stu.Accommodationtime + "'";
}
if (!string.IsNullOrEmpty(stu.leavetime.ToString()))
{
sql += ",[leavetime]='" + stu.leavetime + "'";
}
if (!string.IsNullOrEmpty(stu.Phone))
{
sql += ",[Phone]='" + stu.Phone + "'";
}
if (!string.IsNullOrEmpty(stu.Explains))
{
sql += ",[Explains]='" + stu.Explains + "'";
}
sql += " where [StudentID]='" + stu.StudentID + "'";
return(DBHelper.ExecQuery(sql));
}
public List <Items> GetItems()
{
query = $"select * from Items;";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
List <Items> items = null;
if (reader != null)
{
items = GetItemsInfo(reader);
}
DBHelper.CloseConnection();
return(items);
}
public Items GetItemByID(int?ITemID)
{
query = $"select * from Items where ItemID = " + ITemID + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
Items item = null;
if (reader.Read())
{
item = GetItem(reader);
Console.WriteLine(item.ItemName);
}
DBHelper.CloseConnection();
return(item);
}
public int GetLastInsertOrderID(int?UserID)
{
int orderId = -1;
string queryLastInsertId = @"select order_id from Orders where order_customer = " + UserID + " order by order_id desc limit 1;";
reader = DBHelper.ExecQuery(queryLastInsertId, DBHelper.OpenConnection());
if (reader.Read())
{
orderId = reader.GetInt32("order_id");
}
reader.Close();
return(orderId);
}
public int GetLastInsertOrderID(int?userID)
{
int orderID = -1;
string queryLastInsertID = $@"select orderID from Orders where orderUser = {userID} order by orderID desc limit 1;";
reader = DBHelper.ExecQuery(queryLastInsertID, DBHelper.OpenConnection());
if (reader.Read())
{
orderID = reader.GetInt32("orderID");
}
reader.Close();
return(orderID);
}
public List <Orders> GetOrdersByCustomerId(int customerId)
{
query = @"select * from Orders where CusID = " + customerId + ";";
reader = DBHelper.ExecQuery(query, DBHelper.OpenConnection());
List <Orders> orders = null;
if (reader != null)
{
orders = GetListOrdersInfo(reader);
}
DBHelper.CloseConnection();
return(orders);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Department> GetList()
{
string sql = "select * from Department";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Department> GetList = new List <Department>();
foreach (DataRow item in dt.Rows)
{
Department entity = new Department();
entity.DepartmentID = int.Parse(item["DepartmentID"].ToString());
entity.DepartmentName = item["DepartmentName"].ToString();
entity.DepartmentRemarks = int.Parse(item["DepartmentRemarks"].ToString());
GetList.Add(entity);
}
return(GetList);
}
/// <summary>
/// 查询
/// </summary>
/// <returns></returns>
public List <Role> GetList()
{
string sql = "select * from Role";
db.PrepareSql(sql);
DataTable dt = new DataTable();
dt = db.ExecQuery();
if (dt.Rows.Count == 0)
{
return(null);
}
List <Role> GetList = new List <Role>();
foreach (DataRow item in dt.Rows)
{
Role entity = new Role();
entity.RoleID = int.Parse(item["RoleID"].ToString());
entity.RoleName = item["RoleName"].ToString();
entity.RoleNumber = item["RoleNumber"].ToString();
GetList.Add(entity);
}
return(GetList);
}