Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        /// <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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        /// <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);
        }
Esempio n. 7
0
        /// <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);
        }
Esempio n. 8
0
        /// <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]);
        }
Esempio n. 9
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);
            }
        }
Esempio n. 10
0
        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);
        }
Esempio n. 11
0
        /// <summary>
        /// 获取所有记录
        /// </summary>
        /// <returns></returns>
        public DataTable user_GetAll()
        {
            string sql = "select * from Student";

            return(MSSQL.query(sql));
        }