SqlCommand Class

ExecuteReader( ), ExecuteNonQuery( ) and ExecuteScalar( ) are three important methods in the Command class.

ExecuteReader( ):-This method will execute the command and return an SqlDataReader object.

ExecuteNonQuery( ):- Executes the command and returns the number of records affected.

ExecuteScalar( ): - Executes the query and returns a single result such as count on records in a given table or max value of some field etc.

ExecuteReader( )

Below program Shows how to use ExecuteReader( ) Method:

using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{

static void Main( string[ ] args)
{

string str= "Database = bank ; server = kicit ; Trusted_Connection =true";
string cmd = "SELECT accno, name, balance from account" ;
SqlConnection con = new SqlConnection ( str ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
con.Open();

SqlDataReader r = com.ExecuteReader ( ) ;

while ( r.Read ( ) )

Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;

con.Close ( ) ;

}

}

The output is :

1 Rahul 200000
2 Subash 300000
3 Anita 100000
4 Preeti 120000
5 Kapil 8000

First we need to get connected to the database. To do so, we have to write a connection string. The connection string includes database name, server, database provider, user name, password and whether it�s a trusted connection or not. In our program the database name is bank and server name is kicit. We have initialized Trusted Connection to true. This means even if we don�t specify the username and password its fine. If the value is set to false we have to specify a username and password. This connection string is stored in str.

The command string is stored in cmd. cmd contains the query to be executed. The query we have used here is �SELECT accno, name, balance from account�. This is an Sql query, which selects the specified fields:- accno, name and balance from the �account� table.

To make a connection, we pass the connection string to the SqlConnection constructor. To the SqlCommand constructor we pass the connection as well as the command string. The Open( ) method opens a database connection with the property settings specified by the connection string. The SqlDataReader class reads a stream of rows from an SQL Server database. To create an SqlDataReader class we have to call the ExecuteReader( ) method belonging to the SqlCommand class. This method will execute the command and return an SqlDataReader object. The Read( ) method iterates through the records advancing the SqlDataReader by one record every time.

ExecuteNonQuery( )

Now lets have a look at a program using the ExecteNonQuery( ) method.

using System;
using System.Data;
using System.Data.SqlClient ;
class myclass
{

static void Main( string[] args)
{

string str = " Database = bank ; server = kicit ;
Trusted_Connection =true";
string cmd = "Insert into account values ( 6, 'Aakash', 25000 ) ";
SqlConnection con = new SqlConnection ( str ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
con.Open ( ) ;
com.ExecuteNonQuery ( ) ;
con.Close ( ) ;

}

}

The "Insert into account values (6, 'Aakash', 25000)" query inserts a new row in the table. After opening the connection, the query is executed and the connection is closed.

ExecuteScalar( )

Now lets look at a program a program using the ExecuteScalar( ) method:

using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{

static void Main( string[] args)
{

string str = " Database = bank ; server = kicit ;
Trusted_Connection=true";
string cmd = "SELECT count(*) from account" ;
SqlConnection con = new SqlConnection ( str ) ;
con.Open ( ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
Object o = com.ExecuteScalar( ) ;
Console.WriteLine ( o ) ;
con.Close ( ) ;

}

}

The output here is: - 6. The "SELECT count(*) from account" query counts the no of rows in our database.