I needed to create some reports which need data from multiple tables. Originally I was creating Business Objects based off of views..but that's just ugly to me so.....
I created a dataset that I could bind Crystal Reports to at design time.
First I created a class that would output an .xsd file with the dataset schema.
Imports System.Data
Imports MicroFour.StrataFrame.Business
Public Class CreatePICDataset
Private Shared GenderBO As New PICData.Gender
Private Shared InsuranceCardBO As New PICData.InsuranceCard
Private Shared InsuranceTypeBO As New PICData.InsuranceType
Private Shared LanguageBO As New PICData.Language
Private Shared PatientBO As New PICData.Patient
Private Shared PatientHistoryBO As New PICData.PatientHistory
Private Shared RaceBO As New PICData.Race
Private Shared VoterRegBO As New PICData.VoterReg
Private Shared PICDS As New DataSet
Public Shared Sub CreateDataset()
PICDS.DataSetName = "PICDataSet"
AddBOTableToDataset(GenderBO)
AddBOTableToDataset(InsuranceCardBO)
AddBOTableToDataset(InsuranceTypeBO)
AddBOTableToDataset(LanguageBO)
AddBOTableToDataset(PatientBO)
AddBOTableToDataset(PatientHistoryBO)
AddBOTableToDataset(RaceBO)
AddBOTableToDataset(VoterRegBO)
PICDS.WriteXmlSchema("C:\PICDataSet.xsd")
End Sub
Private Shared Sub AddBOTableToDataset(ByVal myBO As BusinessLayer)
myBO.FillDataTable("Select * from " & myBO.TableName & " where 1 = 0")
PICDS.Tables.Add(myBO.CurrentDataTable)
End Sub
End Class
I call the CreateDataSet method of this class anytime I change my BO's /DB structure. I then copy the output file to my project and set it as an embedded resource.
I then created a class which inherits from dataset and reads it's schema from the embedded .xsd file in the project
Imports System.Data
Public Class PICDataset
Inherits DataSet
Private AppAssembly As Reflection.Assembly = Reflection.Assembly.GetExecutingAssembly()
Private AppAssemblyPath As String = Me.AppAssembly.GetName().Name().Replace(" ", "_")
Public Sub New()
Try
Me.ReadXmlSchema(GetResource("PICDataSet.xsd"))
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Function GetResource(ByVal FileName As String) As System.IO.Stream
Try
Return Me.AppAssembly.GetManifestResourceStream(Me.AppAssemblyPath & "." & FileName)
Catch ex As Exception
MessageBox.Show("Error returning resource: " & ex.ToString(), "GetResource!", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return Nothing
End Try
End Function
End Class
At design time I can now bind a report to a Typed Dataset (you could go a step further and add the relationships in code, but I do this in Crystal)
At runtime to populate my dataset for a report, I create an instance of the dataset class and add tables using the currentdatatable from my business objects
Dim ds As New PICDataset
ds.Tables(PatientBO.TableName).Load(PatientBO.CurrentView.ToTable.CreateDataReader)
ds.Tables(GenderBO.TableName).Load(GenderBO.CurrentDataTable.CreateDataReader)
ds.Tables(LanguageBO.TableName).Load(LanguageBO.CurrentDataTable.CreateDataReader)
ds.Tables(RaceBO.TableName).Load(RaceBO.CurrentDataTable.CreateDataReader)
ds.Tables(InsuranceCardBO.TableName).Load(InsuranceCardBO.CurrentDataTable.CreateDataReader)
rptPIF_English.SetDataSource(ds)
Hopefully this helps someone.
Jerome