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);
}
}
