Learn ASP.NET Core 2 and Angular 6 in October 2018. Instructor-led online courses by Bipin Joshi. Read more...
Instructor-led Online Courses by Bipin Joshi : ASP.NET Core | Angular | Design Patterns
Registration open for October 2018 batches. Read more here.

<%@ Page %>

Obtaining No. Of Record Returned From DataReader

Introduction

DataReader allows forwardonly and readonly data access. Many times we need to know the number of records returned after firing a SELECT query. DataReader do not offer this functionality. We can, however, use some trick to quickly and efficiently get this data. This How To is intended to show you just that.

Executing Batch Queries

The Command object can be used to fire batch of queries instead of single query. In this case we can execute action as well as select queries together. We can then call ExecuteReader method of the Command object. There can be multiple result sets obtained and those can be navigated using NextResult method of DataReader. Following code snippet shows how it works:
Dim cnn As New SqlConnection(Global.GetConnectionString)
Dim cmd As New SqlCommand()
Dim dr As SqlDataReader
cnn.Open()
cmd.CommandText = "select count(*) from employees;
select * from employees"
cmd.Connection = cnn

dr = cmd.ExecuteReader

dr.Read()
Label1.Text = dr.GetValue(0)
dr.NextResult()
DataGrid1.DataSource = dr
DataGrid1.DataBind()
Here, we have first declared objects of connection, command and DataReader. Note how the CommandText property of the command object is set to two SELECT queries separated by a ";". Next, the Command is executed and the output is assigned to DataReader. The DataReader contains two result sets matching our queries. First query gives us the number of records. The NextResult method shifts to the next result set and we can bind the actual data with the DataGrid.

I hope you must have find the article useful. See you soon.


Bipin Joshi is a software consultant, trainer, author and spiritual yoga mentor having 23+ years of experience in software development. He teaches private online courses in ASP.NET Core and Angular to individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Ajapa Yoga to interested individuals. To know more about him click here.

He will be teaching ASP.NET Core 2.1 and Angular 6.1 in October 2018. Click here for his online course schedule and more details.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 08 June 2002


Tags : ADO.NET Data Access SQL Server