Range of records from database
In ADO.NET, DataTable objects are utilized to represent tables within a DataSet. The DataSet, designed for use in disconnected mode, is a container for one or more DataTables that hold data retrieved from a data source.
VB.Net
Dim ds As New DataSet
C#
DataSet ds = new DataSet();
There may be scenarios where you only need to retrieve a specific range of rows from the data source and populate the DataSet accordingly. In such cases, you can use the DataAdapter to fetch and fill the DataSet with the desired range of rows. The following piece of code shows how to fill specific range of rows from DataAdapter to Dataset.
VB.Net
DataAdapter.Fill(Dataset, 5, 3, "tablename")
C#
DataAdapter.Fill(Dataset, 5, 3, "tablename");
The above code will fill the Dataset starting from 5th row and 3 rows.
- 5 is the starting row no
- 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.
Default.aspx
<!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
Conclusion
By utilizing this approach, you can efficiently retrieve and fill a DataSet with only the required subset of rows from the data source. This allows for better performance and reduces the amount of data transferred and stored in memory.