Imports System.Globalization
Imports System.Reflection
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
#Region "Data Binding Code"
'btnDataSet_Click - Illustrates using a DataSet as a data source for an
'ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnDataSet_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDataSet.Click
Me.Cursor = Cursors.WaitCursor
'Dataset to hold data
Dim _InvoiceData As New DataSet()
_InvoiceData.Locale = CultureInfo.InvariantCulture
'Database connection populated from the sample Northwind access database
Dim _nwindConn As New OleDbConnection(GetDBPath.GetNwindConnectString)
'SQL Select command to run against the database
Dim _selectCMD As New OleDbCommand("SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID", _nwindConn)
_selectCMD.CommandTimeout = 30
'Data adapter used to run the select command
Dim _InvoicesDA As New OleDbDataAdapter()
_InvoicesDA.SelectCommand = _selectCMD
'Fill the DataSet
_InvoicesDA.Fill(_InvoiceData, "Invoices")
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _InvoiceData
rpt.DataMember = _InvoiceData.Tables(0).TableName
'Run and view the report
rpt.Run(False)
arvMain.Document = rpt.Document
Me.Cursor = Cursors.Arrow
End Sub 'btnDataSet_Click
'btnDataTable_Click - Illustrates using a DataTable as a data source for an
'ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnDataTable_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDataTable.Click
Me.Cursor = Cursors.WaitCursor
'DataTable to hold the data
Dim _InvoiceData As New DataTable("Invoices")
_InvoiceData.Locale = CultureInfo.InvariantCulture
'Database connection populated from the sample Northwind access database
Dim _nwindConn As New OleDbConnection(GetDBPath.GetNwindConnectString)
'SQL Select command to run against the database
Dim _selectCMD As New OleDbCommand("SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID", _nwindConn)
_selectCMD.CommandTimeout = 30
'Data adapter used to run the select command
Dim _InvoicesDA As New OleDbDataAdapter()
_InvoicesDA.SelectCommand = _selectCMD
'Fill the DataTable
_InvoicesDA.Fill(_InvoiceData)
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _InvoiceData
'Run and view the report
rpt.Run(False)
arvMain.Document = rpt.Document
Me.Cursor = Cursors.Arrow
End Sub 'btnDataTable_Click
'btnDataView_Click - Illustrates using a DataView as a data source for an
'ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnDataView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDataView.Click
Me.Cursor = Cursors.WaitCursor
'Verify that a value Company Name is selected
If Me.cbCompanyName.SelectedItem Is Nothing Then
MessageBox.Show("Please select a Company Name first.")
Me.Cursor = Cursors.Arrow
Return
End If
'DataTable to hold the full data
Dim _InvoiceData As New DataTable("Invoices")
_InvoiceData.Locale = CultureInfo.InvariantCulture
'Database connection populated from the sample Northwind access database
Dim _nwindConn As New OleDbConnection(GetDBPath.GetNwindConnectString)
'SQL Select command to run against the database
Dim _selectCMD As New OleDbCommand("SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID", _nwindConn)
_selectCMD.CommandTimeout = 30
'Data adapter used to run the select command
Dim _InvoicesDA As New OleDbDataAdapter()
_InvoicesDA.SelectCommand = _selectCMD
'Fill the DataTable
_InvoicesDA.Fill(_InvoiceData)
'Create DataView and assign the selected CompanyName RowFilter
Dim _InvoiceDataView As New DataView(_InvoiceData)
_InvoiceDataView.RowFilter = "Customers.CompanyName='" + Convert.ToString(Me.cbCompanyName.SelectedItem).Replace("'", "''") + "'"
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _InvoiceDataView
'Run and view the report
rpt.Run(False)
arvMain.Document = rpt.Document
Me.Cursor = Cursors.Arrow
End Sub 'btnDataView_Click
'btnDataReader_Click - Illustrates using a DataReader as a data source for an
'ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnDataReader_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDataReader.Click
Me.Cursor = Cursors.WaitCursor
'Database connection populated from the sample Northwind access database
Dim _nwindConn As New OleDbConnection(GetDBPath.GetNwindConnectString)
'SQL Select command to run against the database
Dim _selectCMD As New OleDbCommand("SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID", _nwindConn)
_selectCMD.CommandTimeout = 30
'DataReader to use to read the data
Dim _InvoiceDataReader As OleDbDataReader
'Open the database connection and Execute the reader
_nwindConn.Open()
_InvoiceDataReader = _selectCMD.ExecuteReader()
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _InvoiceDataReader
'Run and view the report
rpt.Run(False)
arvMain.Document = rpt.Document
'Close the database connection
_nwindConn.Close()
Me.Cursor = Cursors.Arrow
End Sub 'btnDataReader_Click
'btnOleDb_Click - Illustrates using a Data Dynamics OleDb object as a data source
'for an ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnOleDb_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnOleDb.Click
Me.Cursor = Cursors.WaitCursor
'OleDb Data Source object to use
Dim _OleDb As New DataDynamics.ActiveReports.DataSources.OleDBDataSource()
'Assign the database connection string from the sample Northwind access database
_OleDb.ConnectionString = GetDBPath.GetNwindConnectString
'SQL Select command to run against the database
_OleDb.SQL = "SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID"
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _OleDb
'Run and view the report
rpt.Run(False)
arvMain.Document = rpt.Document
Me.Cursor = Cursors.Arrow
End Sub 'btnOleDb_Click
'btnSqlServer_Click - Illustrates using a Data Dynamics SqlServer object as a data source
'for an ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnSqlServer_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSqlServer.Click
Me.Cursor = Cursors.WaitCursor
'Verify that a SQL Server has been selected
If Me.cbSqlServerList.SelectedItem Is Nothing Then
MessageBox.Show("Please select a SQL Server first.")
Me.Cursor = Cursors.Arrow
Return
End If
'SqlServer Data Source object to use
Dim _SQL As New DataDynamics.ActiveReports.DataSources.SqlDBDataSource()
'Assign the database connection string based on the SQL Server selected
_SQL.ConnectionString = "Data Source=" + Me.cbSqlServerList.SelectedItem + ";Initial Catalog=Northwind;Integrated Security=SSPI"
'SQL Select command to run against the database
_SQL.SQL = "SELECT * FROM invoices ORDER BY CustomerID, OrderID"
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _SQL
'Run and view the report
Try
rpt.Run(False)
arvMain.Document = rpt.Document
Catch ex As System.Data.SqlClient.SqlException
MessageBox.Show(ex.Message)
End Try
Me.Cursor = Cursors.Arrow
End Sub 'btnSqlServer_Click
'btnGenerateXML_Click - Generates a DataSet and saves it out as an XML data file
'to the user selected file name
'Author - Data Dynamics, Ltd.
Private Sub btnGenerateXML_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateXML.Click
'DataSet used to hold the Data
Dim _InvoiceData As New DataSet("Northwind")
_InvoiceData.Locale = CultureInfo.InvariantCulture
'Database connection populated from the sample Northwind access database
Dim _nwindConn As New OleDbConnection(GetDBPath.GetNwindConnectString)
'SQL Select command to run against the database
Dim _selectCMD As New OleDbCommand("SELECT * FROM Invoices ORDER BY Customers.CompanyName, OrderID", _nwindConn)
_selectCMD.CommandTimeout = 30
'Data adapter used to run the select command
Dim _InvoicesDA As New OleDbDataAdapter()
_InvoicesDA.SelectCommand = _selectCMD
'Fill the DataSet
_InvoicesDA.FillSchema(_InvoiceData, SchemaType.Source, "Invoices")
_InvoiceData.Tables("Invoices").Columns("OrderDate").DataType = System.Type.GetType("System.String")
_InvoiceData.Tables("Invoices").Columns("ShippedDate").DataType = System.Type.GetType("System.String")
_InvoicesDA.Fill(_InvoiceData, "Invoices")
'Initalize the Save Dialog Box
dlgSave.Title = "Save Data As..."
dlgSave.FileName = "Invoices.xml"
dlgSave.Filter = "XML Data (*.xml)|*.xml"
dlgSave.ShowDialog()
Dim x As Integer
'If valid name is returned, save out the DataSet to the specified filename
If dlgSave.FileName.Length <> 0 Then
'Format all date fields in the XML for the report
For x = 0 To _InvoiceData.Tables("Invoices").Rows.Count - 1
_InvoiceData.Tables("Invoices").Rows(x)("OrderDate") = Convert.ToDateTime(_InvoiceData.Tables("Invoices").Rows(x)("OrderDate")).ToShortDateString()
If Not _InvoiceData.Tables("Invoices").Rows(x)("ShippedDate").GetType() Is Type.GetType("System.DBNull") Then
_InvoiceData.Tables("Invoices").Rows(x)("ShippedDate") = Convert.ToDateTime(_InvoiceData.Tables("Invoices").Rows(x)("ShippedDate")).ToShortDateString()
End If
Next
_InvoiceData.WriteXml(dlgSave.FileName)
End If
End Sub 'btnGenerateXML_Click
'btnXML_Click - Illustrates using a Data Dynamics XML object as a data source
'for an ActiveReport object
'Author: Data Dynamics, Ltd.
Private Sub btnXML_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnXML.Click
'Initialize the Open Dialog Box
dlgOpen.Title = "Open Data File..."
dlgOpen.FileName = dlgSave.FileName
dlgOpen.Filter = "XML Data (*.xml)|*.xml"
dlgOpen.ShowDialog()
'If valid name is returned, open the data and run the report
If dlgOpen.FileName.Length <> 0 Then
Me.Cursor = Cursors.WaitCursor
'XML Data Source object to use
Dim _XML As New DataDynamics.ActiveReports.DataSources.XMLDataSource()
'Assign the FileName for the selected XML data file
_XML.FileURL = dlgOpen.FileName
'Assign the Recordset Pattern
_XML.RecordsetPattern = "//Northwind/Invoices"
'Create the report and assign the data source
Dim rpt As New Invoice()
rpt.DataSource = _XML
'Run and view the rpeort
rpt.Run(False)
arvMain.Document = rpt.Document
Me.Cursor = Cursors.Arrow
End If
End Sub 'btnXML_Click
#End Region
Imports System
' GetDBPath is a helper class used to locate the Northwind sample database on the computer.
' Author: Data Dynamics, Ltd.
NotInheritable Class GetDBPath
Private Sub New()
End Sub 'New
Private Shared registryKey As String = "SOFTWARE/Data Dynamics/ActiveReports for .NET 3.0/SampleDB"
' Used to get the path to the Sample database installed with ActiveReports
' Author: Data Dynamics, Ltd.
' Returns: String variable with the path to the Sample Database
Public Shared Function GetPath() As String
Dim sPath As String = Nothing
Try
' Create a RegistryKey value to retrieve the localmachine entries, and open the subkey that points to the database
Dim hklm As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.LocalMachine
Dim sampleDbKey As Microsoft.Win32.RegistryKey = hklm.OpenSubKey(registryKey)
sPath = sampleDbKey.GetValue("") '
Catch eReg As System.Exception
' problem occured with attempting to read the local registry (No access rights for current user?)
Throw New System.ApplicationException("An error occured accessing the registry key " & registryKey & ".", eReg)
End Try
If sPath Is Nothing Then
' we couldn't read the registry
Throw New System.ApplicationException("Unable to access the sample database registry value " & registryKey & ".")
End If
Return sPath
End Function 'GetPath
' Used to get the full connection string to the Sample Database installed with ActiveReports
' Author: Data Dynamics, Ltd.
' Returns: String variable with the full connection string to the Sample Database
Public Shared ReadOnly Property GetNwindConnectString() As String
Get
Return "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" + GetDBPath.GetPath() + "/nwind.mdb"
End Get
End Property
End Class 'GetDBPath