ADO.NET Commands Executing

 ADO.NET Commands Executing 

 In this post, we learn about ado.net Commands Executing. Commands have several different methods for executing SQL. The differences between these methods depend on the results we expect from the SQL. Queries return rows of data (result sets), but the INSERT, UPDATE, and DELETE statements don’t. We determine which method to use by considering what we expect to be returned.

 To Known more about ado.net click here.

  Command Execution Methods:

Sr.No	If the Command Is Going to Return	You Should Use
1)	Nothing (it isn’t a query)	ExecuteNonOuery()
2)	Zero or more rows	ExecuteReader()
3)	Only one value	ExecuteScalar()

1. Executing Commands with a Scalar Query

 The ExecuteScalar is the method that is used to execute those SQL statements that consist of scalar functions. Scalar functions are functions that return only one value from an entire set of rows in a table.

 For example, Min( ), Max( ), Sum( ), Count( ), and so on, are a few examples of scalar functions. If we execute a query such as Select Min(Salary) from Employee, then no matter how many rows we have in the table, only one row will be returned.

 Now let’s see how the ExecuteScalar( ) method works with such a SQL query.

  Code: Program.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace CsApp_Connection
{
    class  Program
    {
        public static void Main()
        {
            Console.WriteLine("\tCommand ExecuteScalar Example.");

            string connectionString = "data source=LearnAsp;user
                id=LearnAsp;"
            + "password=LearnAsp; Pooling=true; Max pool size=10; Min pool size=5;";
                       
            OracleConnection myConn = new OracleConnection(connectionString);

           Console.WriteLine("\tDB Connection Opening...");
            myConn.Open();
            string selectString = " select count(*) from
                emp ";
            Console.WriteLine("\tCommand Created...");
            OracleCommand myCommand = myConn.CreateCommand();
            myCommand.CommandText = selectString;

            Console.WriteLine("\tNumber of Employee is
                : " + myCommand.ExecuteScalar().ToString());           

            myConn.Close();
            Console.ReadLine();            
        }
    }
} 

  Output  

2. Executing Commands with Multiple Results

 If we are expecting more than one row from sql query then use the command’s ExecuteReader() method.ExecuteReader() returns a data reader, an instance of the OracleDataReader class. Data readers have methods that allow we to read successive rows in result sets and retrieve individual column values. here of using the ExecuteReader() method to create a SqlDataReader from a command to display query results.

 Now let’s see how the ExecuteReader( ) method works with such a SQL query.

  Code: Program.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace CsApp_Connection
{
    class  Program
    {
        public static void Main()
        {
            Console.WriteLine("\tCommand ExecuteReader Example.");

            string connectionString = "data source=LearnAsp;user id=LearnAsp;"
            + "password=LearnAsp; Pooling=true; Max pool size=10; Min pool size=5;";
                       
            OracleConnection myConn = new OracleConnection(connectionString);

           Console.WriteLine("\tDB Connection Opening...");
            myConn.Open();
            string selectString = " select * from emp
                ";
            Console.WriteLine("\tCommand Created...");
            OracleCommand myCommand = myConn.CreateCommand();
            myCommand.CommandText = selectString;
            OracleDataReader rdr = myCommand.ExecuteReader();

            while (rdr.Read())
            {
                Console.WriteLine("\n Eomployee: ");
               Console.WriteLine(rdr.GetValue(0) + "\t\t" + rdr.GetValue(1));
            }
            myConn.Close();
            Console.ReadLine();            
        }
    }
} 

  Output  

3. Executing Nonquery Statements

 If we executing dml query then we might use ExecuteNonQuery Method Becoz it do not return any records. Such statements consist of INSERT, UPDATE, and DELETE functionality of DataBase. Hence, ExecuteNonQuery() is used to provide the DML statements to the command and execute it. The INSERT, UPDATE, and DELETE Sql statements do not return any records.

 Now let’s see how the ExecuteNonQuery() method works with such a SQL query.

  Code: Program.cs  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;

namespace CsApp_Connection
{
    class  Program
    {
        public static void Main()
        {
            Console.WriteLine("\tCommand ExecuteNonQuery
                Example.");

            string connectionString = "data source=LearnAsp;user
                id=LearnAsp;"
            + "password=LearnAsp; Pooling=true; Max pool size=10; Min pool size=5;";
                       
            OracleConnection myConn = new OracleConnection(connectionString);

           Console.WriteLine("\tDB Connection Opening...");
            myConn.Open();
            string selectString = " insert into emp(empno,ename,job)
                values(1,'abc','job1') ";
            Console.WriteLine("\tCommand Created...");
            OracleCommand myCommand = myConn.CreateCommand();
            myCommand.CommandText = selectString;
            myCommand.ExecuteNonQuery();
            Console.WriteLine("\tNew Record Added Successfully!!");           

            myConn.Close();
            Console.ReadLine();            
        }
    }
} 

  Output