ExecuteScalar() Method:ExecuteScalar() method is used to retrieve a single value from database. It executes the defined query and returns the value in the first column of the first row in the selected result set and ignores all other columns and rows in the result set. It is use to get aggregate value from database, for example count or total of rows. So it works with non action queries that use aggregate functions. ExecuteScalar() method is a faster way when we compare it to other ways to retrieve single value from database. It returns a value as object and we have to cast it to appropriate type.ExecuteNonQuery() Method:ExecuteNonQuery() method is used to manipulate data in database and is used for statements without results such as CREATE, INSERT, UPDATE and DELETE commands. It does not return any data but it returns number of rows affected. If NO COUNT property is ON then it will not return number of rows affected. It will not give access to result set generated by the statement. The return value of number of rows affected is of type integer and you can get it in an integer variable. It will tell you how many rows have been affected in result of your statement. ExecuteNonQuery() method is a flexible method and we can use it input and output parameters.
But as I execute command.ExecuteNonQuery(); It returns -1 all the time and even though data has inserted.what does it means....?
ExecuteNonQuery() returns : the number of rows affected ExecuteScaler() returns : the first column of first row
ExecuteNonQuery() returns integer (number of records affected) ExecuteScalar returns Object
ExecuteNonQuery--> int ExecutScalar-->Object
ExecuteNonQuery() returns number of rows affected(ex: 2 rows updated), so return type of ExecuteNonQuery is Integer.ExecuteScalar() is used to retrieve a single value from database, so return type of ExecuteScalar is Object.
ExecuteScalar() return type is object type. ExecuteNonQuery() return type is Int32 type
i--- non ExecuteScaler ------ object
ExecuteNonQuery() method: This returns how many rows are affected by the query. Means this is user for basically insert, update , delete query ExecuteScalar Method: This returns the object. Means the value from a column of table .
ExecuteNonQurey will return an integer ExecuteScala will return an Object. ExecuteReader will return the DataReader object.
Return Types of Command Methods:- 1- ExecuteNonQuery() - Integer 2-ExecuteScalar() - Object 3-ExecuteReader() - DataReader
ExecuteNonQuery() : Doesn't return any data but returns affected row count. Return type is integer. ExecuteScalar Method(): It returns the value only in the first column of the first row. Return type is object.
ExecuteNonQuery() - Returns number of values (like table of rows). ExecuteScaler() - Returns single value.
ExecuteNonQuery() returns : Return a table of rows (lik enumber of rows). ExecuteScaler() returns : Returns single value or row.
The return type of ExecuteNonQuery() is intezer(int32) typeand ExecuteScalar() returns Object type
ExecuteNonQuery():-is response to execute a command in connected mode and return integer no. that not fire how many row affected. this method suitable for DML file. (execute return type is integer ) ExecuteScalar()-is also a method which also used to executed method in connected mode but it return a single cells value against the select command that should be first row and first column.(execute return type is object).
ExecuteNonQuery() mainly return how many row affected by a DML query i.e. it return an integer value. While ExecuteScalar() method first column of first row value of result set....
Hello Naitik Jani,This both method is part of command object and ExecuteNonQuery return type is Int as per as result is concern it will return count of affected row in database.Talking about ExecuteScalar it will return row or recod from database and return type of ExecuteScalar is Object.
ExecuteNonQuery is used for basic CRUD operations and returns the no. of rows that have been affected. Eg. If you try to delete certain rows over a specific data then you will get the no. of rows that have deleted as output.ExecuteScalar would simply return the value in the First Column of First Row of the output that your query returns.
ExecuteNonQuery returns:- integer ExecuteScalar returns:- Object
Return Value of ExecuteNonQuery is Int32. Return Value of ExecuteScalar is System.Object.
ExecuteScalar() Method mainly return First Column of First Row in the form of Object and ExecuteNonQuery() return now of rows which are affected by DML Query
ExecuteNonQuery expects to run a command, or a stored procedure, that affects the state of the specified table. This means anything but a query command. You normally use this method to issue an INSERT, UPDATE, DELETE, CREATE, and SET statement.ExecuteNonQuery returns only the number of rows affected by the command execution, or ?€“1 should this information be unavailable. It doesn’t give you a chance to access any result set generated by the statement or the stored procedure. Actually, there’s really nothing to prevent you from using this method for a query command, but in this case you get neither the resultset nor the number of the affected rows. cmd.Connection.Open(); nRecsAffected = cmd.ExecuteNonQuery(); cmd.Connection.Close(); // check the record(s) affected here
integer
When we manipulate database data in ADO.NET code, ExecuteReader, ExecuteScalar and ExecuteNonQuery are three basic methods extensively used to run queries. These methods are available in SqlCommand, OledbCommand and DbCommand classes under System.Data.SqlClient, System.Data.OleDb and System.Data.Common namespaces respectively. I will explain purpose of these three methods with examples.ExecuteScalar() MethodExecuteScalar() method is used to retrieve a single value from database. It executes the defined query and returns the value in the first column of the first row in the selected result set and ignores all other columns and rows in the result set. It is use to get aggregate value from database, for example count or total of rows. So it works with non action queries that use aggregate functions. ExecuteScalar() method is a faster way when we compare it to other ways to retrieve single value from database. It returns a value as object and we have to cast it to appropriate type.Here is the example of ExecuteScalar() method. Use System.Data.SqlClient namespace for this example.C#public string GetCustomerName() {string query = "SELECT CustName FROM Customers WHERE CustID = 2";string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";SqlConnection conn = new SqlConnection(connString);SqlCommand cmd = new SqlCommand(query, conn);string custName = "";try{conn.Open();custName = cmd.ExecuteScalar().ToString();}catch (Exception ex){Response.Write(ex.Message);}finally{conn.Close();}return custName; }VB.NETPublic Function GetCustomerName() As StringDim query As String = "SELECT CustName FROM Customers WHERE CustID = 2"Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"Dim conn As New SqlConnection(connString)Dim cmd As New SqlCommand(query, conn)Dim custName As String = ""Tryconn.Open()custName = cmd.ExecuteScalar().ToString()Catch ex As ExceptionResponse.Write(ex.Message)Finallyconn.Close()End TryReturn custName End FunctionExecuteScalar() method is used here to retrieve a single value that is customer name for a specific customer ID. It returns object value so we have to cast it in appropriate type “string” to assign it to variable.ExecuteNonQuery() MethodExecuteNonQuery() method is used to manipulate data in database and is used for statements without results such as CREATE, INSERT, UPDATE and DELETE commands. It does not return any data but it returns number of rows affected. If NO COUNT property is ON then it will not return number of rows affected. It will not give access to result set generated by the statement. The return value of number of rows affected is of type integer and you can get it in an integer variable. It will tell you how many rows have been affected in result of your statement. ExecuteNonQuery() method is a flexible method and we can use it input and output parameters.Here is the example of ExecuteNonQuery() method. Use System.Data.SqlClient namespace for this example.C#public int DeleteCustomer() {string query = "DELETE FROM Customers WHERE CustID = 5";string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";SqlConnection conn = new SqlConnection(connString);SqlCommand cmd = new SqlCommand(query, conn);int rowsAffected = 0;try{conn.Open();rowsAffected = cmd.ExecuteNonQuery(); ;}catch (Exception ex){Response.Write(ex.Message);}finally{conn.Close();}return rowsAffected; }VB.NETPublic Function DeleteCustomer() As IntegerDim query As String = "DELETE FROM Customers WHERE CustID = 5"Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"Dim conn As New SqlConnection(connString)Dim cmd As New SqlCommand(query, conn)Dim rowsAffected As Integer = 0Tryconn.Open()rowsAffected = cmd.ExecuteNonQuery()Catch ex As ExceptionResponse.Write(ex.Message)Finallyconn.Close()End TryReturn rowsAffected End FunctionExecuteNonQuery() method is used here to delete a record for specific customer from database table. This method returns number of rows affected as integer.ExecuteReader() MethodExecuteReader() method is used with SELECT command. It returns set of rows by executing query or stored procedure mentioned in the command object. When we use ExecuteReader() method, It is necessary that query returns value. It can return one or more result sets as a result of query. The returned data has the DataReader return type. ExecuteReader() method is a read-only and forward-only way of retrieving data. It means we cannot edit data in result set. ExecuteReader() is also a connected way of data retrieval. It uses SELECT statement to read through the table from first to last record in a connected way. Do not use ExecuteReader() method when you know that the result of the query is exactly one record. The better is to use ExecuteScalar() in this situation.Here is the example of ExecuteReader() method. Use System.Data.SqlClient namespace for this example.C#public void GetCustomerDetail() {string query = "SELECT * FROM Customers WHERE CustID = 2";string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";SqlConnection conn = new SqlConnection(connString);SqlCommand cmd = new SqlCommand(query, conn);string name = "";string city = "";string state = "";try{conn.Open();SqlDataReader reader = cmd.ExecuteReader();while (reader.Read()){name = reader["CustName"].ToString();city = reader["City"].ToString();state = reader["state"].ToString();}}catch (Exception ex){Response.Write(ex.Message);}finally{conn.Close();} }VB.NETPublic Sub GetCustomerDetail()Dim query As String = "SELECT * FROM Customers WHERE CustID = 2"Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"Dim conn As New SqlConnection(connString)Dim cmd As New SqlCommand(query, conn)Dim name As String = ""Dim city As String = ""Dim state As String = ""Tryconn.Open()Dim reader As SqlDataReader = cmd.ExecuteReader()While reader.Read()name = reader("CustName").ToString()city = reader("City").ToString()state = reader("state").ToString()End WhileCatch ex As ExceptionResponse.Write(ex.Message)Finallyconn.Close()End Try End SubExecuteReader() method is used here to retrieve all detail about a specific customer. It can return any number of records. We have to call the Read() method to read data from result set.
ReturnType of ExecuteNonQuery is 'int' and ReturnType of ExecuteScalar is object
ExecuteNonQuery()'s return type is Int32 and ExecuteSclar's return type is Object.
execute scalar return type is object so must and should we need assign it required type,ExecuteNonquery() is int type so we need assign it to int type variable
ExecuteScalar is typically used when your query returns a single value.ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.). it return integer , either 0 or 1