示例#1
0
        public int GetTotalRecords(SqlConnection connection, UserCriteria criteria)
        {
            if (criteria != null)
            {
                var result = new List <UserInfo>();
                using (var command = new SqlCommand("Select count(*)  as TotalRecords  " +
                                                    " from tbl_User U " +
                                                    " where   1=1  and U.UserID <> 'admin' ", connection))
                {
                    if (criteria.UserName != "" && criteria.UserName != null)
                    {
                        command.CommandText += " and U.UserName like  '%" + criteria.UserName + "%' ";
                    }



                    if (criteria.UserID != "" && criteria.UserID != null)
                    {
                        command.CommandText += " and U.UserID = @UserID ";
                        AddSqlParameter(command, "@UserID", criteria.UserID, System.Data.SqlDbType.VarChar);
                    }

                    WriteLogExecutingCommand(command);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            return(GetDbReaderValue <int>(reader["TotalRecords"]));
                        }
                    }
                }
            }
            else
            {
                using (var command = new SqlCommand("Select count(*) as TotalRecords  from tbl_User where 1 = 1 ", connection))
                {
                    WriteLogExecutingCommand(command);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            return(GetDbReaderValue <int>(reader["TotalRecords"]));
                        }
                    }
                }
            }
            return(0);
        }
示例#2
0
        /// <summary>
        /// Hàm lấy tất cả user trừ user admin, mdx, config
        /// </summary>
        /// <returns>Return DataTable</returns>
        ///
        public List <UserInfo> Getlist(SqlConnection connection, UserCriteria criteria)
        {
            var result = new List <UserInfo>();

            using (var command = new SqlCommand("Select U.*  " +
                                                " from tbl_User U " +
                                                " where   1=1 and U.UserID <> 'admin' ", connection))
            {
                if (criteria.UserName != "" && criteria.UserName != null)
                {
                    command.CommandText += " and U.UserName like  '%" + criteria.UserName + "%' ";
                }

                if (criteria.UserID != "" && criteria.UserID != null)
                {
                    command.CommandText += " and U.UserID like  '%" + criteria.UserID + "%' ";
                }


                if (criteria.pageSize == 0)
                {
                    criteria.pageSize = 10;
                }
                var offSet = criteria.pageIndex * criteria.pageSize;
                command.CommandText += " order by U.UserName ";
                command.CommandText += " OFFSET @OFFSET ROWS FETCH NEXT @PAGESIZE ROWS ONLY ";
                AddSqlParameter(command, "@OFFSET", offSet, System.Data.SqlDbType.Int);
                AddSqlParameter(command, "@PAGESIZE", criteria.pageSize, System.Data.SqlDbType.Int);


                WriteLogExecutingCommand(command);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var info = new UserInfo();
                        info.UserName = GetDbReaderValue <string>(reader["UserName"]);
                        info.UserID   = GetDbReaderValue <string>(reader["UserID"]);
                        //      info.Disable = GetDbReaderValue<byte>(reader["GroupID"]);
                        info.Email   = GetDbReaderValue <string>(reader["Email"]);
                        info.Disable = GetDbReaderValue <Boolean>(reader["Disable"]);
                        result.Add(info);
                    }
                }
                return(result);
            }
        }