C# ADO.NET OleDbCommand - ExecuteScalar

The ExecuteScalar() method within the C# OleDbCommand Object serves the purpose of retrieving a single value from the Database upon the execution of SQL Statements or Stored Procedures.

ExecuteScalar() method

When invoked, the ExecuteScalar() method executes the provided SQL statement or Stored Procedure and returns a scalar value. This scalar value is extracted from the first column of the first row within the returned Result Set. If the Result Set contains multiple columns or rows, the ExecuteScalar() method disregards all other values and solely returns the value from the first column of the first row. In the event that the Result Set is empty, the ExecuteScalar() method returns a NULL reference.

Int32 count = Convert.ToInt32(cmd.ExecuteScalar());

It makes this approach helpful when aggregate functions which return a single value are used such as Count (*) or Sum(), as the result is expected to be one value. With ExecuteScalar() method, the developers should be able to fetch the aggregated values needed easily through this approach.

Moreover, compared to the ExecuteReader() method, the ExecuteScalar() method utilizes fewer system resources. This makes it an efficient choice in scenarios where only a single value is required, minimizing resource consumption and enhancing overall performance.

Full Source C#
using System; using System.Windows.Forms; using System.Data.OleDb; namespace WindowsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { string connetionString = null; OleDbConnection cnn ; OleDbCommand cmd ; string sql = null; connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"; sql = "Your SQL Statement Here like Select Count(*) from product"; cnn = new OleDbConnection(connetionString); try { cnn.Open(); cmd = new OleDbCommand(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 ! "); } } } }

Conclusion

The ExecuteScalar() method in the C# OleDbCommand Object allows developers to retrieve a single value from the Database by executing SQL Statements or Stored Procedures. It returns the value from the first column of the first row in the Result Set, making it particularly beneficial for working with aggregate functions. Additionally, the method's efficiency in resource usage sets it apart from the ExecuteReader() method in certain use cases.