Tuesday, September 22, 2009

Create a Crystal Report from ADO.NET Dataset using Visual Basic .NET

Crystal Report of Visual Studio .NET is the standard reporting tool for Visual Studio .NET. You can host reports on web and windows platform and can publish reports as Report Web services on the web server. It is based on framework of Crystal Report 11.0 and uses open and flexible architecture, with standards like XML, to allow porting reports over the web. Using crystal report expert you can choose report layouts, display charts, calculate summaries, subtotals as grouped data as well as conditionally format text and rotate text objects.

Although Crystal Reports for Visual Studio .NET supports variety of data source like ADO recordset, CDO recordset, DAO recordset, MS Excel workbook, this walkthrough endeavor to explain How to report off ADO.NET DataSet using Visual Basic .NET.

As you all know DataSet is the core component of distributed application and is explicitly designed for data access independent of any data source. Dataset can be created from variety of sources. Whatever the source is, before reporting off ADO.NET DataSet you must perform the following task:
  • Generate an object for the DataSet.

  • Connect report to DataSet Object.

  • Push data into DataSet Object.

  • Bind report to Windows Forms Viewer to display report with actual data at runtime.

  • Requirements:
  • Visual Studio 2005

  • .NET Framework 2.0

  • SQL Server 2005 Express Database

  • Generating an Object for the DataSet
    Object for ADO.NET is a collection of dataset classes created in memory.

    To create a dataset object from database in SQL Server, using ADO.NET DataSet Designer.

    1. In the Solution Explorer, right-click the project name, point to Add, and click Add New Item.
    2. In the Categories area of the Add New Item dialog box, expand the folder and select Data.
    3. In the Templates area, select Dataset.
    4. Accept the default name Dataset1.xsd.

    This creates a new schema file that will be used to generate a strongly typed dataset. The schema file will be displayed in ADO.NET Dataset designer.

    5. In the Solutions Explorer, click on Dataset1.xsd file, if now already the active view.
    6. From the Server Explore, on the right connect to SQL Server and drill down to Northwind Database.
    7. Highlight the Table Customers (or stored procedure if desired) and drag and drop it on the Interface of Dataset1.xsd. Dataset1.xsd should now be displayed in the Dataset tab.

    This creates a dataset object and contains only a description of the database based on the schema in Dataset1.xsd. It does not contain the actual data.

    Connecting Report to an ADO.NET Dataset Object
    From ADO.NET Dataset Object you can add tables to Crystal Report using Database Expert in Crystal Report Designer.

    To create a new report and connect it to Dataset object which contains description for Customers table

    1. In the Visual Studio Solution Explorer, right-click your project to display the shortcut menu.
    2. Point to Add and click Add New Item.
    3. In the Add New Item dialog box, select Crystal Report from the Templates area. Click Open.
    4. You will be presented with Crystal Report Gallery.
    5. You can choose from any of the options provided in Crystal Report Gallery. But for the purpose of this walkthrough choose As a Blank Report and click OK.
    6. On File menu, click Save to save the report.
    7. Right click in the Report Designer, point to Database, and click Add/Remove Database.
    8. You’ll be presented with Database Expert wizard.
    9. In the Database Expert wizard, expand the Project Data folder, expand the ADO.NET Datasets folder and select the dataset object.
    10. If you now drill down Database Fields node, in the Field Explorer, you can view Customers table and all its fields
    11. Drag and drop the fields onto the report and format them as required.

    Pushing data into DataSet object and binding report to Windows Forms Viewer
    In order to display actual data in the report, you should fill the dataset object with the data before you bind the report to Windows Forms Viewer. You should do this in the corresponding source file for Windows Form.

    1. Drag and drop CrystalReportViewer control on Form1 and set the DisplayGroupTree property to False.
    2. Accept the default name as CrystalReportViewer1.
    3. Open Form1 code editor and add the following code on Load event of Form1.

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim rpt As New CrystalReport1() 'The report you created.
    Dim myConnection As SqlConnection
    Dim MyCommand As New SqlCommand()
    Dim myDA As New SqlDataAdapter()
    Dim myDS As New Dataset1() 'The DataSet you created.


    myConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
    "Initial Catalog=northwind;")
    'myConnection = New SqlConnection("server=(local)\SqlExpress;AttachDbFileName=" &_ Application.StartupPath & "\Database1.mdf;Integrated Security=true;User Instance=true")
    MyCommand.Connection = myConnection
    MyCommand.CommandText = "SELECT * FROM Customers"
    MyCommand.CommandType = CommandType.Text
    myDA.SelectCommand = MyCommand

    myDA.Fill(myDS, "Customers")
    CrystalReportViewer1.ReportSource = rpt

    Catch Excep As Exception
    MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
    End Sub

    Or the following code is also useful

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim rpt As New CrystalReport1 'The report you created.
    Dim dbConn As SqlConnection

    Dim dbAdptr As New SqlDataAdapter
    Dim dbDataset As New DataSet1 'The DataSet you created.


    dbConn = New SqlConnection("server=(local)\SqlExpress;AttachDbFileName=" & Application.StartupPath & "\Data\Database1.mdf;Integrated Security=true;User Instance=true")

    dbAdptr = New SqlDataAdapter("select * from table1", dbConn)

    dbAdptr.Fill(dbDataset, "Table1")
    CrystalReportViewer1.ReportSource = rpt

    Catch Excep As Exception
    MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
    End Sub


    Add reference to SqlClient namespace. Imports System.Data.SqlClient

    Check connection to your server.

    Happy programming!!!

    Learn by diving in Programming Ocean...
    Happy Programming!!!

    Friday, September 18, 2009

    Master/Slave Configurations for PC Hardware

    Master/Slave Configurations

    The hard drive or CD-ROM drive controller’s function is to receive commands to the drive and control the action of the drive itself. The technology incorporated in IDE and ATA devices allows one controller to take over the function of more than one drive. This means that you can install up to two drives on a single ribbon cable. This setup is called a master/slave configuration because one drive’s controller directs the activities of both drives. It is important to note here that most computer systems can support a mixture of IDE and ATA drives.

    Installing Master and Slave Hard Drives

    1. Determine, which drive, will be the master. (See the “Choosing a Master” From the Classroom for more information).

    2. Locate the master/slave jumpers, which can be found on the bottom of the drive or, more commonly, on the end by the power and ribbon cable connectors. In Fig 2-1, the jumpers are located to the left of the power connector and the jumper settings are indicated by the standard settings information on the label.

    Fig 2.1 the master/slave jumper set on a typical hard drive

    3. Use the drive label information to determine which jumper settings to use for a master or a slave configuration.

    4. Set this drive as a master using the jumper(s).

    5. Using the procedure explained in Chapter 1, Exercise 1-2, physically install this drive on the end of the ribbon cable and secure it to an available drive bay.

    6. Using the proper jumper setting, configure the second drive as a slave.

    7. Install the second drive in the middle of the ribbon cable. Your hard drive setup should look similar to the one shown in Fig 2-2. Note the position of the hard drives on the ribbon cable.

    The jumpers inform the BIOS about the IDE devices if they are master, slave or cable select. If a device is jumpered as cable select and if it is put in the farthest end from the controller then that device is recognized as master and if it is put in the middle of the cable it is recognized as slave. Whereas for Master and Slave configurations wherever the device is put it is recognized as master or slave. The jumpers vary for different manufacturers and are not the same. Seagate is the only manufacturer whose hard disk when not jumpered is recognized as Slave.

    Note: The master drive must be set to the master jumper setting and be installed on the end of the ribbon cable. The slave drive must be set to the slave jumper setting and be installed in the middle of the ribbon cable

    Most newer computers will detect the presence of a master/slave configuration and name the drives appropriately: Typically, the master will be drive C, and the slave will be drive D. However, some older computers require you to perform drive detection. In this case, when you start the computer, enter the CMOS settings program, as discussed in Chapter 1. Select the Auto-Detect or Detect Hard Drive option (these options might have slightly different names on different computers). This choice forces the BIOS to search all drive controller connections for the presence and configuration of hard or CD-ROM drives.

    Fig 2.2 The finished installation of a master/slave configuration

    Devices per Channel

    Most newer computers have two hard drive controllers. That is, the motherboard has connectors for two ribbon cables (see Fig 2-3). These controllers are termed primary and secondary. If there is only one drive present, it must be attached to the primary controller. An additional drive can be added as either a primary slave or a secondary master. It is important to note that although they are often referred to as hard drive controllers, these devices are not limited to controlling hard drives; they are also used to control CD-ROM drives.

    To add a drive to the secondary controller, simply connect the drive to a ribbon cable and attach the ribbon cable to the secondary controller port on the motherboard, ensuring that the red stripe is aligned with pin 1. You will also of course need to connect a power cable to the drive. There is usually no noticeable performance advantage to configuring a second drive as a secondary master rather than as a primary slave. (See the “Choosing a Master” From the Classroom for a discussion on choosing the master or slave designation.)

    As before, the primary master receives the first available drive letter, typically C. The remaining drives are lettered according to this order of priority: primary slave, secondary master, and primary master.

    When you add or remove a drive to or from a multidrive system, the drives are automatically relettered so that no letters are skipped. For example, suppose a system includes drives C: , D:, and E:. If drive D: is removed, the E: drive will be relabeled D: the next time the computer is started. Windows operating systems also allow for a drive to be configured to hold a certain drive letter.


    Choosing a Master

    How do you determine which drive should be the master and which should be the slave? In many cases, it doesn’t matter which is which. That is, there is no real performance difference between master and slave drives. However, as with most computer configurations, there are some exceptions.

    Some operating systems require that the hard drive containing the OS be configured as a master. This is important to note only if you are installing drives that already contain data. If you are installing new hard drives, you don’t have to worry about this; simply load the OS on the master drive after the drives are installed.

    When using a mixture of old and new hard drives within the same system, set the newer drive as the master and the older drive as the slave. This setting is a good idea because newer drives can recognize and communicate with older drives, but the reverse isn’t true. An older drive’s controller will typically be unable to control the newer drive.

    When using a hard drive and CD-ROM drive together in a master/slave configuration, always set the hard drive as the master and the CD-ROM as the slave because the CD-ROM’s controller is unable to take control of the hard drive. Additionally, some (but not most) CD-ROM drives are designed to work as slaves, even when there is no master present, and they simply cannot be configured as master drives.

    Learn by diving in Programming Ocean...
    Happy Programming!!!