ADO.NET Difference FAQs-3

1.Difference between ExecuteNonQuery() and ExecuteScalar() methods in ADO.NET

S.No	ExecuteNonQuery()	ExecuteScalar()
1	It will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).	It will work with Non-Action Queries that contain aggregate functions.
2	It returns the count of rows effected by the Query.	It returns the first row and first column value of the query result.
3	Return type is int	Return type is object.
4	Return value is optional and can be assigned to an integer variable.	Return value is compulsory and should be assigned to a variable of required type.

Example-1 for ExecuteNonQuery Method -Insert:

SqlCommand cmd = new SqlCommand("Insert Into SampleTable Values('1','2')",con);

//con is the connection object

con.Open();

cmd.ExecuteNonQuery(); //The SQL Insert Statement gets executed

Example-2 for ExecuteNonQuery Method - Update:

public void UpdateEmployeeEmail()

{

SqlConnection conn = new SqlConnection(connString))

String sqlQuery = "UPDATE Employee SET empemail='umar.ali@xyz.com' WHERE empid=5;

SqlCommand cmd = new SqlCommand(sqlQuery, conn);

try

{

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

conn.Close();

}

return count;

}

Example-1 for ExecuteScalar Method:

This returns only one value that is first column value of the first row in the executed query

public int getSomeProdId()

{

int count=0;

SqlConnection conn = new SqlConnection(connString))

String sqlQuery = "SELECT COUNT(*) FROM dbo.region";

SqlCommand cmd = new SqlCommand(sqlQuery, conn);

try

{

conn.Open();

//Since return type is System.Object, a typecast is must

count = (Int32)cmd.ExecuteScalar();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

conn.Close();

}

return count;

}

Example-2 for ExecuteScalar Method:

This returns one value only, no recordsets.

cmd.CommandText = "Select Name, DOB, from Emp where ID=1";

Dim strName As string = cmd.ExecuteScalar.ToString

2.Difference between ExecuteNonQuery() and ExecuteReader() methods in ADO.NET

S.No	ExecuteNonQuery()	ExecuteReader()
1	It will work with Action Queries only (Create,Alter,Drop,Insert,Update,Delete).	It will work with Action and Non-Action Queries (Select)
2	It returns the count of rows effected by the Query.	It returns the collection of rows selected by the Query.
3	Return type is int	Return type is DataReader.
4	Return value is optional and can be assigned to an integer variable.	Return value is compulsory and should be assigned to an another object DataReader.

Example-1 for ExecuteNonQuery Method -Insert:

SqlCommand cmd = new SqlCommand("Insert Into SampleTable Values('1','2')",con);

//con is the connection object

con.Open();

cmd.ExecuteNonQuery(); //The SQL Insert Statement gets executed

Example-2 for ExecuteNonQuery Method - Update:

public void UpdateEmployeeEmail()

{

SqlConnection conn = new SqlConnection(connString))

String sqlQuery = "UPDATE Employee SET empemail='umar.ali@xyz.com' WHERE empid=5;

SqlCommand cmd = new SqlCommand(sqlQuery, conn);

try

{

conn.Open();

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

conn.Close();

}

return count;

}

Example for ExecuteReader Method:

Here, ExecuteReader is used to get set of records by specified query, namely, "select * from emp"

SqlConnection con = new SqlConnection(constr); //constructor can be connection of string.

SqlCommand cmd = new SqlCommand ("select * from emp", con);

con.Open();

SqlDataReader dr = cmd. ExecuteReader (CommandBehavior. CloseConnection); //Implicitly closes the connection because CommandBehavior. CloseConnection was specified.

while(dr.Read())

{

Console.WriteLine (dr.GetString(0));

}

dr.Close();