public BookTicketInfoExModel getByIdAndTypes1(int id)
{
string sql = @"SELECT bti.Id
,TrainNumber
,StartStation
,EndStation
,BookDate
,StudentId
,Phone
,Remark
,TicketSate
FROM dbo.BookTicketInfo bti
INNER JOIN dbo.BookTicketState bts
ON bti.Id = bts.BookTicketInfoId
INNER JOIN dbo.Student s
ON bti.StudentId = s.Id
INNER JOIN dbo.SysRole sr
ON s.RoleId = sr.RoleId
WHERE Types = 1
AND bti.Id = @Id";
SqlParameter par = new SqlParameter("@Id", SqlDbType.Int);
par.Value = id;
BookTicketInfoExModel model = null;
DataTable dt = MSSQL.query(sql, par);
if (dt.Rows.Count > 0)
{
model = Utils.dataTable2List <BookTicketInfoExModel>(dt)[0];
}
return(model);
}
public StudentModel getById(int id)
{
string sql = @"SELECT Id
, StudentNumber
, StudentName
, Password
, Gender
, Identification
, Telephone
, ClassName
, Password
, RoleId
, IsStop
FROM dbo.Student
WHERE Id=@Id";
SqlParameter par = new SqlParameter("Id", SqlDbType.Int);
par.Value = id;
DataTable dt = MSSQL.query(sql, par);
StudentModel model = null;
if (dt.Rows.Count > 0)
{
model = Utils.dataTable2List <StudentModel>(dt)[0];
}
return(model);
}
public List <StudentExModel> getList(string studentNumber, string studentName, string className)
{
StringBuilder sb = new StringBuilder();
// 注意：这样联合 SysRole表 查询，会导致无 角色的学生无法查出
// 注意：N'女' 指定为符合中文，因为在实际部署后发现存在乱码问题，迁移数据时也容易中文变成问好????，这种别名输出居然也会导致???，加上N''即可
sb.Append("select Id,StudentNumber,StudentName,case when Gender='0' then N'女' else N'男' end Sex,Identification, Telephone,ClassName,RoleName");
sb.Append(" from Student a");
sb.Append(" join SysRole b");
sb.Append(" on a.RoleId = b.RoleId");
sb.Append(" where a.IsStop = 0");
if (!string.IsNullOrEmpty(studentNumber))
{
sb.AppendFormat(" and a.StudentNumber = '{0}'", studentNumber);
}
if (!string.IsNullOrEmpty(studentName))
{
sb.AppendFormat(" and a.StudentName like '%{0}%'", studentName);
}
if (!string.IsNullOrEmpty(className))
{
sb.AppendFormat(" and a.ClassName = '{0}'", className);
}
sb.Append(" order by Id desc");
List <StudentExModel> list = Utils.dataTable2List <StudentExModel>(MSSQL.query(sb.ToString()));
// 防止无记录时，gridView不显示表头，添加一行空记录以在无数据时显示表头
if (list.Count == 0)
{
list.Add(new StudentExModel());
}
return(list);
}
/// <summary>
/// 通过学号和密码查找学生
/// </summary>
/// <param name="model">含有学号和密码的学生实体</param>
/// <returns>如果无此用户则返回null，否则返回此人实体信息</returns>
public StudentModel getByNumberAndPwd(StudentModel model)
{
string sql = string.Format("select * from Student where StudentNumber='{0}' and Password='******'", model.StudentNumber, model.Password);
DataTable dt = MSSQL.query(sql);
StudentModel studentModel = null;
if (dt.Rows.Count > 0)
{
studentModel = DataTableToList(dt)[0];
}
return(studentModel);
}
public VwUserModel getByUserCodeAndPwd(string userCode, string password)
{
//string sql = $@"SELECT Id
// , UserCode
// , UserName
// , Password
// , Gender
// , IDCard
// , Telephone
// , Dept
// , RoleId
// FROM vw_userInfo where UserCode='{userCode}' AND Password='******'";
string sql = @"SELECT Id
, UserCode
, UserName
, Password
, Gender
, IDCard
, Telephone
, Dept
, RoleId
FROM vw_userInfo where UserCode=@UserCode AND Password=@Password";
// 注意，不要再给参数加 引号 ''，因为最后程序会为参数值加''
// 错误: UserCode='@UserCode'
#region 参数化后执行SQL
//exec sp_executesql N'SELECT Id
// , UserCode
// , UserName
// , Password
// , Gender
// , IDCard
// , Telephone
// , Dept
// , RoleId
// FROM vw_userInfo where UserCode = @UserCode AND Password = @Password',N'@UserCode nvarchar(50),@Password nvarchar(50)',@UserCode=N'170010347',@Password=N'21232f297a57a5a743894a0e4a801fc3'
#endregion
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@UserCode", SqlDbType.NVarChar, 50),
new SqlParameter("@Password", SqlDbType.NVarChar, 50)
};
pars[0].Value = userCode;
pars[1].Value = password;
DataTable dt = MSSQL.query(sql, pars);
VwUserModel model = null;
if (dt.Rows.Count > 0)
{
model = Utils.dataTable2List <VwUserModel>(dt)[0];
}
return(model);
}
/// <summary>
/// 获取所有记录实体
/// </summary>
/// <returns></returns>
public List <SysFunctionModel> getAll()
{
string sql = "select Id,MenuName,Url,SortNo from SysFunction where IsStop=0 order by SortNo";
DataTable dt = MSSQL.query(sql);
List <SysFunctionModel> list = null;
if (dt.Rows.Count > 0)
{
//list = dataTableToList(dt);
list = Utils.dataTable2List <SysFunctionModel>(dt);
}
return(list);
}
/// <summary>
/// 根据类型获取相关角色
/// </summary>
/// <param name="types"></param>
/// <returns></returns>
public List <SysRoleModel> getByTypes(string types)
{
string sql = "select RoleId, RoleName, IsStop, Types from SysRole where Types=@Types";
SqlParameter par = new SqlParameter("@Types", SqlDbType.Int);
par.Value = types;
DataTable dt = MSSQL.query(sql, par);
List <SysRoleModel> list = null;
if (dt.Rows.Count > 0)
{
list = Utils.dataTable2List <SysRoleModel>(dt);
}
return(list);
}
/// <summary>
/// 通过学号和姓名获取学生
/// </summary>
/// <param name="model">含有学号和学生姓名的实体</param>
/// <returns>符合学号和姓名要求的记录</returns>
public StudentModel student_GeyByNumberAndName(StudentModel model)
{
string sql = "select * from Student where 1=1 ";
if (!string.IsNullOrEmpty(model.StudentNumber))
{
sql += $" and StudentNumber='{model.StudentNumber}'";
}
if (!string.IsNullOrEmpty(model.StudentName))
{
sql += $" and StudentName like '%{model.StudentName}%'";
}
return(Utils.dataTable2List <StudentModel>(MSSQL.query(sql))[0]);
}
public List <SysFunctionModel> getByRoleId(int roleId)
{
string sql = @"select Id,MenuName,Url,SortNo
from SysFunction f
join SysRoleFunction rf on f.Id = rf.FuncId
where IsStop = 0 and RoleId = @RoleId
order by SortNo";
SqlParameter par = new SqlParameter("@RoleId", SqlDbType.Int);
par.Value = roleId;
DataTable dt = MSSQL.query(sql, par);
if (dt.Rows.Count > 0)
{
return(Utils.dataTable2List <SysFunctionModel>(dt));
}
else
{
return(null);
}
}
public List <BookTicketInfoExModel> getList(params string[] conditions)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT bti.Id, TrainNumber, StartStation, EndStation, BookDate, StudentId, Phone, Remark, TicketSate
FROM dbo.BookTicketInfo bti
INNER JOIN dbo.BookTicketState bts
ON bti.Id=bts.BookTicketInfoId
WHERE 1=1");
SqlParameter[] pars = conditionCon(conditions, sb);
sb.Append(" ORDER BY BookDate DESC, TicketSate ASC");
DataTable dt = MSSQL.query(sb.ToString(), pars);
List <BookTicketInfoExModel> list = null;
if (dt.Rows.Count > 0)
{
list = Utils.dataTable2List <BookTicketInfoExModel>(dt);
}
return(list);
}
/// <summary>
/// 获取所有记录
/// </summary>
/// <returns></returns>
public DataTable user_GetAll()
{
string sql = "select * from Student";
return(MSSQL.query(sql));
}