Dynamically add rows to Strongly Typed DataSet using C# and VB.Net in ASP.Net

Hi bhushan98,

Check the below example.

DataBase

For this example i have used Microsoft’s Northwind Database. You can download it from here.

Download Northwind Database

DataSet and Report design

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(Server.MapPath("~/OrderReport.rpt"));
    Orders dsOrders = new Orders();
    string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    SqlCommand cmd = new SqlCommand("SELECT TOP 5 CustomerID FROM Customers");
    using (SqlConnection con = new SqlConnection(conString))
    {
        cmd.Connection = con;
        con.Open();
        using (SqlDataReader sdr = cmd.ExecuteReader())
        {
            while (sdr.Read())
            {
                SqlCommand cmd1 = new SqlCommand("SELECT OrderID,ShipName,Freight FROM Orders WHERE CustomerID = @Id");
                using (SqlConnection con1 = new SqlConnection(conString))
                {
                    cmd1.Connection = con1;
                    cmd1.Parameters.AddWithValue("@Id", sdr["CustomerID"].ToString());
                    con1.Open();
                    using (SqlDataReader sdr1 = cmd1.ExecuteReader())
                    {
                        while (sdr1.Read())
                        {
                            DataRow dr = dsOrders.Tables[0].Rows.Add();
                            dr["OrderID"] = sdr1["OrderID"].ToString();
                            dr["ShipName"] = sdr1["ShipName"].ToString();
                            dr["Freight"] = sdr1["Freight"].ToString();
                        }
                    }
                    con1.Close();
                }
            }

        }
        con.Close();
    }
    crystalReport.SetDataSource(dsOrders);
    crystalReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, true, "Orders");
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim crystalReport As ReportDocument = New ReportDocument()
    crystalReport.Load(Server.MapPath("~/OrderReport.rpt"))
    Dim dsOrders As Orders = New Orders()
    Dim conString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand("SELECT TOP 5 CustomerID FROM Customers")
    Using con As SqlConnection = New SqlConnection(conString)
        cmd.Connection = con
        con.Open()
        Using sdr As SqlDataReader = cmd.ExecuteReader()
            While sdr.Read()
                Dim cmd1 As SqlCommand = New SqlCommand("SELECT OrderID,ShipName,Freight FROM Orders WHERE CustomerID = @Id")
                Using con1 As SqlConnection = New SqlConnection(conString)
                    cmd1.Connection = con1
                    cmd1.Parameters.AddWithValue("@Id", sdr("CustomerID").ToString())
                    con1.Open()
                    Using sdr1 As SqlDataReader = cmd1.ExecuteReader()
                        While sdr1.Read()
                            Dim dr As DataRow = dsOrders.Tables(0).Rows.Add()
                            dr("OrderID") = sdr1("OrderID").ToString()
                            dr("ShipName") = sdr1("ShipName").ToString()
                            dr("Freight") = sdr1("Freight").ToString()
                        End While
                    End Using
                    con1.Close()
                End Using
            End While
        End Using
        con.Close()
    End Using
    crystalReport.SetDataSource(dsOrders)
    crystalReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, True, "Orders")
End Sub

Screenshot

Exported PDF