How to update a SQL Server database by using the SqlDataAdapter object in Visual C++ .NET (308510)

SUMMARY

The SqlDataAdapter object serves as a bridge between an ADO.NET DataSet object and a Microsoft SQL Server database. It is an intermediary object that you can use to do the following:

Populate an ADO.NET DataSet with data retrieved from a SQL Server database.

-and-
Update the database to reflect the changes (inserts, updates, deletes) made to the data by using the DataSet.

This article provides Visual C++ .NET code samples to demonstrate how the SqlDataAdapter object can be used to update a SQL Server database with data modifications executed on a DataSet object that is populated with data from a table in the database.

back to the top

More Information

The InsertCommand, UpdateCommand, and DeleteCommand properties of the SqlDataAdapter object are used to update the database with the data modifications executed on a DataSet object. Each of these properties are SqlCommand objects that specify the respective INSERT, UPDATE, and DELETE TSQL commands used to post the DataSet modifications to the target database. The SqlCommand objects assigned to these properties can be created manually in code, or can be automatically generated by using the SqlCommandBuilder object.

The first code sample in this article demonstrates how the SqlCommandBuilder object can be used to automatically generate the UpdateCommand property of the SqlDataAdapter object. The second sample uses a scenario in which automatic command generation cannot be used, and therefore demonstrates the process by which you can manually create and use a SqlCommand object as the UpdateCommand property of a SqlDataAdapter object.

back to the top

Create the sample SQL Server table

To create a sample SQL Server table to use in the Visual C++ .NET code samples documented in this article, follow these steps:

Open SQL Server Query Analyzer, and then connect to a database in which you want to create the sample table. The code samples in this article use the Northwind database that comes with SQL Server.

Execute the following T-SQL statements to create a sample table called CustTest, and then insert a record into it:

Create Table CustTest
(
 CustID int primary key,
 CustName varchar(20)
)

Insert into CustTest values(1,'John')

back to the top

Code sample 1: Automatically generated commands

If the SELECT statement to retrieve the data used to populate a DataSet is based on a single database table, you can take advantage of the CommandBuilder object to automatically generate the DeleteCommand, InsertCommand, and UpdateCommand properties of the DataAdapter. This simplifies and reduces the code required to perform INSERT, UDPATE, and DELETE operations.

As a minimum requirement, you must set the SelectCommand property for automatic command generation to work. The table schema retrieved by the SelectCommand determines the syntax of the automatically generated INSERT, UPDATE, and DELETE statements.

The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated.

To create a sample Visual C++ .NET console application that demonstrates how to use the SqlCommandBuilder object to automatically generate the InsertCommand, DeleteCommand, and UpdateCommand SqlCommand object properties for a SqlDataAdapter object, follow these steps:

Start Visual Studio .NET, and then create a new Managed C++ application. Name it updateSQL.

Copy and paste the following code in updateSQL.cpp (replacing its default contents):

#include "stdafx.h"

#using <mscorlib.dll>
#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

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

#ifdef _UNICODE
int wmain(void)
#else
int main(void)
#endif
{
        SqlConnection *cn = new SqlConnection();
        DataSet *CustomersDataSet = new DataSet();
        SqlDataAdapter *da;
        SqlCommandBuilder *cmdBuilder;
  
        //Set the connection string of the SqlConnection object to connect
        //to the SQL Server database in which you created the sample
        //table in Section 1.0
        cn->ConnectionString =  "Server=server;Database=northwind;UID=login;PWD=password;";

        cn->Open();      

        //Initialize the SqlDataAdapter object by specifying a Select command 
        //that retrieves data from the sample table
        da = new SqlDataAdapter("select * from CustTest order by CustId", cn);

        //Initialize the SqlCommandBuilder object to automatically generate and initialize
        //the UpdateCommand, InsertCommand and DeleteCommand properties of the SqlDataAdapter
        cmdBuilder = new SqlCommandBuilder(da);

        //Populate the DataSet by executing the Fill method of the SqlDataAdapter
        da->Fill(CustomersDataSet, "Customers");

        //Display the Update, Insert and Delete commands that were automatically generated
        //by the SqlCommandBuilder object 
	Console::WriteLine("Update command Generated by the Command Builder : ");
	Console::WriteLine("==================================================");
	Console::WriteLine(cmdBuilder->GetUpdateCommand()->CommandText);
	Console::WriteLine("         ");

	Console::WriteLine("Insert command Generated by the Command Builder : ");
	Console::WriteLine("==================================================");
	Console::WriteLine(cmdBuilder->GetInsertCommand()->CommandText);
	Console::WriteLine("         ");        

	Console::WriteLine("Delete command Generated by the Command Builder : ");
	Console::WriteLine("==================================================");
	Console::WriteLine(cmdBuilder->GetDeleteCommand()->CommandText);
	Console::WriteLine("         ");

        //Write out the value in the CustName field before updating the data using the DataSet
	DataRow *rowCust = CustomersDataSet->Tables->Item["Customers"]->Rows->Item[0];
	Console::WriteLine("Customer Name before Update : {0} ", rowCust->Item["CustName"]);
		
        //Modify the value of the CustName field
	String *newStrVal = new String("Jack");  
        rowCust->set_Item("CustName", newStrVal);

        //Modify the value of the CustName field again
        String *newStrVal2 = new String("Jack2");  
        rowCust->set_Item("CustName", newStrVal2);

        //Post the data modification to the database
        da->Update(CustomersDataSet, "Customers");        

        Console::WriteLine("Customer Name after Update : {0} ", rowCust->Item["CustName"]);


        //Close the database connection
        cn->Close();

        //Pause
	Console::ReadLine();
 	    
		return 0;
}

In the code you copied and pasted in step 2, modify the line of connect string code to properly connect to your SQL Server computer, as follows:
```
cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";
						
```
After you run this code, you can connect with your SQL Server installation and then log on.

Save and execute the application. A console window will open up and display the following output:

Update command generated by the Command Builder: 
==================================================
UPDATE CustTest SET CustID = @p1 , CustName = @p2 WHERE ( (CustID = @p3) AND ((CustName IS NULL AND @p4 IS NULL) OR (CustName = @p5)) )
         
Insert command generated by the Command Builder : 
==================================================
INSERT INTO CustTest( CustID , CustName ) VALUES ( @p1 , @p2 )
         
Delete command generated by the Command Builder : 
==================================================
DELETE FROM  CustTest WHERE ( (CustID = @p1) AND ((CustName IS NULL AND @p2 IS NULL) OR (CustName = @p3)) )   
      
Customer Name before Update : John
Customer Name after Update : Jack2

Press any key to dismiss the console window and stop the application.

back to the top

Code sample 2: Manually create and initialize the UpdateCommand property

The output generated by Code Sample 1 indicates that the logic for generating commands automatically for UPDATE statements is based on optimistic concurrency. That is, records are not locked for editing and can be modified by other users or processes at any time. Because a record may have been modified after it was returned from the SELECT statement but before the UPDATE statement is issued, the automatically generated UPDATE statement contains a WHERE clause so that a row is updated only if it contains all original values and has not been deleted. This is done to ensure that new data is not overwritten. In cases where an automatically generated update attempts to update a row that has been deleted or does not contain the original values found in the DataSet, the command does not affect any records, and a DBConcurrencyException is thrown.

If you want the UPDATE to complete regardless of original values, you will need to explicitly set the UpdateCommand for the DataAdapter rather than rely on automatic command generation.

To manually create and initialize the UpdateCommand property of the SqlDataAdapter object used in Code Sample 1, follow these steps:

Copy and paste the following code (overwriting the existing code) in the Main() function within the UpdateSQL.cpp file in the C++ application created in Code Sample 1:

SqlConnection *cn = new SqlConnection();
DataSet *CustomersDataSet = new DataSet();
SqlDataAdapter *da;
SqlCommand *DAUpdateCmd;

cn->ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";
cn->Open();

da = new SqlDataAdapter("select * from CustTest order by CustId", cn);

//Initialize the SqlCommand object that will be used as the DataAdapter's UpdateCommand
//Notice that the WHERE clause uses only the CustId field to locate the record to be updated
DAUpdateCmd = new SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da->SelectCommand->Connection);

//Create and append the parameters for the Update command
DAUpdateCmd->Parameters->Add(new SqlParameter("@pCustName", SqlDbType::VarChar));
DAUpdateCmd->Parameters->Item["@pCustName"]->SourceVersion = DataRowVersion::Current;
DAUpdateCmd->Parameters->Item["@pCustName"]->SourceColumn = "CustName";

DAUpdateCmd->Parameters->Add(new SqlParameter("@pCustId", SqlDbType::Int));
DAUpdateCmd->Parameters->Item["@pCustId"]->SourceVersion = DataRowVersion::Original;
DAUpdateCmd->Parameters->Item["@pCustId"]->SourceColumn = "CustId";

//Assign the SqlCommand to the UpdateCommand property of the SqlDataAdapter
da->UpdateCommand = DAUpdateCmd;        
da->Fill(CustomersDataSet, "Customers");        

DataRow *rowCust = CustomersDataSet->Tables->Item["Customers"]->Rows->Item[0];
Console::WriteLine("Customer Name before Update : {0} ", rowCust->Item["CustName"]);

//Modify the value of the CustName field
String *newStrVal = new String("Jack"); 
rowCust->set_Item("CustName", newStrVal);

//Modify the value of the CustName field again
String *newStrVal2 = new String("Jack2");  
rowCust->set_Item("CustName", newStrVal2);

da->Update(CustomersDataSet, "Customers");        

Console::WriteLine("Customer Name after Update : {0} ", rowCust->Item["CustName"]);

cn->Close();
Console::ReadLine();
return 0;

Modify the line of connect string code in the preceding code sample as follows:

cn.ConnectionString = "Server=server;Database=northwind;UID=login;PWD=password;";

If you have already run the code in the "Code Sample 1" section of this article, open your CustTest table in SQL Server, and then change the CustName value in the first record back to John.
Save and execute the application. A console window will open up and display the following output:
```
Customer Name before Update : John
Customer Name after Update : Jack2
					
```

back to the top

How to update a SQL Server database by using the SqlDataAdapter object in Visual C++ .NET (308510)

IN THIS TASK

SUMMARY

More Information

Create the sample SQL Server table

Code sample 1: Automatically generated commands

Code sample 2: Manually create and initialize the UpdateCommand property