Upgrading from ADO to ADO.NET

ADO and ADO.NET both provide the data access API that enables you to select, insert, update, and delete data. ADO objects conform to COM while ADO.NET objects conform to the object-oriented model that .NET Framework provides. ADO.NET runs on .NET Framework’s Common Language Runtime (CLR) component. ADO and ADO.NET features, such as the connection model and data representation, differ in some ways. For example, ADO is a connection-oriented model although it contains disconnected recordsets. ADO.NET is a disconnected model, where the DataAdapter and DataSet classes provide disconnected access, although you can also perform connection-oriented data access.

Creating an ADO-Based VB Application

An ADO application contains different components and functions that are part of the ADO architecture, include:

• Application component: Allows ADO objects, collections, methods, and properties to interact with the database, submits queries, and processes output.

• ADO: Handles the interaction between an application and the Object Linking and Embedding Databases (OLE DB) provider of the application.

• OLE DB provider: Processes all ADO requests from the application, connects to the database, sends SQL statements from the application to the database, and returns output to the application.

• Database: Stores the data that the provider needs to access a data instance.

ADO performs the following tasks in an application when interacting with the database:

• Connects to the database.

• Sends SQL statements to the database.

• Executes the output of statements from the database.

• Executes errors and messages.

• Ends the connection with the database.

Consider a Sales Report VB application that allows end users to view sales reports based on sales by product, customer, and year. The Sales Report application contains two projects:

• SalesReportProvider: Provides sales information based on products, customer, and year by retrieving information from the database. This project compiles to SalesReportProvider.dll. You can use this dll as a component in the SalesReport project. The SalesReportProvider project consists of the following files:
  
  

  • SalesYear.cls: Contains methods that allow the end user to retrieve information about the total sales of a specified year.

  • SalesCustomer.cls: Contains methods that allow end users to retrieve information about sales to customers over a specified period.

  • SalesProduct.cls: Contains methods that allow end users to retrieve information about the sales of products over a specified period.

  
  

• SalesReport: Allows end users to view three different reports Sales by Product, Sales by Customer, and Sales by Year. This project contains the following files:
  
  

  • frmMain.frm: Provides end users with options to view the Sales by Product, Sales by Customer, and Sales by Year reports.

  • frmSalesYear.frm: Allows end users to view the sales report for a particular year. It displays the total sales and the total number of orders placed in the year.

  • frmSalesCustomer.frm: Allows end users to view the total sales to different customers for a specified period.

  • frmSalesProduct.frm: Allows end users to view the sales of products over a specified period.

  
  

Creating the SalesReportProvider Project

To create the SalesReportProvider project, you need to create the SalesYear, SalesProduct, and SalesCustomer classes.

Creating the SalesYear Class

The SalesYear.cls file defines the SalesYear class that retrieves sales information for a particular year and the total number of orders placed in that year. Listing 4-1 shows how to create the SalesYear.cls file:

Listing 4-1: The SalesYear.cls File

 Option Explicit Dim objCon As Connection 'This method returns a recordset containing the total number of orders placed in the specified year Public Function getNoOfOrderByYear(ByVal strYear As String) As Recordset setConnection Dim objcom As New ADODB.Command objcom.CommandText = "select count(OrderId) as [Total Orders] from orders where OrderDate between '1/1/" & strYear & "' and '12/31/" & strYear & "' " objcom.ActiveConnection = objCon Dim rs As New ADODB.Recordset Set rs = objcom.Execute Set getNoOfOrderByYear = rs End Function 'This method returns a recordset containing the sales by order in the specified year Public Function getByOrderSalesByYear(ByVal strYear As String) As Recordset setConnection Dim objcom As New ADODB.Command objcom.CommandText = "select orders.orderid,ContactName,orderdate,sum(unitprice*quantity) as sales from orders inner join [order details] on orders.orderid=[order details].orderid inner join customers on customers.customerid=orders.customerid and orderdate between '1/1/" & strYear & "' and '12/31/" & strYear & "' group by orders.orderid,orderdate,contactname order by orders.orderid" objcom.ActiveConnection = objCon Dim rs As New ADODB.Recordset Set rs = objcom.Execute Set getByOrderSalesByYear = rs End Function Public Sub setConnection() Set objCon = New ADODB.Connection 'Specifying the connection string for opening the connection objCon.ConnectionString = "Provider=SQLOLEDB.1;Server=localhost;User Id=sa;Password=sa; Initial Catalog=Northwind" objCon.Open End Sub 

The above listing defines the following methods:

• getNoOfOrderByYear(): Returns a recordset that contains the total number of orders placed in the specified year.

• getByOrderSalesByYear(): Returns a recordset that contains sales information, such as the contact name of a customer, the order ID, and the number of the orders that the customer placed in a particular year.

• setConnection(): Opens a connection to the database using the ADO Connection object.

Creating the SalesCutomer Class

The SalesCustomer.cls file defines the SalesCustomer class that retrieves information about the total sales to different customers during a period. Listing 4-2 shows how to create the SalesCustomer.cls file:

Listing 4-2: The SalesCustomer.cls File

 Option Explicit Dim objCon As ADODB.Connection 'This method returns a recordset containing the information about customer sales during a particular period Public Function getSalesByCustomer(ByVal fromDate As Date, ByVal toDate As Date) As Recordset setConnection Dim objCom As New ADODB.Command objCom.CommandText = "select contactname,customers.customerId,sum([order details].UnitPrice*[order details].Quantity) as sales from customers inner join orders on customers.customerId=orders.CustomerId inner join [order details]on [Order Details].orderId=orders.orderId and OrderDate between '" & fromDate & "' and '" & toDate & "' group by contactname, customers.customerId" objCom.ActiveConnection = objCon Dim rs As New ADODB.Recordset Set rs = objCom.Execute() Set getSalesByCustomer = rs End Function Public Sub setConnection() Set objCon = New ADODB.Connection 'Specifying the connection string for opening the connection objCon.ConnectionString = "Provider=SQLOLEDB.1; Server=localhost; User Id=sa; Password=sa; Initial Catalog=Northwind" objCon.Open End Sub 

The above listing defines the following methods:

• getSalesByCustomer(): Returns a recordset that contains information about total sales to different customers during a period.

• setConnection() method: Opens a connection to the database using the ADO Connection object.

Creating the SalesProduct Class

The SalesProduct.cls file defines the SalesProduct class that retrieves sales information about products during a period. Listing 4-3 shows how to create the SalesProduct.cls file:

Listing 4-3: The SalesProduct.cls File

 Option Explicit Dim objCon As ADODB.Connection Dim objCmd As ADODB.Command 'This method returns a recordset containing the information about sales by products during a particular period Public Function getSalesByProduct(ByVal fromDate As String, ByVal toDate As String) As ADODB.Recordset setConnection Set objCmd = New ADODB.Command objCmd.CommandText = "select productName,products.productid,sum([order details].unitprice*[order details].quantity) as sales from products,[order details] inner join orders on [order details].orderid=orders.orderid where products.productid=[order details].productid and orderdate between '" & fromDate & "' and '" & toDate & "' group by productname, products.productid order by products.productid" objCmd.ActiveConnection = objCon Dim rs As New ADODB.Recordset Set rs = objCmd.Execute() Set getSalesByProduct = rs End Function Public Sub setConnection() Set objCon = New ADODB.Connection 'Specifying the connection string for opening the connection objCon.ConnectionString = "Provider=SQLOLEDB.1; Server=localhost; User Id=sa;Password=sa; Initial Catalog=Northwind" objCon.Open End Sub 

The above listing defines the following methods:

• getSalesByProduct(): Returns a recordset that contains information about sales by products during a period.

• setConnection(): Opens a connection to the database using the ADO Connection object.

Creating the SalesReport Project

To create the SalesReport project, you need to create the Choose Report Option, Sales by Year, Sales by Customer, and Sales by Product windows.

Creating the Choose Report Option Window

The frmMain.frm file defines options to view the Sales by Product, Sales by Customer, and Sales by Year reports. Listing 4-4 shows how to create the frmMain.frm file:

Listing 4-4: The frmMain.frm File

 Option Explicit Private Sub mnuSalesByCustomer_Click() frmSalesCustomer.Show frmSalesCustomer.WindowState = 0 frmSalesCustomer.Height = Me.Height frmSalesCustomer.Width = Me.Width frmSalesCustomer.WindowState = 2 End Sub Private Sub mnuSalesByProduct_Click() frmSalesProduct.Show frmSalesProduct.WindowState = 0 frmSalesProduct.Height = Me.Height frmSalesProduct.Width = Me.Width frmSalesProduct.WindowState = 2 End Sub Private Sub mnuSalesByYear_Click() frmSalesYear.Show frmSalesYear.WindowState = 0 frmSalesYear.Height = Me.Height frmSalesYear.Width = Me.Width frmSalesYear.WindowState = 2 End Sub 

The above listing defines the following methods:

• mnuSalesByCustomer_Click(): Allows you to view the Sales by Customer report.

• mnuSalesByProduct_Click(): Allows you to view the Sales by Product report.

• mnuSalesByYear_Click(): Allows you to view the Sales by Year report.

Figure 4-1 shows the output of Listing 4-4:

Figure 4-1: The Choose Report Option Window

Creating the Sales by Year Window

The frmSalesYear.frm file defines the Sales by Year window. An end user can specify the year and click the Show Report button to view the sales report for that year along with the total sales achieved and the total number of orders placed in that year. Listing 4-5 shows how to create the frmSalesYear.frm file:

Listing 4-5: The frmSalesYear.frm File

 Option Explicit Private Sub cmdYearReport_Click() Dim objS As New SalesReportProvider.SalesYear Dim rs As New ADODB.Recordset Dim strYear As String strYear = Right(selDateFrom.Value, 4) Dim tempListViewItem As ListItem Label3.Caption = "Loading Report" Set rs = objS.getByOrderSalesByYear(strYear) If Not rs.RecordCount = 0 Then Dim totalSales As Double While Not rs.EOF Set tempListViewItem = ListViewYear.ListItems.Add() tempListViewItem.Text = rs.Fields("orderId").Value tempListViewItem.SubItems(1) = rs.Fields("contactName").Value tempListViewItem.SubItems(2) = rs.Fields("OrderDate").Value tempListViewItem.SubItems(3) = rs.Fields("sales").Value totalSales = totalSales + rs.Fields("sales").Value rs.MoveNext Wend txtTotSales.Text = totalSales Set rs = objS.getNoOfOrderByYear(strYear) If Not rs.EOF Then txtTotOrders.Text = rs.Fields("Total Orders").Value End If Else MsgBox "No records found", vbOKOnly, "No records found" End If Label3.Visible = False End Sub 

In the above listing, the cmdYearReport_Click() method executes when the end user clicks the Show Report button. This method calls the getByOrderSalesByYear() method that the SalesYear class of the SalesReportProvider component provides. This method then loads the ListviewYear listview control with the data contained in the recordset object.

Figure 4-2 shows the output of Listing 4-5:

Figure 4-2: The Sales by Year Window

Creating the Sales by Customer Window

The frmSalesCustomer.frm file defines the Sales by Customer window. The end user can specify date parameters and click the Show Report button to view the report for Sales by Customer. Listing 4-6 shows how to create the frmSalesCustomer.frm file:

Listing 4-6: The frmSalesCustomer.frm File

 Option Explicit Private Sub cmdCustomerReport_Click() Label3.Visible = True Dim objS As New SalesReportProvider.SalesCustomer Dim rs As New ADODB.Recordset Dim tempListViewItem As ListItem Label3.Caption = "Loading Report" Set rs = objS.getSalesByCustomer(selDateFrom.Value, selDateTo.Value) If Not rs.RecordCount = 0 Then While Not rs.EOF Set tempListViewItem = ListViewCust.ListItems.Add() tempListViewItem.Text = rs.Fields("customerId").Value tempListViewItem.SubItems(1) = rs.Fields("contactName").Value tempListViewItem.SubItems(2) = rs.Fields("sales").Value rs.MoveNext Wend Else MsgBox "No records found", vbOKOnly, "No records found" End If Label3.Visible = False End Sub  

In the above listing, the cmdCustomerReport_Click() method executes when the end user clicks the Show Report button. This method calls the getSalesByCustomer() method that the SalesCustomer class of the SalesReportProvider component provides. This method then loads the ListviewYear listview control with the data contained in the recordset object.

Figure 4-3 shows the output of Listing 4-6:

Figure 4-3: The Sales by Customer Window

Creating the Sales by Product Window

The frmSalesProduct.frm file defines the Sales by Product window. The end user can view the report for sales by product in this window by specifying date parameters and clicking the Show Report button. Listing 4-7 shows how to create the frmSalesProduct.frm file:

Listing 4-7: The frmSalesProduct.frm File

 Option Explicit Private Sub cmdProductReport_Click() Label3.Visible = True Dim objS As New SalesReportProvider.SalesProduct Dim rs As New ADODB.Recordset Dim tempListViewItem As ListItem Label3.Caption = "Loading Report" Set rs = objS.getSalesByProduct(selDateFrom.Value, selDateTo.Value) If Not rs.RecordCount = 0 Then While Not rs.EOF Set tempListViewItem = ListViewS.ListItems.Add() tempListViewItem.Text = rs.Fields("productid").Value tempListViewItem.SubItems(1) = rs.Fields("productName").Value tempListViewItem.SubItems(2) = rs.Fields("sales").Value rs.MoveNext Wend Else MsgBox "No records found", vbOKOnly, "No records found" End If Label3.Visible = False End Sub 

In the above listing, the cmdProductReport_Click() method executes when the end user clicks the Show Report button. This method calls the getSalesByProduct() method that the SalesProduct class of the SalesReportProvider component provides. This method then loads the ListviewYear listview control with the data contained in the recordset object.

Figure 4-4 shows the output of Listing 4-7:

Figure 4-4: The Sales by Product Window

Converting an ADO-Based VB Application to a VB.NET Application

When you convert a VB application to a VB.NET application using the Upgrade wizard, ADO does not convert to ADO.NET. You need to first delete ADO code and then write ADO.NET code manually. ADO code can run with the converted application but because ADO objects are COM-based, .NET Framework builds a wrapper around the objects to make them work in the .NET environment. This increases overheads because accessing COM components in the .NET environment involves calls to the COM environment. To overcome this performance overhead and utilize the features that ADO.NET provides, you can explicitly use ADO.NET objects when you convert the Sales Report VB application to VB.NET.

To upgrade the SalesReport project from VB to VB.NET using the Upgrade wizard:

1. Select Start ->Programs -> Microsoft Visual Studio .NET ->Microsoft Visual Studio .NET. The Visual Studio.NET IDE opens.

2. Select File->Open->Convert. The Convert window, which shows the available upgrade options, appears.

3. Select the VB .NET Upgrade wizard and click OK. The Welcome window of the Visual Basic Upgrade wizard opens.

4. Click Next. The Choose a Visual Basic 6.0 project file window of the Visual Basic Upgrade wizard opens.

5. Specify the path of the SalesReport.vbp file and click Next. The Choose a Project Type window of the Visual Basic Upgrade wizard opens.

6. Select the EXE type and click Next. The Specify a Location for Your New Project window of the Visual Basic Upgrade wizard opens.

7. Specify the path where you want to create the .NET version of the Sales Report application and click Next. The Ready to Upgrade window of the Visual Basic Upgrade wizard opens.

8. Click Next to begin the upgrade.

After the Upgrade wizard finishes executing, the SalesReport project code converts from VB code to the equivalent VB.NET code, although a few migration issues are involved.

Important

In the Sales Report application, you only convert the SalesReport VB project to VB.NET.

You do not convert the SalesReportProvider VB project that provides the SalesReportProvider.dll file through the Upgrade wizard. Instead, you create a SalesReportProvider Web service that provides the same functionality that the SalesReportProvider COM component provides. The difference is that the SalesReportProvider COM component provides sales information in the form of recordsets and the SalesReportProvider Web service provides the same information in the form of datasets.

The Sales Report .NET application also consists of two projects:

• SalesReportProvider: Creates the Sales by Product, Sales by Customer, and Sales by Year Web services that contain a number of methods to enable the client application to retrieve information about products, customers, and orders in the form of generated reports. The Web services connect to SQL Server to retrieve the requested information and return the information to the client application.

• SalesReport: Allows the end user to view the sales reports by making calls to Web services. This is the converted project from VB to VB.NET. The end user can view the Sales by Product, Sales by Customer, and Sales by Year reports. While one requested report is downloaded, the end user can view other reports.

Optimizing Code to Use ADO.NET

You replace the class files of the SalesReportProvider project with the Web services for the SalesReportProvider .NET project. Web services allow efficient communication in a distributed environment. The SalesReport .NET project uses different Web services to retrieve the Sales by Product, Sales by Customer, and Sales by Year reports. Different classes implement the Web services. You need to create the following Web service files for the SalesReportProvider project:

• SalesYear.asmx: Provides the SalesYear class that fetch data about annual sales from the database.

• SalesCustomer.asmx: Provides the SalesCustomer class that retrieves information about the orders that a customer places.

• SalesProduct.asmx: Provides the SalesProduct class that includes methods to fetch data about product sales from the database.

The Upgrade wizard converts VB code of the SalesReport project to VB.NET without optimizing the code. Forms with .frm extensions in the VB application convert to Windows forms with the .vb extension.

The Upgrade wizard creates the following files in the SalesReport .NET project:

• frmMain.vb: Provides the frmMain class that presents the end user with the interface to choose and view reports by product, customer, or year.

• frmSalesYear.vb: Provides the frmSalesYear class that exposes the same function as the frmSalesYear form.

• frmSalesCustomer.vb: Provides the frmSalesCustomer class that exposes the same function as the frmSalesCustomer form.

• frmSalesProduct.vb: Provides the frmSalesProduct class that exposes the same function as the frmSalesProduct form.

Optimizing the SalesReportProvider Project

To optimize the SalesReportProvider project in .NET, you need to create the Sales by Year, Sales by Customer, and Sales by Product Web services.

Creating the Sales by Year Web Service

The Sales Report application uses the Sales by Year Web service to retrieve information about annual sales from the server. The Sales by Year Web service uses the SalesYear class to define the Sales by Year Web service. The Web service connects to SQL Server and retrieves information about sales in a specified year from the database. Listing 4-8 shows how to create the SalesYear.asmx file:

Listing 4-8: The SalesYear.asmx File

 Imports System.Web.Services Imports System.Data.SqlClient Imports System.IO <System.Web.Services.WebService(Namespace := "http://tempuri.org/SalesReportProvider/SalesYear") > _ Public Class SalesYear Inherits System.Web.Services.WebService Dim strSQLConnection As String Dim objCom As New SqlCommand Dim objCon As New SqlConnection Dim objDa As New SqlDataAdapter Dim objDs As DataSet <System.Diagnostics.DebuggerStepThrough()>Private Sub InitializeComponent() components = New System.ComponentModel.Container Try Dim sr As New StreamReader(Server.MapPath(".") & "\Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=Northwind" End Try End Sub 'This method returns a dataset containing the sales by order in the specified year <WebMethod()> _ Public Function getByOrderSalesByYear(ByVal strYear As String) As DataSet Try 'Specifying the connection string for opening the connection objCon.ConnectionString = strSQLConnection objCon.Open() objCom.CommandText = "select orders.orderid, ContactName, orderdate, sum(unitprice*quantity) as sales from orders inner join [order details] on orders.orderid=[order details].orderid inner join customers on customers.customerid=orders.customerid and orderdate between '1/1/" & strYear & "' and '12/31/" & strYear & "' group by orders.orderid, orderdate, contactname order by orders.orderid" objCom.Connection = objCon objDa.SelectCommand = objCom 'Filling DataSet from Data Adapter objDa.Fill(objDs) objCon.Close() Catch ex As Exception Return Nothing End Try Return objDs End Function 'This method returns a DataSet containing the total number of orders placed in the specified year <WebMethod()> _ Public Function getNoOfOrderByYear(ByVal strYear As String) As DataSet Try 'Specifying the connection string for opening the connection objCon.ConnectionString = strSQLConnection objCon.Open() objCom.CommandText = "select count(OrderId) as [Total Orders] from orders where OrderDate between '1/1/" & strYear & "' and '12/31/" & strYear & "' " objCom.Connection = objCon objDa.SelectCommand = objCom objDa.Fill(objDs) Catch ex As Exception Return Nothing End Try Return objDs End Function End Class 

In the above listing, the SalesYear.asmx file defines the Sales by Year Web service and provides methods that fetch annual sales data from the database. The SalesYear class communicates with the database and fetches annual sales information for the year that the end user specifies. The SalesYear class contains the following methods:

• GetNoOfOrderByYear(): Returns a dataset that contains the total number of orders placed in the specified year.

• GetByOrderSalesByYear(): Returns a dataset that contains the sales by order information in the specified year.

Creating the Sales by Customer Web Service

The Sales Report application uses the Sales by Customer Web service to retrieve sales by customer information from the server. The Web service uses the SalesCustomer class to define the Sales by Customer Web service, which connects to SQL Server and retrieves information about customers from the database. Listing 4-9 shows how to create the SalesCustomer.asmx file:

Listing 4-9: The SalesCustomer.asmx File

 Imports System.Web.Services Imports System.Data.SqlClient Imports System.IO <System.Web.Services.WebService(Namespace := "http://tempuri.org/SalesReportProvider/SalesCustomer")> _ Public Class SalesCustomer Inherits System.Web.Services.WebService Dim strSQLConnection As String Dim objCom As New SqlCommand Dim objCon As New SqlConnection Dim objDa As New SqlDataAdapter Dim objDs As DataSet <System.Diagnostics.DebuggerStepThrough()>Private Sub InitializeComponent() components = New System.ComponentModel.Container Try Dim sr As New StreamReader(Server.MapPath(".") & "\Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=Northwind" End Try End Sub 'This method returns a dataset containing the information about customer sales during a particular period <WebMethod()> _ Public Function getSalesByCustomer(ByVal fromDate As Date, ByVal toDate As Date) As DataSet Try 'Specifying the connection string for opening the connection objCon.ConnectionString = strSQLConnection 'Opening the connection objCon.Open() 'Specifying the CommandText for the SqlCommand object objCom.CommandText = "select contactname, customers.customerId, sum([order details].UnitPrice*[order details].Quantity) as sales from customers inner join orders on customers.customerId=orders.CustomerId inner join [order details]on [Order Details].orderId=orders.orderId and OrderDate between '" & fromDate & "' and '" & toDate & "' group by contactname, customers.customerId" 'Specifying the connection for the SqlCommand object objCom.Connection = objCon objDa.SelectCommand = objCom 'Filling DataSet from Data Adapter objDa.Fill(objDs) 'Closing the connection objCon.Close() Catch ex As Exception Return Nothing End Try Return objDs End Function End Class 

In the above listing, the SalesCustomer.asmx file defines the Sales by Customer Web service and provides methods that fetch data about customer sales from the database. The SalesCustomer class contains the getSalesByCustomer() method that returns a dataset that contains information about customer sales during a period.

Creating the Sales by Product Web Service

The Sales Report application uses the Sales by Product Web service to retrieve sales by product data from the server. The Web service uses the SalesProduct class to define the Sales by Product Web service, which connects to SQL Server and retrieves information about products from the database. Listing 4-10 shows how to create the SalesProduct.asmx file:

Listing 4-10: The SalesProduct.asmx File

 Imports System.Web.Services Imports System.Data.SqlClient Imports System.IO <System.Web.Services.WebService(Namespace:="http://tempuri.org/SalesReportProvider/Service1")> _ Public Class SalesProduct Inherits System.Web.Services.WebService 'A string to hold connectionstring for establishing SQL connection Dim strSQLConnection As String <System.Diagnostics.DebuggerStepThrough()>Private Sub InitializeComponent() components = New System.ComponentModel.Container Try Dim sr As New StreamReader(Server.MapPath(".") & "\Settings.txt") strSQLConnection = sr.ReadLine sr.Close() Catch ex As Exception strSQLConnection = "SERVER=localhost;UID=sa;PWD=sa;Initial Catalog=Northwind" End Try End Sub 'This method returns a dataset containing the information about sales by products during a particular period <WebMethod()> _ Public Function getSalesByProduct(ByVal fromDate As String, ByVal toDate As String) As DataSet 'Defining a DataSet object Dim ds As New DataSet Try 'Specifying the connection string for opening the connection Dim objCon As New SqlConnection objCon.ConnectionString = strSQLConnection objCon.Open() 'Retrieving the information about sales by products during a particular period 'Defining a SqlCommand object Dim objCmd As New SqlCommand("select productName,products.productid,sum([order details].unitprice*[order details].quantity) as sales from products,[order details] inner join orders on [order details].orderid=orders.orderid where products.productid=[order details].productid and orderdate between '" & fromDate & "' and '" &toDate & "' group by productname,products.productid order by products.productid", objCon) 'Defining a SqlDataAdapter object Dim adapter As New SqlDataAdapter(objCmd) 'Filling DataSet from Data Adapter adapter.Fill(ds) 'Closing the connection objCon.Close() Catch ex As Exception Return Nothing End Try Return ds End Function End Class  

In the above listing, the frmSalesProduct.asmx file contains the SalesProduct class that defines the Sales by Product Web service and provides methods that fetch product sales data from the database. This class retrieves the address of the database server from the settings.txt text file when the Web service is initialized. This class communicates with the database and fetches product sales information in a dataset. This Web service sends the information to the client application. The SalesProduct class contains the getSalesByProduct() method that returns a dataset containing information about sales by products during a period.

Optimizing the SalesReport Project

To optimize the SalesReport project in .NET, you need to optimize the Sales by Year, Sales by Customer, and Sales by Product windows. The Upgrade wizard modifies the Choose Report Option window.

Modifying the Choose Report Option Window

The frmMain class defines the main application window. The end user can select options to view reports by product, customer, or year. Listing 4-11 shows the modified frmMain.vb file:

Listing 4-11: The frmMain.vb File

 Option Strict Off Option Explicit On Friend Class frmMain Inherits System.Windows.Forms.Form Public Sub mnuSalesByCustomer_Popup(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByCustomer.Popup mnuSalesByCustomer_Click(eventSender, eventArgs) End Sub Public Sub mnuSalesByCustomer_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByCustomer.Click 'Create an object of type frmSalesCustomer Dim objSalesCustomer As New frmSalesCustomer objSalesCustomer.Show() objSalesCustomer.Height = Me.Height objSalesCustomer.Width = Me.Width objSalesCustomer.WindowState =  FormWindowState.Maximized End Sub Public Sub mnuSalesByProduct_Popup(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByProduct.Popup mnuSalesByProduct_Click(eventSender, eventArgs) End Sub Public Sub mnuSalesByProduct_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByProduct.Click 'Create an object of type frmSalesProduct Dim objSalesProduct As New frmSalesProduct objSalesProduct.Show() objSalesProduct.Height = Me.Height objSalesProduct.Width = Me.Width objSalesProduct.WindowState = FormWindowState.Maximized End Sub Public Sub mnuSalesByYear_Popup(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByYear.Popup mnuSalesByYear_Click(eventSender, eventArgs) End Sub Public Sub mnuSalesByYear_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles mnuSalesByYear.Click Dim objSalesYear As New frmSalesYear objSalesYear.Show() objSalesYear.Height = Me.Height objSalesYear.Width = Me.Width objSalesYear.WindowState = FormWindowState.Maximized   End Sub End Class   

In the above listing, the frmMain.vb file defines the frmMain window, which enables the end user to view different reports and switch between the windows that display the reports. The frmMain class shows the Sales by Product, Sales by Customer, and Sales by Year windows to view product, customer, and annual sales reports. The frmMain class contains the mnuSalesByCustomer_Click(), mnuSalesByProduct_Click, and mnuSalesByYear_Click methods to view the reports.

Optimizing the Sales by Year Window

The frmSalesYear class defines the Sales by Year window. The end user can specify the year and click the Show Report button to view a report for that year. The report also displays the total sales and the total number of orders placed in that year. Listing 4-12 shows how to optimize the frmSalesYear.vb file:

Listing 4-12: The frmSalesYear.vb File

 Option Strict Off Option Explicit On Imports VB = Microsoft.VisualBasic Friend Class frmSalesYear Inherits System.Windows.Forms.Form Private Sub cmdYearReport_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdYearReport.Click 'Create a DataSet object Dim ds As New DataSet Dim strYear As String strYear = VB.Right(selDateFrom.Value, 4) Dim tempListViewItem As mscomctl.ListItem Label3.Text = "Loading Report" 'Create an instance of SalesYear Web service Dim objSalesYear As New SalesYearService.SalesYear Dim totalSales As Double Try 'Calling the getByOrderSalesByYear method of   SalesYear Web service ds =  objSalesYear.getByOrderSalesByYear(strYear) If Not ds.Tables.Count = 0 And Not ds.Tables(0).Rows.Count = 0 Then 'An integer variable to iterate through the DataSet Dim rowCount As Integer For rowCount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewYear.ListItems.Add() tempListViewItem.Text = ds.Tables(0).Rows(rowCount).Item(0) tempListViewItem.SubItems(1) = ds.Tables(0).Rows(rowCount).Item(1) tempListViewItem.SubItems(2) = ds.Tables(0).Rows(rowCount).Item(2) tempListViewItem.SubItems(3) = ds.Tables(0).Rows(rowCount).Item(3) totalSales = totalSales + ds.Tables(0).Rows(rowCount).Item(3) Next 'Calling the getNoOfOrderByYear method of SalesYear Web service ds = New DataSet objSalesYear.getNoOfOrderByYear(strYear) If Not ds.Tables.Count = 0 And Not ds.Tables(0).Rows.Count = 0 Then txtTotOrders.Text = ds.Tables(0).Rows(0).Item(0) End If Else MsgBox("No records found", MsgBoxStyle.OKOnly, "No records found") Exit Sub End If Catch ex As Exception MsgBox("Error in retrieving information", MsgBoxStyle.OKOnly, "Retrieval Error") End Try Label3.Visible = False End Sub End Class  

In the above listing, the frmSalesYear.vb file defines the Sales by Year report window. The frmSalesYear class provides functions to request the Sales by Year report by invoking the getByOrderSalesByYear method of the proxy class of the frmSalesYear class. The end user specifies the year parameter and clicks the Show Report button. The click event of the cmdYearReport button calls the getNoOfOrderByYear method of the SalesYear Web service and fetches information in ListViewYear.

Optimizing the Sales by Customer Window

The frmSalesCustomer class defines the Sales by Customer window. The end user can specify date parameters and click the Show Report button to view the Sales by Customer report. Listing 4-13 shows how to optimize the frmCustomerYear.vb file:

Listing 4-13: The frmCustomerYear.vb File

 Option Strict Off Option Explicit On  Friend Class frmSalesCustomer Inherits System.Windows.Forms.Form Private Sub cmdCustomerReport_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdCustomerReport.Click Label3.Visible = True 'Create a DataSet object Dim ds As New DataSet Dim tempListViewItem As mscomctl.ListItem Label3.Text = "Loading Report" 'Create an instance of SalesCustomer Web service Dim objSalesCustomer As New SalesCustomerService.SalesCustomer Try 'Calling the getSalesByCustomer method of SalesCustomer Web service ds = objSalesCustomer.getSalesByCustomer(selDateFrom.Value, selDateTo.Value) If Not ds.Tables.Count = 0 And Not ds.Tables(0).Rows.Count = 0 Then 'An integer variable to iterate through the DataSet Dim rowCount As Integer For rowCount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewCust.ListItems.Add() tempListViewItem.Text = ds.Tables(0).Rows(rowCount).Item(0) tempListViewItem.SubItems(1) = ds.Tables(0).Rows(rowCount).Item(1) tempListViewItem.SubItems(2) = ds.Tables(0).Rows(rowCount).Item(2) Next Else MsgBox("No records found", MsgBoxStyle.OKOnly, "No records found") Exit Sub End If Catch ex As Exception MsgBox("Error in retrieving information", MsgBoxStyle.OKOnly, "Retrieval Error") End Try Label3.Visible = False End Sub End Class  

In the above listing, the frmCustomerYear.vb file defines the Sales by Customer report window. The frmSalesCustomer class provides functions to request the Sales by Customer report by calling the getSalesByCustomer() method of the SalesCustomer Web service.

Optimizing the Sales by Product Window

The frmSalesProduct class defines the Sales by Product window. The end user can view the Sales by Product report in this window by specifying date parameters and clicking the Show Report button. Listing 4-14 shows how to optimize the frmSalesProduct.vb file:

Listing 4-14: The frmSalesProduct.vb File

 Option Strict Off Option Explicit On Friend Class frmSalesProduct Inherits System.Windows.Forms.Form Private Sub cmdProductReport_Click(ByVal eventSender As System.Object, ByVal eventArgs As System.EventArgs) Handles cmdProductReport.Click Label3.Visible = True Dim ds As New DataSet Dim tempListViewItem As mscomctl.ListItem Label3.Text = "Loading Report" 'Create an instance of SalesProduct Web service Dim objSalesProduct As New SalesProductService.SalesProduct Try 'Calling the getSalesByProduct method of SalesProduct Web service ds = objSalesProduct.getSalesByProduct(selDateFrom.Value, selDateTo.Value) If Not ds.Tables.Count = 0 And Not ds.Tables(0).Rows.Count = 0 Then 'An integer variable to iterate through the DataSet Dim rowCount As Integer For rowCount = 0 To ds.Tables(0).Rows.Count - 1 tempListViewItem = ListViewS.ListItems.Add() tempListViewItem.Text = ds.Tables(0).Rows(rowCount).Item(0) tempListViewItem.SubItems(1) = ds.Tables(0).Rows(rowCount).Item(1) tempListViewItem.SubItems(2) = ds.Tables(0).Rows(rowCount).Item(2) Next Else MsgBox("No records found", MsgBoxStyle.OKOnly, "No records found") Exit Sub End If Catch ex As Exception MsgBox("Error in retrieving information", MsgBoxStyle.OKOnly, "Retrieval Error") End Try Label3.Visible = False End Sub End Class  

In the above listing, the frmSalesProduct.vb file defines the Sales by Product report window. The frmSalesProduct class provides functions to request the Sales By Product report by calling the getSalesByProduct() method of the SalesProduct Web service.

Note

To learn more about database connectivity using ADO.NET, see the Implementing database connectivity using Visual Basic .NET ReferencePoint.