ExecuteScalar ExecuteReader ExecuteNonQuery : What is the Difference

ASP.NET C# : Today we are going to see the answers for these questions: What is the difference between ExecuteScalar,ExecuteReader and ExecuteNonQuery When to use ExecuteScalar? When to use ExecuteReader? When to use ExecuteNonQuery? What is the code for deleting a record? What is the code for inserting a record? What is the code for updating a record? gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg Point No. 1: When to use ExecuteScalar,ExecuteReader and ExecuteNonQuery? It depends on the SQL Statement used in your code. If the output of the sql statement is JUST A SINGLE VALUE, then use : ExecuteScalar. If the output of the sql statement is MORE THAN ONE VALUE then use : ExecuteReader If the output of the sql statement is NONE, then use : ExecuteNonQuery. ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg Point No 2. Can you give some SQL statements which returns JUST A SINGLE VALUE? "Select count(*) from emp" "Select EmpName where EmpID=25 "Select Sum(marks) from MarksMaster where StudentId=25" Note: If your sql statement output has more than one value,ExecuteScalar will return the value of first column of first row. gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg Point No.3 Can you give some SQL statements which returns MORE THAN ONE VALUE? "Select EmpID,EmpName,Salary from emp" "Select * from emp where emp_id=25" gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg Point No. 4: Can you give some sql statements which returns no output data: "Insert into emp values(1,'raja',2000)" "Update emp set EmpName='Raja' where EmpId=25" "Delete from emp where EmpID=25"   Note: ExecuteNonQuery will not return any records. but it will return number of records affected. If "No Count" property of the database is ON, then it will NOT return number of records affected. Instead it will return just -1 (minus one). gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg   ExecuteReader EXAMPLE-01 string sConn= "Data Source=SALES;user id=scott;password=tiger;"; string sql = "select * from emp"; OracleConnection myConn = new OracleConnection(sConn); OracleCommand myCmd=new OracleCommand(sql,myConn); DropDownList1.DataTextField="EmpName"; myConn.Open(); DropDownList1.DataSource = myCmd.ExecuteReader(); DropDownList1.DataBind(); gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg ExecuteReader EXAMPLE-02 string sConn= "Data Source=SALES;user id=scott;password=tiger;"; string sql = "select * from emp"; OracleConnection myConn = new OracleConnection(sConn); OracleCommand myCmd=new OracleCommand(sql,myConn); myConn.Open(); OracleDataReader myReader= myCmd.ExecuteReader(); while (myReader.Read()) { Response.Write(myReader[0]); } gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg ExecuteReader EXAMPLE-03 public void ReadOra() { OracleDataReader myReader= null; try { conn.Open(); OracleCommand myCmd = new OracleCommand("select * from emp",myConn); myReader =myCmd.ExecuteReader(); while (myReader.Read()) { Response.Write(myReaderr[0]); } } finally { if (myReader != null) { myReader.Close(); } if (myConn != null) { myConn.Close(); } } } gggggggggggggggggggggggggggggggggggggggggggggggggggggg ExecuteScalar EXAMPLE-01 string sConn= "Data Source=SALES;user id=scott;password=tiger;"; string sql = "select count(*) from emp" OracleConnection myConn = new OracleConnection(sConn); OracleCommand myCmd=new OracleCommand(sql,myConn); myConn.Open(); int cnt=(int)myCmd.ExecuteScalar(); note: ExecuteSclar() will return an object. If you want integer as output, then you should use "int" cast operator as shown above. ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg ExecuteNonQuery EXAMPLE-01 public void DeleteMe() { try { myConn.Open(); string sql = "delete from emp where EmpID=25"; OracleCommand myCmd = newOracleCommand(sql,myConn); myCmd.ExecuteNonQuery(); } finally { if (myConn != null) { myConn.Close(); } } } ggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg ExecuteNonQuery EXAMPLE-02 public void InsertOra() { try { conn.Open(); string sql = "insert into emp values(23,'Raja'); OracleCommand myCmd = new OracleCommand(sql,myConn); myCmd.ExecuteNonQuery(); } finally { if (myConn != null) { myConn.Close(); } } }       ExecuteNonQuery EXAMPLE-03 string sConn= "Data Source=SALES;user id=scott;password=tiger;"; string sql = "insert into emp values{2300,'Raja')"; OracleConnection myConn = new OracleConnection(sConn); OracleCommand myCmd=new OracleCommand(sql,myConn); myConn.Open(); myCmd.ExecuteNonQuery();   ExecuteNonQuery : Example 02 string sConn= "Data Source=SALES;user id=scott;password=tiger;"; string sql = "delete from emp where EmpId=25" OracleConnection myConn = new OracleConnection(sConn); OracleCommand myCmd=new OracleCommand(sql,myConn); myConn.Open(); myCmd.ExecuteNonQuery(); gggggggggggggggggggggggggggggggggggggggggggggggggggggggggg