The DataView provides different views of the data stored in aDataTable. DataView can be used to sort, filter, and search in a DataTable , additionally we can add new rows and modify the content in a DataTable. DataViews can be created and configured both design time and run time . Changes made to a DataView affect the underlying DataTable automatically, and changes made to the underlying DataTable automatically affect any DataView objects that are viewing the DataTable.

We can create DataView in two different ways. We can use theDataView constructor, or you can create a reference to the DefaultView property of the DataTable. The DataView constructor can be empty, or it can take either a DataTable as a single argument, or a DataTable along with filter criteria, sort criteria, and a row state filter.

dataView = dataSet.Tables(0).DefaultView

The following source code shows how to create a DataView in VB.NET. Create a new VB.NET project and drag a DataGridView and a Button on default Form Form1 , and copy and paste the following Source Codeon button click event.

Imports System.Data.SqlClient Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click         Dim connetionString As String         Dim connection As SqlConnection         Dim command As SqlCommand         Dim adapter As New SqlDataAdapter         Dim ds As New DataSet         Dim dv As DataView         Dim sql As String   connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"         sql = "Select  * from product"         connection = New SqlConnection(connetionString)         Try             connection.Open()             command = New SqlCommand(sql, connection)             adapter.SelectCommand = command             adapter.Fill(ds, "Create DataView")             adapter.Dispose()             command.Dispose()             connection.Close()              dv = ds.Tables(0).DefaultView             DataGridView1.DataSource = dv          Catch ex As Exception             MsgBox(ex.ToString)         End Try     End Sub End Class  

ADO stands for ActiveX Data Objects and it relies on COM whereas ADO.NET relies on managed providers defined by the .NET CLR (Common Language Runtime). ADO.NET provides consistent access todata sources such as SQL Server, as well as data sources exposed through OLE DB and XML. While there are similarities between ADO and ADO.NET, the way they operate and their foundations are quite different. The following are some Advantages of ADO.Net over ADO in basic level.

A major difference in creating connections with ADO and ADO.NET is that ADO fits all connections to all types of data sources into a single Connection object. ADO.NET can have separate Objects that represent connections to different data sources. In ADO.NET you can create multiple data provider namespaces to connect specifically with a particular data source, making access faster and more efficient and allowing each namespace to exploit the features of its targeted data provider.

  Dim connection As SqlConnection 
  connection = New SqlConnection("connetionString")

  Dim connection As OleDbConnection 
  connection = New OleDbConnection("connetionString")

ADO allows you to create client side cursors only whereas ADO.NET gives you the choice of either using client side or server side cursors.

ADO.NET introduces a new way of getting a single value from a query's results when you expect only one row and one column to return. The ADO.NET command object has an ExecuteScalar method which returns the first row and column's value from its associated query.

ADO.Net dataset represents in memory representation of a database. ADO recordsets is merely a set of rows retrieved from a data source.

ADO recordsets can hold data from one data source at a time. ADO.Net datasets can hold data from various sources and integrate the data and write it back to one or several data sources.

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

In the case of Data Communication , ADO objects communicate in binary mode while ADO.NET uses XML for passing the data.

You can find more information on ADO to ADO.NET from the following link :

http://msdn.microsoft.com/en-us/magazine/cc163954.aspx

The .Net Framework includes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider ,OLEDB Data Provider and ODBC Data provider. You can see from the following links how these Data Providers making connection to the specified data Sources.

SQL Server Connection

OLEDB Connection

ODBC Connection

The four Objects from the .Net Framework provide the functionality of Data Providers in ADO.NET. They are Connection Object, Command Object , DataReader Object and DataAdapter Object. The following link shows in details about these Objects.

Connection

Command

DataReader

DataAdapter

Connection String is a normal String representation which contains Database connection information to establish the connection between Datbase and the Application. The Connection String includes parameters such as the name of the driver, Server name and Database name , as well as security information such as user name and password. Data providers use a connection string containing a collection of parameters to establish the connection with the database.

The .NET Framework provides mainly three data providers: MicrosoftSQL Server, OLEDB and ODBC. Here you can see how to make connection string to these ADO.NET Data Providers.

Microsoft SQL Server Connection String

connetionString ="Data Source = ServerName; Initial Catalog = Databasename; User ID = UserName; Password=Password"

OLEDB Data Provider Connection String

connetionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = yourdatabasename.mdb;"

ODBC Connection String

connetionString = "Driver = {Microsoft Access Driver (*.mdb)}; DBQ = yourdatabasename.mdb;"

Note : You have to provide the necessary informations to the Connection String attributes.

In the following section you can see how to these ADO.NET Data Providers establish connection to the Databse in detail.

SQL Server Connection

OLEDB Connection

ODBC Connection

In the previous section Subreport in Crystal Report is described how to insert a subreport in Crystal Reports . In that case the subreport is embedded within the main Crystal Reports. Here we are creating a subreport , and the subreport has only a link in the main Report , on-demand subreports . That is when the user click the link , then only the subreport display.

Here we are using our previous example Subreport in Crystal Report and make a link in the min Crystal Reports for on-demand subreport.

Select the subreport object in the Crystal Reports and right click , then select Format Object .

Then you will get Format Editor . Select Subreport tab from Format Editor , you can find there a check box On-demand Subreport . You have to select that check box , then the subreport become as a link in your main Crystal Reports. If you want to change the title , you can change it in subreport name textbox. Finally click OK button.

Now the designing part is over and you are ready to generate subreport on-demand. Next step is to select the default form(Form1.vb) and add a Button and Crystal Report Viewer to the Form.

Put the following vb.net source code in your form and run the program .

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 crp As New CrystalReport1         CrystalReportViewer1.ReportSource = crp         CrystalReportViewer1.Refresh()     End Sub End Class

Start your first VB.NET Crystal Reports .

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 .

Open Visual Studio .NET and select a new Visual Basic .NET Project.

Create a new Crystal Reports for Product table from the above database crystalDB. The Product Table has three fields (Product_id,Product_name,Product_price) and we are showing the whole table data in the Crystal Reports.

From main menu in Visual Studio select PROJECT-->Add New Item . Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal Reports gallery.

Accept the default settings and click OK.

Next step is to select the appropriate connection to your database. Here we are going to select OLEDB connection for SQL Server

Select OLE DB (ADO) from Create New Connection .

Select Microsoft OLE DB Provider for SQL Server .

Next screen is the SQL Server authentication screen . Select your Sql Server name, enter userid , password and select your Database Name . Click next , Then the screen shows OLE DB Property values , leave it as it is , and click finish.

Then you will get your Server name under OLEDB Connection from there select database name (Crystaldb) and click the tables , then you can see all your tables from your database.

From the tables list select Product table to the right side list .

Click Next Button

Select all fields from Product table to the right side list .

Click Finish Button. Then you can see the Crystal Reports designer window . You can arrange the design according your requirements. Your screen look like the following picture.

Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control .

Select the default form (Form1.vb) you created in VB.NET and drag a button andCrystalReportViewer control to your form.

Select Form's source code view and put the code on top

Imports CrystalDecisions.CrystalReports.Engine

Put the following source code in the button click event

Imports CrystalDecisions.CrystalReports.Engine 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 Reports is in your project location, there you can seeCrystalReport1.rpt . So give the full path name of report here.

After you run the source code you will get the report like this.



Hope this tutorial help you to create your first Crystal Reports.

In the following section you can see , how to create a sample Database and Tables and data for running Crystal Reports Tutorials . All examples in the VB.NETCrystal Reports Tutorials are based on the following database . First we have to create a database . Give the database name as "crystaldb"

Create a DataBase "crystaldb"

In the crystaldb database , create three tables

OrderMaster , OrderDetails , Product .

OrderMaster

OrderMaster_id

OrderMaster_createduser

OrderDetails

OrderDetails_id

OrderDetails_qty

Product

Product_id

Product_price

The following picture shows the relations of each table :

SQL command for creation tables are follows :

CREATE TABLE [dbo].[OrderMaster] (

) ON [PRIMARY]

Enter data to the tables :

Order Master Table Data

Order Details Table Data

Product Table Data

Crystal Reports for Visual Studio ships with your deployment projects that enable you to deploy Crystal Reports components and assemblies on the target machine. We can use different approaches to install Crystal Reports runtime files on the target machine .

If you are using Visual Studio then during your setup and deployment you can add the CRRedist2005_x86.msi file to your setup file and distribute it as a single setup file . The installer can execute the setup file automatically with your .Net Project in a single click. You can find theCRRedist2005_x86.msi file in your system's C:\Program Files\Microsoft Visual Studio8\SDK\v2.0\BootStrapper\Packages\CrystalReports . Also you can distribute the CRRedist2005_x86.msi separately and install it on the target machine.

The other way to install Crystal Reports on target machine is to create a setup file using Merge Modules and distribute it with your application or as a separate setup file. Click the following link to see how to make a setup file with Merge Modules.

Crystal Reports installer using Merge Modules.

In this section you can see , creating a Crystal Reports from XML file instead of database . This is very similer to creating Crystal Reports from database , the only difference is to sslect the data source as your XML file.

Here we are creating an XML file name is Product is XML file the same structure of the Product table in the sample Database Structure.

Download Product.XML

The basics of Crystal Reports creation provided in previous tutorials , if you dont have much knowledge in Crystal Reports , take a look at the tutorial step by step Crystal Report before start this section.

The change happen only from previous report , when you select Data for Crsyatl Report , you have to select Create New Connection - Database Filesand select the XML file you want to generate Crystal Reports (In this case you select the Product.xml ).

Select all the fields from Product and click finish button

Now the designer part is over . Next step is to select the default form(Form1.vb) and add a Button and Crystal Reports Viewer to the Form.

Put the following vb.net source code in your form and run the program .

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

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. 

Usually Crystal Reports we are using to fetch data from database and show it as a report. Here we are going to generate a Crystal Reports without using a datbase . For generating a Crystal Reports without database , here we are using a Strongly Typed Dataset and a Data Table.

The basics of Crystal Reports creation you can find in the previous section of this tutorial , before we start this tutorial you can take a look at the step by step Crystal Report.

Generating a Strongly Typed DataSet

Create a new VB.NET Project and accept the default settings. Create a new Dataset from Project - Add New Item Dialogue Box.

Accept the default name DataSet1.xsd .

Create a data table for DataSet1.xsd .

Select DataSet1.xsd from Solution Explorer and right click . Select datatable from the menu. Then you will get a datatable in the Datast . Right click the datatable and select Add-Column .

Here we are making a two column Crystal Reports , so we need two column in the data table . Add and ID column and Name column in the Data Table.

Now the dataset part is over . Next step is to create a Crystal Reports from this Dataset . Before going to make Crystal Reports design please take a look at step by step Crystal Report for easy creation of Crystal Reports.

Select a new Crystal Reports from Add New Item menu and accept the default settings. The next screen is to select appropriate data source . There you can find the Datatable1 from Project data - ADO.NET Datasets , and select Datatable1 to the right side.

Click Next button and select ID and Name from the datatable1 to right side and click finish.

Now the Crystal Reports designer part is over . Next part is to create data for the Crystal Reports . For that we have to create a Data Table through programmatically and add data to dataset1.

Select the default form (Form1.vb) you created in VB.NET and drag one button and CrystalReportViewer control to your form.

Put the following source code in the button click events

Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, _             ByVal e As System.EventArgs) Handles Button1.Click         Dim ds As New DataSet1         Dim t As DataTable = ds.Tables.Add("Items")         t.Columns.Add("id", Type.GetType("System.Int32"))         t.Columns.Add("Item", Type.GetType("System.String"))          Dim r As DataRow         Dim i As Integer         For i = 0 To 9             r = t.NewRow()             r("id") = i             r("Item") = "Item" & i             t.Rows.Add(r)         Next          Dim objRpt As New CrystalReport1         objRpt.SetDataSource(ds.Tables(1))         CrystalReportViewer1.ReportSource = objRpt         CrystalReportViewer1.Refresh()     End Sub End Class

We can create Crystal Reports in VB.NET using SQL Query String . Here we create a Strongly Typed dataset for Crystal Reports design and create a connection object and execute the SQL Query String .

All Crystal Report 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 .

The basics of Crystal Reports creation you can find in the previous section of this tutorial , before we start this tutorial you can take a look at the step by step Crystal Report.

Generating a Strongly Typed DataSet

In the previous section you can see a detailed tutorial about to create a strongly typed datset and its Crystal Reports design . After create the dataset and Crystal Reports design you should create a connection object and fetch the data from database.

Select the default form (Form1.vb) you created in VB.NET and drag one button and CrystalReportViewer control to your form.

Put the following source code in the button click events

Imports System.Data.SqlClient Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Imports System.Data Public Class Form1     Private Sub Button1_Click(ByVal sender As System.Object, _         ByVal e As System.EventArgs) Handles Button1.Click    Dim ds As New DataSet1         Dim cnn As SqlConnection         Dim connectionString As String         Dim sql As String           connectionString = "data source=servername; _  initial catalog=crystaldb;user id=username;password=password;"         cnn = New SqlConnection(connectionString)         cnn.Open()  sql = "SELECT Product_id,Product_name,Product_price FROM Product"         Dim dscmd As New SqlDataAdapter(sql, cnn)         Dim ds As New DataSet1         dscmd.Fill(ds, "Product")         MsgBox(ds.Tables(1).Rows.Count)         cnn.Close()          Dim objRpt As New CrystalReport1         objRpt.SetDataSource(ds.Tables(1))         CrystalReportViewer1.ReportSource = objRpt         CrystalReportViewer1.Refresh()     End Sub End Class 

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 .

Here we are going to do is to generate Crystal Reports from multiple tables. Here we have three table (ordermaster , orderdetails amd product ) and we are generating report from these three tables by connecting each tables with their related fields.

Open Visual Studio .NET and select a new Visual Basic .NET Project.

From main menu in Visual Studio select PROJECT-->Add New Item . Then Add New Item dialogue will appear and select Crystal Reports from the dialogue box.

Select Report type from Crystal Reports gallery.

Accept the default settings and click OK.

Next step is to select the appropriate connection to your database. Here we are going to select OLEDB connection for SQL Server

Select OLE DB (ADO) from Create New Connection .

Select Microsoft OLE DB Provider for SQL Server .

Next screen is the SQL Server authentication screen . Select your Sql Server name, enter userid , password and select your Database Name . Click next , Then the screen shows OLE DB Property values , leave it as it is , and click finish.

Then you will get your Server name under OLEDB Connection from there selectdatabase name (Crystaldb) and click the tables , then you can see all your tables from your database.

Select all table from the table list to right side list box, because we are creating report from three tables ( OrderMaster, OrderDetails, Product) .

The next step is to make relation between these selected tables. Here we are connecting the related fields from each table. For that we arrange the tables in visible area in the list (this is not necessary ) and select the field we are going to make relation and drag to the related field of the other table. After made the relation the screen is look like the following picture .

Next step is to select the fields from the tables . Here we are selecting only Customername , orderdate from ordermastertable , Productname from product table and quantity from order details.

Click the Finish button because now we are not using other functionalities of this wizard. After that you will get the Crystal Reports designer window . You can arrange the fields in the designer window according to your requirement to view the report . For rearranging you can drag the field object in the screen . For editing right click the field object and select Edit Text Object. The following picture shows the sample of designer window after rearrange the field.

Now the designing part is over and the next step is to call the created Crystal Reports in VB.NET through Crystal Reports Viewer control .

Select the default form (Form1.vb) you created in VB.NET and drag a button andCrystalReportViewer control to your form.

Select Form's source code view and put the code on top

Imports CrystalDecisions.CrystalReports.Engine

Put the following source code in the button click event

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