Range of records from database

In ADO.NET, DataTable objects are used to represent the tables in a DataSet. The DataSet is designed to be used in a disconnected mode.

Dim ds As New DataSet
DataSet ds = new DataSet();

In some situations we need to retrieve data only specific range of rows. In this situations we can fill the Dataset from DataAdapter only that specific range of rows. The following piece of code shows how to fill specific range of rows from DataAdapter to Dataset.

DataAdapter.Fill(Dataset, 5, 3, "tablename")
DataAdapter.Fill(Dataset, 5, 3, "tablename");

The above code will fill the Dataset starting from 5th row and 3 rows.

  1. 5 is the starting row no
  2. 3 is no of rows we want to fill.

The following ASP.NET program select data from authors table and display row no 5 to 3 rows.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" /> <br /> <asp:ListBox ID="ListBox1" runat="server"></asp:ListBox> <br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </div> </form> </body> </html>
Full Source | C#
using System; using System.Data ; using System.Data.SqlClient ; using System.Configuration; public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString(); SqlConnection connection = new SqlConnection(connectionString); SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); try { connection.Open(); adapter = new SqlDataAdapter("select au_lname from authors", connection ); connection.Close(); adapter.Fill(ds, 5, 3, "authors"); //5 is starting row no. //3 is no of rows to retrieve ListBox1.DataSource = ds.Tables[0]; ListBox1.DataTextField = "au_lname"; ListBox1.DataBind(); } catch (Exception ex) { Label1.Text = "Error in execution " + ex.ToString(); } } }
Full Source | VB.NET
Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connectionString As String Dim connection As SqlConnection connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString connection = New SqlConnection(connectionString) Dim adapter As SqlDataAdapter Dim ds As New DataSet Try connection.Open() adapter = New SqlDataAdapter("select au_lname from authors", connection) adapter.Fill(ds, 5, 3, "authors") '5 is starting row no. '3 is no of rows to retrieve connection.Close() ListBox1.DataSource = ds.Tables(0) ListBox1.DataTextField = "au_lname" ListBox1.DataBind() Catch ex As Exception Label1.Text = "Error in execution " & ex.ToString End Try End Sub End Class

Click the following links to see full source code

C# Source Code
VB.NET Source Code