Knowledge Base

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

Article ID: 818779

Article Last Modified on 5/16/2007

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

For a Microsoft Visual Basic .NET version of this article, see 301216.

For a Microsoft Visual C# .NET version of this article, see 314145.

This article refers to the following Microsoft .NET Framework Class Library namespace:

System.Data.SqlClient

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.

back to the top

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

back to the top

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:

Start Visual Studio .NET or Visual Studio 2005.
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.
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>
```
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;
```
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();
```
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);
```
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");
```
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"];
```

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

Save your project. On the Debug menu, click Start to run the project.

back to the top

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:

Click Project, and then click <ProjectName> Properties.

Note <ProjectName> is a placeholder for the name of the project.

Expand Configuration Properties, and then click General.
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

back to the top

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

back to the top

Keywords: kbsystemdata kbsqlclient kbhowtomaster kbhowto KB818779

Microsoft KB Archive/818779

Contents

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

IN THIS TASK

SUMMARY

MORE INFORMATION

Requirements

Fill a DataSet

Complete Code Listing

REFERENCES