public async Task <T> ById <T>(int id) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { return(await conn.QueryFirstOrDefaultAsync <T>(CommonSql.ById(_table, id))); } }
public async Task <PlanedInspectionRoute> ByTrainId(int id) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { return(await conn.QueryFirstOrDefaultAsync <PlanedInspectionRoute>(CommonSql.ByPropertyId(_table, "PlanedRouteTrainId", id))); } }
/// <summary> /// 注册实现方法 /// </summary> /// <param name="employees">用户实体类成员</param> public bool Add(Employees employees) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [Employees]("); strSql.Append("EmployeeName,EmployeeRight,EmployeePassword,EmployeeDepart"); strSql.Append(")"); strSql.Append("values("); strSql.Append("'" + employees.EMPLOYEENAME + "',"); strSql.Append("'" + employees.EMPLOYEERIGHT + "',"); strSql.Append("'" + employees.EMPLOYEEPASSWORD + "',"); strSql.Append("'" + employees.EMPLOYEEDEPART + "'"); strSql.Append(")"); //CommonDatabaseInterface pComm = new CommonSql(); CommonSql pComm = new CommonSql(); try { pComm.Execute(strSql.ToString()); pComm.Close(); return(true); } catch (Exception e) { pComm.Close(); throw e; } }
public async Task <List <Inspection> > GetByTrainId(int id) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var result = await conn.QueryAsync <Inspection>(CommonSql.ByPropertyId(_tableName, "TrainId", id)); return(result.ToList()); } }
public async Task <List <Inspection> > GetAll() { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var result = await conn.QueryAsync <Inspection>(CommonSql.GetAll(_tableName)); return(result.ToList()); } }
public async Task <List <Inspection> > GetAllSortByProperty(string property, string direction) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var result = await conn.QueryAsync <Inspection>(CommonSql.GetAllSortByProperty(_tableName, property, direction)); return(result.ToList()); } }
//public async Task<Route> Update(Route input) //{ // using (var conn = new SqlConnection(AppSettings.ConnectionString)) // { // await conn.ExecuteAsync(_sql.Update(input)); // return await ById(input.Id); // } //} public async Task <List <PlanedRouteTrain> > ByTrainId(int trainId) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var data = await conn.QueryAsync <PlanedRouteTrain>(CommonSql.ByPropertyId(_table, "TrainId", trainId)); return(data.ToList()); } }
public async Task <List <TechPass> > GetAll() { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var result = await conn.QueryAsync <TechPass>(CommonSql.Select(_table)); return(result.ToList()); } }
//public async Task<List<Stantion>> GetAllPaging(DevExtremeTableData.Paging paging) //{ // using (var conn = new SqlConnection(AppSettings.ConnectionString)) // { // var result = await conn.QueryAsync<Stantion>(CommonSql.GetAll(_tableName)); // return result.ToList(); // } //} public async Task <List <Stantion> > ByTripId(int id) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var sql = Sql.SqlQueryCach["Stantion.ById"]; var result = await conn.QueryAsync <Stantion>(CommonSql.ByPropertyId(_tableName, "TripId", id)); return(result.ToList()); } }
public async Task <TechPass> Delete(int id) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var toDel = await ById(id); if (toDel == null) { throw new NotFoundException(); } await conn.ExecuteAsync(CommonSql.Delete(_table, id)); return(toDel); } }
/// <summary> ///用户登录方法实现 /// </summary> /// <param name="employees">用户实体类</param> public bool LoginUser(Employees employees, out DataTable dt) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM [Employees] WHERE "); strSql.Append("EmployeeName="); strSql.Append("'" + employees.EMPLOYEENAME + "'");//特别留意该处语法 CommonSql pComm = new CommonSql(); string dtlName = "mydatatable"; try { dt = pComm.ExeForDtl(strSql.ToString(), dtlName); pComm.Close(); //将从数据库中读取的用户数据写入xml文件中 //WriteUserInfoXML(dt); //检验权限 //校验密码正确性 if (dt.Rows.Count > 0) { if (employees.EMPLOYEEPASSWORD == dt.Rows[0]["EmployeePassword"].ToString().Trim()) { return(true); } else { return(false); } } else { return(false); } } catch (Exception e) { pComm.Close(); throw e; } }
/// <summary> /// 根据扫描枪的ID信息从数据库中查找对应的数据条目 /// </summary> /// <param name="DeviceID">输入界面中编号</param> /// <param name="typenumber">输入界面中类型</param> public DataTable CxInfofromdatabase(string DeviceID, int typenumber) { //Devices devices = new Devices(); //将从数据库中读取的数据存入dt中之后,再存入实体类中,便于报表调用数据 BJTable bjtable = new BJTable(); YBTools ybtools = new YBTools(); HCTable hcttable = new HCTable(); CommonSql pComm = new CommonSql(); DataTable dt = new DataTable(); StringBuilder strSql = new StringBuilder(); string dtlName = "mydatatable"; /// <summary> /// 根据输入页面输入的不同,操作不同的数据库表 /// </summary> switch (typenumber) { case 1: //备件 strSql.Append("SELECT * FROM [BJTable] WHERE "); strSql.Append("BJId="); strSql.Append("'" + DeviceID + "'"); dt = pComm.ExeForDtl(strSql.ToString(), dtlName); #region 将dt中的数据存入实体类中,便于报表调用 bjtable.BJID = dt.Rows[0][0].ToString(); bjtable.BJNAME = dt.Rows[0][1].ToString(); bjtable.BJPN = dt.Rows[0][2].ToString(); bjtable.BJSN = dt.Rows[0][3].ToString(); bjtable.BJFACILITY = dt.Rows[0][4].ToString(); bjtable.BJDEPART = dt.Rows[0][5].ToString(); bjtable.BJSTATE = dt.Rows[0][6].ToString(); bjtable.BJISINSTORAGE = dt.Rows[0][7].ToString(); bjtable.BJPERSONINCHARGE = dt.Rows[0][8].ToString(); bjtable.BJSTORAGEMANAGER = dt.Rows[0][9].ToString(); #endregion pComm.Close(); break; #region 无用代码 //if (dt != null && dt.Rows.Count > 0)//从数据库中未查到相关数据的判断 //{ // bjtable.BJID = dt.Rows[0]["BJId"].ToString().Trim(); // bjtable.BJNAME = dt.Rows[1]["BJName"].ToString().Trim(); // bjtable.BJPN = dt.Rows[2]["BJPn"].ToString().Trim(); // bjtable.BJSN = dt.Rows[3]["BJSn"].ToString().Trim(); // bjtable.BJFACILITY = dt.Rows[4]["BJFacility"].ToString().Trim(); // bjtable.BJDEPART = dt.Rows[5]["BJDepart"].ToString().Trim(); // bjtable.BJSTATE = dt.Rows[6]["BJState"].ToString().Trim(); // bjtable.BJISINSTORAGE = dt.Rows[7]["BJIsInStorage"].ToString().Trim(); // bjtable.BJPERSONINCHARGE = dt.Rows[8]["BJPersonInCharge"].ToString().Trim(); // bjtable.BJSTORAGEMANAGER = dt.Rows[9]["BJStorageManager"].ToString().Trim(); // return bjtable; //} //else //{ // devices = null; // return devices; //} #endregion case 2: //仪表 strSql.Append("SELECT * FROM [YBTools] WHERE "); strSql.Append("YBToolId="); strSql.Append("'" + DeviceID + "'"); dt = pComm.ExeForDtl(strSql.ToString(), dtlName); #region #endregion pComm.Close(); break; case 3: //耗材 strSql.Append("SELECT * FROM [HCTable] WHERE "); strSql.Append("HCId="); strSql.Append("'" + DeviceID + "'"); dt = pComm.ExeForDtl(strSql.ToString(), dtlName); pComm.Close(); break; } return(dt);//return只能放在最后,不能放在case下面 #region 无用代码 //strSql.Append("SELECT * FROM [Devices] WHERE "); //strSql.Append("DeviceId="); //strSql.Append("'"+DeviceID+"'"); ////string dtlName = "mydatatable"; //try //{ // dt = pComm.ExeForDtl(strSql.ToString(),dtlName); // pComm.Close(); // if (dt!=null&&dt.Rows.Count>0)//从数据库中未查到相关数据的判断 // { // devices.DEVICEID = dt.Rows[0]["DeviceId"].ToString().Trim(); // devices.DEVICENAME = dt.Rows[0]["DeviceName"].ToString().Trim(); // devices.DEVICEDEPART = dt.Rows[0]["DeviceDepart"].ToString().Trim(); // devices.DEVICEPN = dt.Rows[0]["DevicePn"].ToString().Trim(); // devices.DEVICESN = dt.Rows[0]["DeviceSn"].ToString().Trim(); // devices.DEVICESUM = (int)dt.Rows[0]["DeviceSum"]; // devices.DEVICERESIDUE = (int)dt.Rows[0]["DeviceResidue"]; // devices.DEVICEOFINDETECTIONTIME = (DateTime)dt.Rows[0]["DeviceOfInDetectionTime"]; // devices.DEVICEOFSPDETECTIONTIME = (DateTime)dt.Rows[0]["DeviceOfSpDetectionTime"]; // devices.DEVICEPICROOT = dt.Rows[0]["DevicePicRoot"].ToString().Trim(); // devices.DEVICEDEPOTS = dt.Rows[0]["DeviceDepots"].ToString().Trim(); // return devices; // } // else // { // devices = null; // return devices; // } //} //catch(Exception e) //{ // pComm.Close(); // throw e; //} #endregion }
/// <summary> /// 根据扫描信息对数据库中的备件仪表进行状态更新 /// </summary> /// <param name="flag">操作时出库还是入库的标志变量</param> /// <param name="hcnumber">更新的耗材数量</param> /// <param name="id">项目的ID号</param> /// <param name="number">0备件,1仪表还是2耗材</param> public bool UpdateInfotodatebase(string flag, int number, string id, int hcnumber) { try { CommonSql pComm = new CommonSql(); string strSql = null; pComm.Open(); switch (number) { case 1: //备件 if (flag == "c") { strSql = "UPDATE BJTable SET BJIsInStorage='0' WHERE BJId='id'"; pComm.Execute(strSql); pComm.Close(); } else if (flag == "r") { strSql = "UPDATE BJTable SET BJIsInStorage='1' WHERE BJId='id'"; pComm.Execute(strSql); pComm.Close(); } break; case 2: //仪表 if (flag == "c") { strSql = "UPDATE YBTools SET YBToolsIsInStorage='0' WHERE YBToolId='id'"; pComm.Execute(strSql); pComm.Close(); } else if (flag == "r") { strSql = "UPDATE BJTable SET YBToolsIsInStorage='1' WHERE YBToolId='id'"; pComm.Execute(strSql); pComm.Close(); } break; case 3: //耗材 if (flag == "c") { strSql = "UPDATE HCTable SET HCResidue=HCResidue-hcnumber WHERE HCId='id'"; pComm.Execute(strSql); pComm.Close(); } else if (flag == "r") { strSql = "UPDATE HCTable SET HCResidue=HCResidue+hcnumber WHERE HCId='id'"; pComm.Execute(strSql); pComm.Close(); } break; } return(true); } catch { return(false); } }