0317取出指定行区间数据几种方法

数据库端参数方法

        public List<GroupModel> GetAll(int startRowNum, int endRowNum)
        {

            string cmdText = @"Select * FROM (Select ROW_NUMBER() OVER (ORDER BY GroupId ASC) AS RowNum, * FROM ContactGroup) AS T where  @startRow <= RowNum AND RowNum < @endRow";

            var list = new List<GroupModel>();

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.Parameters.Add(new SqlParameter("@startRow", startRowNum));

                cmd.Parameters.Add(new SqlParameter("@endRow", endRowNum));
                conn.Open();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var model = new GroupModel();
                        model.GroupId = (int)reader["GroupId"];
                        //model.GroupId = DaoHelper.GetValueFromReader<int>(reader, "GroupId");
                        model.UserId = DaoHelper.GetValueFromReader<int>(reader, "UserId");
                        model.GroupName = DaoHelper.GetValueFromReader<string>(reader, "GroupName");
                        model.Description = DaoHelper.GetValueFromReader<string>(reader, "Description");
                        model.AddTime = DaoHelper.GetValueFromReader<DateTime?>(reader, "AddTime");

                        list.Add(model);
                    }
                }
            }
            return list;
        }

///reader.read()/Web Server循环控制

        public List<ContactModel> GetAll(int startIndex, int pageSize)
        {

            var list = new List<ContactModel>();
            string cmdText = "Select * From Contact ";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                conn.Open();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    var index = 0;
                    while (reader.Read())
                    {
                        if (index >= startIndex && index < startIndex + pageSize)
                        {
                            var model = new ContactModel();

                            model.ContactId = (int)reader["ContactId"];

                            model.Name = DaoHelper.GetValueFromReader<string>(reader, "Name");
                            model.Sex = DaoHelper.GetValueFromReader<bool?>(reader, "Sex");
                            model.Mobile = DaoHelper.GetValueFromReader<string>(reader, "Mobile");
                            model.Tel = DaoHelper.GetValueFromReader<string>(reader, "Tel");
                            model.QQ = DaoHelper.GetValueFromReader<string>(reader, "QQ");
                            model.EMail = DaoHelper.GetValueFromReader<string>(reader, "EMail");
                            model.Address = DaoHelper.GetValueFromReader<string>(reader, "Address");
                            model.Zip = DaoHelper.GetValueFromReader<string>(reader, "Zip");
                            model.Birthday = DaoHelper.GetValueFromReader<DateTime?>(reader, "Birthday");
                            model.Memo = DaoHelper.GetValueFromReader<string>(reader, "Memo");
                            model.AddTime = DaoHelper.GetValueFromReader<DateTime?>(reader, "AddTime");
                            model.GroupId = DaoHelper.GetValueFromReader<int?>(reader, "GroupId");

                            list.Add(model);

                            index++;

                        }
                        else
                        {
                            if (index < startIndex)
                            {
                                index++; continue;
                            }
                            else
                            {
                                cmd.Cancel();
                                break;
                            }
                        }
                    }

                }
            }
            return list;

        }

/DateTable/

 public DataTable GetByPage(int pageIndex, int pageSize)
        {

                        string cmdText = @"Select * From Contact";
                   using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(cmdText, conn);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "Table1");
                return ds.Tables[0];

            }
        }