How to fetch records of specific rows from data table?

Question

I am using C# and SQL server. I used top query but it is giving me only top result from data table. I want results of rows like 20-40 or 30-100 by entering this value in textbox.

CREATE TABLE [dbo].[newpatient] (
[id]          INT           IDENTITY (1, 1) NOT NULL,
[serialno]    VARCHAR (MAX) NULL,
[patientname] VARCHAR (100) CONSTRAINT [DF__newpatien__patie__1273C1CD] DEFAULT ('') NULL,
[age]         INT           CONSTRAINT [DF__newpatient__age__1367E606] DEFAULT ((0)) NULL,
[address]     VARCHAR (100) CONSTRAINT [DF__newpatien__addre__145C0A3F] DEFAULT ('') NULL,
[symptoms]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__sympt__15502E78] DEFAULT ('') NULL,
[medicine]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__medic__164452B1] DEFAULT ('') NULL,
[bookingdate] DATETIME      NULL,
[alloteddate] DATETIME      NULL,
[village]     VARCHAR (MAX) CONSTRAINT [DF__newpatien__villa__173876EA] DEFAULT ('') NULL,
[thana]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__thana__182C9B23] DEFAULT ('') NULL,
[district]    VARCHAR (MAX) CONSTRAINT [DF__newpatien__distr__1920BF5C] DEFAULT ('') NULL,
[state]       VARCHAR (MAX) CONSTRAINT [DF__newpatien__state__1A14E395] DEFAULT ('') NULL,
[isvalid]     BIT           CONSTRAINT [DF__newpatien__isval__1B0907CE] DEFAULT ('') NULL,
CONSTRAINT [pk_id_newpatient] PRIMARY KEY CLUSTERED ([id] ASC)

);

try
   {
     SuperClass sc = new SuperClass();
     Cursor = Cursors.WaitCursor;
     timer1.Enabled = true;
     rptPatients rpt = new rptPatients();// created report
     SqlCommand MyCommand = new SqlCommand();
     SqlDataAdapter myDA = new SqlDataAdapter();
     DB_DOCTORDataSet myDS = new DB_DOCTORDataSet();//created dataset
     SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=DB_DOCTOR;Integrated Security=True;Asynchronous Processing=True");
     MyCommand.Connection = con;
     MyCommand.CommandText = "select top '" + textBox1.Text + "' * from NewPatient";
     MyCommand.CommandType = CommandType.Text;
     myDA.SelectCommand = MyCommand;
     myDA.Fill(myDS, "NewPatient");
     rpt.SetDataSource(myDS);
     crystalReportViewer1.ReportSource = rpt;
   }
catch (Exception ex)
   {
     MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
   }

@RahulSharma , Can you please show the structure of your table ??? — Sohail, Dec 14 '13 at 10:20
i want fetch specific range of rows from table....i cant post pics bcoz my points are only 22 :).... — Rahul Sharma, Dec 14 '13 at 10:22
Try using CTE this is already similar to this http://stackoverflow.com/questions/3747186/best-paging-solution-using-sql-server-2005 — Jade, Dec 14 '13 at 10:39
you may find your answer from this URL: http://stackoverflow.com/questions/17259716/selecting-specific-row-number-in-sql — fasadat, Dec 14 '13 at 10:56

Daniel B · Accepted Answer · 2013-12-14T10:47:09.893

1

You cannot use the TOP clause select an interval such as 20 to 40. The simplest approach is to select the TOP 40 and then discard the first 19 rows on the client.

Slightly less simply is to change the query to something like

WITH T AS
(
    SELECT TOP 40 NP.*, row_number() OVER (ORDER BY id) AS RN from NewPatient NP Order by xx
)
SELECT * from T where RN>=20

edited Dec 14 '13 at 10:47

answered Dec 14 '13 at 10:41

Daniel B

797
4
13

how can i select interval ? – Rahul Sharma Dec 14 '13 at 10:47
can u help me to use above query using C# ? – Rahul Sharma Dec 14 '13 at 10:55
1

Just replace the current query text ´"select top '" + textBox1.Text + "' * from NewPatient"´ with the above query text. You need to substitute "20" and "40" with parameters. – Daniel B Dec 14 '13 at 11:17
1

Take away the single quotes around 40, add a comma after 40. – Daniel B Dec 14 '13 at 12:01

score 0 · Answer 2 · answered Dec 14 '13 at 23:32

0

If the data in the DataBase is ordered by the ID, why not use:

MyCommand.CommandText = "select * from NewPatient where id between  " + textBox1.Text + " and " + textBox2.Text + " from NewPatient";

Where textBox1 and textBox2 holds the range of records you want to get.

answered Dec 14 '13 at 23:32

Al3x

125
2
9

What about when there are gaps in the ID range? Using BETWEEN will not always return a full "page" of rows in that case. – Tony Dec 15 '13 at 23:09
If they are ordered by the ID and the range goes from 10 to 30, the gaps you talk about would be like "11,12,15,16,22,27"? In that case you'll still get all those records because they are inside the between range. – Al3x Dec 19 '13 at 14:05
When selecting pages of rows you always want 10 rows, irrespective of the ID. Using `BETWEEN` to get the rows 10-20 from the example list "11,12,15,16,22,27" would result in only 4 rows being returned, not 10. – Tony Dec 19 '13 at 15:36
I see what you mean but if you specify a range and in that range the aren't 10 items, you can't magically get 10. You should specify your criteria to select items. For example, you could want the next 10 items starting from a fixed id: MyCommand.CommandText = "select top(10) from NewPatient where id >= " + textBox1.Text + " from NewPatient"; Here textBox1.Text holds the lowerest ID you want to get, and the top(10) only returns you 10 items. You could add "order by desc" or "asc" if the entries are not in order. – Al3x Dec 22 '13 at 13:08
"if you specify a range and in that range the aren't 10 items, you can't magically get 10" True, but your solution will never return a full page of rows if there are gaps in the ID range, even if there are hundreds of rows. To get a full page of rows you can use `ROW_NUMBER` to sequentially number them and then select 10; see the accepted answer to this question. – Tony Dec 22 '13 at 22:33
Maybe I don't get what a "full page" is? I gather that the accepted answer would return the next 40 rows starting at row 20. So a full page is 40 rows? Then the "range" thing is misleading, you just can't set a range and expect it to "fill a full page", if there are gaps, the range will limit the amount of items you could get. Now, if what you want are a fixed amount of items starting at a given ID, setting the row_number() then ordering is very inefficient. IF the ID is ordered (being identity for example) a simple `select top(40) from table where id>=starting_id` would do the trick I think – Al3x Dec 27 '13 at 13:23

How to fetch records of specific rows from data table?

2 Answers2