C# .NET Report Data Binding With Stored Procedures and Parameters

Data binding is the process of establishing a connection between the front-end design of a report or piece of software and a data source. When it comes to reporting, this will generally be the first thing you’ll need to do when creating a new report.

This article will walk through data binding in your C# .NET Report application. We'll explore how to use a stored procedure as a data set and how to bind to a generic SQL data table.

We will show data binding with the ActiveReports End User Designer. Whether you're a beginner or an experienced developer, this guide will walk you through data binding in your C# .NET application while growing your data binding knowledge.

• Scenario 1 - Stored Procedure and Parameters
  • Part 1 - Establish Datasource and Stored Procedure
  • Part 2 - Create the Parameter
  • Part 3 - Apply the Selected Filter
• Scenario 2 - Basic SQL Table

If you’d like to follow along with the example, you can get the NorthWind sample database (follow the provided instructions from the readme).

• Note: this will require installing SQL server on your machine if you don’t already have it, and setting up the SQL Server Configuration Manager.

• You’ll also need to run this script to add the stored procedure we're using to your NorthWind database:

  • USP_CustomerDetails Stored Procedure.sql

Ready to Start Data Binding? Download ActiveReports.NET Today!

Let’s get started by launching the ActiveReports End User Designer. Go to start, search “ActiveReports”, and select “ActiveReports Designer”.

Scenario 1

How to Data Bind in C# .NET Reporting Using a Stored Procedure and Parameters

Here, we’ll run you through an example of data binding with a stored procedure. Many of these steps will not be exactly the same on your end as it will depend on the type of data source you are using and what it contains, but this should give you a good understanding of the general process.

Part 1 - Establish Datasource and Stored Procedure

With the End User Designer open, we’ll first add a data source connection. We’ll use our SQL server on the local machine and bind it to the NorthWind Database.

1. Right-click Data Sources in the Report Explorer and select Add Data Source.

2. On your end, you may need to follow different steps here, as it will depend on the kind of data source you are using. In our example, we’ll do the following:

• • For Type, select Microsoft SQL Client Provider
  • For the Server name, select (local)
  • Under “Select or enter a database name:”, select the NWind database that we have installed locally

We’ll add a dataset connection and call it StoredProcedure so we don’t confuse it with the second dataset we will add later for the parameter. We also need to make sure the Command Type is set to StoredProcedure.

1. Right-click on the data source we just added and choose Add Data Set.

2. Set the Name to StoredProcedure.

3. Click on the Query tab in the side navigation bar.

4. Change the Command Type to StoredProcedure (or the name of the Data Set you just created if you used a different name).

5. For the query, we will enter dbo.USP_CustomerDetails (This is another step that will depend on your specific scenario).

As you can see, the stored procedure pulls certain fields from the Customers data table and filters them by a City parameter.

We’ll pass a specific city value directly to the stored procedure for testing purposes.

1. Click the Parameters tab in the side navigation menu.

2. Select the City Parameter.

3. Set the value to London.

Next, we’ll add a table to the design surface to display the data from our stored procedure.

1. From the list of controls, drag the Table control to the design surface

2. Add each field from your data set that you’d like to see to columns in the table in the form of expressions. In our case, this will be:

1. • =[CustomerID], =[ContactName], and =[City]
   • Note: You can type these out manually, or simply drag them straight from the list under your data set

Once the table is filled out, it will look something like this:

Now, upon previewing the report, we can see that it is now functional, displaying each value from the fields we added to the table above, but only from the city of London since we hard-coded our City parameter to London earlier:

Part 2 - Create the Parameter

Next, we want to provide the user with a distinct list of cities and pass that to the stored procedure. To do this, we will create another dataset specifically for that parameter.

We’ll get the distinct cities from the Customers data table like so:

1. Like before, right-click on your data source and select Add Data Set.

2. This time, we’ll enter ParamDS (short for Parameter Data Set) in the Name field.

3. Click the Query tab in the side menu.

4. Here, we will write a basic query to get a distinct list of cities from the Customers table:

• • “select distinct city from customers”

Now, we’ll add a parameter to the report, naming it cityParam, and pass the ParamDS dataset we just created as the available values.

1. Right-click on Parameters in the Report Explorer and choose Add Parameter.

2. In our example, we’ll name the Parameter cityParam.

3. And then for the “Text for prompting users for a value”, We’ll set that to “Select City:”.

4. Switch to the Available Values tab.

5. Check the From query radio button.

6. In our example, we will set the Dataset to ParamDS.

• • Remember, this is our data set of distinct city names that we created earlier

7. Lastly, we’ll set the Value and Label fields both to city.

Upon running the report, the user should be prompted to select a city, but their selection will not yet affect the data.

Part 3 - Apply Selected Filter

Finally, we need to pass the user's selection to the Stored Procedure to filter the dataset. To do this, we’ll go back and edit the StoredProcedure dataset, change the City parameter value to the user-selected value, and then change the value field to the parameter the user would have selected.

1. Right-click on the StoredProcedure data set we created earlier.

2. Click on the Parameters tab in the side menu.

3. Now, we’ll remove our hard-coded “London” Value for the @City Parameter and replace it with an expression that grabs the value selected by the user in our cityParam Parameter that we created earlier.

• • “=Parameters!cityParam.Value”

Upon previewing the report and selecting the same parameter value again, we should see our table filtered to only the results containing Anchorage.

That concludes scenario 1, but we’ll continue with another quick example of a simpler data binding option in the following scenario, starting from a different premade report.

Download our completed sample report.

• Note: You will need to edit the Data Source to target your own instance of the NorthWind database (with my stored procedure script also added) for the sample to run properly.

Scenario 2

How to Data Bind in C# .NET Reporting With a Basic SQL Table

Now, for binding a basic SQL table. We have a pre-made report file with a grouped table for this tutorial.

If you’d like to follow along, you can download the starter sample.

You can also get the AdventureWorks sample database.

• Note: We are using the lightweight version, which will have different naming conventions than the standard one, so make sure you grab the lightweight version if you want to follow along.

Alternatively, you can follow along with your own database and report and use mine as a reference to get a general idea of what to do.

Here is the table we're starting with our my example:

First, let’s establish a connection to our local instance of the SQL server and the AdventureWorks database.

1. Right-click on Data Sources in the Report Explorer and choose Add Data Source.

2. For Type, select Microsoft SQL Client Provider.

3. Under the Server name, add the connection details for your server.

• • In our case, this will just be (local) as it is running on our own machine
  • Also, set up the login information if necessary. In our case, we will just be using Windows Authentication

4. Once the above is properly configured, you should be able to select a database from the dropdown.

• • In our case, we will select AdventureWorksLT2022

Now, we can add a dataset, and we’ll use one of our existing queries from our notes to select every record from the Products table with a thumbnail image of the product.

1. Right-click on your newly created data source and choose Add Data Set

2. Click on the Query tab in the side menu

3. From here, you can either type out an SQL query if you are comfortable with SQL or click on the pencil icon to open our Visual Query Builder and assemble one with a simple interface

• • For our example, the query will be:

SELECT *
FROM SalesLT.Product
WHERE (SalesLT.Product.ThumbnailPhotoFileName NOT IN (SELECT
  SalesLT.Product.ThumbnailPhotoFileName FROM SalesLT.Product WHERE
  SalesLT.Product.ThumbnailPhotoFileName = 'no_image_available_small.gif'))

At this point, if you were following along with your report and database, you should add some controls, such as a table to put your data in and add some fields from your data set.

We started with a pre-configured report, so from here, we’ll go ahead and preview it, and we should be able to see the data we queried neatly displayed in the table now:

If you’d like, you can download the completed sample.

• Note: You will need to adjust the Data Source to target your own instance of the AdventureWorksLT database for it to work properly.

For much more information and details on data binding in ActiveReports.NET, we recommend checking out the ActiveReports.NET Data Binding documentation.

Ready to Start Data Binding? Download ActiveReports.NET Today!