Merging Data

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

70
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] Recipe 5.6 Merging Data Problem You have two DataSet objects with the same schema, but containing different data. You need to combine data from these two DataSet objects without creating duplicate rows. Solution Use the DataSet.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Merging Data

[ Team LiB ] Recipe 5.6 Merging Data Problem You have two DataSet objects with the same schema, but containing different data. You need to combine data from these two DataSet objects without creating duplicate rows. Solution Use the DataSet.Merge( ) method with the appropriate MissingSchemaAction enumeration values. The sample code contains four event handlers and a single method: Form.Load Sets up the sample by creating two DataSet objects each with a single DataTable containing different subset of data from the Employees table in Northwind. The default view for each table is bound to a data grid on the form. MergeA Button.Click The first Button.Click calls the Merge( ) method to merge the first DataSet into the second. MergeB Button.Click A second Button.Click calls the Merge( ) method to merge the second DataSet into the first. Clear Button.Click A third Button.Click clears the data grid displaying the results of either merge. Merge( ) This method takes two DataTable arguments. The first DataTable is copied to a new DataTable and the second DataTable is merged into it with the specified MissingSchemaAction. The default view of the result DataTable is bound to a data grid on the form.
The C# code is shown in Example 5-6. Example 5-6. File: MergingDataForm.cs // Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; private SqlDataAdapter daA, daB; private DataSet dsA, dsB; // . . . private void MergingDataForm_Load(object sender, System.EventArgs e) { // Fill the schema and data for table A. String sqlText = "SELECT EmployeeID, LastName, FirstName, Title " + "FROM Employees WHERE EmployeeID BETWEEN 1 AND 5"; // Set up the DataAdapter and CommandBuilder for table A. SqlCommandBuilder cbA = new SqlCommandBuilder(daA); daA = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]); // Define DataSet A and fill its table A with schema and data. dsA = new DataSet("A"); daA.FillSchema(dsA, SchemaType.Source, "Employees"); daA.Fill(dsA, "Employees"); // Bind the default view for table A to the grid. dataGridA.DataSource = dsA.Tables["Employees"].DefaultView; // Fill the schema and data for table B. sqlText = "SELECT EmployeeID, LastName, FirstName, " + "BirthDate, HireDate " + "FROM Employees WHERE EmployeeID BETWEEN 4 AND 8"; // Set up the DataAdapter and CommandBuilder for table B. daB = new SqlDataAdapter(sqlText, ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]); SqlCommandBuilder cbB = new SqlCommandBuilder(daB); // Define DataSet B and fill its table B with schema and data. dsB = new DataSet("B"); daB.FillSchema(dsB, SchemaType.Source, "Employees");
daB.Fill(dsB, "Employees"); // Bind the default view for table B to the grid. dataGridB.DataSource = dsB.Tables["Employees"].DefaultView; } private void Merge(DataTable dtSource, DataTable dtDest) { // Set the missing schema value to the default and read // actual value, if otherwise, from the radio buttons. MissingSchemaAction msa = MissingSchemaAction.Add; if(addWithKeyRadioButton.Checked) msa = MissingSchemaAction.AddWithKey; else if(errorRadioButton.Checked) msa = MissingSchemaAction.Error; else if(ignoreRadioButton.Checked) msa = MissingSchemaAction.Ignore; // Create the merge DataSet and copy table B into it. DataSet ds = new DataSet("Merge"); ds.Tables.Add(dtDest.Copy( )); try { // Merge table A into the DataSet. ds.Merge(dtSource, false, msa); } catch (Exception ex) { MessageBox.Show(ex.Message); } // Bind the merge result table default view to the grid. dataGridMerge.DataSource = ds.Tables[0].DefaultView; dataGridMerge.CaptionText = "Merge Results: " + dtSource.DataSet.DataSetName + " into " + dtDest.DataSet.DataSetName; } private void mergeAIntoBButton_Click(object sender, System.EventArgs e) { Merge(dsA.Tables["Employees"], dsB.Tables["Employees"]); }
private void mergeBIntoAButton_Click(object sender, System.EventArgs e) { Merge(dsB.Tables["Employees"], dsA.Tables["Employees"]); } private void clearResultsButton_Click(object sender, System.EventArgs e) { dataGridMerge.DataSource = null; dataGridMerge.CaptionText = ""; } Discussion The Merge( ) method of the DataSet can merge a DataRow array, a DataTable, or a DataSet into an existing DataSet. If the existing DataSet has a primary key defined, the incoming data is matched to rows having the same primary key values. Where matches are found, the existing row is updated with the new values. Otherwise, rows are appended to the existing table. There are two arguments that can be optionally specified in the overloaded Merge( ) methods. The first, preserveChanges, is a Boolean value that indicates whether incoming values will overwrite changes made to the existing DataSet. If preserveChanges is false, the default, both the Current and Original row are overwritten with incoming values and the RowState of the row is set to the RowState of the incoming row. Exceptions are shown in Table 5-2. Table 5-2. Exceptions to PreserveChanges argument when PreserveChanges = false. Incoming Existing New RowState RowState RowState Modified, Unchanged Modified Deleted, or Added Unchanged, Modified. Also, data in the Original version of the Added Modified, or existing row is not overwritten because the Original Deleted version of the incoming row does not exist. If preserveChanges is specified as true, the values in the Current version of the existing row are maintained while values in the Original version of the existing row are
overwritten with the Original values for the incoming row. The RowState of the existing row is set to Modified. Exceptions are shown in Table 5-3. Table 5-3. Exceptions to PreserveChanges argument when PreserveChanges = true Incoming Existing New RowState RowState RowState Any Deleted Deleted Modified. Data in the Original version of the row is not Added Any overwritten because the Original version of the incoming row does not exist. The second argument is the missingSchemaAction argument, which accepts a value from the MissingSchemaAction enumeration that specifies how the Merge( ) method will handle schema elements in the incoming data that are not part of the existing DataSet. Table 5-4 describes the values in the MissingSchemaAction enumeration. Table 5-4. MissingSchemaAction enumeration Value Decription Add the new schema information and populate the new schema with Add incoming values. This is the default value. Add the new schema and primary key information and populate the new AddWithKey schema with incoming values. Throw an exception if the incoming schema does not match the schema Error of the existing DataSet. Ignore Ignore new schema information. [ Team LiB ]