Update a SQL Server database by using the SqlDataAdapter object in Visual C++
This article introduces how to use the SqlDataAdapter
object to update a SQL Server database in Microsoft Visual C++.
Original product version: Visual C++
Original KB number: 308510
Summary
The SqlDataAdapter
object serves as a bridge between an ADO.NET DataSet
object and a 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. - 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 theSqlDataAdapter
object can be used to update a SQL Server database with data modifications executed on aDataSet
object that is populated with data from a table in the database.
This article refers to the .NET Framework Class Library namespace System::Data::SqlClient
.
SqlDataAdapter object and properties
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 is 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.
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')
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
, UPDATE
, 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 is 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 sign in.
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.
Code sample 2: Manually create and initialize the UpdateCommand property
The output generated by the 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 the 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 the 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 CustTesttable 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
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for