In this article I am going to show how we can display crystal report from more then one table.
In my previous article Crystal Report From More Than One Table in ASP.NET I also explained same thing but in this article I am going to use Stored Procedure.
Now we will learn this step by step:
Below is my SQL server table, for which I will show crystal report.
Below 3 tables I am using to create a Crystal Report.
1. Customer
Image 1.
2. Product
Image 2.
3. Customer Product Order
Image 3.
I also create a Stored Procedure here GetCustProdOrderDetail
CREATE PROCEDURE GetCustProdOrderDetail
AS
BEGIN
SELECT O.Cust_Prod_Order_ID,C.Customer_Name,
P.Product_Name,O.Quantity,O.Order_Date
FROM Cust_Prod_Order As O
INNER JOIN Customer AS C
ON O.Customer_ID=C.Customer_ID
INNER JOIN Product AS P
ON O.Product_ID= P.Product_ID
END
Now Open Visual Studio -> File -> New Web Site.
Image 4.
Now Right Click on Solution Explorer -> Add New Item -> Select DataSet
Image 5.
Click Yes.
Image 6.
Now Right click -> Add -> Data Table
Image 7.
Now Right Click on Header -> Add ->Column
Make sure column name and their Data type should be same as in your data table. You can change data type by selecting your added column ->Right click and select Data type property
Image 8.
Image 9.
Now Right click on solution explorer -> Add New Item -> Select Crystal Report
Image 10.
Image 11.
Select Project Data -> ADO.NET Data Sets-> Your Created Data Set -> Your Table -> Click Next
Image 12.
Now select column for report and click on finish.
Image 13.
Now Crystal Report will look like below.
Image 14.
Now open your Default.aspx page and add a Report Viewer here like below.
Image 15.
Now on your aspx page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Display Crystal Report Using Stored Procedure</title>
</head>
<body>
<form id="form1" runat="server">
<table cellpadding="10" cellspacing="10" width="70%" height="300px" align="center" style="border: solid 2px gray;">
<tr>
<td align="center" style="background-color: SkyBlue;">
<span style="font-family: Times New Roman; font-size: 18pt; color: Green;">Customer
Product Order Detail Report</span>
</td>
</tr>
<tr>
<td align="center">
<asp:Panel ID="pnlReport" runat="server" Height="400px">
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
</asp:Panel>
</td>
</tr>
</table>
</form>
</body>
</html>
Now aspx.cs code is:
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GetData();
}
protected void GetData()
{
string connStr = @"Data Source=MyPC\SqlServer2k8;Integrated Security=True;Initial Catalog=Business";
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand();
DataSet ds = null;
SqlDataAdapter adapter;
try
{
con.Open();
cmd.CommandText = "GetCustProdOrderDetail";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.ExecuteNonQuery();
ds = new DataSet();
adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
//Assigning Data To report
ReportDocument rd = new ReportDocument();
rd.Load(Server.MapPath("CrystalReport.rpt"));
rd.SetDataSource(ds.Tables[0]);
CrystalReportViewer1.ReportSource = rd;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
if (con.State != ConnectionState.Closed)
{
con.Close();
}
}
}
}
Now run the application:
Image 16.