To Create a Dataset Object from Northwind database in SQL Server, using ADO.NET

1. In the

Item

2. In the Categories area of the Add New Item dialog box, expand the folder and select

Solution Explorer, right-click the project name, point to Add, and click Add New.

Data

3. In the Templates area, select

4. Accept the default name

 Solutions Explorer, click on Dataset1.xsd file, if now already the active view.Server Explore, on the right connect to SQL Server and drill down to

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

6. From the

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

as under

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 in Northwind database

1. In the Visual Studio .NET

shortcut menu.

2. Point to

3. In the Add New Item dialog box, select

Solution Explorer, right-click your project to display theAdd and click Add New Item.Crystal Report from the Templates area. ClickOpen

4.

5. You can choose from any of the options provided in Crystal Report Gallery. But for the

purpose of this walkthrough choose

6. On

7. Right click in the Report Designer, point to

8. You’ll be presented with

9. In the Database Expert wizard, expand the

Datasets

10. If you now drill down

Customers table and all its fields

11. Drag and drop the fields onto the report and format them as required.

Database Fields node, in the Field Explorer, you can view

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

2. Accept the default name as

3. Open

 

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.

Try

myConnection =

& _

"Initial Catalog=northwind;")

MyCommand.Connection = myConnection

MyCommand.CommandText = "SELECT * FROM Customers"

MyCommand.CommandType = CommandType.Text

myDA.SelectCommand = MyCommand

myDA.Fill(myDS, "Customers")

rpt.SetDataSource(myDS)

CrystalReportViewer1.ReportSource = rpt

New SqlConnection("Data Source=localhost;Integrated Security=SSPI;"

Catch

MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK,

MessageBoxIcon.Error)

Excep As Exception

End Try

End Sub

CrystalReportViewer1.Form1 code editor and add the following code on Load event of Form1.False, as shown belowFile menu, click Save to save the report.Database, and click Add/Remove Database.Database Expert wizard.Project Data folder, expand the ADO.NETfolder and select the dataset object as shown belowAs a Blank Report and click OK.Crystal Report Gallery will be displayed, as shown below..Dataset.Dataset1.xsd.

SSRS Reports Using Reports Wizards

1. Start a new Business Intelligence project in Visual Studio 2005. Then, from the menu, select Project > Add New Item. Select the Report Wizard option and call the file "FirstReportWZ.rdl". Click Add. Click Next on the Welcome screen and this will bring you to the Select the Data Source screen:

Select the New data source radio button and give the data source a name. Select Microsoft SQL Server as the type.


Tip: Use of the Shared data source option is actually a better option in most cases but, for the sake of this example, we will just create a new data source. We're going to set up a shared data source later in this article. 

Click the Edit button to bring up the Connection Properties dialog:

Enter or select a server name. The default is to log on using Windows Authentication. If you are using SQL Server Authentication, choose that setting, and enter the username and password. Finally, select a database, eg : ReportingDemo. Make sure you test the connection before you click OK.



Next up is the Design the Query screen. You can use the Query Builder by clicking the button at the top left, but in this case simply enter "Select * From Customer" into the Query string text box and click Next. On the Select the Report Type simply select the type you prefer (tabular or matrix) and hit Next.



This brings up the Design the Table screen:

Select State in the Available fields box and click the Group button. This will group the data in the result set by State. Select Next. On the Choose the Table Layout page, select the Stepped option and check the Enable Drilldown checkbox. On the next screen, choose a style for your report (I chose Ocean) and click Next.



This brings up the final screen, Completing the Wizard:

It shows a summary of your report options, and gives you an opportunity to rename the report and to preview it. Click Finish to end the wizard.



This is how your report will look in the standard Report Designer:

There are three tabs: Data, Layout and Preview. You should be on the Layout tab. Click on the Preview tab to view the report. Since this report doesn't take any arguments, it will run as soon as you click on the tab.

Use the +/- symbols next to the State abbreviations to expand and collapse the grouping of the report.

Creating the RDL Generator Visual Studio Project

1. On the File menu, point to New, and then click Project to open the New Project dialog box.
2. Click the Visual C# node in the Project types pane.
3. Click the Console Application icon.
4. In the Name box, enter a name for your project. Type the name RDLGenerator.
5. In the Location box, enter the path where you want to save your project, or click Browse to navigate to the folder.
6. Click OK. A collapsed view of your project appears in Solution Explorer.
   
   In Solution Explorer, expand the project node. A code file with the default name of Program.cs has been added to your project.

When you have finished creating the application template, replace the contents of the code file with the following:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace MyRDLGenerator
{
   class RdlGenerator
   {
      SqlConnection m_connection;
      string m_connectString;
      string m_commandText;
      ArrayList m_fields;

      public static void Main()
      {
         RdlGenerator FirstRdlGenerator = new RdlGenerator();
         myRdlGenerator.Run();
      }

      public void Run()
      {
         try
         {
            // Call methods to create the RDL
            this.OpenConnection();
            this.GenerateFieldsList();
            this.GenerateRdl();

            Console.WriteLine("RDL file generated successfully.");
         }

         catch (Exception exception)
         {
            Console.WriteLine("An error occurred: " + exception.Message);
         }

         finally
         {
            // Close the connection string
            m_connection.Close();
         }
      }

      public void OpenConnection()
      {
         // Create a connection object
   m_connection = new SqlConnection();
        
   // Create the connection string
   m_connectString = @"data source=localhost;initial catalog=DatabaseName;integrated security=SSPI";
   m_connection.ConnectionString = m_connectString;
        
   // Open the connection
   m_connection.Open();

      }

      public void GenerateFieldsList()
      {
         SqlCommand command;
   SqlDataReader reader;

   // Executing a query to retrieve a fields list for the report
   command = m_connection.CreateCommand();
   m_commandText ="SELECT Person.CountryRegion.Name AS CountryName, Person.StateProvince.Name AS StateProvince " +
      "FROM Person.StateProvince " +
      "INNER JOIN Person.CountryRegion ON Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode " +
      "ORDER BY Person.CountryRegion.Name";
   command.CommandText = m_commandText;
  
   // Execute and create a reader for the current command
   reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
  
   // For each field in the resultset, add the name to an array list
   m_fields = new ArrayList();
   for (int i = 0; i <= reader.FieldCount - 1; i++)
   {
      m_fields.Add(reader.GetName(i));
   }

      }

     public void GenerateRdl()
{
    // Create an XML document
    XmlDocument doc = new XmlDocument();
    string xmlData = "<Report " +
    "xmlns=\"link\">" +
        "</Report>";
    doc.Load(new StringReader(xmlData));

    // Report element
    XmlElement report = (XmlElement)doc.FirstChild;
    AddElement(report, "AutoRefresh", "0");
    AddElement(report, "ConsumeContainerWhitespace", "true");

    //DataSources element
    XmlElement dataSources = AddElement(report, "DataSources", null);
    //DataSource element
    XmlElement dataSource = AddElement(dataSources, "DataSource", null);
    XmlAttribute attr = dataSource.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "DataSource1";
    XmlElement connectionProperties = AddElement(dataSource, "ConnectionProperties", null);
    AddElement(connectionProperties, "DataProvider", "SQL");
    AddElement(connectionProperties, "ConnectString", m_connectString);
    AddElement(connectionProperties, "IntegratedSecurity", "true");
    //DataSets element
    XmlElement dataSets = AddElement(report, "DataSets", null);
    XmlElement dataSet = AddElement(dataSets, "DataSet", null);
    attr = dataSet.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "DataSet1";
    //Query element
    XmlElement query = AddElement(dataSet, "Query", null);
    AddElement(query, "DataSourceName", "DataSource1");
    AddElement(query, "CommandText", m_commandText);
    AddElement(query, "Timeout", "30");
    //Fields element
    XmlElement fields = AddElement(dataSet, "Fields", null);
    XmlElement field = AddElement(fields, "Field", null);
    attr = field.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "CountryName";
    AddElement(field, "DataField", "CountryName");
    field = AddElement(fields, "Field", null);
    attr = field.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "StateProvince";
    AddElement(field, "DataField", "StateProvince");

    //end of DataSources

    //ReportSections element
    XmlElement reportSections = AddElement(report, "ReportSections", null);
    XmlElement reportSection = AddElement(reportSections, "ReportSection", null);
    AddElement(reportSection, "Width", "6in");
    AddElement(reportSection, "Page", null);
    XmlElement body = AddElement(reportSection, "Body", null);
    AddElement(body, "Height", "1.5in");
    XmlElement reportItems = AddElement(body, "ReportItems", null);
    // Tablix element
    XmlElement tablix = AddElement(reportItems, "Tablix", null);
    attr = tablix.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "Tablix1";
    AddElement(tablix, "DataSetName", "DataSet1");
    AddElement(tablix, "Top", "0.5in");
    AddElement(tablix, "Left", "0.5in");
    AddElement(tablix, "Height", "0.5in");
    AddElement(tablix, "Width", "3in");
    XmlElement tablixBody = AddElement(tablix, "TablixBody", null);
    //TablixColumns element
    XmlElement tablixColumns = AddElement(tablixBody, "TablixColumns", null);
    XmlElement tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
    AddElement(tablixColumn, "Width", "1.5in");
    tablixColumn = AddElement(tablixColumns, "TablixColumn", null);
    AddElement(tablixColumn, "Width", "1.5in");
    //TablixRows element
    XmlElement tablixRows = AddElement(tablixBody, "TablixRows", null);
    //TablixRow element (header row)
    XmlElement tablixRow = AddElement(tablixRows, "TablixRow", null);
    AddElement(tablixRow, "Height", "0.5in");
    XmlElement tablixCells = AddElement(tablixRow, "TablixCells", null);
    // TablixCell element (first cell)
    XmlElement tablixCell = AddElement(tablixCells, "TablixCell", null);
    XmlElement cellContents = AddElement(tablixCell, "CellContents", null);
    XmlElement textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "HeaderCountryName";
    AddElement(textbox, "KeepTogether", "true");
    XmlElement paragraphs = AddElement(textbox, "Paragraphs", null);
    XmlElement paragraph = AddElement(paragraphs, "Paragraph", null);
    XmlElement textRuns = AddElement(paragraph, "TextRuns", null);
    XmlElement textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "CountryName");
    XmlElement style = AddElement(textRun, "Style", null);
    AddElement(style, "TextDecoration", "Underline");
    // TablixCell element (second cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "HeaderStateProvince";
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "StateProvince");
    style = AddElement(textRun, "Style", null);
    AddElement(style, "TextDecoration", "Underline");
    //TablixRow element (details row)
    tablixRow = AddElement(tablixRows, "TablixRow", null);
    AddElement(tablixRow, "Height", "0.5in");
    tablixCells = AddElement(tablixRow, "TablixCells", null);
    // TablixCell element (first cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "CountryName";
    AddElement(textbox, "HideDuplicates", "DataSet1");
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "=Fields!CountryName.Value");
    style = AddElement(textRun, "Style", null);
    // TablixCell element (second cell)
    tablixCell = AddElement(tablixCells, "TablixCell", null);
    cellContents = AddElement(tablixCell, "CellContents", null);
    textbox = AddElement(cellContents, "Textbox", null);
    attr = textbox.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "StateProvince";
    AddElement(textbox, "HideDuplicates", "DataSet1");
    AddElement(textbox, "KeepTogether", "true");
    paragraphs = AddElement(textbox, "Paragraphs", null);
    paragraph = AddElement(paragraphs, "Paragraph", null);
    textRuns = AddElement(paragraph, "TextRuns", null);
    textRun = AddElement(textRuns, "TextRun", null);
    AddElement(textRun, "Value", "=Fields!StateProvince.Value");
    style = AddElement(textRun, "Style", null);
    //End of second row

    //End of TablixBody

    //TablixColumnHierarchy element
    XmlElement tablixColumnHierarchy = AddElement(tablix, "TablixColumnHierarchy", null);
    XmlElement tablixMembers = AddElement(tablixColumnHierarchy, "TablixMembers", null);
    AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMembers, "TablixMember", null);

    //TablixRowHierarchy element
    XmlElement tablixRowHierarchy = AddElement(tablix, "TablixRowHierarchy", null);
    tablixMembers = AddElement(tablixRowHierarchy, "TablixMembers", null);
    XmlElement tablixMember = AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMember, "KeepWithGroup", "After");
    AddElement(tablixMember, "KeepTogether", "true");
    tablixMember = AddElement(tablixMembers, "TablixMember", null);
    AddElement(tablixMember, "DataElementName", "Detail_Collection");
    AddElement(tablixMember, "DataElementOutput", "Output");
    AddElement(tablixMember, "KeepTogether", "true");
    XmlElement group = AddElement(tablixMember, "Group", null);
    attr = group.Attributes.Append(doc.CreateAttribute("Name"));
    attr.Value = "Table1_Details_Group";
    AddElement(group, "DataElementName", "Detail");
    XmlElement tablixMembersNested = AddElement(tablixMember, "TablixMembers", null);
    AddElement(tablixMembersNested, "TablixMember", null);

    //End of Tablix, ReportItems, ReportSections

    //Save XML document to file
    doc.Save("Report1.rdl");

}

public XmlElement AddElement(XmlElement parent, string name, string value)
{
    XmlElement newelement = parent.OwnerDocument.CreateElement(name,
        "link");
    parent.AppendChild(newelement);
    if (value != null) newelement.InnerText = value;
    return newelement;
}

   }
}