C++/CLI Code Snippet - Get First Row As Object Array From SqlDataReader

C++/CLI Code Snippet - Get First Row As Object Array From SqlDataReader

This .Net C++/CLI code snippet execute a SQL statement using open database connection and return all the columns data in SqlDataReader for first row. Additional rows are ignored. This function is useful for query for one data record row, after querying function will convert data row into dictionary object array and close SqlDataReader. Conversion of SqlDataReader into dictionary object array and closing SqlDataReader allows to create multiple object arrays without having to worry about opening more than one SqlDataReader for SQL Server. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlDataReader. Modify the exception handling section for your project requirements.

System::Collections::Generic::Dictionary<System::String^, System::Object^> ^GetSqlDataReaderFirstRow(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    // temporary dictionary object array to hold SqlDataReader first row
    System::Collections::Generic::Dictionary<System::String^, System::Object^> ^_FirstRow = gcnew System::Collections::Generic::Dictionary<System::String^, System::Object^>();

    // Set temporary variable to create data reader
    System::Data::SqlClient::SqlDataReader ^_SqlDataReader = nullptr;

    // lets run the given SQL statement and get the data to SqlDataReader
    try
    {
        // Pass the connection to a command object
        System::Data::SqlClient::SqlCommand ^_SqlCommand = gcnew System::Data::SqlClient::SqlCommand(_SQL, _SqlConnection);

        // get query results
        _SqlDataReader = _SqlCommand->ExecuteReader();

    }
    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);

        // failed SQL execution, lets return null
        return nullptr;
    }

    // SQL successfully executed, lets return the SqlDataReader First Data Row
    if (_SqlDataReader != nullptr && _SqlDataReader->HasRows && _SqlDataReader->Read())
    {
        // put the first data row in a temporary object array before close SqlDataReader
        for (int _Column = 0; _Column <= _SqlDataReader->FieldCount - 1; _Column++)
        {
            try
            {
                // Add each colum to dictionary
                _FirstRow->Add(_SqlDataReader->GetName(_Column), _SqlDataReader[_Column]);
            }
            catch(...)
            {
            }
        }

        // close SqlDataReader
        _SqlDataReader->Close();
        delete _SqlDataReader;

        return _FirstRow;
    }
    else
    {
        if (_SqlDataReader != nullptr)
        {
            // close SqlDataReader
            _SqlDataReader->Close();
            delete _SqlDataReader;
        }

        // no data found, return null
        return nullptr;
    }
}

Here is a simple example showing how to use above function (GetSqlDataReaderFirstRow) to connect to SQL database and get Dictionary Object Array for given SQL statement.

// Lets call above function to create a new SqlDataReader
// using given database connection string and SQL statement
    // Pass database connection string to function
    // Pass SQL statement to create SqlDataReader
System::Data::SqlClient::SqlDataReader ^_SqlDataReader = GetSqlDataReader("Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;", "SELECT NAME, PRICE, COST FROM INVENTORY");

// Check we have data in SqlDataReader
if (_SqlDataReader != nullptr && _SqlDataReader->HasRows)
{
    // We have data in SqlDataReader, lets loop through and display the data
    while (_SqlDataReader->Read())
    {
        Console::WriteLine(_SqlDataReader["name"]->ToString());
    }

    // Close SqlDataReader
    _SqlDataReader->Close();
    delete _SqlDataReader;
}

// Check for valid open database connection before query database
if (_SqlConnection != nullptr && _SqlConnection->State == ConnectionState::Open)
{
    // Lets call above function to create a new SqlDataReader
    // using open database connection and SQL statement
        // Pass open database connection to function
        // Pass SQL statement to create SqlDataReader
    System::Collections::Generic::Dictionary<System::String^, System::Object^> ^_SqlDataReaderFirstRow = GetSqlDataReaderFirstRow(_SqlConnection, "SELECT NAME, PRICE, COST FROM INVENTORY");

    // Check we have data
    if (_SqlDataReaderFirstRow != nullptr && _SqlDataReaderFirstRow->Count > 0)
    {
        Console::WriteLine(_SqlDataReaderFirstRow["model"]->ToString());
    }

    // close database connection
    _SqlConnection->Close();
}

C++/CLI Keywords Used:

• SqlDataReader
• SqlConnection
• ConnectionString
• SqlCommand
• ExecuteReader
• Exception
• Dictionary
• nullptr
• Close
• delete
• Console
• WriteLine

Code Snippet Information:

• Applies To: .Net, C++, CLI, SqlDataReader, Dictionary, SQL, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
• Programming Language : C++/CLI

External Resources:

• SqlDataReader Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• SqlCommand.ExecuteReader Method
• Exception Class

Leave a comment