How to fill a dataset with 3 different adapters in Vb.net?

Question

Aim to Achieve : I want to have 3 different dataTables from 3 different SQL queries from 3 different places into 1 single DataSet which I will have to return form my function.

I have :

Private Function getDataSet()
    Dim ad1,ad2,ad3 As Object
    ad1 = New Data.OleDb.OleDbDataAdapter(query1, conStr1)
    ad2 = New Data.SqlClient.SqlDataAdapter(query2, conStr2)
    ad3 = New Data.SqlClient.SqlDataAdapter(query3, conStr3)
    Dim dataSet As New Data.DataSet

    // I want to fill dataSet with the 3 adapters !
    // ad1.Fill(dataSet) : ad2.Fill(dataSet) : ad3.Fill(dataSet)
    // Will this work ?

    ad1.Dispose() : ad2.Dispose() : ad3.Dispose()  // What does Dispose() method do ?
    Return dataSet
End Function

Dim ds = getDataSet()
data1 = ds.Tables(0)
data2 = ds.Tables(1)
data3 = ds.Tables(2)

Please Help..

I want to use the best possible implementation of above task.

Tim Schmelter · Accepted Answer · 2011-08-08T21:13:59.227

I would not fill the DataSet but the DataTables of the DataSet with each DataAdapter:

Private Function getDataSet() As DataSet
    Dim ds As New Data.DataSet
    ds.Tables.Add(New DataTable("Table1"))
    ds.Tables.Add(New DataTable("Table2"))
    ds.Tables.Add(New DataTable("Table3"))

    Using con As New OleDb.OleDbConnection("conStr1")
        Using ad As New Data.OleDb.OleDbDataAdapter("query1", con)
            con.Open()
            ad.Fill(ds.Tables("Table1"))
        End Using
    End Using
    Using con As New OleDb.OleDbConnection("conStr2")
        Using ad As New Data.OleDb.OleDbDataAdapter("query2", con)
            con.Open()
            ad.Fill(ds.Tables("Table2"))
        End Using
    End Using
    Using con As New OleDb.OleDbConnection("conStr3")
        Using ad As New Data.OleDb.OleDbDataAdapter("query3", con)
            con.Open()
            ad.Fill(ds.Tables("Table3"))
        End Using
    End Using

    Return ds
End Function

Any number of DataAdapter-objects can be used with a DataSet. Each DataAdapter can be used to fill one or more DataTable objects and resolve updates back to the relevant data source. DataRelation and Constraint objects can be added to the DataSet locally, which enables you to relate data from dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed through OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.

More...

" // What does Dispose() method do ?"

http://msdn.microsoft.com/en-us/library/system.idisposable.dispose.aspx

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

I would prefer the using-statement(see code-sample above) because it's simplier and also closes connection etc.

We are opening and closing the connection 3 times here, can't we do all the 3 queries in `1 connection` if all the 3 has the same `conStr` ? — Yugal Jindle, Aug 09 '11 at 02:03
@Yuga. Glad i could help. But don't misunderstood that you can use the same connection for all. Since the Connection-Pool will use the same connection(when the connection-string is the same) even if you will create/open/close it in different methods. So you should not have one "global" connection and use this. Create and use it where you need it and the ADO.NET Connection-Pool will do the rest for you. — Tim Schmelter, Aug 09 '11 at 08:16

How to fill a dataset with 3 different adapters in Vb.net?

1 Answers1