The ADO.NET Framework supports two models of Data AccessArchitecture, Connection Oriented Data Access Architecture and Disconnected Data Access Architecture.

In Connection Oriented Data Access Architecture the application makes a connection to the Data Source and then interact with it through SQL requests using the same connection. In these cases the application stays connected to the database system even when it is not using any Database Operations.

ADO.Net solves this problem by introduces a new component called Dataset. The DataSet is the central component in the ADO.NET Disconnected Data Access Architecture. A DataSet is an in-memory data store that can hold multiple tables at the same time. DataSets only hold data and do not interact with a Data Source. One of the key characteristics of the DataSet is that it has no knowledge of the underlying Data Source that might have been used to populate it.

  Dim ds As New DataSet

In Connection Oriented Data Access, when you read data from a database by using a DataReader object, an open connection must be maintained between your application and the Data Source. Unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when you populate it. It is the DataAdapter that manages connections between Data Source and Dataset by fill the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. The DataAdapter acts as a bridge between the Connected and Disconnected Objects.

  Dim adapter As New SqlDataAdapter("sql", "connection")   Dim ds As New DataSet 
  adapter.Fill(ds, "Src Table")

By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance.

ADO.NET is a data access technology from Microsoft .Net Framework , which provides communication between relational and non-relational systems through a common set of components . ADO.NET consist of a set of Objects that expose data access services to the .NET environment. ADO.NET is built for disconnected architecture , so it enables truly disconnected Data Access and Data Manipulation through its Dataset Object, which is completely independent from the Data Source.

The two key components of ADO.NET are Data Providers and DataSet . The .Net Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider, OLEDBData Provider and ODBC Data Provider. SQL Server uses theSqlConnection object , OLEDB uses the OleDbConnection Object and ODBC uses OdbcConnection Object respectively.

The four Objects from the .Net Framework provide the functionality of Data Providers in the ADO.NET. They are Connection Object,Command Object , DataReader Object and DataAdapter Object. The Connection Object provides physical connection to the Data Source. The Command Object uses to perform SQL statement or stored procedure to be executed at the Data Source. The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. Finally the DataAdapter Object , which populate a Dataset Object with results from a Data Source .

DataSet provides a disconnected representation of result sets from the Data Source, and it is completely independent from the Data Source. DataSet provides much greater flexibility when dealing with related Result Sets. DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. The DataTable contains a collection of DataRow and DataCoulumn Object which contains Data. The DataAdapter Object provides a bridge between the DataSet and the Data Source.

In the following section you can see each of the ADO.NET components in details with vb.net source code.

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure .

In this tutorial we are taking the sum of field value of Total . This is the continuation of the previous tutorial Crystal Report Formula Field . So before we start this tutorial , take a look at the previous tutorial Crystal Report Formula Field.

Here we are taking the grand total of the Total field . The Total field is a Formula field is the result of qty X price .

In the Crystal Reports designer view right click on the Report Footer , just below the Total field and select Insert -> Summary .

Then you will get a screen , select the Total from the combo box and Sum from next Combo Box , and summary location Grand Total (Report Footer) . Click Ok button

Now you can see @Total is just below the Total field in the report Footer.

Now the designing part is over . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form.

Select Form's source code view and import the following :

Imports CrystalDecisions.CrystalReports.Engine

Put the following source code in the button click event

Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared 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

NOTES:

cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

The Crystal Report is in your project location, there you can seeCrystalReport1.rpt . So give the full path name of report here.

When you run this program you will get the following screen.

All Crystal Reports programming samples in this tutorials is based on the following database (crystaldb) . Please take a look at the database structure before you start this tutorial - Click here to see Database Structure .

In this tutorial we are adding a Formula Field in existing Crystal Reports .

SITUATIONS :

If you have a Crystal Reports with Qty and Price , you need an additional field in your Crystal Reports for the Total of QTY X PRICE . In this situation you have to use the Formula Field in Crystal Reports.

In this tutorial we are showing the all orders with qty and price and the total of each row , that means each in each row we are showing the total of qty and price. Before starting this tutorial.

Create a new Crystal Reports with fields CustomerName , Order Date , Product Name and Product Price . If you do not know how to create this report , just look the previous tutorial Crystal Report from multiple tables . In that report selecting only four fields , here we need one more field Prodcut->Price .

After you create the Crystal Reports you screen is look like the following picture :

Next is to create the a Formula Field for showing the total of Qty and Price .

Right Click Formula Field in the Field Explorer and click New. Then you will get an Input Message Box , type Total in textbox and click Use Editor

Now you can see Formula Editor screen . Now you can enter which formula you want . Here we want the result of Qty X Price . For that we select OrderDetails.Qty , the multiplication operator and Product.Price . Double click each field for selection.

Now you can see Total Under the Formula Field . Drag the field in to theCrystal Reports where ever you want .

Now the designing part is over . Select the default form (Form1.vb) you created in VB.NET and drag a button and CrystalReportViewer control to your form.

Select Form's source code view and import the following :

Imports CrystalDecisions.CrystalReports.Engine

Put the following source code in the button click event

Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared 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

NOTES:

cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

The Crystal Report is in your project location, there you can seeCrystalReport1.rpt . So give the full path name of report here.

When you run this program you will get the following screen.