لطفا منتظر باشید ...
Working with ADO.NET
ADO.NET is a new data interface standard introduced with the .NET Framework that provides access to many different types of data, including SQL Server, OLE-DB-compliant data sources, and XML. Using ADO.NET, all of the data access and manipulation is handled by a number of individual components that work together to select from, insert into, and update data sources.When working with ADO.NET, the resulting data can be stored in a special structure called a dataset, which can be used by the application itself and as the data source for a Crystal Report.
A dataset is a disconnected, in-memory data collection. Applications use ADO.NET to populate the DataSet object, which you can then write to, search, copy from, and so on. To run reports that show this list of information, we don't actually need to go back to the database again as the information is held within the dataset.We can design the report based on the structure of this dataset, and when the report is run, we point the report to this data, which is used to preview and print the report. This provides a definite performance advantage and represents best practice for writing reports that use application data. In the following sections, we are going to briefly cover how to create an ADO.NET dataset and then use it as the data source for a report.
An ADO.NET Dataset
To create an ADO.NET dataset, we need to use the ADO.NET Dataset Designer. To invoke the Designer, create a new Windows Application called Reporting_App. Select Project | Add New Item and select DataSet from the list of available templates.
Set the name of the dataset to CustomerOrders.xsd, and then click on open to see the Dataset Designer with a blank view. You should notice that in the Solution Explorer the CustomerOrders dataset that you have just created has appeared.
To start building your dataset, you will need to specify where the underlying data comes from. On the left-hand side of the Visual Studio .NET IDE, locate the Server Explorer and find the section marked Data Connections. This toolbar contains all of the data sources that can be accessed, including the Northwind database that we will use in this example (accessing this database is discussed earlier in Chapter 3).If your data source is not listed, right-click on Data Connections and select Add Connection from the right-click menu. To set up a new Data Connection, you will need to specify a provider and the appropriate server name and credentials for your data source.The Northwind database that we want to use should be available. The tables and fields within the data source should appear below its node.
To build a dataset, you can simply drag-and-drop the required tables from your database connection onto the dataset design surface and specify the relationships and keys between the data sources. In our example, we are going to use the Customers and Orders tables from the Northwind database, so add these tables.The relationship between the two tables is on the CustomerID, so to create this link you can click and drag from the CustomerID in the Orders table to the CustomerID in the Customers table. This action opens the Edit Relation dialog (also opened by right-clicking on an existing link, and selecting Edit Relation), in which you select the parent and child elements from the two drop-down lists. Our parent element should be set to Customers, our child element should be set to Orders, the key should be CustomerOrdersKey1, and both of the key fields should be set to CustomerID.
Select OK to see the customers and orders relationship. Make sure that CustomerID and OrderID are the only key fields in each of the tables, because we want the tables in the dataset to match the tables in the Northwind database.
You can view the XML that describes this relationship and data by clicking on the XML tab at the bottom left of the dataset design window. You can check that your relationship has been entered correctly, by comparing the three fields that can be found at the bottom of your XML schema with these three fields here - they should match:
</xs:element>
</xs:choice>
</xs:complexType>
<xs:unique name="CustomerOrdersKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Customers" />
<xs:field xpath="mstns:CustomerID" />
</xs:unique>
<xs:unique name="CustomerOrdersKey2" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:OrderID" />
</xs:unique>
<xs:keyref name="CustomersOrders" refer="CustomerOrdersKey1">
<xs:selector xpath=".//mstns:Orders" />
<xs:field xpath="mstns:CustomerID" />
</xs:keyref>
</xs:element>
</xs:schema>
You can manually edit the XML, although it is much easier to stay within the bounds of the development environment, and edit the dataset using the properties window. Information on creating complex datasets is available in Fast Track ADO.NET (1-86100-760-4) from Wrox Press.Now you have finished creating your dataset, select Build Solution from the Build menu to build your dataset and generate the database object for this dataset. Create a copy of this project so we can use this dataset in a report that we will build later in this chapter. We will now go on to display this dataset in a DataGrid on a Windows form.
Viewing the Contents of a Dataset
Open Form1.vb in design mode, and from the Windows Forms section of the Toolbox, drag a DataGrid to the form. Just for presentation, add a label to the top of the form saying The CustomerOrders Dataset.
Next add an OleDbDataAdapter to the form by dragging and dropping one from the Data section of the Toolbox. This action opens the Data Adapter Configuration Wizard that will help you step through the process of setting up your data adapter. The first thing you will have to do is select your data connection. Make sure this is the same as the data source you built your dataset from, in this case, the Northwind database.
If it is not the default enter the details for your Northwind Database. Click on New Connection to open the Data Link Properties window. Enter the location of your SQL Server and the security settings, and select Northwind from the list of available databases. Test the connection, and if all is good, click on OK to continue setting up the OleDbDataAdapter.
The next dialog allows you to choose a query type, to decide how the data adapter will query the database. We will use SQL statements for this purpose, so select Use SQL Statements. Click on Next to get to the Generate the SQL Statements dialog, and then click on Query Builder to generate your statement. This opens the Query Builder, and in front of it, the Add Table dialog:
Add the Customers and Orders tables that make up our dataset, and click on Close, and the SQL query will start to be built. Then simply check the (All Columns) boxes in both of the tables to complete our query:
Click on OK and then Finish, to complete configuration of the data adapter.
Now the data source has been defined, double-click on the form (not in DataGrid1) to open up the code designer for Form1.vb. This action creates a procedure called Form1_load, which fires when the form is loaded. Insert the following code into this procedure:
Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
Dim CustomerOrders As New DataSet()
OleDbDataAdapter1.Fill(CustomerOrders)
DataGrid1.SetDataBinding(CustomerOrders, "Customers")
End Sub
This code will use the data adapter we have just set up to fill the dataset with information from the Customers table in the Northwind Database, and display it in DataGrid1.
Working with ADO.NET requires two namespaces: System.Data, and since we are using OLEDB, the System.Data.OleDb namespace as well. Add the imports statements to the top of Form1.vb.
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Inherits System.Windows.Forms.Form
Run the code and your application should open, and display the dataset it has been loaded with, providing you with access to data from the CustomerOrders dataset:
The application you have created should be the same as Reporting_App_ViewData, which is available in the code download. Now we will go on to using the dataset as the data source for a report.
Creating a Report from an ADO.NET Dataset
The CustomerOrders dataset for the database object we have built will be populated when the application is started. When designing a report based on this dataset, we cannot browse the content of any of the fields as the dataset hasn't been populated at this point, but if you do have any questions about what type of content the fields could contain, view the database to find out, or use a viewing application like the one we have just built.To create a report based on an ADO.NET dataset, start by opening the copy you made of Reporting_App at the end of the section An ADO.NET Dataset. Select Project | Add New Item, and select Crystal Report from the list of available templates. Call the report adonet_sample.rpt, click on Open, and the familiar Crystal Report Gallery will open with options for creating your report.Again, we are going to use the Standard Report Expert, although you could use any other expert you wish to get started. Since we are using the Standard Report Expert, the first step is to select the data for our report. The Data Explorer contains a folder, for adding Project Data.
All of the dataset objects that were generated during the build will be listed here, in the format of Project.datasetName (for example, Reporting_App.CustomerOrders). Simply add the tables from the dataset to your report, to gain access to all of the fields within the dataset.You can then use these tables and fields in your report development just like using any other source, with one catch. When Crystal Reports.NET looks at the fields in your dataset, it ignores the length that the string actually is, and instead thinks that all of the string fields are the maximum length (65,534 characters).This shouldn't affect you unless your are using any formulas that use the length or other string functions dependent on the length, in which case you will need to use the trim function before you attempt to get the length of a string (not the best solution if you have leading or trailing spaces in your string).
This issue has been tracked by Crystal Decisions, and should be fixed in future releases of the product.Click the Fields tab to select the fields that will appear in your report. Choose CompanyName, City, and Region from the Customers table. Then select OrderDate and ShippedDate from the Orders table.
Select Finish to create the report from your ADO.NET dataset. Now you can simply save the report and integrate it into your application. The section below outlines how to use this type of report with the Windows Report Viewer, and provides a brief note of how to use it with the Web Report Viewer.
Viewing Reports Containing an ADO.NET Dataset
If you were to set the ReportSource property for the Windows Report Viewer to your newly created report based on ADO.NET, and previewed it exactly the same as you would a normal report, nothing would appear. By default, the ADO.NET dataset we have used does not contain any data.To continue from our previous example, where we created a report from the dataset based on the Customers and Orders tables, we are going to look at the code required to integrate this report (based on an ADO.NET dataset) into a Windows Application.To start with, we need a form to host the Crystal Reports Viewer. From your project, open Form1.vb, and drag the CrystalReportViewer from the Toolbox to the form. Next, add the report as you normally would, by dragging ReportDocument from the Components section of the Toolbox, and selecting our new report, adonet_sample.rpt.
Next we have to add an OleDbDataApdapter just as we did in the data viewer project earlier. This involves dragging the control onto our form, and stepping through the Data Adapter Configuration Wizard, which in brief comprises (look back to the Viewing the Contents of a Dataset section if you need a guide through these steps):
Choosing your data connection - if it isn't the default, enter the details for your Northwind Database.
Choosing a query type - we will use SQL statements
Generating the SQL Statements - click on the Query Builder button, and use the Add Tables dialog to add the Customers and Orders tables as we did before. Select (All Columns), and check the SQL generated is as follows:
SELECT
Customers.*,
Orders.*
FROM
Customers
INNER JOIN
Orders
ON
Customers.CustomerID = Orders.CustomerID
Notice how OleDbConnection1 has also appeared on the report next to OleDbDataApdapter1. This is generated from the connection information that you provided the Data Adapter Configuration Wizard with.
With the form now set up to access the dataset, we need to look at some code. Add the same namespaces as we did to view the dataset earlier in this chapter. In addition, since we will be printing a report based on this data, we need to add the CrystalDecisions.CrystalReports.Engine namespace to the code behind this form, as shown here:
Imports System.Data
Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Public Class Form1
Inherits System.Windows.Forms.Form
Next we need to add variables to dimension the connection, the data adapter, and the dataset, and get the form ready to accept data. We will connect to the Northwind database using OleDbConnectionl (as seen on the form designer), and submit a SQL query that will return a result set, filling the Customers table, and then the Orders table in the dataset. To do this, double-click on the CrystalReportViewer in the form designer to generate the CrystalReportViewer1_Load procedure. Then insert the following code:
Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs)
Handles CrystalReportViewer1.Load
Dim myDataSet = New CustomerOrders()
Dim sqlString As String = "Select * from Customers"
OleDbDataAdapter1 = New OleDbDataAdapter(sqlString, OleDbConnection1)
OleDbDataAdapter1.Fill(myDataSet, "Customers")
sqlString = "Select * from Orders"
OleDbDataAdapter1 = New OleDbDataAdapter(sqlString, OleDbConnection1)
OleDbDataAdapter1.Fill(myDataSet, "Orders")
End Sub
With the data now in the dataset, we can set our report source and preview the report itself, by adding this code to the bottom of the CrystalReportViewer1_Load procedure:
OleDbDataAdapter1.Fill(myDataSet, "Orders")
Dim myReport = New adonet_sample()
myReport.SetDataSource(myDataSet)
CrystalReportViewer1.ReportSource = myReport
End Sub
When you run your application and view this form, your report will be filled with data from the ADO.NET dataset, and will be displayed within the viewer, as shown in the following screenshot:
The only difference in integration between the Windows and the Web Crystal Report Viewer is that you will need to add the appropriate report viewer for the environment you are working with (discussed in Chapters 3 and 4). You can change the contents of the ADO.NET dataset numerous times within your application, and you can call the DataSource method at any time before previewing or refreshing your report.
