ByteScout BarCode Generator SDK - Crystal Reports - Report from SQL Server (VB.NET 2015) - ByteScout

ByteScout BarCode Generator SDK – Crystal Reports – Report from SQL Server (VB.NET 2015)

  • Home
  • /
  • Articles
  • /
  • ByteScout BarCode Generator SDK – Crystal Reports – Report from SQL Server (VB.NET 2015)

ByteScout BarCode Generator SDK – Crystal Reports – Report from SQL Server (VB.NET 2015)

CrystalReport1.vb

'------------------------------------------------------------------------------
' <auto-generated>
'     This code was generated by a tool.
'     Runtime Version:4.0.30319.42000
'
'     Changes to this file may cause incorrect behavior and will be lost if
'     the code is regenerated.
' </auto-generated>
'------------------------------------------------------------------------------

Option Strict Off
Option Explicit On

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Imports System
Imports System.ComponentModel


Public Class CrystalReport1
    Inherits ReportClass
    
    Public Sub New()
        MyBase.New
    End Sub
    
    Public Overrides Property ResourceName() As String
        Get
            Return "CrystalReport1.rpt"
        End Get
        Set
            'Do nothing
        End Set
    End Property
    
    Public Overrides Property NewGenerator() As Boolean
        Get
            Return true
        End Get
        Set
            'Do nothing
        End Set
    End Property
    
    Public Overrides Property FullResourceName() As String
        Get
            Return "ReportFromSqlServer.CrystalReport1.rpt"
        End Get
        Set
            'Do nothing
        End Set
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public ReadOnly Property Section1() As CrystalDecisions.CrystalReports.Engine.Section
        Get
            Return Me.ReportDefinition.Sections(0)
        End Get
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public ReadOnly Property Section2() As CrystalDecisions.CrystalReports.Engine.Section
        Get
            Return Me.ReportDefinition.Sections(1)
        End Get
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public ReadOnly Property Section3() As CrystalDecisions.CrystalReports.Engine.Section
        Get
            Return Me.ReportDefinition.Sections(2)
        End Get
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public ReadOnly Property Section4() As CrystalDecisions.CrystalReports.Engine.Section
        Get
            Return Me.ReportDefinition.Sections(3)
        End Get
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public ReadOnly Property Section5() As CrystalDecisions.CrystalReports.Engine.Section
        Get
            Return Me.ReportDefinition.Sections(4)
        End Get
    End Property
End Class

<System.Drawing.ToolboxBitmapAttribute(GetType(CrystalDecisions.&#91;Shared&#93;.ExportOptions), "report.bmp")>  _
Public Class CachedCrystalReport1
    Inherits Component
    Implements ICachedReport
    
    Public Sub New()
        MyBase.New
    End Sub
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public Overridable Property IsCacheable() As Boolean Implements CrystalDecisions.ReportSource.ICachedReport.IsCacheable
        Get
            Return true
        End Get
        Set
            '
        End Set
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public Overridable Property ShareDBLogonInfo() As Boolean Implements CrystalDecisions.ReportSource.ICachedReport.ShareDBLogonInfo
        Get
            Return false
        End Get
        Set
            '
        End Set
    End Property
    
    <Browsable(false),  _
     DesignerSerializationVisibilityAttribute(System.ComponentModel.DesignerSerializationVisibility.Hidden)>  _
    Public Overridable Property CacheTimeOut() As System.TimeSpan Implements CrystalDecisions.ReportSource.ICachedReport.CacheTimeOut
        Get
            Return CachedReportConstants.DEFAULT_TIMEOUT
        End Get
        Set
            '
        End Set
    End Property
    
    Public Overridable Function CreateReport() As CrystalDecisions.CrystalReports.Engine.ReportDocument Implements CrystalDecisions.ReportSource.ICachedReport.CreateReport
        Dim rpt As CrystalReport1 = New CrystalReport1()
        rpt.Site = Me.Site
        Return rpt
    End Function
    
    Public Overridable Function GetCustomizedCacheKey(ByVal request As RequestContext) As String Implements CrystalDecisions.ReportSource.ICachedReport.GetCustomizedCacheKey
        Dim key As [String] = Nothing
        '// The following is the code used to generate the default
        '// cache key for caching report jobs in the ASP.NET Cache.
        '// Feel free to modify this code to suit your needs.
        '// Returning key == null causes the default cache key to
        '// be generated.
        '
        'key = RequestContext.BuildCompleteCacheKey(
        '    request,
        '    null,       // sReportFilename
        '    this.GetType(),
        '    this.ShareDBLogonInfo );
        Return key
    End Function
End Class

Form1.vb

Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports System.Data.SqlClient
Imports Bytescout.BarCode

Public Partial Class Form1
	Inherits Form
	Public Sub New()
		InitializeComponent()

		Try
			' MODIFY THE CONNECTION STRING WITH YOUR SERVER CONNECTION INFO!!!
			Const  connectionString As String = "Data Source=localhost\SQLEXPRESS;Initial Catalog=master;Integrated Security=true;"

			Using connection As New SqlConnection(connectionString)
				connection.Open()

				' Create a database for demonstration purposes
				'''////////////////////////////////////////////////////////////////////////////////////

				Dim o As [Object] = ExecuteQueryScalar(connection, "SELECT DB_ID('example_db')")

				' if 'example_db' does not exist, create it
				If o Is Nothing OrElse TypeOf o Is DBNull Then
					' Create empty database
					ExecuteQueryWithoutResult(connection, "CREATE DATABASE example_db")
					' Switch to created database
					ExecuteQueryWithoutResult(connection, "USE example_db")
					' Create a table
					ExecuteQueryWithoutResult(connection, "CREATE TABLE Products ([Product ID] int, [Product Name] nvarchar(100), [Product Description] nvarchar(255))")
					' Fill the table with data
					ExecuteQueryWithoutResult(connection, "INSERT Products VALUES(1, 'Spreadsheet Tools', 'Convert XLS, XLSX, CSV, ODS spreadsheet into HTML, PDF, XLS, XLSX, CSV formats WITHOUT EXCEL installed')")
					ExecuteQueryWithoutResult(connection, "INSERT Products VALUES(2, 'Watermarking PRO', 'Professional tool to protect images: multiple watermarks, custom position for watermarks, image effects, EXIF and IPTC macros for text and more')")
					ExecuteQueryWithoutResult(connection, "INSERT Products VALUES(3, 'Watermarking', 'Protect copyrights for your images with professional looking watermarks with this easy to use tool')")
					ExecuteQueryWithoutResult(connection, "INSERT Products VALUES(4, 'PPT To Video Scout', 'converts PowerPoint presentations (PPT, PPTX) into AVI,MPEG,WMV, FLV (flash video) video movies with sound')")
				End If


				' Create a datataset from query.
				' Query result columns must conform to field names we used in the report designer

				Dim dataAdapter As New SqlDataAdapter("SELECT [Product ID], [Product Name], [Product Description] FROM example_db.dbo.Products", connection)

				' fill dataset
				Dim dataSet As New DataSet()
				dataAdapter.Fill(dataSet)

				' don't forget to close the connection
				connection.Close()

				' add virtual column into the result table
				dataSet.Tables(0).Columns.Add(New DataColumn("BarCode", GetType(Byte())))

				' create barcode object
				Dim bc As New Barcode(SymbologyType.Code39)
				bc.DrawCaption = False

				For Each row As DataRow In dataSet.Tables(0).Rows
					' set barcode value
					bc.Value = (Convert.ToString(row("Product ID")))

					' retrieve generated image bytes
					Dim barcodeBytes As Byte() = bc.GetImageBytesWMF()

					' fill virtual column with generated image bytes
					row("BarCode") = barcodeBytes
				Next

				' set report datasource
				CrystalReport11.SetDataSource(dataSet.Tables(0))
			End Using
		Catch ex As Exception
			Trace.WriteLine("Error: " & ex.Message)
		End Try
	End Sub

	Private Shared Sub ExecuteQueryWithoutResult(connection As SqlConnection, query As String)
		Using command As New SqlCommand(query, connection)
			command.ExecuteNonQuery()
		End Using
	End Sub

	Private Shared Function ExecuteQueryScalar(connection As SqlConnection, query As String) As Object
		Using command As New SqlCommand(query, connection)
			Return command.ExecuteScalar()
		End Using
	End Function
End Class

Program.vb

Imports System.Collections.Generic
Imports System.Windows.Forms

NotInheritable Class Program
	Private Sub New()
	End Sub
	<STAThread> _
	Friend Shared Sub Main()
		Application.EnableVisualStyles()
		Application.Run(New Form1())
	End Sub
End Class

DataSet1.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="DataSet1" targetNamespace="http://tempuri.org/DataSet1.xsd" xmlns:mstns="http://tempuri.org/DataSet1.xsd" xmlns="http://tempuri.org/DataSet1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" FunctionsComponentName="QueriesTableAdapter" Modifier="AutoLayout, AnsiClass, Class, Public" SchemaSerializationMode="IncludeSchema" xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections>
          <Connection AppSettingsObjectName="Settings" AppSettingsPropertyName="example_dbConnectionString" ConnectionStringObject="" IsAppSettingsProperty="True" Modifier="Assembly" Name="example_dbConnectionString (Settings)" ParameterPrefix="@" PropertyReference="ApplicationSettings.ReportFromSqlServer.Settings.GlobalReference.Default.example_dbConnectionString" Provider="System.Data.SqlClient">
          </Connection>
        </Connections>
        <Tables>
          <TableAdapter BaseClass="System.ComponentModel.Component" DataAccessorModifier="AutoLayout, AnsiClass, Class, Public" DataAccessorName="ProductsTableAdapter" GeneratorDataComponentClassName="ProductsTableAdapter" Name="Products" UserDataComponentName="ProductsTableAdapter">
            <MainSource>
              <DbSource ConnectionRef="example_dbConnectionString (Settings)" DbObjectName="example_db.dbo.Products" DbObjectType="Table" FillMethodModifier="Public" FillMethodName="Fill" GenerateMethods="Both" GenerateShortCommands="True" GeneratorGetMethodName="GetData" GeneratorSourceName="Fill" GetMethodModifier="Public" GetMethodName="GetData" QueryType="Rowset" ScalarCallRetval="System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" UseOptimisticConcurrency="True" UserGetMethodName="GetData" UserSourceName="Fill">
                <InsertCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="False">
                    <CommandText>INSERT INTO [dbo].[Products] ([Product ID], [Product Name], [Product Description]) VALUES (@Product_ID, @Product_Name, @Product_Description)</CommandText>
                    <Parameters>
                      <Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="Int32" Direction="Input" ParameterName="@Product_ID" Precision="0" ProviderType="Int" Scale="0" Size="0" SourceColumn="Product ID" SourceColumnNullMapping="False" SourceVersion="Current">
                      </Parameter>
                      <Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="String" Direction="Input" ParameterName="@Product_Name" Precision="0" ProviderType="NVarChar" Scale="0" Size="0" SourceColumn="Product Name" SourceColumnNullMapping="False" SourceVersion="Current">
                      </Parameter>
                      <Parameter AllowDbNull="True" AutogeneratedName="" DataSourceName="" DbType="String" Direction="Input" ParameterName="@Product_Description" Precision="0" ProviderType="NVarChar" Scale="0" Size="0" SourceColumn="Product Description" SourceColumnNullMapping="False" SourceVersion="Current">
                      </Parameter>
                    </Parameters>
                  </DbCommand>
                </InsertCommand>
                <SelectCommand>
                  <DbCommand CommandType="Text" ModifiedByUser="False">
                    <CommandText>SELECT [Product ID], [Product Name], [Product Description] FROM dbo.Products</CommandText>
                    <Parameters>
                    </Parameters>
                  </DbCommand>
                </SelectCommand>
              </DbSource>
            </MainSource>
            <Mappings>
              <Mapping SourceColumn="Product ID" DataSetColumn="Product ID" />
              <Mapping SourceColumn="Product Name" DataSetColumn="Product Name" />
              <Mapping SourceColumn="Product Description" DataSetColumn="Product Description" />
            </Mappings>
            <Sources>
            </Sources>
          </TableAdapter>
        </Tables>
        <Sources>
        </Sources>
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="DataSet1" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="DataSet1" msprop:Generator_DataSetName="DataSet1">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Products" msprop:Generator_UserTableName="Products" msprop:Generator_RowDeletedName="ProductsRowDeleted" msprop:Generator_TableClassName="ProductsDataTable" msprop:Generator_RowChangedName="ProductsRowChanged" msprop:Generator_RowClassName="ProductsRow" msprop:Generator_RowChangingName="ProductsRowChanging" msprop:Generator_RowEvArgName="ProductsRowChangeEvent" msprop:Generator_RowEvHandlerName="ProductsRowChangeEventHandler" msprop:Generator_TablePropName="Products" msprop:Generator_TableVarName="tableProducts" msprop:Generator_RowDeletingName="ProductsRowDeleting">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Product_x0020_ID" msprop:Generator_UserColumnName="Product ID" msprop:Generator_ColumnPropNameInRow="Product_ID" msprop:Generator_ColumnVarNameInTable="columnProduct_ID" msprop:Generator_ColumnPropNameInTable="Product_IDColumn" type="xs:int" minOccurs="0" />
              <xs:element name="Product_x0020_Name" msprop:Generator_UserColumnName="Product Name" msprop:Generator_ColumnPropNameInRow="Product_Name" msprop:Generator_ColumnVarNameInTable="columnProduct_Name" msprop:Generator_ColumnPropNameInTable="Product_NameColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="100" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="Product_x0020_Description" msprop:Generator_UserColumnName="Product Description" msprop:Generator_ColumnPropNameInRow="Product_Description" msprop:Generator_ColumnVarNameInTable="columnProduct_Description" msprop:Generator_ColumnPropNameInTable="Product_DescriptionColumn" minOccurs="0">
                <xs:simpleType>
                  <xs:restriction base="xs:string">
                    <xs:maxLength value="255" />
                  </xs:restriction>
                </xs:simpleType>
              </xs:element>
              <xs:element name="BarCode" msprop:Generator_UserColumnName="BarCode" msprop:Generator_ColumnPropNameInRow="BarCode" msprop:Generator_ColumnVarNameInTable="columnBarCode" msprop:Generator_ColumnPropNameInTable="BarCodeColumn" type="xs:base64Binary" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

Tutorials:

prev
next