C++/CLI Code Snippet - Get DataTable using open connection

C++/CLI Code Snippet - Get DataTable using open connection

This .Net C++/CLI code snippet connect connects to SQL server and executes SQL statement and return DataTable. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlDataAdapter. Modify the exception handling section to as your project requirements.

DataTable ^GetDataTable(System::Data::SqlClient::SqlConnection ^%_SqlConnection, System::String ^_SQL)
{
    // Pass the connection to a command object
    System::Data::SqlClient::SqlCommand ^_SqlCommand = gcnew System::Data::SqlClient::SqlCommand(_SQL, _SqlConnection);
    System::Data::SqlClient::SqlDataAdapter ^_SqlDataAdapter = gcnew System::Data::SqlClient::SqlDataAdapter();
    _SqlDataAdapter->SelectCommand = _SqlCommand;

    DataTable ^_DataTable = gcnew DataTable();
    _DataTable->Locale = System::Globalization::CultureInfo::InvariantCulture;

    // Adds or refreshes rows in the DataSet to match those in the data source
    try
    {
        _SqlDataAdapter->Fill(_DataTable);
    }
    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 nullptr;
    }

    return _DataTable;
}

Here is a simple example showing how to use above function (GetDataTable) to connect to SQL database and get DataTable for given SQL statement.

// set temporary variable for database connection
System::Data::SqlClient::SqlConnection ^_SqlConnection = gcnew 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 != 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
    DataTable ^_DataTable = GetDataTable(_SqlConnection, "SELECT * FROM sampletable");

    // Check we have data
    if (_DataTable != nullptr)
    {
        dataGridView1->DataSource = _DataTable;
    }

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

C++/CLI Keywords Used:

• DataTable
• SqlDataAdapter
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

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

External Resources:

• DataTable Class
• SqlDataAdapter Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Leave a comment