Microsoft KB Archive/815660

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

Article ID: 815660

Article Last Modified on 3/19/2007

-

APPLIES TO


 * Microsoft Visual C++ 2005 Express Edition
 * Microsoft Visual C++ .NET 2002 Standard Edition

-





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



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

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.SqlClient

IN THIS TASK

 * SUMMARY
 * Requirements
 * How to Update a Database from DataSet Object
 * Complete Code Listing
 * References



SUMMARY
This step-by-step article shows you how to take a DataSet that contains data (which is loaded from a database), how to modify that data, and then how to send it back to the database to update the original source.

DataSet objects, a key part of data access in the Microsoft .NET Framework, 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, or Microsoft Windows NT 4.0 Server
 * One of the following: Microsoft SQL Server version 7.0, Microsoft SQL Server 2000, or Microsoft Data Engine (MSDE) with the PUBS sample database installed with the appropriate permissions, UserID and password values.
 * Microsoft Visual Studio .NET or Microsoft Visual Studio 2005

This article assumes that you are familiar with the following topics:
 * Database terminology
 * Structured Query Language (SQL)

back to the top

How to Update a Database from a DataSet Object
This section demonstrates how to use the DataSet object to update data in a database.

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

After the DataSet is loaded, you can modify the data. The DataSet will track these changes. The DataSet object can be considered an in-memory cache of data that is retrieved from a database. The DataSet object consists of a collection of tables, relationships, and constraints.

To update a DataSet and send those updates back to the database, follow these steps:  Open Microsoft Visual Studio .NET or Microsoft Visual Studio 2005. Create a new Managed C++ Application in Visual C++ .NET. Visual Studio creates a main function. Remove any generated code from the main function.  To add a reference to the System and System.Data namespaces, add the following code  Use the USING statement on the System, System.Data, and System.Data.SqlClient namespaces so that you are not required 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::Data::SqlClient;   Before you can modify the data and submit the changes back to the database, you must load the information into the DataSet.
 * 1) using 
 * 2) using 
 * 3) using <System.Xml.Dll> </li>

The connection string in the following code points to a SQL Server that is located on the local computer (or the computer where the code is running). Replace this string with your own settings.

To summarize, a connection is created, and then a data adapter is created, which is used to fill the DataSet with data. SqlConnection *objConn; String *sConnectionString; sConnectionString = &quot;Password=StrongPassword;User ID=UserName;Initial Catalog=pubs;Data Source=(local)&quot;;

objConn = new SqlConnection(sConnectionString); objConn->Open; // Create an instance of a DataAdapter. SqlDataAdapter* daAuthors = new SqlDataAdapter(&quot;Select * From Authors&quot;, objConn);

// Create an instance of a DataSet, and retrieve data from the Authors table. DataSet* dsPubs = new DataSet(&quot;Pubs&quot;); daAuthors->FillSchema(dsPubs,SchemaType::Source, &quot;Authors&quot;); daAuthors->Fill(dsPubs,&quot;Authors&quot;); 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: <ol style="list-style-type: lower-alpha;"> Click Project, and then click  Properties.

Note  is a placeholder for the name of the project.</li> Expand Configuration Properties, and then click General.</li> 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.</li></ol>

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

These steps apply to the whole article. </li>  Now that the data is loaded, you can modify it. There are many ways to add a row (or record). This code sample uses a three-step procedure: <ol style="list-style-type: lower-alpha;"> Obtain a new DataRow object from the DataTable.</li> Set the DataRow field values as necessary.</li> Pass that new object into the Add method of the DataTable.Rows collection.</li></ol>

Paste the following code after the code in step 5: // BEGIN ADD CODE // Create a new instance of a DataTable. DataTable* tblAuthors = dsPubs->Tables->Item[&quot;Authors&quot;];

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

// Set the DataRow field values as necessary. drCurrent->set_Item(&quot;au_id&quot;,new String(&quot;993-21-3427&quot;));

drCurrent->set_Item(&quot;au_fname&quot;,new String(&quot;George&quot;)); drCurrent->set_Item(&quot;au_lname&quot;,new String(&quot;Johnson&quot;)); drCurrent->set_Item(&quot;phone&quot;,new String(&quot;800 226-0752&quot;)); drCurrent->set_Item(&quot;address&quot;, new String (&quot;1956 Arlington Pl.&quot;)); drCurrent->set_Item(&quot;city&quot;, new String(&quot;Winnipeg&quot;)); drCurrent->set_Item(&quot;state&quot;, new String(&quot;MB&quot;)); drCurrent->set_Item(&quot;contract&quot;,__box(1));

// Pass that new object into the Add method of the DataTable. tblAuthors->Rows->Add(drCurrent); Console::WriteLine(&quot;Add was successful, Click any key to continue!!&quot;); Console::ReadLine;

// END ADD CODE </li>  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, which is much easier because you loaded the schema of the table as well as the data (the call to FillSchema in step 5). With the schema in place, the table knows which column is its primary key, and the Find method of the Rows collection is available.

The Find method returns the DataRow object with a specific value in its primary key (in this case, au_id). After you have that DataRow, you can modify the columns. You do not have to wrap the modifications in BeginEdit and EndEdit, but this simplifies the work that the DataSet has to do and allows the DataSet to perform its validation checks simultaneously when EndEdit is called.

Paste the following code after the code in step 6: // BEGIN EDIT CODE

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

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

// END EDIT CODE </li>  To update the original database with all of these changes, pass the DataSet into the Update method of the DataAdapter object.

However, before you can call Update, you must set the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter object. You can manually write SQL and populate these three properties with corresponding SqlCommand objects, but you can also use Visual Studio .NET to generate these three commands automatically.

To generate the required commands when they are needed, you must create an instance of the SqlCommandBuilder object and use the DataAdapter in the constructor. If you want to use this method (shown in the code sample that follows), you must have primary key information available for your table. To access primary key information, call FillSchema, and then set the MissingSchemaAction property of your DataAdapter to AddWithKey, or manually set the primary key in your code.

Paste the following code after the code in step 7. // BEGIN SEND CHANGES TO SQL SERVER

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

// END SEND CHANGES TO SQL SERVER </li>  To delete a row completely, use the Delete method of the DataRow object.

Note The Rows collection contains two methods, Remove and RemoveAt, which seem to delete the row but instead just remove the row from the collection. Only the Delete method sends your deletion back to the source database.

Paste the following code after the code in step 8: //BEGIN DELETE CODE

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

//END DELETE CODE </li>  Send the changes to SQL Server to remove the record that you added earlier.

Paste the following code after the code in step 9: // CLEAN UP SQL SERVER daAuthors->Update(dsPubs, &quot;Authors&quot;); Console::WriteLine(&quot;SQL Server updated successfully, Check Server explorer to see changes&quot;); Console::ReadLine; objConn->Close ; </li> Save your project.</li> On the Debug menu, click Start to run the project.

Notice that several message boxes appear, which indicate the progress of the code and allow you to review the current state of the data as the code progresses.</li></ol>

back to the top

Complete Code Listing
// This is the main project file for VC++ application project // generated using an Application Wizard.


 * 1) include &quot;stdafx.h&quot;


 * 1) using <mscorlib.dll>
 * 2) using <System.Dll>
 * 3) using <System.Data.Dll>
 * 4) using <System.Xml.dll>
 * 5) include <tchar.h>

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

// This is the entry point for this application int _tmain(void) {   SqlConnection  *objConn; try {       String *sConnectionString; sConnectionString = &quot;Password=StrongPassword;User ID=UserName;Initial Catalog=pubs;Data Source=(local)&quot;;

objConn = new SqlConnection(sConnectionString); objConn->Open; // Create an instance of a DataAdapter. SqlDataAdapter* daAuthors = new SqlDataAdapter(&quot;Select * From Authors&quot;, objConn);

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

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

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

// Set the DataRow field values as necessary. drCurrent->set_Item(&quot;au_id&quot;,new String(&quot;993-21-3427&quot;));

drCurrent->set_Item(&quot;au_fname&quot;,new String(&quot;George&quot;)); drCurrent->set_Item(&quot;au_lname&quot;,new String(&quot;Johnson&quot;)); drCurrent->set_Item(&quot;phone&quot;,new String(&quot;800 226-0752&quot;)); drCurrent->set_Item(&quot;address&quot;, new String (&quot;1956 Arlington Pl.&quot;)); drCurrent->set_Item(&quot;city&quot;, new String(&quot;Winnipeg&quot;)); drCurrent->set_Item(&quot;state&quot;, new String(&quot;MB&quot;)); drCurrent->set_Item(&quot;contract&quot;,__box(1));

// Pass that new object into the Add method of the DataTable. tblAuthors->Rows->Add(drCurrent); Console::WriteLine(&quot;Add was successful, Click any key to continue!!&quot;); Console::ReadLine;

// END ADD CODE // BEGIN EDIT CODE

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

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

// END EDIT CODE

// BEGIN SEND CHANGES TO SQL SERVER

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

// END SEND CHANGES TO SQL SERVER

//BEGIN DELETE CODE

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

//END DELETE CODE

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

back to the top

<div class="references_section">