C# Code Snippet - Get Scalar using open connection

C# Code Snippet - Get Scalar using open connection

This .Net C# code snippet connects to SQL server and executes SQL statement and returns the first column of the first row in the result set returned by the query as a object. Additional columns or rows are ignored. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlCommand. Modify the exception handling section to as your project requirements.

public object ExecuteScalar(
        ref System.Data.SqlClient.SqlConnection _SqlConnection,
        string _SQL)
{
    object _SqlRetVal = null;

    try
    {
        // Executes the query, and returns the first column of the first row in the result 
        // set returned by the query. Additional columns or rows are ignored.
        System.Data.SqlClient.SqlCommand _SqlCommand
                = new System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection);

        _SqlRetVal = _SqlCommand.ExecuteScalar();

        // Dispose command
        _SqlCommand.Dispose();
        _SqlCommand = null;
    }
    catch (Exception _Exception)
    {
        // Error occurred while trying to execute reader
        // send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message);

        return null;
    }

    return _SqlRetVal;
}

Here is a simple example showing how to use above function (ExecuteScalar) to connect to SQL database and get first column of the first row in the result set returned by the query as a object.

// set temporary variable for database connection
System.Data.SqlClient.SqlConnection _SqlConnection = new System.Data.SqlClient.SqlConnection();

// assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;";

// Connect to database
try
{
    _SqlConnection.Open();
}
catch (Exception _Exception)
{
    // Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message);
}

// Check for valid open database connection before query database
if (_SqlConnection != null && _SqlConnection.State == ConnectionState.Open)
{
    // Lets call above function to execute scalar
    // using open database connection and SQL statement
    object _object = ExecuteScalar(
        // Pass open database connection to function
        ref _SqlConnection,
        // Pass SQL statement to create SqlCommand
        "SELECT name FROM sampletable");

    // Check we have data
    if (_object != null)
    {
        Console.WriteLine(_object.ToString());
    }
                
    // close database connection
    _SqlConnection.Close();
}

C# Keywords Used:

• ExecuteScalar
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, C#, CLI, SQL, SqlCommand, ExecuteScalar, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
• Programming Language : C# (C-Sharp)

External Resources:

• SqlCommand.ExecuteScalar Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Leave a comment