[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

1 Dataset, 2 Datatables - Example Needed

Posted on 2004-10-22
10
Medium Priority
?
543 Views
Last Modified: 2012-06-27
I need a simple / best practices example of creating a dataset and filling it with 2 datatables, from two different stored procedures.
0
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 2
10 Comments
 

Author Comment

by:tbaseflug
ID: 12383897
Need it in VB.NET
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12383932
what is the relation between the two tables?
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12383949
if its a foreign key relation look here:
http://aspnet.4guysfromrolla.com/articles/101602-1.aspx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:AerosSaga
ID: 12383965
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12384033
so create a relation or merge;)

Aeros
0
 

Author Comment

by:tbaseflug
ID: 12384331
AerosSaga -

I like the idea of merging the two - I am trying to wrap them into a function - My code is below - how would I return the resulting combination?
------------------------
    Public Shared Function NavMenuGET3() As DataSet

        'Open a connection to Northwind
        Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("HODEV1ConnectionString"))
        objConn.Open()

        'Create the stored procedure command object
        Dim objCmd As New SqlCommand("usp_Menu_GET2", objConn)
        objCmd.CommandType = CommandType.StoredProcedure

        Dim objCmd2 As New SqlCommand("usp_MyJobPostings_GET", objConn)
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd2.Parameters.AddWithValue("@PostedByID", 19829)

        'create DataAdapter and DataSet objects
        Dim objDA As New SqlDataAdapter(objCmd)
        Dim objDS As New DataSet("Results")

        Dim objDA2 As New SqlDataAdapter(objCmd2)
        Dim objDS2 As New DataSet("Results2")

        'fill the dataset
        objDA.Fill(objDS)

        objDS.Merge(objDS2)

        Return objDS

    End Function
0
 
LVL 17

Expert Comment

by:AerosSaga
ID: 12384375
depending on your data that looks ok, have you tried to run this yet.  You may need to establish some relations.

0
 
LVL 17

Accepted Solution

by:
AerosSaga earned 2000 total points
ID: 12384392
You can use the Merge method of the DataSet to merge the contents of a DataSet, DataTable, or DataRow array into an existing DataSet. Several factors and options affect how new data is merged into an existing DataSet.
Primary Keys

If the table receiving new data and schema from a merge has a primary key, new rows from the incoming data are matched with existing rows that have the same Original primary key values as those in the incoming data. If the columns from the incoming schema match those of the existing schema, the data in the existing rows is modified. Columns that do not match the existing schema are either ignored or added based on the MissingSchemaAction parameter (see "MissingSchemaAction" later in this topic). New rows that have primary key values that do not match any existing rows are appended to the existing table.

If incoming or existing rows have a row state of Added, their primary key values are matched using the Current primary key value of the Added row because no Original row version exists.

If an incoming table and an existing table contain a column with the same name but differing data types, an exception is thrown and the MergeFailed event of the DataSet is raised. If an incoming table and an existing table both have primary keys defined, but the primary keys are for different columns, an exception is thrown and the MergeFailed event of the DataSet is raised.

If the table receiving new data from a merge does not have a primary key, new rows from the incoming data cannot be matched to existing rows in the table and are instead appended to the existing table.
preserveChanges

When you pass a DataSet, DataTable, or DataRow array to the Merge method, you can include optional parameters that specify whether or not to preserve changes in the existing DataSet, and how to handle new schema elements found in the incoming data. The first of these parameters after the incoming data is a Boolean flag, preserveChanges, which specifies whether or not to preserve the changes in the existing DataSet. If the preserveChanges flag is set to true, incoming values will not overwrite existing values in the Current row version of the existing row. If the preserveChanges flag is set to false, incoming values will overwrite the existing values in the Current row version of the existing row. If the preserveChanges flag is not specified, it is set to false by default. For more information about row versions, see Row States and Row Versions.

When preserveChanges is true, the data from the existing row is maintained in the Current row version of the existing row, while the data from the Original row version of the existing row is overwritten with the data from the Original row version of the incoming row. The RowState of the existing row is set to Modified. The following exceptions apply:

    * If the existing row has a RowState of Deleted, this RowState will remain Deleted and will not be set to Modified. In this case, the data from the incoming row will still be stored in the Original row version of the existing row, overwriting the Original row version of the existing row (unless the incoming row has a RowState of Added).
    * If the incoming row has a RowState of Added, the data from the Original row version of the existing row will not be overwritten with data from the incoming row because the incoming row does not have an Original row version.

When preserveChanges is false, both the Current and Original row versions in the existing row are overwritten with the data from the incoming row, and the RowState of the existing row is set to the RowState of the incoming row. The following exceptions apply:

    * If the incoming row has a RowState of Unchanged and the existing row has a RowState of Modified, Deleted, or Added, the RowState of the existing row is set to Modified.
    * If the incoming row has a RowState of Added, and the existing row has a RowState of Unchanged, Modified, or Deleted, the RowState of the existing row is set to Modified. Also, the data from the Original row version of the existing row is not overwritten with data from the incoming row because the incoming row does not have an Original row version.

MissingSchemaAction

You can use the optional MissingSchemaAction parameter of the Merge method to specify how Merge will handle schema elements in the incoming data that are not part of the existing DataSet.

The following table describes the options for MissingSchemaAction.
MissingSchemaAction       Description
Add       Add the new schema information to the DataSet and populate the new columns with the incoming values. This is the default.
AddWithKey       Add the new schema and primary key information to the DataSet and populate the new columns with the incoming values.
Error       Throw an exception if mismatched schema information is encountered.
Ignore       Ignore the new schema information.
Constraints

With the Merge method, constraints are not checked until all new data has been added to the existing DataSet. Once the data has been added, constraints are enforced on the current values in the DataSet. You must ensure that your code is written to handle any exceptions that might be thrown due to constraint violations.

Consider as an example a case where an existing row in a DataSet is an Unchanged row with a primary key value of 1. During a merge operation with a Modified incoming row with an Original primary key value of 2 and a Current primary key value of 1, the existing row and the incoming row are not considered matching because the Original primary key values differ. However, when the merge is completed and constraints are checked, an exception will be thrown because the Current primary key values violate the unique constraint for the primary key column.

The following code example merges two DataSet objects with differing schemas into one DataSet with the combined schemas of the two incoming DataSet objects.

[Visual Basic]
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)

nwindConn.Open()

Dim custDS As DataSet = New DataSet()
custDA.FillSchema(custDS, SchemaType.Source, "Customers")
custDA.Fill(custDS, "Customers")

Dim orderDS As DataSet = New DataSet()
orderDS.ReadXml("Orders.xml", XmlReadMode.ReadSchema)
orderDS.AcceptChanges()

nwindConn.Close()

custDS.Merge(orderDS, True, MissingSchemaAction.AddWithKey)
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);

nwindConn.Open();

DataSet custDS = new DataSet();
custDA.FillSchema(custDS, SchemaType.Source, "Customers");
custDA.Fill(custDS, "Customers");

DataSet orderDS = new DataSet();
orderDS.ReadXml("Orders.xml", XmlReadMode.ReadSchema);
orderDS.AcceptChanges();

nwindConn.Close();

custDS.Merge(orderDS, true, MissingSchemaAction.AddWithKey);

The following code example takes an existing DataSet with updates and passes those updates to a DataAdapter to be processed at the data source. The results are then merged into the original DataSet. After rejecting changes that resulted in an error, the merged changes are committed with AcceptChanges.

[Visual Basic]
  Dim custTable As DataTable = custDS.Tables("Customers")

  ' Make modifications to the Customers table.

  ' Get changes to the DataSet.
  Dim updDS As DataSet = custDS.GetChanges()

  ' Add an event handler to handle the errors during Update.
  AddHandler custDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)

  nwindConn.Open()
  custDA.Update(updDS, "Customers")
  nwindConn.Close()

  ' Merge the updates.
  custDS.Merge(updDS, true, MissingSchemaAction.Add)

  ' Reject changes on rows with errors and clear the error.
  Dim errRows() As DataRow = custDS.Tables("Customers").GetErrors()
  Dim errRow As DataRow
  For Each errRow In errRows
    errRow.RejectChanges()
    errRow.RowError = Nothing
  Next

  ' Commit the changes.
  custDS.AcceptChanges()


Private Shared Sub OnRowUpdated(sender As Object, args As SqlRowUpdatedEventArgs)
  If args.Status = UpdateStatus.ErrorsOccurred
    args.Row.RowError = args.Errors.Message
    args.Status = UpdateStatus.SkipCurrentRow
  End If
End Sub
[C#]
  DataTable custTable = custDS.Tables["Customers"];

  // Make modifications to the Customers table.

  // Get changes to the DataSet.
  DataSet updDS = custDS.GetChanges();

  // Add an event handler to handle the errors during Update.
  custDA.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

  nwindConn.Open();
  custDA.Update(updDS, "Customers");
  nwindConn.Close();

  // Merge the updates.
  custDS.Merge(updDS, true, MissingSchemaAction.Add);

  // Reject changes on rows with errors and clear the error.
  DataRow[] errRows = custDS.Tables["Customers"].GetErrors();
  foreach (DataRow errRow in errRows)
  {
    errRow.RejectChanges();
    errRow.RowError = null;
  }

  // Commit the changes.
  custDS.AcceptChanges();


protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
  if (args.Status == UpdateStatus.ErrorsOccurred)
  {
    args.Row.RowError = args.Errors.Message;
    args.Status = UpdateStatus.SkipCurrentRow;
  }
}

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconmergingdatasetcontents.asp
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question