/// <summary> /// 查询所有老师信息 /// </summary> /// <returns></returns> public static DataTable TeacherQuery(string TName, int Tid) { string WhereJ = ""; if (TName != "") { WhereJ += " and T_Name like '%" + TName + "%'"; } if (Tid == 0 || Tid == 1) { WhereJ += " and T_Category=" + Tid; } string sql = "select* from Teachers a left join OperationLogs b on a.T_ID = b.T_ID where 1=1" + WhereJ; DataTable dt = DBHelper.GetDataTable(sql, null); return(dt); }
/// <summary> /// 查询所有实训班级信息 /// </summary> /// <param name="T_Name"></param> /// <param name="T_Category"></param> /// <returns></returns> public static DataTable TrainClassQuery(string T_Name, int T_id) { string whereJIao = ""; if (T_Name != "") { whereJIao += " and TC_Name like '%" + T_Name + "%'"; } if (T_id != 0) { whereJIao += " and T_ID =" + T_id; } string sql = " select * from stu_TeacherQURY where 1=1" + whereJIao; DataTable dt = DBHelper.GetDataTable(sql, null); return(dt); }
public static DataTable ProjectsByUserID(int UserID, DateTime indexDate) { try { DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH); string sql = "Select DISTINCT TOP 10 Projects.* FROM " + "(((Users INNER JOIN ProjectRequests ON Users.ID = ProjectRequests.UserID) " + "INNER JOIN ProjectPositions ON ProjectPositions.ID = ProjectRequests.PositionID) INNER JOIN Projects ON ProjectPositions.ProjectID = Projects.ProjectID) WHERE (ProjectPositions.UserID = " + UserID + " OR Projects.AdminUsID = " + UserID + ") AND Projects.ProjectStatus <> 3" + $"AND Projects.DateCreated< FORMAT(#{indexDate}#, 'mm / dd / yyyy hh: nn: ss') ORDER BY Projects.DateCreated DESC;"; DataTable dt = helper.GetDataTable(sql); return(dt); } catch (Exception e) { Console.WriteLine(e.ToString()); return(null); } }
/// <summary> /// returns userrate by his id. /// </summary> public static int ReturnUserRate(int UserID) { DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH); string sql = $"SELECT Users.UserRate FROM Users WHERE ID = {UserID};"; DataTable dt = helper.GetDataTable(sql); DataRow user = dt.Rows[0]; int rate = 0; if (user != null) { rate = (int)user["UserRate"]; return(rate); } else { return(rate); } }
/// <summary> /// 学校和省的连接查询 /// </summary> /// <param name="Pid"></param> /// <param name="School_Name"></param> /// <returns></returns> public static DataTable ProvicesQueryS(int Pid, int School_ID) { string whereJi = ""; if (Pid != 0) { whereJi += " and Provinces.P_ID like'%" + Pid + "%' "; } if (School_ID != 0) { whereJi += " and School_ID like '%" + School_ID + "%'"; } string sql = " select * from Schools , Provinces where Schools.P_ID = Provinces.P_ID and 1=1 " + whereJi; DataTable dt = DBHelper.GetDataTable(sql, null); return(dt); }
/// <summary> /// gets a user by his username and email. /// </summary> public static DataTable RetrieveUserTableByCredentials(string UsNa, string email, string country, string lang) { try { DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH); string sql = $"SELECT Users.ID, Users.UserName, Users.Pass, Users.Email, Users.Birthday, Languages.LangName, " + $"Countries.Name AS Country, Users.WeeklyFreeTime, Users.NumRateVoters, Users.UserRate, Users.IsBanned, Users.RegistrationDate, Users.LogInDate, Users.UserType " + $"FROM((Users " + $"INNER JOIN Languages ON Users.NativeLang = Languages.ID) " + $"INNER JOIN Countries ON Users.Country = Countries.ID) " + $"WHERE UserName LIKE '{UsNa}%' AND Email LIKE '{email}%' AND Countries.Name LIKE '{country}%' AND Languages.LangName LIKE '{lang}%' ORDER BY(Users.ID); "; DataTable userTable = helper.GetDataTable(sql); return(userTable); } catch (Exception) { return(null); } }
/// <summary> /// 查询书本表 /// </summary> /// <returns></returns> public static List <Books> GetBooks() { string sql = "select * from Books"; DataTable dt = DBHelper.GetDataTable(sql); List <Books> list = new List <Books>(); Books books = new Books(); foreach (DataRow rows in dt.Rows) { books.BooksID = int.Parse(rows["BooksID"].ToString()); books.BooksName = rows["BooksName"].ToString(); books.SalePrice = decimal.Parse(rows["SalePrice"].ToString()); books.StorePrice = decimal.Parse(rows["StorePrice"].ToString()); books.StockDate = DateTime.Parse(rows["StockDate"].ToString()); books.StockNum = int.Parse(rows["StockNum"].ToString()); books.TypeID = int.Parse(rows["TypeID"].ToString()); list.Add(books); } return(list); }
/// <summary> /// 根据校区编号查询所有校区专业班级信息 /// </summary> /// <param name="Sid"></param> /// <returns></returns> public static DataTable SchoolClassQuery(int Sid, int SP_id, string Cname) { string where = ""; if (SP_id != 0) { where += " and Specialty_ID= " + SP_id; } else if (Cname != "") { where += " and School_Name='" + Cname + "'"; } string sql = " select * from Schools c left join (select a.SC_ID,a.SC_Name,a.School_ID,a.SC_FemaleAmount,a.SC_MaleAmount,a.SC_Supervisor,a.SC_Teacher,b.Specialty_ID,b.Specialty_Name from SchoolClasses a,Specialties b where a.Specialty_ID=b.Specialty_ID) h on c.School_ID=h.School_ID where c.School_ID=@School_ID " + where; SqlParameter[] para = { new SqlParameter("School_ID", Sid) }; DataTable dt = DBHelper.GetDataTable(sql, para); return(dt); }
//查询所有商品信息 public static List <MODGoodsInformation> DALSelectGoodsInformationAll() { string sqlGoodsInformation = string.Format("select * from Goods,MaxType where Goods.MaxTypeID=MaxType.MaxTypeID"); DataTable dt = DBHelper.GetDataTable(sqlGoodsInformation); //实例化泛型集合 List <MODGoodsInformation> listGoodsInformation = new List <MODGoodsInformation>(); //模型类获取值 for (int i = 0; i < dt.Rows.Count; i++) { //实例化模型类 MODGoodsInformation goods = new MODGoodsInformation(); goods.GoodsID = int.Parse(dt.Rows[i][0].ToString()); goods.GoodName = dt.Rows[i][1].ToString(); goods.GoodsTypeName = dt.Rows[i][7].ToString(); goods.Price = double.Parse(dt.Rows[i][3].ToString()); //goods.Count = int.Parse(dt.Rows[i][4].ToString()); //填充 listGoodsInformation.Add(goods); } return(listGoodsInformation); }
/// <summary> /// 查询所有分班管理信息 /// </summary> /// <returns></returns> public static DataTable TrainClassStudentQuery(int classstu, int P_id, int Sid, int Scid, int StionID) { string sql = ""; string whereJoao = ""; if (P_id != 0) { whereJoao += " and P_ID = " + P_id; } if (Sid != 0) { whereJoao += " and School_ID =" + Sid; } if (Scid != 0) { whereJoao += " and SC_ID = " + Scid; } if (StionID != 0) { whereJoao += " and Station_ID =" + StionID; } if (classstu == 1) //未分班 { sql = "select * from view_Stu where Student_ID not in(select Student_ID from TrainClassStudents) " + whereJoao; } else if (classstu == 2) //已分班 { sql = "select * from view_Stu where Student_ID in(select Student_ID from TrainClassStudents) " + whereJoao; } else //全部 { sql = " select * from view_Stu where 1=1" + whereJoao; } DataTable dt = DBHelper.GetDataTable(sql, null); return(dt); }
/// <summary> /// 插入一条数据 /// </summary> /// <param name="heat"></param> public static void InsertIntoOneHeatIntoTmpTable(CjdHeat heat) { //从数据库里读,不自己构建了 DataTable dtEndTable = DBHelper.GetDataTable("select top 1 * from Measure_heat"); DataRow tmpRow = dtEndTable.NewRow(); tmpRow["表地址"] = heat.Address; tmpRow["上次抄表热量"] = heat.LastHeat; tmpRow["当前热量"] = heat.CurrentHeat; tmpRow["热功率"] = heat.ThermalPower; tmpRow["瞬时流量"] = heat.FlowRate; tmpRow["累计流量"] = heat.TotalFlow; tmpRow["供水温度"] = heat.SupplyWaterTemperature; tmpRow["回水温度"] = heat.BackWaterTemperature; tmpRow["温差"] = heat.TemperateDifference; tmpRow["累计工作时间"] = heat.TotalTime; tmpRow["实时时间"] = DateTime.Now.ToString(); tmpRow["采集时间"] = DateTime.Now.ToString(); tmpRow["单价"] = 1; tmpRow["通讯状态"] = 1; tmpRow["社区编号"] = heat.AreaID; tmpRow["楼房编号"] = heat.BuildID; tmpRow["楼层"] = 1; tmpRow["单元编号"] = heat.UnitID; tmpRow["房间号"] = heat.HourseID; tmpRow["户主编号"] = heat.UserID; tmpRow["户主姓名"] = heat.UserRealName; ; tmpRow["室温"] = 19; tmpRow["设定室温"] = 19; tmpRow["阀门状态"] = 1; dtEndTable.Rows.Add(tmpRow); }
/// <summary> /// this method returns Users from the database, but it can filter them by /// 1. profession 2. age 3. the weekly free time they chose 4. their native language 5. their year of birth(their age) /// </summary> /// <param name="profID">default is -1</param> /// <param name="age">default year is 1940 in order to show all users(without date age filter)</param> /// <param name="langID">if unfiltered must be set to -1</param> /// <param name="WeeklyFreeTime">default is 0</param> /// <param name="userRate">default is 0</param> /// <param name="IndexUserID">should be updated in order to load more users from the table</param> /// <returns>a datatable that contains the user's credentials. indexUserID needs to be loaded to viewstate in order to load more users</returns> public static DataTable ShowUsers(int profID, DateTime age, int langID, int WeeklyFreeTime, int userRate, int IndexUserID) { DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH); string query = $"SELECT DISTINCT TOP 10 Users.* " + $"FROM Users INNER JOIN UserProf ON Users.ID = UserProf.UserID " + $"WHERE Users.Birthday <= #{age.ToString("MM/dd/yyyy HH:mm:ss")}# " + $"AND Users.WeeklyFreeTime >= {WeeklyFreeTime} AND Users.ID > {IndexUserID} AND (Users.UserRate / IIF(Users.NumRateVoters = 0, 1, Users.NumRateVoters) >= {userRate})"; if (langID != -1) { query += $" AND Users.NativeLang = {langID} "; } if (profID != -1) { query += $" AND UserProf.ProfID = {profID} "; } query += $"ORDER BY Users.ID; "; DataTable dt = helper.GetDataTable(query); return(dt); }
/// <summary> /// 新增一个实训班级信息 /// </summary> /// <param name="T"></param> /// <param name="C"></param> /// <returns></returns> public static bool TeacherClassAdd(TrainClassesInfo T, int jyid, int ddid) { string sql = "insert into TrainClasses(TC_Name, TC_Grade, TC_BeginTime,TC_EndTime, TC_MaxAmount, TC_MaleAmount, TC_FemaleAmount, TC_Period)values(@TC_Name, @TC_Grade, @TC_BeginTime, @TC_EndTime, @TC_MaxAmount, '4', '1',@TC_Period); select @@IDENTITY as id;"; SqlParameter[] para = { new SqlParameter("TC_Name", T.TC_Name), new SqlParameter("TC_Grade", T.TC_Grade), new SqlParameter("TC_BeginTime", T.TC_BeginTime), new SqlParameter("TC_EndTime", T.TC_EndTime), new SqlParameter("TC_MaxAmount", T.TC_MaxAmount), new SqlParameter("TC_Period", T.TC_Period) //new SqlParameter ("T_ID",C.T_ID) }; DataTable dt = DBHelper.GetDataTable(sql, para); string strSql = "insert into TrainClassTeachers "; //如果jyid,ddid不等于0时执行sql语句,可以同时追加两条 if (jyid != 0) { strSql += "select " + dt.Rows[0][0] + "," + jyid + " union "; } if (ddid != 0) { strSql += " select " + dt.Rows[0][0] + "," + ddid + " union "; } strSql = strSql.Substring(0, strSql.Length - 6);//去掉最后一个union(union)的字段为6 int i = DBHelper.ExecuteNonQuery(strSql, null); if (i > 0) { return(true); } else { return(false); } }
/// <summary> /// 根据校区编号查询所有校区信息 /// </summary> /// <param name="Pid"></param> /// <returns></returns> public static DataTable ProvinceSid(int Pid, string School_Code, string School_Name) { string WhereJia = ""; if (School_Code != null || School_Code != "") { WhereJia += " and School_Code like '%" + School_Code + "%' "; } if (School_Name != null || School_Name != "") { WhereJia += " and School_Name like '%" + School_Name + "%'"; } string sql = "select School_ID, School_Code,School_Name,School_Remark from Schools inner join Provinces on Schools.P_ID = Provinces.P_ID where Provinces.P_ID = @P_ID " + WhereJia; SqlParameter[] para = { new SqlParameter("School_Code", School_Code), new SqlParameter("School_Name", School_Name) }; DataTable dt = DBHelper.GetDataTable(sql, para); return(dt); }
//查询所有订单 public static List <MODOrder> DALSelectOrderAll() { string sqlOrder = string.Format("select Orders.OrdersID,Customer.UserName,Goods.GoodName,Orders.Time,(Orders.OrdersCount*Goods.Price)Price,Orders.OrdersCount,Orders.Introduction from Orders,Customer,Goods where Orders.UserID=Customer.UserID and Orders.GoodsID=Goods.GoodsID"); DataTable dt = DBHelper.GetDataTable(sqlOrder); //实例化泛型集合 List <MODOrder> listOrder = new List <MODOrder>(); //模型类获取值 for (int i = 0; i < dt.Rows.Count; i++) { //实例化模型类 MODOrder order = new MODOrder(); order.OrdersID = int.Parse(dt.Rows[i][0].ToString()); order.UserName = dt.Rows[i][1].ToString(); order.GoodName = dt.Rows[i][2].ToString(); order.Time = dt.Rows[i][3].ToString(); order.Price = double.Parse(dt.Rows[i][4].ToString()); order.OrdersCount = int.Parse(dt.Rows[i][5].ToString()); order.Introduction = dt.Rows[i][6].ToString(); //填充 listOrder.Add(order); } return(listOrder); }
//查询所有用户 public static List<MODCustomer> DALSelectCustomerAll() { string selCustomerSql = string.Format("select * from Customer"); DataTable dt = DBHelper.GetDataTable(selCustomerSql); //实例化泛型集合 List<MODCustomer> listCustomer = new List<MODCustomer>(); //模型类获取值 for (int i = 0; i < dt.Rows.Count; i++) { //实例化模型类 MODCustomer customer = new MODCustomer(); customer.UserID = int.Parse(dt.Rows[i][0].ToString()); customer.UserName = dt.Rows[i][1].ToString(); customer.Login = dt.Rows[i][2].ToString(); customer.Pwd = dt.Rows[i][3].ToString(); customer.Address = dt.Rows[i][4].ToString(); customer.Phone = dt.Rows[i][5].ToString(); customer.Email = dt.Rows[i][6].ToString(); //填充 listCustomer.Add(customer); } return listCustomer; }
/// <summary> /// returns the admin's project Professions on the project his in, to determine wether you need to add another position to the project /// if the project admin updates the project. /// </summary> public static List <int> returnProjAdminProf(int ProjectID, int AdminID) { try { DBHelper helper = new DBHelper(Constants.PROVIDER, Constants.PATH); string query = $"SELECT Profession FROM ProjectPositions WHERE ProjectID = {ProjectID} AND UserID = {AdminID} AND IsDeleted <> 2;"; DataTable dt = helper.GetDataTable(query); List <int> professionIDs = new List <int>(); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { int professionID = (int)row["Profession"]; professionIDs.Add(professionID); } } return(professionIDs); } catch { return(null); } }
/// <summary> /// 查询销售员是否存在 /// </summary> /// <param name="phone">电话号码</param> /// <returns></returns> public static bool SelectSalemanByMobile(string phone) { string sql = "select * from Saleman where Mobile=" + phone; return(int.Parse(DBHelper.GetDataTable(sql).Rows.Count.ToString()) > 0); }
/// <summary> /// 查询员工表 /// </summary> /// <returns></returns> public static DataTable GetSaleman() { string sql = "select * from Saleman"; return(DBHelper.GetDataTable(sql)); }
/// <summary> /// 登录 /// </summary> /// <param name="userName">用户名</param> /// <param name="password">密码</param> /// <param name="work">职务</param> /// <returns></returns> public static bool Login(string userName, string password, string work) { string sql = string.Format("select * from Saleman where SalesmanName='{0}'and Pwd='{1}' and Work='{2}'", userName, password, work); return(int.Parse(DBHelper.GetDataTable(sql).Rows.Count.ToString()) > 0); }
/// <summary> /// 根据电话号码返回 员工姓名 /// </summary> /// <param name="phone"></param> /// <returns></returns> public static string GetSalemanNameByPhone(string phone) { string sql = "select SalesmanName from saleman where Mobile='" + phone + "'"; return(DBHelper.GetDataTable(sql).Rows[0][0].ToString()); }
/// <summary> /// 查询员工根据姓名模糊查询 /// </summary> /// <param name="Name"></param> /// <returns></returns> public static DataTable GetSalemanLikeName(string Name) { string sql = string.Format("select * from [dbo].[Saleman] where [SalesmanName] like '%{0}%'", Name); return(DBHelper.GetDataTable(sql)); }
/// <summary> /// 查询类型 /// </summary> /// <returns></returns> public static DataTable selecttype() { string sql1 = "select * from type"; return(DBHelper.GetDataTable(sql1)); }
public DataTable Query(string sql) { return(DBHelper.GetDataTable(sql, CommandType.Text)); }
public DataTable DemandShop(string ShopName, string ShopColor, int ShopSize) { string sql = string.Format("select * from Goods where GName='{0}'and GColor ='{1}' and GSize='{2}' ", ShopName, ShopColor, ShopSize); return(DBHelper.GetDataTable(sql)); }
public DataTable ShowShopCar() { return(DBHelper.GetDataTable("select * from Shopping a join Goods b on a.GId = b.GId where SState = 0")); }
public DataTable Index()//商品显示 { string sql = "select * from Goods"; return(DBHelper.GetDataTable(sql)); }
public DataTable ShowPhone(string Phone) { string sql = string.Format("select * from Customer where CPhone ='{0}'", Phone); return(DBHelper.GetDataTable(sql)); }
/// <summary> /// 模糊查询类型名 /// </summary> /// <param name="TypeName"></param> /// <returns></returns> public static DataTable GetDataTableLikeTypeName(string TypeName) { string sql = string.Format("select * from Type where TypeName like '%{0}%'", TypeName); return(DBHelper.GetDataTable(sql)); }
public DataTable ShopName() { return(DBHelper.GetDataTable("select * from Goods")); }