How to populate a DataSet object from a database by using Visual C++ .NET or Visual C++ 2005

Article translations Article translations
Article ID: 818779 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article describes how to fill a DataSet object with the results of one or more database queries and then how to access the data after the query is loaded in the DataSet object. DataSet objects are in-memory objects that can hold tables, views, and relationships that form a key part of data access in the Microsoft .NET Framework.


MORE INFORMATION

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you must have:
  • Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
  • Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or Microsoft Data Engine with the Pubs sample database installed
  • Microsoft Visual Studio .NET or Microsoft Visual Studio 2005
This article assumes that you are familiar with the following topics:
  • Database terminology
  • Microsoft SQL Server


Fill a DataSet

By using a variety of objects in the System.Data namespace, you can connect to a database server, run a query, and then have the results placed in a DataSet object. The DataSet is a disconnected object. Therefore, after the data is loaded, the connection to the database is no longer used until you want to load more data or to update the server with the changes that you made to your in-memory copy of the information.

To load data from a database to a DataSet, follow these steps:
  1. Start Visual Studio .NET or Visual Studio 2005.
  2. Create a new Managed C++ application in Visual C++ .NET 2002, or create a new console application in Visual C++ .NET 2003, or create a new CLR Console Application in Visual C++ 2005. Name the Project MyApplication and then click OK.
  3. Add the following code to the MyApplication.cpp file.

    This adds a reference to the System.Dll namespace, the System.Xml namespace, and the System.Data namespace.
    #using <System.Dll>
    #using <System.Data.Dll>
    #using <System.Xml.Dll>
    
  4. Use the USING statement on the System namespace, the System.Data namespace, the System.Xml namespace, the System.Collections namespace, and the System.Data.SqlClient namespace so that you do not have to qualify declarations from these namespaces later in your code. You must use these statements before any other declarations.
    using namespace System;
    using namespace System::Data;
    using namespace System::Xml;
    using namespace System::Collections;
    using namespace System::Data::SqlClient;
    
  5. Move the data from the database to the DataSet.

    To do this, you must establish a database connection. This requires a System.Data.SqlClient.SqlCommand object and a connection string. The connection string in the code connects a computer that is running SQL Server that is located on the local computer (the computer where the code runs). You must modify this connection string as appropriate for your environment. After the SqlConnection object is created, call the Open method of that object to establish the actual database link.
    SqlConnection* objConn;
    String* sConnectionString;
    sConnectionString = "Password=myPassword;User ID=myUserID;Initial Catalog=pubs;Data Source=(local)";
    
    objConn = new SqlConnection(sConnectionString);
    objConn->Open();
    
  6. Create a DataAdapter object that represents the link between the database and your DataSet object. You can specify SQL Server or another type of command that is used to retrieve data as part of the constructor object of the DataAdapter. The following sample uses an SQL statement that retrieves records from the Authors table in the Pubs database.
    SqlDataAdapter* daAuthors = new SqlDataAdapter("Select * From Authors", objConn);
    
  7. Declare and then create an instance of a DataSet object.

    When you do this, you must supply a name for the whole DataSet before you can start to load any data. The name may contain several distinct tables.
    DataSet* dsPubs = new DataSet("Pubs");
  8. Run FillSchema followed by Fill while handling the data loading.

    The SqlDataAdapter class provides two methods, the Fill method and the FillSchema method, that are crucial to loading this data. Both these methods load information to a DataSet. The Fill method loads the data itself, and the FillSchema method loads all the available metadata about a particular table (such as column names, primary keys, and constraints).
    daAuthors->FillSchema(dsPubs,SchemaType::Source, "Authors");
    daAuthors->Fill(dsPubs,"Authors");
    

    If you only use Fill, you can only load the basic metadata that you must have to describe the column names and the data types. The Fill method does not load the primary key information. To change this default behavior, you can set the MissingSchemaAction property of the DataAdapter object to MissingSchemaAction.AddWithKey. This loads the primary key metadata in addition to the default information.
    daAuthors->MissingSchemaAction = MissingSchemaAction::AddWithKey;
    daAuthors->Fill(dsPubs,"Authors");
    
    The data is now available as an individual DataTable object in the Tables collection of the DataSet. If you specify a table name in the calls to FillSchema and to Fill, you can use that name to access the specific table that you require.
    DataTable* tblAuthors = dsPubs->Tables->Item["Authors"];
    
  9. Use the GetEnumerator function to access the DataRow objects in the Rows collection of the DataTable. Use while loop to iterate each row of the table. You can access columns by name or by positional index. Zero (0) is the first column position.
    IEnumerator* iEnum = tblAuthors->Rows->GetEnumerator();
            
     while(iEnum->MoveNext())
     {
            Console::WriteLine("{0}   {1}",dynamic_cast<String*>(dynamic_cast<DataRow *>(iEnum->Current)->get_Item("au_fname")),
                                           dynamic_cast<String*>(dynamic_cast<DataRow *>(iEnum->Current)->get_Item("au_lname")));
     }
    
  10. Save your project. On the Debug menu, click Start to run the project.



Complete Code Listing

// This is the main project file for the VC++ application project 
// that is generated by using the Application wizard.

#include "stdafx.h"

#using <mscorlib.dll>
#include <tchar.h>
#using <System.Dll>
#using <System.Data.Dll>
#using <System.Xml.Dll>

using namespace System;
using namespace System::Data;
using namespace System::Xml;
using namespace System::Collections;
using namespace System::Data::SqlClient;

// This is the entry point for this application.
int _tmain(void)
{
    SqlConnection* objConn;
    try
    {

        
        String* sConnectionString;
        sConnectionString = "Password=myPassword;User ID=myUserID;Initial Catalog=pubs;Data Source=(local)";

        objConn = new SqlConnection(sConnectionString);
        objConn->Open();
        
        SqlDataAdapter* daAuthors = new SqlDataAdapter("Select * From Authors", objConn);
        DataSet* dsPubs = new DataSet("Pubs");
        
        daAuthors->FillSchema(dsPubs,SchemaType::Source, "Authors");
        daAuthors->Fill(dsPubs,"Authors");

        DataTable* tblAuthors = dsPubs->Tables->Item["Authors"];
        
        IEnumerator* iEnum = tblAuthors->Rows->GetEnumerator();
        
        while(iEnum->MoveNext())
        {
            Console::WriteLine("{0}   {1}",dynamic_cast<String*>(dynamic_cast<DataRow *>(iEnum->Current)->get_Item("au_fname")),
                                           dynamic_cast<String*>(dynamic_cast<DataRow *>(iEnum->Current)->get_Item("au_lname")));
            
        }
    }
    catch(Exception *ex)
    {
        Console::WriteLine( ex->Message );
    }
    __finally
    {
        objConn->Close();
    }
    
    return 0;
}
Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile the previous code sample. To add the common language runtime support compiler option in Visual C++ 2005, follow these steps:
  1. Click Project, and then click <ProjectName> Properties.

    Note <ProjectName> is a placeholder for the name of the project.
  2. Expand Configuration Properties, and then click General.
  3. Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting in the right pane, click Apply, and then click OK.
For more information about the common language runtime support compiler option, visit the following Microsoft Web site:
/clr (Common Language Runtime Compilation)
http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx

REFERENCES

For more information, visit the following Microsoft Web sites:

http://msdn2.microsoft.com/en-us/library/ms810293.aspx

http://msdn2.microsoft.com/en-us/library/ms973217.aspx

http://msdn.microsoft.com/net

Properties

Article ID: 818779 - Last Review: May 16, 2007 - Revision: 3.2
APPLIES TO
  • Microsoft Visual C++ 2005 Express Edition
  • Microsoft Visual C++ .NET 2003 Standard Edition
  • Microsoft Visual C++ .NET 2002 Standard Edition
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
Keywords: 
kbsystemdata kbsqlclient kbhowtomaster kbhowto KB818779

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com