activeReport3.0 Bind data demo

标签: databasedatasetobjectreportcommandstring
1111人阅读 评论(0) 收藏 举报
 Imports System
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
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1523347次
    • 积分:18001
    • 等级:
    • 排名:第518名
    • 原创:370篇
    • 转载:207篇
    • 译文:0篇
    • 评论:254条
    最新评论