DataTables and DataSets

[Roey]

I have an n-tiered application which uses a (Data Access Layer) DAL to retrieve DataSets from the Database using "SELECT * FROM tblItemMaster" statements from individual tables as oppossed to using inner joins against multiple tables. I now want to be able to add relationships between these returned DataSets but realize that this is impossible as each table must be a DataTable inside a single DataSet.

 

My question (at last) is how do you call multiple select calls from a middle tier layer and then combine them into one dataset as tables, as oppossed to retrieveing multiple datasets.

[Robby]

You can have your DAL return the requested datatables, then setup the relationhips and/or datasets in the business layer.

[Roey]

Thanks Robby. Here's a simplified example of my DAL function, as you can see it returns a DataSet, with a table inside it called item.

 

Public Function Get_All() As DataSet

 

selectSQL = "SELECT * FROM tblItemMaster"

 

Dim mySelectCommand As OleDbCommand = New OleDbCommand(selectSQL, oConnection)

Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectCommand)

 

Try

myOleDbDataAdapter.Fill(dsReturn, "Item")

myOleDbDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

myOleDbDataAdapter.FillSchema(dsReturn, SchemaType.Source, "Item")

Get_All = dsReturn

Catch exc As Exception

Throw exc

Finally

mySelectCommand.Dispose()

myOleDbDataAdapter.Dispose()

oConnection.Dispose()

End Try

 

End Function

 

 

My other DAL calls also return a Dataset named dsReturn with their own relevant table names, but I am not sure how to call these correctly from Business layer in order to combine them into one dataset.

 

Sorry if this reads a bit confusing....

[Robby]

I prefer to not have the Select statement in my DAL as to keep it generic enough so it can be used by other applications. I setup sql statements as properties or arguments for the method so they are not known by the DAL.

Also, you can inherit the DAL from the business layer with MustInherit so that it is not instansiated.

[Roey]

That makes a lot of sense, but is their any way of doing what I want without a massive restructure of my code ?

[Robby]

It's not that much of a restructure, create a couple of private members to hold the SQL statement strings and have your methods execute them.

 

 

As for the relationships just move them to the business layer.

[Roey]

Won't I still end up with multiple DataSets instead of DataTables as the DataAdapter would still be in the DAL.

 

PS. Sorry if I'm not getting the concept correctly

[Robby]

Once the DataAdpter is out of scope (after the datatable is returned) in a sense it is disposed and you don't need to worry about it.

[Robby]

Oh wait a second, you're probably using some update methods of the DataAdpter right?

[Roey]

This is what I have:

 

Bus Level

 

Dim objCategory As New elantis_DAL.DALCategoryMaster()

Dim objItem As New elantis_DAL.DALItemMaster()

Dim objProductType As New elantis_DAL.DALProductType()

Dim objUnitOfMeasure As New elantis_DAL.DALUnitOfMeasure()

 

Try

Dim dsCategory As New DataSet()

Dim dsItem As New DataSet()

Dim dsProductType As New DataSet()

Dim dsUnitOfMeasure As New DataSet()

 

dsCategory = objCategory.Get_All()

dsItem = objItem.Get_All()

dsProductType = objProductType.Get_All() dsUnitOfMeasure = objUnitOfMeasure.Get_All()

 

 

I am ending up with 4 datasets instead of 1 dataset and 4 datatables.