Requirements

• 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

• Database terminology
• Microsoft SQL Server

Back to the top

Fill a DataSet

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.

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

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.

Back to the top