Sqlcommand. executescalar Method

Sqlcommand. executescalar Method

Returns the first column of the First row in the returned result set. Ignore other columns or rows.

Namespace:System. Data. sqlclient
Assembly:System. Data (in system. Data. dll
Medium)

Syntax

C #

public override Object ExecuteScalar ()

Return Value

The first column or empty reference of the first row in the result set (if the result set is empty ).

Exception

Exception type	Condition
Sqlexception	An exception occurred while executing the command on the locked line. If Microsoft. NET Framework 1.0 is used, this exception is not generated.

Remarks

UseExecutescalarMethod to retrieve a single value (for example, an aggregate value) from the database ). And use executereader
Method, and then use sqldatareader
Compared to the operations required to generate a single value for the returned data, this operation requires less code.

TypicalExecutescalarThe Query format is similar to the following C # example:

Copy code

 cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";Int32 count = (Int32) cmd.ExecuteScalar();

Example

The following example creates a sqlcommand and then uses
Executescalar
Run it. Two strings are passed to this example. One String indicates the new value to be inserted into the table, and the other string is used to connect to the data source. If a new row has been inserted, this function returns a new"Identity"Column value. If it fails, return
0.

C #
Copy code

static public int AddProductCategory(string newName, string connString){Int32 newProdID = 0;string sql ="INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "+ "SELECT CAST(scope_identity() AS int)";using (SqlConnection conn = new SqlConnection(connString)){SqlCommand cmd = new SqlCommand(sql, conn);cmd.Parameters.Add("@Name", SqlDbType.VarChar);cmd.Parameters["@name"].Value = newName;try{conn.Open();newProdID = (Int32)cmd.ExecuteScalar();}catch (Exception ex){Console.WriteLine(ex.Message);}}return (int)newProdID;