A Closer Look at ADO.NET: The Command Object - The ExecuteScalar() Method

(Page 2 of 5 )

In the following code example I have established a connection with the database pubs (located in the server MichaelServer) and I have used the method ExecuteScalar() of the SqlComm1 object to execute a command against the titles table, so let's take a look at the code:

using System;
// reference to the namespace that contains most
// of the classes that form the ADO.NET Architecture
using System.Data;
// referene to the namespace of the SQL Server .NET Data provider
using System.Data.SqlClient;

namespace AdoApp
{
  class Class1
  {
    static void Main(string[] args)
    {
      SqlConnection SqlConn1 = new SqlConnection();
      SqlConn1.ConnectionString = "Server=(local);Database=pubs;User ID=sa;Password=;";
      // creating and initializing the SqlCommand Object
      SqlCommand SqlComm1 = new SqlCommand();
      SqlComm1.Connection = SqlConn1;
      SqlComm1.CommandType = CommandType.Text;
      SqlComm1.CommandText = "SELECT COUNT(*) FROM titles";
      try
      {
        SqlConn1.Open();
        Console.WriteLine("There are " + SqlComm1.ExecuteScalar().ToString() + " records in titles");
      }
      catch(Exception ex)
      {
        Console.WriteLine(ex.Message);
      } 
      finally
      {
        // here we call the Close() method to close the Connection
        SqlConn1.Close();
        Console.ReadLine(); 
      } 
    }
  }
}

Here's the result of running this code example:

Let's walk through the code line by line. The first statement creates a SqlConnection instance called SqlConn1, then the following statement assigns a connection string to the property ConnectionString of the object SqlConn1. The third statement in the Main() method creates a SqlCommand instance called SqlComm1, then the next statement assigns the SqlConn1 object reference to the SqlComm1 object through the Connection property of the SqlComm1 object.

Because I wanted to execute a T-SQL statement against the titles table, I have assigned the enumeration value CommandType.Text to the property SqlComm1.CommandType. I have also assigned the T-SQL Statement as a string value to the SqlComm1.CommandText. In the Try block we open the connection to the database using the SqlConn1.Open(), then we use the SqlComm1.ExecuteScalar() method to print out the result of executing the text (the T-SQL Statement) on the server.

Actually, the ExecuteScalar() method returns a scalar value, or we can say that it returns the first value of the first column in the first record of the returned result set. So we use the ExecuteScalar() method when our T-SQL Statement returns only one value, a statement like SELECT COUNT(*) FROM titles. The ExecuteScalar() method returns the value as object so we have two solutions. The first is to use the ToString() method of the ExecuteScalar() return value, which is the object to return the value as a string. The second solution is to cast the return value to a specific data type and store it in a variable. I will talk more about that later when we discuss ADO.NET Data Types. There's nothing special about the rest of the code, so let's use the method ExecuteNonQuery().

Next: The ExecuteNonQuery() Method (INSERT Data) >>
 

More Database Code Articles
More By Michael Youssef

Comments On: A Closer Look at ADO.NET: The Command Object

· Its the tutorial I've been looking for. Awesome tutorial. Everything Nicely...    · Hello, Thanks and I hope that you like my C# articles too :-). About ADO.NET...   >>> Post your comment now!