querying a whole dataset at once, with joins?

Question

I am working in C# and Visual Studio 2005. I have a typed DataSet that the wizard created. I see that I can run MyGeneratedDataSet.tblFoo.Select() and get data from each table, but is there a way to do a query of the whole thing, like the little in memory database that it is, or do I need to do it piecemeal table by table?

Thank you! Joshua

Answer 1

You want to bring the entire database into the dataset? This is a bad idea for lots of reasons, but mostly because as your database grows (which is the point), your resource consumption in your app will grow too, and your application will slowly grind to a halt.

Unless your database only has two tables (ok, maybe three), I can't imagine why you'd want to do this. You should design your application so that it only consumes what it needs when the users asks for that piece.

Try sketching out some windows with good old paper and pencil and mock up what the user will see. I think you'll see that you don't want all your data in your hand, just that which is pertinent to the current action the user is taking.

Answer 2

Say your dataset has two tables (A,B)
The database has the same tables (A,B)
They may differ in name beween DB and DataSet but then you need to make mappings

Its actually quite easy.
(SQL SERVER EXAMPLE, pseudoish code)

string fQuery = "SELECT * FROM A;SELECT * FROM B:";
SqlCommand fCommand = new SqlCommand(fQuery, <connection>);
SqlDataAdapter fAdpter = new SqlDataAdapter(fCommand);
DataSet fSet = new DataSet();
fAdpter .Fill(pSet);

Now the whole set is filled in one query.
This can actually be great for performance.

Answer 3

(Answer in VB, but you should be able to convert it)

To use one query to load an entire dataset...

1. Create your multi-result query.
2. Make it a query for one of your dataset's tableadapters (to take advantage of the typed params, etc).
3. Overload that query with a copy of the generated code.
4. Replace the final fill(datatable) statement with a fill(dataset) statement.
5. Run code to map tables to correct typed tables.

1) Write a stored procedure or sql statement that has multiple select statements that return results.

2) In one of your tableadapters in your dataset, add the new query.

3) Get the generated code for this query. The easiest way is to use the method for the query in code and then right click on the method and select "Go To Definition". Put that method in a separate cs or vb file. (See code-block B)

You need to get the namespace and tableadapter partial class for the method to work.

4) Add the dataset as a parameter to the overloaded function. That dataset will be the target of the new fill statement. (See code-block B)

5) Use the following code to match your dataset result tables.

Public Shared Sub DatasetAutoMerge(ByVal Source As Data.DataSet, ByVal Target As Data.DataSet)
    Target.EnforceConstraints = False

    For Each dtTarget As Data.DataTable In Target.Tables
        For Each dtSource As Data.DataTable In Source.Tables
            Dim dtMatch = dtSource
            For Each dcTarget As Data.DataColumn In dtTarget.Columns
                If Not dtSource.Columns.Contains(dcTarget.ColumnName) Then
                    'The source does not have a column we need by name, not a match'
                    dtMatch = Nothing
                    Exit For
                End If
            Next

            If dtMatch IsNot Nothing Then
                dtTarget.Merge(dtMatch, False, Data.MissingSchemaAction.Ignore)
                Exit For
            End If
        Next
    Next

    Target.EnforceConstraints = True
End Sub

code-block B (Sample of final version of override Method, VB)

Namespace dsMyDatasetTableAdapters
    Partial Public Class Table1TableAdapter
        Public Overridable Overloads Function Fill(ByVal dataset As dsMyDataset, ByVal OrderNumber As String) As Integer
            Me.Adapter.SelectCommand = Me.CommandCollection(0)
            If (RecordID Is Nothing) Then
                Me.Adapter.SelectCommand.Parameters(0).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.SelectCommand.Parameters(0).Value = CType(OrderNumber, String)
            End If

            'end autogenerated code'

            'Start Custom Code'
            Dim dsDump As New Data.DataSet

            Me.Adapter.Fill(dsDump)

            DatasetAutoMerge(dsDump, dataset)
        End Function

    End Class
End Namespace

I've used this process a few times. It is much better than writing separate queries if you know you want to load all this at the same time. I hope this helps you!