Neodynamic
Well-known member
- Joined
- Dec 5, 2005
- Messages
- 137
- Programming Experience
- 10+
How to create Crystal Reports featuring barcode images using SQL Stored Procedure in ASP.NET
Prerequisites
- Neodynamic Barcode Professional 3.0 (or greater) for ASP.NET (WebControl)
- Microsoft .NET Framework 2.0 (or greater)
- Microsoft Visual Studio 2005
- Microsoft SQL Server 2000 (or greater) with Northwind Database sample installed
- Crystal Report for Visual Studio .NET 2005
In the following Step-By-Step Guide we're going to create a Crystal Report which features barcoding capabilities by using Barcode Professional for ASP.NET and using as data source for the report a SQL Stored Procedure.
Follow these steps:
- Open Visual Studio and create a new ASP.NET Website naming it CrystalReportBarcodeSP
- Preparing a SQL Stored Procedure with barcoding capabilities for using it in Crystal Reports
Suppose you already have a SQL Stored Procedure which returns all customers from Northwind Database, for example:
ALTER PROCEDURE dbo.GetCustomers AS
SELECT CustomerID, CompanyName, City, Country
FROM Customers
RETURN
In order to support barcoding capabilities in Crystal Reports by using a SQL Stored Procedure as the data source, we'll need to modify the involved SP so it returns an additional column of type SQL Image. The purpose of such SQL Image field will be to hold the barcode image generated by Barcode Professional for each customer in the result set.
So let's modify that hypothetical SP so it returns a result set with a SQL Image type. You must add the following SP into your local Northwind Database.
ALTER PROCEDURE dbo.GetCustomers AS
/*
Local variable to initialize Barcode field
*/
DECLARE @barcodeImage AS BINARY
SET @barcodeImage = NULL
/*
Create a SQL Temp Table with the same structure as Customers Table
and adding an additional field of type Image for barcoding
*/
CREATE TABLE #CustomerTempTable
(
CustomerID NCHAR(5),
CompanyName NVARCHAR(40),
City NVARCHAR(15),
Country NVARCHAR(15),
Barcode IMAGE
)
/*
Populate the temp table
*/
INSERT INTO #CustomerTempTable
SELECT CustomerID, CompanyName, City, Country, @barcodeImage
FROM Customers
/*
Return result set with barcode column
*/
SELECT * FROM #CustomerTempTable
/*
Delete temp table
*/
DROP TABLE #CustomerTempTable
- Now add a new Crystal Report item to the project and name it CrystalReportBarcode.rpt.
Adding a Crystal Report to the project
Click Add button. Next, choose "Blank Report" in the Crystal Report Gallery dialog box.
Creating a blank report
Click OK button.
- Launch Crystal Report Database Expert by going to Crystal Reports > Database > Database Expert...
Opening Crystal Reports Database Expert dialog box
In the Database Expert dialog box, click on the [+] (Plus button) for "OLE DB (ADO)" item under "Create New Connection" element as is shown in the following figure.
Adding a new connection using OLE DB Provider
The OLE DB Provider dialog box will be opened. Please select "Microsoft OLE DB Provider for SQL Server" in the Providers list and then click Next.
Choosing OLE DB Provider for SQL Server
In the following wizard step, please provide the needed info in order to connect to Northwind Database
Providing info to connect to Northwind Database
Click Finish to close OLE DB dialog box. After that, in the Database Expert dialog box, please look for GetCustomers Stored Procedure in the left pane and add it to the Selected Tables list in the right pane as is shown in the following figure.
Choosing the GetCustomers Stored Procedure as data source of the Crystal Report
Click OK button. Now, the GetCustomers SP should be available in the Field Explorer Window as is shown in the following figure. NOTE: To display the Field Explorer press Ctrl+Alt+T
The GetCustomers SP available in the Field Explorer
- Please design the report so it looks like the following figure. Just drag & drop the fields from the Field Explorer onto the report as is shown in the figure.
The barcode report layout
The most important field in our scenario is Barcode (Please DO NOT change the dimensions of such field on the report surface). Select the Barcode item on the report and right-clicking onto it select "Format Object" from the context menu to open the Format Editor dialog box. In that dialog box, please check Can Grow option and click OK button.
Setting up "Can Grow" option for the Barcode item in the Format Editor dialog box
- Save the report.
- Now Create/Open an ASP.NET at design time and drag & drop a CrystalReportViewer control onto it.
- After that, from the Solution Explorer, add a reference to Barcode Professional for ASP.NET assembly: Neodynamic.WebControls.Barcodeprofessional.dll
- Write the following code in the Page_Load event procedure.
Visual Basic .NET
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Getting Customers from DB
Dim cnn As New System.Data.SqlClient.SqlConnection("Data Source=neo3\sql2000;Initial Catalog=Northwind;Integrated Security=True")
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "GetCustomers"
Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
Dim dsCustomers As New DataSet()
da.Fill(dsCustomers)
'Create an instance of Barcode Professional
Dim bcp As New Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional()
'Barcode settings
bcp.Symbology = Neodynamic.WebControls.BarcodeProfessional.Symbology.Code39
bcp.Extended = True
bcp.AddChecksum = False
bcp.BarHeight = 0.4F
bcp.QuietZoneWidth = 0
Dim row As DataRow
'Update Customers DataTable with barcode image
For Each row In dsCustomers.Tables(0).Rows
'Set the value to encode
bcp.Code = row("CustomerID").ToString()
'Generate the barcode image and store it into the Barcode Column
row("Barcode") = bcp.GetBarcodeImage(System.Drawing.Imaging.ImageFormat.Png)
Next
'Create a report object
'and set its data source with the DataSet
Dim report As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
Dim rptFile As String = Server.MapPath("CrystalReportBarcode.rpt")
report.Load(rptFile)
report.SetDataSource(dsCustomers.Tables(0))
Me.CrystalReportViewer1.ReportSource = report
End Sub
Visual C# .NET
protected void Page_Load(object sender, EventArgs e)
{
//Getting Customers from DB
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(@"Data Source=neo3\sql2000;Initial Catalog=Northwind;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetCustomers";
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
DataSet dsCustomers = new DataSet();
da.Fill(dsCustomers);
//Create an instance of Barcode Professional
Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional bcp = new Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional();
//Barcode settings
bcp.Symbology = Neodynamic.WebControls.BarcodeProfessional.Symbology.Code39;
bcp.Extended = true;
bcp.AddChecksum = false;
bcp.BarHeight = 0.4f;
bcp.QuietZoneWidth = 0;
//Update Customers DataTable with barcode image
foreach (DataRow row in dsCustomers.Tables[0].Rows)
{
//Set the value to encode
bcp.Code = row["CustomerID"].ToString();
//Generate the barcode image and store it into the Barcode Column
row["Barcode"] = bcp.GetBarcodeImage(System.Drawing.Imaging.ImageFormat.Png);
}
//Create a report object
//and set its data source with the DataSet
CrystalDecisions.CrystalReports.Engine.ReportDocument report;
report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
string rptFile = Server.MapPath("CrystalReportBarcode.rpt");
report.Load(rptFile);
report.SetDataSource(dsCustomers.Tables[0]);
this.CrystalReportViewer1.ReportSource = report;
}
- That's it. Run your application. You should get the barcode images displayed on the report.
The Crystal Report featuring barcodes generated by Barcode Professional
CrystalReportViewer control lets you to export the displayed report to Acrobat PDF, Microsoft Excel XLS as well as other well know document formats. In all cases the barcode images are maintained.
The Crystal Repor report in Acrobat PDF format featuring barcodes generated by Barcode Professional
Links:
This Demo
More Demos
Download Barcode Professional for ASP.NET
More Information about Neodynamic Barcode Professional for ASP.NET
Neodynamic
.NET Components & Controls
Neodynamic
Neodynamic Barcode Professional for ASP.NET
Prerequisites
- Neodynamic Barcode Professional 3.0 (or greater) for ASP.NET (WebControl)
- Microsoft .NET Framework 2.0 (or greater)
- Microsoft Visual Studio 2005
- Microsoft SQL Server 2000 (or greater) with Northwind Database sample installed
- Crystal Report for Visual Studio .NET 2005
In the following Step-By-Step Guide we're going to create a Crystal Report which features barcoding capabilities by using Barcode Professional for ASP.NET and using as data source for the report a SQL Stored Procedure.
Follow these steps:
- Open Visual Studio and create a new ASP.NET Website naming it CrystalReportBarcodeSP
- Preparing a SQL Stored Procedure with barcoding capabilities for using it in Crystal Reports
Suppose you already have a SQL Stored Procedure which returns all customers from Northwind Database, for example:
ALTER PROCEDURE dbo.GetCustomers AS
SELECT CustomerID, CompanyName, City, Country
FROM Customers
RETURN
In order to support barcoding capabilities in Crystal Reports by using a SQL Stored Procedure as the data source, we'll need to modify the involved SP so it returns an additional column of type SQL Image. The purpose of such SQL Image field will be to hold the barcode image generated by Barcode Professional for each customer in the result set.
So let's modify that hypothetical SP so it returns a result set with a SQL Image type. You must add the following SP into your local Northwind Database.
ALTER PROCEDURE dbo.GetCustomers AS
/*
Local variable to initialize Barcode field
*/
DECLARE @barcodeImage AS BINARY
SET @barcodeImage = NULL
/*
Create a SQL Temp Table with the same structure as Customers Table
and adding an additional field of type Image for barcoding
*/
CREATE TABLE #CustomerTempTable
(
CustomerID NCHAR(5),
CompanyName NVARCHAR(40),
City NVARCHAR(15),
Country NVARCHAR(15),
Barcode IMAGE
)
/*
Populate the temp table
*/
INSERT INTO #CustomerTempTable
SELECT CustomerID, CompanyName, City, Country, @barcodeImage
FROM Customers
/*
Return result set with barcode column
*/
SELECT * FROM #CustomerTempTable
/*
Delete temp table
*/
DROP TABLE #CustomerTempTable
- Now add a new Crystal Report item to the project and name it CrystalReportBarcode.rpt.
Adding a Crystal Report to the project
Click Add button. Next, choose "Blank Report" in the Crystal Report Gallery dialog box.
Creating a blank report
Click OK button.
- Launch Crystal Report Database Expert by going to Crystal Reports > Database > Database Expert...
Opening Crystal Reports Database Expert dialog box
In the Database Expert dialog box, click on the [+] (Plus button) for "OLE DB (ADO)" item under "Create New Connection" element as is shown in the following figure.
Adding a new connection using OLE DB Provider
The OLE DB Provider dialog box will be opened. Please select "Microsoft OLE DB Provider for SQL Server" in the Providers list and then click Next.
Choosing OLE DB Provider for SQL Server
In the following wizard step, please provide the needed info in order to connect to Northwind Database
Providing info to connect to Northwind Database
Click Finish to close OLE DB dialog box. After that, in the Database Expert dialog box, please look for GetCustomers Stored Procedure in the left pane and add it to the Selected Tables list in the right pane as is shown in the following figure.
Choosing the GetCustomers Stored Procedure as data source of the Crystal Report
Click OK button. Now, the GetCustomers SP should be available in the Field Explorer Window as is shown in the following figure. NOTE: To display the Field Explorer press Ctrl+Alt+T
The GetCustomers SP available in the Field Explorer
- Please design the report so it looks like the following figure. Just drag & drop the fields from the Field Explorer onto the report as is shown in the figure.
The barcode report layout
The most important field in our scenario is Barcode (Please DO NOT change the dimensions of such field on the report surface). Select the Barcode item on the report and right-clicking onto it select "Format Object" from the context menu to open the Format Editor dialog box. In that dialog box, please check Can Grow option and click OK button.
Setting up "Can Grow" option for the Barcode item in the Format Editor dialog box
- Save the report.
- Now Create/Open an ASP.NET at design time and drag & drop a CrystalReportViewer control onto it.
- After that, from the Solution Explorer, add a reference to Barcode Professional for ASP.NET assembly: Neodynamic.WebControls.Barcodeprofessional.dll
- Write the following code in the Page_Load event procedure.
Visual Basic .NET
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Getting Customers from DB
Dim cnn As New System.Data.SqlClient.SqlConnection("Data Source=neo3\sql2000;Initial Catalog=Northwind;Integrated Security=True")
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = cnn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "GetCustomers"
Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
Dim dsCustomers As New DataSet()
da.Fill(dsCustomers)
'Create an instance of Barcode Professional
Dim bcp As New Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional()
'Barcode settings
bcp.Symbology = Neodynamic.WebControls.BarcodeProfessional.Symbology.Code39
bcp.Extended = True
bcp.AddChecksum = False
bcp.BarHeight = 0.4F
bcp.QuietZoneWidth = 0
Dim row As DataRow
'Update Customers DataTable with barcode image
For Each row In dsCustomers.Tables(0).Rows
'Set the value to encode
bcp.Code = row("CustomerID").ToString()
'Generate the barcode image and store it into the Barcode Column
row("Barcode") = bcp.GetBarcodeImage(System.Drawing.Imaging.ImageFormat.Png)
Next
'Create a report object
'and set its data source with the DataSet
Dim report As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
Dim rptFile As String = Server.MapPath("CrystalReportBarcode.rpt")
report.Load(rptFile)
report.SetDataSource(dsCustomers.Tables(0))
Me.CrystalReportViewer1.ReportSource = report
End Sub
Visual C# .NET
protected void Page_Load(object sender, EventArgs e)
{
//Getting Customers from DB
System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(@"Data Source=neo3\sql2000;Initial Catalog=Northwind;Integrated Security=True");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetCustomers";
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
DataSet dsCustomers = new DataSet();
da.Fill(dsCustomers);
//Create an instance of Barcode Professional
Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional bcp = new Neodynamic.WebControls.BarcodeProfessional.BarcodeProfessional();
//Barcode settings
bcp.Symbology = Neodynamic.WebControls.BarcodeProfessional.Symbology.Code39;
bcp.Extended = true;
bcp.AddChecksum = false;
bcp.BarHeight = 0.4f;
bcp.QuietZoneWidth = 0;
//Update Customers DataTable with barcode image
foreach (DataRow row in dsCustomers.Tables[0].Rows)
{
//Set the value to encode
bcp.Code = row["CustomerID"].ToString();
//Generate the barcode image and store it into the Barcode Column
row["Barcode"] = bcp.GetBarcodeImage(System.Drawing.Imaging.ImageFormat.Png);
}
//Create a report object
//and set its data source with the DataSet
CrystalDecisions.CrystalReports.Engine.ReportDocument report;
report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
string rptFile = Server.MapPath("CrystalReportBarcode.rpt");
report.Load(rptFile);
report.SetDataSource(dsCustomers.Tables[0]);
this.CrystalReportViewer1.ReportSource = report;
}
- That's it. Run your application. You should get the barcode images displayed on the report.
The Crystal Report featuring barcodes generated by Barcode Professional
CrystalReportViewer control lets you to export the displayed report to Acrobat PDF, Microsoft Excel XLS as well as other well know document formats. In all cases the barcode images are maintained.
The Crystal Repor report in Acrobat PDF format featuring barcodes generated by Barcode Professional
Links:
This Demo
More Demos
Download Barcode Professional for ASP.NET
More Information about Neodynamic Barcode Professional for ASP.NET
Neodynamic
.NET Components & Controls
Neodynamic
Neodynamic Barcode Professional for ASP.NET
