C# ADO.NET SqlCommand - ExecuteScalar

The ExecuteScalar() method within the C# SqlCommand Object serves as a valuable tool for retrieving a single value from the Database after executing an SQL Statement. This method is specifically designed to execute SQL statements and Stored Procedures, returning a scalar value located in the first column of the first row within the resulting Result Set.

ExecuteScalar() method

In situations where the Result Set contains multiple columns or rows, the ExecuteScalar() method focuses solely on extracting the value found in the first column of the first row, disregarding all other values. If the Result Set is empty, the method will return a NULL reference.

The ExecuteScalar() method proves particularly beneficial when working with aggregate functions such as Count(*) or Sum(). By utilizing this method, developers can efficiently retrieve and utilize specific scalar values derived from the executed SQL Statement or Stored Procedure.

Full Source C#
using System; using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; SqlConnection cnn ; SqlCommand cmd ; string sql = null; connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"; sql = "Your SQL Statement Here like Select Count(*) from product"; cnn = new SqlConnection(connetionString); try { cnn.Open(); cmd = new SqlCommand(sql, cnn); Int32 count = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Dispose(); cnn.Close(); MessageBox.Show (" No. of Rows " + count); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); } } } }

One notable advantage of the ExecuteScalar() method over ExecuteReader() is its ability to conserve System resources. By retrieving only a single value, this method minimizes resource consumption compared to the broader Result Set retrieval provided by ExecuteReader().

Conclusion

The ExecuteScalar() method within the C# SqlCommand Object offers a convenient and efficient means of retrieving a single value from the Database. Its specialized functionality, combined with resource optimization, makes it a valuable tool when dealing with scalar values and aggregate functions within the ADO.NET framework.