VB.NET Advanced Topics: Crystal Reports, Excel Automation, Email, and XML

Module 9: VB.NET Crystal Report Introduction

  • Objective: Generate reports using Crystal Reports in VB.NET based on database table data.
  • Objectives:
    • Understand Crystal Reports in VB.NET.
    • Create a VB.NET application to generate printed reports.
    • Use Crystal Reports in a VB.NET program.
    • Import database table data into Crystal Reports.
    • Configure database connection and table data in Crystal Reports.

Lecture: Creating Database and Tables for Crystal Reports

  • Create a database named "crystaldb".
  • Create three tables in the crystaldb database.
    • OrderMaster
    • OrderDetails
    • Product
OrderMaster Table Fields:
  • OrderMaster_id (int, NOT NULL)
  • OrderMaster_date (datetime, NULL)
  • OrderMaster_customername (varchar(50))
  • OrderMaster_createduser (varchar(50))
OrderDetails Table Fields:
  • OrderDetails_id (int, NOT NULL)
  • OrderDetails_masterid (int, NULL)
  • OrderDetails_productid (int, NULL)
  • OrderDetails_qty (int, NULL)
Product Table Fields:
  • Product_id (int, NOT NULL)
  • Product_name (varchar(50))
  • Product_price (numeric(18, 0), NULL)

SQL Commands for Table Creation

  • SQL commands to create the tables:
    • OrderMaster:
CREATE TABLE [dbo].[OrderMaster] (
 [OrderMaster_id] [int] NOT NULL ,
 [OrderMaster_date] [datetime] NULL ,
 [OrderMaster_customername] [varchar] (50),
 [OrderMaster_createduser] [varchar] (50)
) ON [PRIMARY]

*   OrderDetails:

CREATE TABLE [dbo].[OrderDetails] (
 [OrderDetails_id] [int] NOT NULL ,
 [OrderDetails_masterid] [int] NULL ,
 [OrderDetails_productid] [int] NULL ,
 [OrderDetails_qty] [int] NULL
) ON [PRIMARY]

*   Product:

CREATE TABLE [dbo].[Product] (
 [Product_id] [int] NOT NULL ,
 [Product_name] [varchar] (50) ,
 [Product_price] [numeric](18, 0) NULL
) ON [PRIMARY]

Creating a Crystal Report in VB.NET

  • Open Visual Studio .NET and create a new Visual Basic .NET Project.
  • Create a new Crystal Report for the Product table from the crystalDB database.
  • The Product table has three fields: Product_id, Product_name, Product_price.
  • Display the entire table data in the Crystal Report.
  • From the main menu, select PROJECT --> Add New Item.
  • In the Add New Item dialog, select Crystal Reports.
  • Select the Report type from the Crystal Reports gallery.
  • Select OLE DB (ADO) from Create New Connection.
  • Select Microsoft OLE DB Provider for SQL Server.
  • Enter SQL Server name, User ID, password, and database name.
  • Click Next, and then Finish on the OLE DB Property values screen (leave as is).
  • Select the database name (Crystaldb) under OLEDB Connection, then click on Tables.
  • Select the Product table from the tables list to the right-side list.
  • Select all fields from the Product table to the right-side list.
  • Click the Finish button.
  • The Crystal Reports designer window appears.
  • Arrange the design according to your requirements.

Calling Crystal Reports in VB.NET

  • Call the created Crystal Reports in VB.NET through the Crystal Reports Viewer control.
  • Select the default form (Form1.vb) and drag a button and CrystalReportViewer control to the form.
  • Add the following import statement at the top of the form's source code.
Imports CrystalDecisions.CrystalReports.Engine
  • Add the following source code in the button click event:
Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click
 Dim cryRpt As New ReportDocument
 cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")
 CrystalReportViewer1.ReportSource = cryRpt
 CrystalReportViewer1.Refresh()
 End Sub
End Class
  • Note: Replace "PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt" with the full path to your Crystal Report file (CrystalReport1.rpt).

PrintPreviewDialog Control Overview

  • The Windows Forms PrintPreviewDialog control is a pre-configured dialog box used to display how a PrintDocument will appear when printed.
  • Use it within your Windows-based application as a simple solution instead of configuring your own dialog box.
  • The control contains buttons for printing, zooming, displaying one or multiple pages, and closing the dialog box.
Key properties and methods
  • Document: Sets the document to be previewed. The document must be a PrintDocument object.
  • ShowDialog Method: To display the dialog box, you must call its ShowDialog method.
  • UseAntiAlias Property: Set to true to make the text appear smoother.
  • Columns and Rows Properties: Determine the number of pages displayed horizontally and vertically on the control (accessible through PrintPreviewDialog1.PrintPreviewControl.Columns in Visual Basic)

PrintPreviewDialog Slow Initialization

  • The PrintPreviewDialog control initializes very slowly under the following conditions:
    • A network printer is used.
    • User preferences for this printer, such as duplex settings, are modified.
  • The optimization is not applied if the EnablePrintPreviewOptimization key is set to any other value, or if the key is not present.
  • This key has no effect if the application is running on .NET Framework 4.6 or later.
  • The performance of the PrintPreviewDialog control will not improve even if an optimization configuration switch is set, if you use the QueryPageSettings event to modify printer settings.

Print Preview Implementation

  • Offer print preview in addition to printing services.
  • Use a PrintPreviewDialog control in combination with the PrintPage event-handling logic.
Steps to Preview a Text Document
  1. In Visual Studio, use the Solution Explorer pane and double-click the form you want to print from.
  2. From the Toolbox pane, double-click both the PrintDocument component and the PrintPreviewDialog component, to add them to the form.
  3. Add a Button to the form, or use a button that is already on the form.
  4. Select the button in the Visual Designer of the form. In the Properties pane, select the Event filter button and then double-click the Click event to generate an event handler.
  5. Set the DocumentName property to the document you wish to print.
  6. Open and read the document's contents to the string you added previously.
  7. In the PrintPage event handler, use the Graphics property of the PrintPageEventArgs class and the file contents to calculate lines per page and render the document's contents.
  8. After each page is drawn, check to see if it's the last page, and set the HasMorePages property of the PrintPageEventArgs accordingly. The PrintPage event is raised until HasMorePages is false.
  9. When the document has finished rendering, reset the string to be rendered. Also, ensure that the PrintPage event is associated with its event-handling method.
  10. Set the Document property of the PrintPreviewDialog control to the PrintDocument component on the form.
  11. Call the ShowDialog method on the PrintPreviewDialog control.

VB.NET Scripting Example

Imports System.Data.OleDb
Public Class Form1
 Dim connectionstring As String
 Dim dbconnection As OleDbConnection
 Dim dbadapter As New OleDbDataAdapter
 Dim dbdataset As New DataSet
 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 dgv.AllowUserToAddRows = False
 connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = student.mdb;"
 dbconnection = New OleDbConnection(connectionstring)
 Try
 dbconnection.Open()
 dbdataset.Clear()
 dbadapter = New OleDbDataAdapter("Select * from StudInfo", connectionstring)
 dbadapter.Fill(dbdataset, "StudInfo")
 dgv.DataSource = dbdataset.Tables("StudInfo").DefaultView
 dbconnection.Close()
 Label2.Text = "Connected"
 Label2.ForeColor = Color.LimeGreen
 Catch ex As Exception
 Label2.Text = "Disconnected"
 Label2.ForeColor = Color.Red
 End Try
 End Sub
 Private Sub btnPRINT_Click(sender As Object, e As EventArgs) Handles btnPRINT.Click
 PrintPreviewDialog1.Document = PrintDocument1
 PrintPreviewDialog1.WindowState = FormWindowState.Maximized
 PrintPreviewDialog1.ShowDialog()
 End Sub
 Private mRow As Integer = 0
 Private newPage As Boolean = True
 Private Sub PrintDocument1_PrintPage(sender As Object, e As Printing.PrintPageEventArgs) _
 Handles PrintDocument1.PrintPage
 Dim Format As New StringFormat
 Format.Alignment = StringAlignment.Center
 e.Graphics.DrawString("Student List", New Font("Century Gothic", 20, FontStyle.Bold), _
 Brushes.Black, New Point(400, 20), Format)
 Dim fmt As StringFormat = New StringFormat(StringFormatFlags.LineLimit)
 fmt.LineAlignment = StringAlignment.Center
 fmt.Trimming = StringTrimming.EllipsisCharacter
 fmt.Alignment = StringAlignment.Center
 Dim y As Integer = 100
 Dim x As Integer = 150
 Dim h As Integer = 0
 Dim rc As Rectangle
 Dim row As DataGridViewRow
 If newPage Then
 row = dgv.rows(mRow)
 x = 150
 For Each cell As DataGridViewCell In row.Cells
 If cell.Visible Then
 rc = New Rectangle(x, y, cell.Size.Width, cell.Size.Height)
 e.Graphics.FillRectangle(Brushes.LightGray, rc)
 e.Graphics.DrawRectangle(Pens.Black, rc)
 e.Graphics.DrawString(dgv.Columns(cell.ColumnIndex).HeaderText, dgv.Font, _
 Brushes.Black, rc, fmt)
 x += rc.Width
 h = Math.Max(h, rc.Height)
 End If
 Next
 y += h
 End If
 newPage = False
 Dim displayNow As Integer
 For displayNow = mRow To dgv.RowCount - 1
 row = dgv.Rows(displayNow)
 x = 150
 h = 0
 For Each cell As DataGridViewCell In row.Cells
 If cell.Visible Then
 rc = New Rectangle(x, y, cell.Size.Width, cell.Size.Height)
 e.Graphics.DrawRectangle(Pens.Black, rc)
 fmt.Alignment = StringAlignment.Near
 rc.Offset(10, 0)
 e.Graphics.DrawString(cell.FormattedValue.ToString(), dgv.Font, Brushes.Black, rc, fmt)
 x += rc.Width
 h = Math.Max(h, rc.Height)
 End If
 Next
 y += h
 Next
 End Sub
End Class

Module 10: VB.NET Excel Automation

  • Introduction:
    • Focuses on reading and importing Excel files into datasets or datatables in VB.NET.
    • Covers exporting data from databases to Excel.
    • Includes exporting data from DataGridViews to Excel.
  • Objectives:
    1. Understand importing Excel files into datasets or datatables.
    2. Create a VB.NET application to read and import Excel files.
    3. Describe exporting data from databases to Excel using VB.NET.
    4. Explain exporting data from DataGridView to Excel using VB.NET.
    5. Create a VB.NET application to export data to Excel.

Reading Excel Data into DataTable or Dataset (OLEDB)

  • Without using Excel objects, you can insert, edit, delete, and select cell content in an Excel file using OLEDB in VB.NET.
  • Utilize OleDbConnection, OleDbDataAdapter, and DataSet for these operations.
  • Import System.Data in the project.
  • Use the SELECT command like in SQL.
sql = "select * from [Sheet1$]"
  • Connection string example:
"provider=Microsoft.Jet.OLEDB.4.0;Data Source='Your Filename';Extended Properties=Excel 8.0;"
  • Fastest Way to Read Excel Sheet into DataTable
    • Get the contents of an Excel file into a DataGrid.
Sample Source Code:
Imports System.Data
Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click
 Try
 Dim MyConnection As System.Data.OleDb.OleDbConnection
 Dim DtSet As System.Data.DataSet
 Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
 MyConnection = New System.Data.OleDb.OleDbConnection _
 ("provider=Microsoft.Jet.OLEDB.4.0;" _
 & "Data Source='c:\testfile.xls'; " _
 & "Extended Properties=Excel 8.0;")
 MyCommand = New System.Data.OleDb.OleDbDataAdapter _
 ("select * from [Sheet1$]", MyConnection)
 MyCommand.TableMappings.Add("Table", "TestTable")
 DtSet = New System.Data.DataSet
 MyCommand.Fill(DtSet)
 DataGridView1.DataSource = DtSet.Tables(0)
 MyConnection.Close()
 Catch ex As Exception
 MsgBox(ex.ToString)
 End Try
 End Sub
End Class

Exporting Data from Database to Excel

  • Load data from the database to a dataset, then create a new Excel file and write the data to it.
  • Load the Product table data to the dataset (refer to Database Structure for details).
  • Create a new Excel file and write the data from the dataset to the Excel file.
For i = 0 To ds.Tables(0).Rows.Count - 1
 For j = 0 To ds.Tables(0).Columns.Count - 1
 xlWorkSheet.Cells(i + 1, j + 1) = _
 ds.Tables(0).Rows(i).Item(j)
 Next
Next
Sample Source Code:
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click
 Dim cnn As SqlConnection
 Dim connectionString As String
 Dim sql As String
 Dim i, j As Integer
 Dim xlApp As Excel.Application
 Dim xlWorkBook As Excel.Workbook
 Dim xlWorkSheet As Excel.Worksheet
 Dim misValue As Object = System.Reflection.Missing.Value

 xlApp = New Excel.ApplicationClass
 xlWorkBook = xlApp.Workbooks.Add(misValue)
 xlWorkSheet = xlWorkBook.Sheets("sheet1")

 connectionString = "data source=servername;" & _
 "initial catalog=databasename;user id=username;password=password;"
 cnn = New SqlConnection(connectionString)
 cnn.Open()

 sql = "SELECT * FROM Product"
 Dim dscmd As New SqlDataAdapter(sql, cnn)
 Dim ds As New DataSet
 dscmd.Fill(ds)

 For i = 0 To ds.Tables(0).Rows.Count - 1
 For j = 0 To ds.Tables(0).Columns.Count - 1
 xlWorkSheet.Cells(i + 1, j + 1) = _
 ds.Tables(0).Rows(i).Item(j)
 Next
 Next

 xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
 xlWorkBook.Close()
 xlApp.Quit()

 releaseObject(xlApp)
 releaseObject(xlWorkBook)
 releaseObject(xlWorkSheet)

 cnn.Close()
 MsgBox("You can find the file C:\vbexcel.xlsx")
 End Sub

 Private Sub releaseObject(ByVal obj As Object)
 Try
 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
 obj = Nothing
 Catch ex As Exception
 obj = Nothing
 Finally
 GC.Collect()
 End Try
 End Sub
End Class

Exporting Data from DataGridView to Excel

  • Connect to the database, load data from the database to the DataGridView, create a new Excel file, and write the data from the DataGridView to the Excel file.
  • Load the Product table data to DataGridView (refer to Database Structure for details).
  • Create a new Excel file and write the data from the DataGridView to the Excel file.
Sample Source Code:
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click
 Dim cnn As SqlConnection
 Dim connectionString As String
 Dim sql As String

 connectionString = "data source=servername;" & _
 "initial catalog=databasename;user id=username;password=password;"
 cnn = New SqlConnection(connectionString)
 cnn.Open()

 sql = "SELECT * FROM Product"
 Dim dscmd As New SqlDataAdapter(sql, cnn)
 Dim ds As New DataSet
 dscmd.Fill(ds)

 DataGridView1.DataSource = ds.Tables(0)
 cnn.Close()
 End Sub

 Private Sub Button2_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button2.Click
 Dim xlApp As Excel.Application
 Dim xlWorkBook As Excel.Workbook
 Dim xlWorkSheet As Excel.Worksheet
 Dim misValue As Object = System.Reflection.Missing.Value
 Dim i As Integer
 Dim j As Integer

 xlApp = New Excel.ApplicationClass
 xlWorkBook = xlApp.Workbooks.Add(misValue)
 xlWorkSheet = xlWorkBook.Sheets("sheet1")

 For i = 0 To DataGridView1.RowCount - 2
 For j = 0 To DataGridView1.ColumnCount - 1
 xlWorkSheet.Cells(i + 1, j + 1) = _
 DataGridView1(j, i).Value.ToString()
 Next
 Next

 xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
 xlWorkBook.Close()
 xlApp.Quit()

 releaseObject(xlApp)
 releaseObject(xlWorkBook)
 releaseObject(xlWorkSheet)

 MsgBox("You can find the file C:\vbexcel.xlsx")
 End Sub

 Private Sub releaseObject(ByVal obj As Object)
 Try
 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
 obj = Nothing
 Catch ex As Exception
 obj = Nothing
 Finally
 GC.Collect()
 End Try
 End Sub
End Class

Module 11: VB.NET Email Communication

  • Introduction:
    • Covers sending email using a VB.NET application.
    • Includes sending emails with attachments.
  • Objectives:
    1. Describe email communication using VB.NET.
    2. Understand the concept of email communication.
    3. Identify the protocol in email communication.
    4. Create a VB.NET application with email communication.
    5. Understand the concept of email communication with email attachment.

Sending Email from VB.NET

SMTP (Simple Mail Transfer Protocol)
  • A protocol for sending e-mail messages between servers.
  • An Internet standard for email transmission.
  • Default TCP port: 25
  • SMTPS (secured by SSL): port 465
SMTP Servers
  • Simplify the communication of email messages between email servers.
  • Format: smtp.domain.com or mail.domain.com (e.g., smtp.gmail.com for Gmail).
SMTP Authentication (SMTP AUTH)
  • An extension of SMTP where a client logs in using an authentication mechanism supported by the SMTP servers.
  • Example:
SmtpServer.Credentials = New _
 Net.NetworkCredential("username@gmail.com", "password")
Sending Mail Using VB.NET
  • VB.NET uses SMTP protocol for sending email.
  • The System.Net.Mail namespace is used.
  • Instantiate the SmtpClient class and assign the Host and Port.
  • Default port: 25 (may vary).
Sending Email using Gmail in VB.NET
  • Gmail SMTP server name: smtp.gmail.com
  • Port: 587
  • Use NetworkCredential for password-based authentication.
Sample Code:
Imports System.Net.Mail

Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button1.Click
 Try
 Dim SmtpServer As New SmtpClient()
 Dim mail As New MailMessage()

 SmtpServer.Credentials = New _
 Net.NetworkCredential("username@gmail.com", "password")
 SmtpServer.Port = 587
 SmtpServer.Host = "smtp.gmail.com"

 mail = New MailMessage()
 mail.From = New MailAddress("YOURusername@gmail.com")
 mail.To.Add("TOADDRESS")
 mail.Subject = "Test Mail"
 mail.Body = "This is for testing SMTP mail from GMAIL"

 SmtpServer.Send(mail)
 MsgBox("mail send")
 Catch ex As Exception
 MsgBox(ex.ToString)
 End Try
 End Sub
End Class
  • Provide Gmail username, password, and recipient address.

VB.NET Email Attachment

  • Use System.Net classes to communicate with other applications via HTTP, TCP, UDP, Socket, etc.
  • System.Net.Mail namespace is used for sending email.
Dim attachment As System.Net.Mail.Attachment
attachment = New System.Net.Mail.Attachment("your attachment file")
mail.Attachments.Add(attachment)
Sample Code for Email with Attachment:
Imports System.Net.Mail

Public Class Form1
 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As _
 System.EventArgs) Handles Button1.Click
 Dim mail As New MailMessage()
 Dim SmtpServer As New SmtpClient("smtp.gmail.com")

 mail.From = New MailAddress("your_email_address@gmail.com")
 mail.To.Add("to_address")
 mail.Subject = "Test Mail - 1"
 mail.Body = "mail with attachment"

 Dim attachment As System.Net.Mail.Attachment
 attachment = New System.Net.Mail.Attachment("your attachment file")
 mail.Attachments.Add(attachment)

 SmtpServer.Port = 587
 SmtpServer.Credentials = New _
 System.Net.NetworkCredential("username", "password")
 SmtpServer.EnableSsl = True

 SmtpServer.Send(mail)
 MessageBox.Show("mail Send")
 End Sub
End Class

Module 12: VB.Net XML

  • Introduction:
    • XML is a general-purpose, tag-based language for transferring and storing data across applications.
  • Objectives:
    1. Describe the concept of ADO.Net Interoperability in Excel Sheet.
    2. Describe the Concept of Send Email in VB.Net Application.
    3. Understand the concept of XML Processing.
    4. Understand Dynamic Web Application.
    5. Understand a VB.Net Program Basic Components in XML.

XML in VB.NET

  • XML is a general-purpose, tag-based language that is easy to transfer and store data across applications.
  • Like HTML, XML is a subset of SGML (Standard Generalized Markup Language).
  • XML is platform independent.
  • XML is a self-describing language.
  • XML files are made up of tags containing data, typically with a start and end tag.
<Header>Header Content Here</Header>
Important Notes:
  • XML is case-sensitive.
  • Tags must be closed in the reverse order they were opened.
<first-tag><second-tag>Data here</second-tag></first-tag>

XML Support in .NET

  • The .NET Framework provides classes for reading, writing, and other XML operations.
  • These classes are stored in namespaces like System.Xml, System.Xml.Schema, System.Xml.Serialization, System.Xml.XPath, System.Xml.Xsl.
  • The Dataset in ADO.NET uses XML as its internal storage format.
  • Use any text editor to create XML files.
How to create an XML file in VB.NET
  • XML is a platform independent language, so the information formatted in XML can be used in any other platforms (Operating Systems).
  • If we create an XML file in one platform it can be used in other platforms also.
How to open and read XML file in VB.NET
  • XML is a self-describing language and it gives the data as well as the rules to extract what the data it contains.
  • Reading an XML file means that we are reading the information embedded in XML tags in an XML file.

Reading XML with XmlReader in VB.Net

  • XmlReader is faster and consumes less memory. It provides a lower-level abstraction over the XML file structure.
  • The XmlReader class allows you run through the XML string one element at a time, while allowing you to look at the value, and then moves on to the next XML element.

Reading XML with XmlDocument in VB.Net

  • XmlDocument reads the entire XML content into memory and then allow you to navigate back and forward in it or even query the XML document using the XPath technology.
How to create an XML file in VB.NET using Dataset
  • XML is a tag-based language, that means the document is made up of XML tags that contain information.
  • We can create an XML file in several ways.
  • Here we are creating an XML file using an ADO.NET Dataset.
  • For that we have to manually create a Datatable first and add the data of Product.XML in the Datatable.
  • Then add the Datatable in a Dataset.
  • Call the method WriteXml of Dataset and pass the file name Product.XML as argument.
How to read an XML file in VB.NET using ADO.NET - Dataset
  • XML is a platform independent language, so the information formatted in XML can be use in any other platforms (Operating Systems).
  • The .Net technology is widely supported XML file format.
  • The .Net Framework provides the Classes for read, write, and other operations in XML formatted files.
  • Here we are going to read an XML file using a Dataset.
  • Here Dataset is using an XmlReader for read the content of the file.
  • Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset.
How to create an XML file from SQL in VB.NET
  • XML is a general-purpose tag-based language and very easy to transfer and store data across applications.
  • The .Net technology is widely supported XML file format.
  • The .Net Framework provides the Classes for read, write, and other operations in XML formatted files.
  • Moreover, the Dataset in ADO.NET uses XML format as its internal storage format.
  • There are several ways to create an XML file.
  • In the previous sections we already saw how to create an XML file using XmlTextWriter and also created an XML file using manually created Dataset.
  • Here we are going to create an XML file from Database.
  • Make an SQL connection to the Database and execute the sql and store the data in a DataSet.
  • Call Dataset's WriteXml() method and pass the file name as argument.