Changes the data in the data source to match the data in the DataSet.
[Visual Basic] Function Update( _ ByVal dataSet As DataSet _ ) As Integer [C#] int Update( DataSet dataSet ); [C++] int Update( DataSet* dataSet ) = 0; [JScript] function Update( dataSet : DataSet ) : int;
Exception Type | Condition |
---|---|
UpdateRequiresSourceTable(System.String) | A source table could not be found. |
TableMappings are required to be mapped for this method to operate. The default source table is named "Table".
This method pulls rows from the table listed in the first mapping before running an update. The adapter will clone the insert, update, or delete command appropriate for the row. Then the OnRowUpdating event is raised, allowing the user to inspect the DataSet row, modify the cloned command, or cancel the default processing. The cloned command is then executed against the data source. If the cloned command is configured incorrectly, an error will be raised. The data set command then will refresh the row according to UpdatedRowSource property of the command. Any additional rows returned will be ignored. After any data is loaded back into the DataSet, the OnRowUpdated event is raised, allowing the user to inspect the reconciled DataSet row and any output parameters returned by the command. When the OnRowUpdated event returns, the data set command releases the cloned copy of the command and any changes made to it between the events are lost. After a row updates successfully, the changes to that row will have been accepted. If the connection was closed prior to Update, the adapter will close the connection.
When using Update, there are two events that occur per data row updated. The order of execution is as follows:
1) The values in the DataRow are moved to the parameter values.
2) The OnRowUpdating event is raised.
3) The command executes.
4) If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow
5) If there are output parameters, they are placed in the DataRow
6) The OnRowUpdated event is raised.
7) AcceptChanges is called.
The following example uses the SQLDataSetCommand class, which implements the IDataSetCommand class, to Update a SQL Server data source with new records from an Access data source.
[C#]
private DataSet InitializeMyDataSet() { // creates the table and its columns for the DataSet DataSet newData = new DataSet(); DataTable newTable = newData.Tables.Add("Catagories"); // add Columns to table newTable.Columns.Add("CategoryID",typeof(Int32)); newTable.Columns.Add("CategoryName", typeof(String)); newTable.Columns.Add("Description", typeof(String)); newTable.Columns.Add("Picture",typeof(Byte[])); return newData; } public void AccessToSQLServer() { // set Access connection and select strings const string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND_RW.MDB"; const string strAccessSelect = "SELECT * FROM Categories ORDER BY CategoryID"; // set SQL Server connection and insert strings const string strSQLServerConn = "Data Source=VBsql7;Initial Catalog=Northwind;User ID=sa;Password=;" const string strSQLServerInsert = "INSERT INTO Categories(CategoryName, Description, Picture) Values(@CategoryName,@Description,@Picture)"; // create my DataSet DataSet myDataSet = InitializeMyDataSet(); // create my Access objects ADOConnection myAccessConn = new ADOConnection(strAccessConn); ADODataSetCommand myAccessDataSetCmd = new ADODataSetCommand(); myAccessDataSetCmd.SelectCommand = new ADOCommand(strAccessSelect,myAccessConn); // create my SQL Server objects SQLConnection mySQLConn = new SQLConnection(strSQLServerConn); SQLDataSetCommand mySQLDataSetCmd = new SQLDataSetCommand(); mySQLDataSetCmd.InsertCommand = new SQLCommand(strSQLServerInsert,mySQLConn); // fill myDataSet with data from Access myAccessConn.Open(); try { myAccessDataSetCmd.FillDataSet(myDataSet); } finally { myAccessConn.Close(); } // do ugly work to turn original rows into new rows // update SQL Server with data from the DataSet mySQLConn.Open(); try { mySQLDataSetCmd.InsertCommand.Parameters[0].SourceColumn = "CategoryName"; mySQLDataSetCmd.InsertCommand.Parameters[1].SourceColumn = "Description"; mySQLDataSetCmd.InsertCommand.Parameters[2].SourceColumn = "Picture"; mySQLDataSetCmd.Update(myDataSet); } finally { mySQLConn.Close(); } }
IDataSetCommand Interface | IDataSetCommand Members | System.Data Namespace