4 important properties of SqlDataReader in C#

Sourav.Kayal
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 22527 red flag

Four popular properties of SqlDataReader object

4 important properties of SqlDataReader object in C#

In this article, we will discuss about four important properties of SqlDataReader in ADO.NET C#.

We know SqlDataReader is very well known ADO.NET class and it is used to read data in connected architecture. Let’s see various properties of SqlDataReader one by one each of them.


FieldsCount to count number of columns in table.

Using this property we can count number of column in fetched table. Here one table which has 3 columns is used.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
 
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "select * from test";
            SqlDataReader rd = cmd.ExecuteReader();
 
            Console.WriteLine("Number of Colums :- " +  rd.FieldCount);
 
 
            Console.ReadLine();
        }
    }
}
 

Here is sample output.


HasRows Property to check row if exists or not.

Using this property we can check whether any row is present or not in SqlDataReader. To check existence of any row this property is useful. In below we can see sample code.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "select * from test";
            SqlDataReader rd = cmd.ExecuteReader();
 
     Console.WriteLine("Rows present or not :- " +  rd.HasRows);
            Console.ReadLine();
        }
    }
}

Output is here.


RecordsAffected property to count number of record affected by query.

This property works only with insert /update or deletes command and return number of effected row(s). In below example we are inserting one record ,so the output is 1.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "Insert into test values('test',20)";
            SqlDataReader rd = cmd.ExecuteReader();
 
            Console.WriteLine("Number of record effected: " +  rd.RecordsAffected);
            Console.ReadLine();
        }
    }
}


IsClosed and IsOpen to check reader is open or close.

Using this property we can check whether reader is open or close.

using System;
using System.Collections;
using System.Globalization;
using System.Data.SqlClient;
using System.Data;
 
namespace Test1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString="Data Source=SOURAV-PC\\SQL_INSTANCE2;Initial Catalog=test;Integrated Security=True";
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
 
            cmd.CommandText = "select * from test";
            SqlDataReader rd = cmd.ExecuteReader();
 
            Console.WriteLine("Status of Reader :- " +  rd.IsClosed);
            rd.Close();
            Console.WriteLine("Status of Reader :- " + rd.IsClosed);
 
            Console.ReadLine();
        }
    }
}

 

 

 

Page copy protected against web site content infringement by Copyscape

About the Author

Sourav.Kayal
Full Name: Sourav Kayal
Member Level: Silver
Member Status: Member,MVP
Member Since: 6/20/2013 2:09:01 AM
Country: India
Read my blog here http://ctrlcvprogrammer.blogspot.in/
http://www.dotnetfunda.com
I am .NET developer working for HelixDNA Technologies,Bangalore in healthcare domain. Like to learn new technology and programming language. Currently working in ASP.NET ,C# and other microsoft technologies.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)