How to update a database from a DataSet object by using Visual C++ .NET 2003 or Visual C++ 2005 (837625)

INTRODUCTION

This step-by-step article describes how to create a DataSet object that contains data that is loaded from a database and shows you how to modify that data. This article also describes how to send the data back to the database to update the original source.

DataSet objects are a key part of data access in the Microsoft .NET Framework. DataSet objects are in-memory objects that can hold tables, views, and relationships.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• One of the following:
  • Microsoft Windows 2000 Professional
  • Microsoft Windows 2000 Server
  • Microsoft Windows 2000 Advanced Server
  • Microsoft Windows NT 4.0 Server
  • Microsoft Windows XP
  • Microsoft Windows Server 2003
• One of the following:
  • Microsoft SQL Server version 7.0
  • Microsoft SQL Server 2000
  • Microsoft Data Engine (MSDE) with the PUBS sample database installed, and that has the appropriate permissions, UserID, and password values
• Microsoft Visual Studio .NET 2003 or Microsoft Visual Studio 2005

This article assumes that you are familiar with the following topics:

• Programming with Microsoft Visual C++ .NET 2003 or Microsoft Visual Studio 2005
• Database terminology
• SQL Server

back to the top

Create a console application

To create a console application that is used to describe how to update the database by using the DataSet object, follow these steps:

1. Start Microsoft Visual Studio .NET 2003 or Microsoft Visual Studio 2005.
2. On the File menu, point to New, and then click Project.
3. Click Visual C++ Projects under Project Types, and then click Console Application (.NET) under Templates.
   
   Note In Visual Studio 2005, click Visual C++ under Project Types, and then click CLR Console Application under Templates.
4. In the Name box, type UpdateDataSet, and then click OK.
5. In Solution Explorer, right-click the UpdateDataSet project node, and then click Add Reference.
6. In the Add Reference dialog box, click the .NET tab.
7. Under Component Name, double-click System.Xml.dll, and then click OK.
8. In Solution Explorer, expand all the folders, and then double-click UpdateDataSet.cpp in the Source Files folder.
9. Add the following code in the code window after the using statement:

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

back to the top

Update the database by using the DataSet object

This section demonstrates how to use the DataSet object to update the data in a database.

Important You can also use a SqlCommand object to insert, to update, and to delete data directly in a database.

The DataSet object can be viewed as an in-memory cache of data that is retrieved from a database. The DataSet object includes a collection of tables, relationships, and constraints. Before you can modify the data and submit the changes back to the database, you must load the information in the DataSet object. After the DataSet object is loaded, you can modify the data. The DataSet object will track these changes. To use a DataSet object to update the database, follow these steps:

1. Replace the code in the _tmain function of the UpdateDataSet.cpp file with the following code:

   SqlConnection  *objConn;
   
   String *sConnectionString;
   sConnectionString = S"Password=StrongPassword;User ID=UserName;Initial Catalog=pubs;Data Source=(local)";
   
   objConn = new SqlConnection(sConnectionString);
   objConn->Open();
   
   // Create an instance of a DataAdapter.
   SqlDataAdapter* daAuthors = new SqlDataAdapter(S"Select * From Authors", objConn);
   
   // Create an instance of a DataSet, and retrieve data from the Authors table.
   DataSet* dsPubs = new DataSet(S"Pubs");
   daAuthors->FillSchema(dsPubs,SchemaType::Source, S"Authors");
   daAuthors->Fill(dsPubs,S"Authors");

   Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile this code sample. To do this, follow these steps:
   1. Click Project, and then click ProjectName Properties.
      
      Note ProjectName represents 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 on the right pane, click Apply, and then click OK.
   For more information about the common language runtime support compiler options, visit the following Microsoft Web site:

   /clr (Common Language Runtime Compilation)
   http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx

   The connection string in this code points to a SQL Server server that is located on the local computer (or the computer where the code is running). Replace this string with your own settings.
   
   In this code, a SqlConnection object is created, and then a SqlDataAdapter object is created that is used to fill the DataSet object with data.
2. Now that the data is loaded, you can modify it. There are many ways to add a row (or a record).This code sample uses the following three-step procedure:
   1. Obtain a new DataRow object from the DataTable object.
   2. Set the DataRow field values as necessary.
   3. Pass the new DataRow object as a parameter to the Add method of the DataTable->Rows collection.
   To do this, add the following code after the code that you added in step 1:

   // BEGIN ADD CODE 
   // Create a new instance of a DataTable.
   DataTable* tblAuthors = dsPubs->Tables->Item[S"Authors"];
   
   
   // Obtain a new DataRow object from the DataTable.
   DataRow* drCurrent = tblAuthors->NewRow();
   
   // Set the DataRow field values as necessary.
   drCurrent->set_Item(S"au_id",new String(S"993-21-3427"));
   drCurrent->set_Item(S"au_fname",new String(S"George"));
   drCurrent->set_Item(S"au_lname",new String(S"Johnson"));
   drCurrent->set_Item(S"phone",new String(S"800 226-0752"));
   drCurrent->set_Item(S"address", new String (S"1956 Arlington Pl."));
   drCurrent->set_Item(S"city", new String(S"Winnipeg"));
   drCurrent->set_Item(S"state", new String(S"MB"));
   drCurrent->set_Item(S"contract",__box(1));
   
   // Pass the new DataRow object as a parameter to the Add method of the DataTable Rows collection
   tblAuthors->Rows->Add(drCurrent);
   Console::WriteLine(S"Add was successful, Click any key to continue!!");
   Console::ReadLine();
   
   // END ADD CODE  

3. To edit existing rows, obtain the appropriate DataRow object, and then provide new values for one or more columns. You must first find the correct row. This is much easier because you loaded the schema of the table, and you loaded the data (the call to the FillSchema method in step 1). With the schema in place, the table recognizes which column is the primary key, and recognizes that the Find method of the Rows collection is available.
   
   To do this, add the following code after the code that you added in step 2:

   // BEGIN EDIT CODE 
   
   drCurrent = tblAuthors->Rows->Find(new String(S"213-46-8915"));
   drCurrent->BeginEdit();
   
   drCurrent->set_Item(S"phone",String::Concat(S"342",(static_cast<String *>(drCurrent->Item[S"phone"]))->Substring(3)));
   drCurrent->EndEdit ();
   Console::WriteLine(S"Record edited successfully, Click any key to continue!!");
   Console::ReadLine();
   
   // END EDIT CODE

4. To update the original database with all these changes, pass the DataSet object to the Update method of the DataAdapter object.
   
   However, before you can call the Update method, you must set the InsertCommand, the UpdateCommand, and the DeleteCommand properties of the DataAdapter object. You can manually write SQL code and populate these three properties with corresponding SqlCommand objects. You can also use Microsoft Visual Studio .NET 2003 to generate these three commands automatically.
   
   To generate the required commands when they are required, you must create an instance of the SqlCommandBuilder object and use the SqlDataAdapter object in the constructor. If you want to use this method, you must have primary key information available for your table. This is described in the following code sample. To access primary key information, call the FillSchema method, and then set the MissingSchemaAction property of your SqlDataAdapter object to AddWithKey, or manually set the primary key in your code.
   
   To do this, add the following code after the code that you added in step 3:

   // BEGIN SEND CHANGES TO SQL SERVER 
   
   SqlCommandBuilder* objCommandBuilder = new SqlCommandBuilder(daAuthors);
   daAuthors->Update(dsPubs, S"Authors");
   Console::WriteLine(S"SQL Server updated successfully, Check Server explorer to see changes");
   Console::ReadLine();
   
   // END SEND CHANGES TO SQL SERVER

5. To delete a row, use the Delete method of the DataRow object.
   
   Note The Rows collection contains two methods: the Remove method and the RemoveAt method. These methods seem to delete the row. However, the methods only remove the row from the collection. Only the Delete method sends your deletion back to the source database.
   
   To delete a row, add the following code after the code that you added in step 4:

   //BEGIN DELETE CODE 
   
   drCurrent = tblAuthors->Rows->Find(new String(S"993-21-3427"));
   drCurrent->Delete();
   Console::WriteLine(S"Record deleted successfully, Click any key to continue!!"); 
   Console::ReadLine();
   
   //END DELETE CODE  

6. Send the changes to SQL Server to remove the record that you added earlier.
   
   To do this, add the following code after the code that you added in step 5:

   // CLEAN UP SQL SERVER
   daAuthors->Update(dsPubs, S"Authors");
   Console::WriteLine(S"SQL Server updated successfully, Check Server explorer to see changes");
   Console::ReadLine();

7. Set up a structured exception handling mechanism. To do this, move all the code that you added (except the declaration of SqlConnection object in the first line) in a try block and then close the database connection inside a finally block. In the catch block, add code to display the exception message.
8. Press CTRL+SHIFT+S to save the project.
9. Press CTRL+SHIFT+B to build the solution.
10. Press CTRL+F5 to run the program.
    
    Notice that several messages appear in the console window that indicate the progress of the code so that you can review the current state of the data as the code progresses.

back to the top

Complete code listing

// This is the main project file for Visual C++ application project 
// that is generated by using an application wizard.

#include "stdafx.h"

#using <mscorlib.dll>

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


int _tmain()
{
    SqlConnection  *objConn;
    try
    {
		String *sConnectionString;
		sConnectionString = S"Password=StrongPassword;User ID=UserName;Initial Catalog=pubs;Data Source=(local)";

		objConn = new SqlConnection(sConnectionString);
		objConn->Open();

		// Create an instance of a DataAdapter.
		SqlDataAdapter* daAuthors = new SqlDataAdapter(S"Select * From Authors", objConn);

		// Create an instance of a DataSet, and retrieve data from the Authors table.
		DataSet* dsPubs = new DataSet(S"Pubs");
		daAuthors->FillSchema(dsPubs,SchemaType::Source, S"Authors");
		daAuthors->Fill(dsPubs,S"Authors");

		// BEGIN ADD CODE 
		// Create a new instance of a DataTable.
		DataTable* tblAuthors = dsPubs->Tables->Item[S"Authors"];

		// Obtain a new DataRow object from the DataTable.
		DataRow* drCurrent = tblAuthors->NewRow();

		// Set the DataRow field values as necessary.
		drCurrent->set_Item(S"au_id",new String(S"993-21-3427"));
		drCurrent->set_Item(S"au_fname",new String(S"George"));
		drCurrent->set_Item(S"au_lname",new String(S"Johnson"));
		drCurrent->set_Item(S"phone",new String(S"800 226-0752"));
		drCurrent->set_Item(S"address", new String (S"1956 Arlington Pl."));
		drCurrent->set_Item(S"city", new String(S"Winnipeg"));
		drCurrent->set_Item(S"state", new String(S"MB"));
		drCurrent->set_Item(S"contract",__box(1));

		// Pass the new DataRow object as a parameter to the Add method of the DataTable Rows collection.
		tblAuthors->Rows->Add(drCurrent);
		Console::WriteLine(S"Add was successful, Click any key to continue!!");
		Console::ReadLine();

		// END ADD CODE  

		// BEGIN EDIT CODE 

		drCurrent = tblAuthors->Rows->Find(new String(S"213-46-8915"));
		drCurrent->BeginEdit();

		drCurrent->set_Item(S"phone",String::Concat(S"342",(static_cast<String *>(drCurrent->Item[S"phone"]))->Substring(3)));
		drCurrent->EndEdit ();
		Console::WriteLine(S"Record edited successfully, Click any key to continue!!");
		Console::ReadLine();

		// END EDIT CODE

		// BEGIN SEND CHANGES TO SQL SERVER 

		SqlCommandBuilder* objCommandBuilder = new SqlCommandBuilder(daAuthors);
		daAuthors->Update(dsPubs, S"Authors");
		Console::WriteLine(S"SQL Server updated successfully, Check Server explorer to see changes");
		Console::ReadLine();

		// END SEND CHANGES TO SQL SERVER 

		//BEGIN DELETE CODE 

		drCurrent = tblAuthors->Rows->Find(new String(S"993-21-3427"));
		drCurrent->Delete();
		Console::WriteLine(S"Record deleted successfully, Click any key to continue!!"); 
		Console::ReadLine();

		//END DELETE CODE  

		// CLEAN UP SQL SERVER
		daAuthors->Update(dsPubs, S"Authors");
		Console::WriteLine(S"SQL Server updated successfully, Check Server explorer to see changes");
		Console::ReadLine();
    }
    catch (Exception* ex)
    {
		Console::WriteLine (ex->Message );
    }
    __finally
    {
		objConn->Close ();
    }
}

back to the top