The Baker’s Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services

The Baker’s Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services

re you moving a Windows desktop application to the browser, and sweating bullets, or perhaps just not quite sure about how all the new web and data tools work together? With each passing year, Microsoft offers newer and more powerful tools for building rich database applications on the web. So many and so frequently, in fact, that it can be hard to keep up with the new tools and still meet the requirements of your job! This article will show you how to get the most out of the new features in ASP.NET 3.5. The article will also show how you can use features in LINQ, even if you only use stored procedures for data access. And finally, since most applications use reporting, I’ll throw in a few nuggets on using SQL Server Reporting Services.

The ‘411’ on Visual Studio 2008 and Web Applications
Visual Studio 2008, recently released, includes a new version of ASP.NET (version 3.5). You may wonder why Microsoft didn’t release a version called ASP.NET 3.0. The reason is that the .NET 3.0 framework focused on Windows Presentation Foundation (WPF), Windows Communication Foundation (WCF), Windows WorkFlow (WF), and Windows CardSpaces. So the .NET framework version that accompanies Visual Studio 2008 is 3.5.

Before discussing ASP.NET 3.5, the new Visual Studio 2008 environment will greatly please developers who are building web applications for different versions of the .NET framework. Visual Studio 2008 allows you to “target” a project to a specific version of the framework. Figure 1 shows how you can create a new web project and target a specific version of the .NET framework. In other words, you can now use one version of the development environment to target any of the the .NET 2.0, 3.0, or 3.5 frameworks. (Note that .NET 1.1 is not included.)

Figure 1. Framework-Targeting: Visual Studio 2008 provides the ability to target projects to a specific version of the .NET Framework.

You may wonder, “Does this mean that I can completely uninstall my Visual Studio 2005 environment?” Well, like most things, it depends. For many developers, the answer will be “Yes.” However, if you are also using Business Intelligence Development Studio to create SQL Server 2005 projects for Analysis Services, Integration Services, and Reporting Services, you will want to keep the Visual Studio 2005 environment, because Visual Studio 2008 does not provide new project templates for SSIS/SSAS/SSRS.

Alternatively, if you had SQL Server Developer Edition installed locally, you “could” remove Visual Studio 2005 altogether, and then remove/reinstall SQL Server 2005, which would give you the basic BIDS environment, though some might view that as a great deal of work for limited dividends.

What’s on the Menu?
Let’s get right to the menu. This Baker’s Dozen article provides these 13 tips:

  • Tips 1-3: Using event delegation for reusable data maintenance functions
  • Tip 4: Subclassing master pages
  • Tip 5: ASP.NET AJAX is now built directly into ASP.NET 3.5!
  • Tip 6: Improved support for nested master pages
  • Tip 7: Paging result sets?why you still can’t beat a stored procedure
  • Tip 8: Using LINQ to build a basic data access piece for stored procedures
  • Tip 9: Using LINQ to handle multiple result sets from a stored procedure
  • Tip 10: Building a data-aware web page with LINQ (Part 1 of 2)
  • Tip 11: Building a data-aware web page with LINQ (Part 2 of 2)
  • Tip 12: Using LINQ to XML to handle large XML files
  • Tip 13: The Baker’s Dozen Potpourri?some tidbits for SQL Server Reporting Services 2005
Editor’s Note: This article was first published in the May/June 2008 issue of CoDe Magazine, and is reprinted here by permission.

Tip 1: Building Reusable Data Maintenance Functions with Event Delegation (Part 1 of 3)
At some point, most developers have had to build a web database application that featured several data entry screens. Most of them probably resembled something like Figure 2: an entry screen for employees (or products, or tax codes, etc.), along with a navigation toolbar, and a toolbar for other data maintenance functions.

Figure 2. Basic Database Application: Here’s a basic three-tab entry page with a reusable toolbar.

Building these functions might seem boring or even mundane. However, there’s nothing mundane about situations where developers spend hours and hours duplicating functionality across multiple screens?or implementing the functionality inconsistently. The ideal solution is to reuse code and select reusable methodologies so you can build these data maintenance functions quickly and efficiently.

Writing a reusable user interface (UI) toolbar in a web browser environment presents some interesting challenges, especially if you want to build a separate class for any maintenance functions associated with the toolbar options. For instance, you may have buttons to navigate through records, and the navigation methods might exist in a different library. Fortunately, you can use a technique known as event delegation. In this technique, you will do the following:

  • In the user control, publish an event for each toolbar button.
  • In any web form that contains the user control, associate (or “wire,” if you will) the published event from the first step to any method. The method could be a base method from a base ASP.NET page class from which the web form is derived.

Follow these steps to build some reusable data maintenance functions (working from the inside out):

  1. Create an ASP.NET page class (inherited from System.Web.UI.Page) that will contain public properties and virtual functions for the common editing and navigating tasks.
  2. Create a master page that contains all of the visual controls (toolbar and command buttons).
  3. In the master page, create event handlers for each of the toolbar command buttons.
  4. For each data maintenance page, associate each event handler with the actual corresponding virtual function.

You might be saying, “OK, that makes sense.” Or you may be saying, “Huh???” Well, fret not, the next tip will cover these steps in detail.

Tip 2: Reusable Data Maintenance Functions (Part 2 of 3)
Tip 1 covered the four general steps for producing a reusable solution; this tip and the next covers those steps in detail.

First, you need to create the ASP.NET page class that will contain public properties and virtual functions for the common editing and navigating tasks. Start by creating a new ASP.NET page class:

   public class cgsBaseWebEditPage:System.Web.UI.Page

In the ASP.NET page class, you’ll need to define properties for generic data maintenance, such as the number of rows in the current result set that the user is navigating, the relative row position based on the current view order, and so forth:

   public int ParentPosition { get; set; }    public int ParentPrimaryKey  { get; set; }    public DataView ParentView    { get; set; } 

Finally, still in the ASP.NET page class, you’ll want to add base virtual methods to handle the core maintenance tasks. For instance, here’s the method to navigate to the next row, based on the current view:

   public virtual void NavigateNext(object sender, EventArgs e)    {      if (this.ParentPosition <          this.ParentView.Count)      {         this.ParentPosition++;         this.SetCurrentKey();         this.RetrieveDataForKey();      }   }

The method simply increments the ParentPosition property, sets the ParentPrimaryKey value, and retrieves the data from the database based on the ParentPrimaryKey value.

See also  Comparing different methods of testing your Infrastructure-as-Code

The method that sets the current key value is as follows:

   public virtual void SetCurrentKey()   {      this.ParentPrimaryKey =          Convert.ToInt32(      this.ParentView[this.ParentPosition]         [this.ParentKeyName]);   } 

Finally, the method RetrieveDataForKey is simply a stub virtual method that you can override when you subclass this ASP.NET page class. In your subclassed web page, you can access the ParentPrimaryKey value to retrieve the full row from the database for that key.

OK, I hope that was easy. In the next tip, you'll build the master page and create the event handlers to cover steps 2-4.

Tip 3: Reusable Data Maintenance Functions (Part 3 of 3)
Now that you've created the ASP.NET page class, you can focus on the master page, the event handlers, and then create a web page that consumes all of this.

From the list of steps in Tip 1, you'll need to create a master page that contains all of the visual controls (toolbar and command buttons). You can create any type of master page with command buttons like those shown in Figure 2.

Next, you can create event handlers for each of the toolbar command buttons. Here is one example, based on the NavigateNext method. At the top of the master page you can specify the following event handler:

   public event EventHandler NextEventClick;   protected void OnEventNext(EventArgs e)   {      if (this.NextEventClick != null)         this.NextEventClick(this, e);   }

Then, in the Click event of the "next" command button in the MasterPage toolbar, you can insert the following code to call the OnEventNext method:

   protected void btnNext_Click(object sender,       ImageClickEventArgs e)   {      OnEventNext(e);   }

This will, in turn, call any method that you "wire" to the NextEventClick method.

Finally, you can wire the NextEventClick method to the base method NavigateNext (from the base web edit page) when you create any application web page, (a customer page, a product page, and so forth):

   DataMaintenanceMaster oMaster =       (DataMaintenanceMaster)this.Master;   oMaster.NextEventClick +=       new EventHandler(this.NavigateNext);  

Tip 4: Subclassing Master Pages
If you're strong enough in object-oriented development, you may have wondered about subclassing the master page. Here's an example that shows how to create an abstract class for all future master pages:

   public abstract class cgsBaseMasterPage       : MasterPage    {       public abstract Label HeaderText { get; }      public abstract Label FooterText { get; }    } 

Then, in any master page that you create, simply refer to the abstract class:

   public partial class cgsAppMasterPage :       cgsBaseMasterPage    {          public override Label HeaderText {         get { return lblHeader; } }         public override Label FooterText {         get { return lblFooter; } }         protected void Page_Load(object sender,          EventArgs e)        {        }   }  

Tip 5: AJAX Now Built In!
Roughly two years ago, Microsoft introduced ASP.NET AJAX, which allowed developers to build more responsive user interfaces. Generally, AJAX provides tools so that developers can place web content inside update panels, for purposes of doing partial page refreshes. The end result is that web applications appear more like a standard Windows Forms application, as opposed to the common web scenario, where the browser must fetch and refresh an entire page for every change.

Initially, ASP.NET AJAX was a separate download, because Microsoft released AJAX after Visual Studio 2005. However, AJAX is now part of Visual Studio 2008 and ASP.NET 3.5. After you install Visual Studio 2008, you'll immediately see the AJAX extensions as part of the toolbox (see Figure 3).

Figure 3. AJAX Extensions: The figure shows the AJAX extensions in the Visual Studio toolbox.

Note that while Visual Studio 2008 automatically ships with the AJAX extensions, the AJAX Control Toolkit is still available as a separate download. The toolkit is a set of customizable and extensible ASP.NET AJAX extenders and controls to create interactive web experiences.

Tip 6: Improved Support for Nested Master Pages
ASP.NET 2.0 introduced the concept of master pages, which allowed web developers to easily build web pages with consistent layout. ASP.NET 2.0 also allowed you to build nested master pages so you could build a master page based on another master page. Unfortunately, the WYSIWYG editor in Visual Studio 2005 didn't allow you to edit the nested master page?you had to edit the master page manually.

Fortunately, the WYSIWYG editor in Visual Studio 2008 does allow you to edit nested master pages. Scott Guthrie wrote a good blog post on this subject that includes an example of creating single-column and multi-column master pages.

Tip 7: Paging: Why You Still Can't Beat a Stored Procedure
A common task in web applications is paging result sets. A user may run a query that results in 500 matching rows, but you may only want to show the user 20 rows at a time. Several of the data-bound controls (such as the GridView) offer paging capabilities?but these generally work by retrieving all 500 rows from the database, persisting them in the application layer, and then paging them to the user, one page at a time.

While this approach works, it also possibly retrieves a large number of rows that might never be displayed, and those get stored in the application layer. A more efficient (and scalable) method is to manage the paging in a database stored procedure.

In the example with the 500 rows, suppose you are showing 20 rows at a time, in name order. In SQL Server, you can write the query to return the 500 rows into a temporary table or a Common Table Expression (CTE), and then execute a subsequent query to retrieve and return rows 1 through 20. Then if someone clicks "Next" on a navigation toolbar, you can run the same process to return rows 21 through 40. (In the application, you'll need to track the relative row position that you want to use as the starting point for a subsequent result set.)

SQL Server 2005 makes it easy to handle this through the ROW_NUMBER function that assigns a sequential ranking to each row in a result set, relative to whatever sort order you specify. For example, the following SQL Server 2005 code stores the results of the first query into a CTE:

   WITH CustListTemp AS   (SELECT      CustomerID, LastName, FirstName,     Address, City, State, Zip,      ROW_NUMBER() OVER (ORDER BY      CASE @SortCol       WHEN 'LASTNAME'         THEN LastName + Firstname       WHEN 'ZIP'           THEN ZIP+LastName+Firstname       ELSE LastName + Firstname      END)    AS RowNum   FROM Customers)

A subsequent query against the CTE retrieves the relative row range (for example, rows 21-40, rows 101-120, etc.), using supplied parameters for the starting row (@StartRowIndex) and the number of rows to retrieve (@MaxRows):

   SELECT TOP (@MaxRows) CustomerID, LastName,     FirstName, Address, City, State, Zip,      RowNum FROM (SELECT CustListTemp.*,     (SELECT COUNT(*) from CustListTemp) AS RecCount   FROM CustListTemp )CustList    WHERE RowNum BETWEEN      (CASE @StartRowIndex       WHEN -1 THEN  RecCount - @MaxRows       ELSE  @StartRowIndex END )   AND      (CASE @StartRowIndex       WHEN -1 THEN ( RecCount )- @MaxRows       ELSE @StartRowIndex END) + @MaxRows    THEN 1   ELSE 0 END  = 1

I covered this technique in detail in an earlier article that first appeared in the March/April 2007 issue of CoDe Magazine.

Tip 8: Using LINQ to Build a DAL for Stored Procedure Access
Before I start the next tip, I'd like to respond to something that I often see developers doing with LINQ (and until recently, I did as well): querying into an IEnumerable, and then looping through the IEnumerable to populate a List class. Usually, the code will look something like this:

   foreach (VendorResults oRec in oVendorResults)      oListResults.Add(oRec); 

While that's certainly valid code, you can instead populate the list using a method overload that specifies an IEnumerable:

   List oListResults = new        List(oVendorResults); 

Most developers still use stored procedures for data access. You can do that and still leverage LINQ's capabilities by calling stored procedures using strong typing. The next two tips will cover examples of this.

First, here's a simple example of calling a stored procedure that returns one result set. LINQ to SQL contains an interface called ISingleResult that you can use to cast a return object from a LINQ call to a stored procedure:

   AdventureWorks db = new        AdventureWorks(this.GetConnString());   XElement XmlOrders = new XElement("Orders",      new XElement("OrderIDs",      new XElement("IDpk", 1)),      new XElement("OrderIDs",      new XElement("IDpk", 2)),      new XElement("OrderIDs",      new XElement("IDpk", 3)));   ISingleResult POHeaderData =       db.GetPOSummary(XmlOrders);

Generally speaking, the code sample above does two things:

  • Creates an XML XElement object for a variable number of keys as a stored procedure parameter (the stored procedure parameter will shred the XML into a table variable as part of the query). If you're interested in seeing the function that shreds an XML string into a table variable, check this article from the September/October 2007 CoDe Magazine issue.
  • Create an object called POHeaderData, of type SingleResult.
  • SqlMetal (which I'll cover in the next tip) creates a default type of GetPOSummaryResult from the name of the stored procedure (GetPOSummary).

Tip 9: Using LINQ to Handle Multiple Result Sets
Many stored procedures return multiple result sets. LINQ to SQL provides the IMultipleResults interface to handle calls to these types of stored procedures:

   // Call a stored proc that returns    // multiple results   IMultipleResults result =       db.GetPODetails(XmlOrders);   IEnumerable  oHeader =       result.GetResult();   IEnumerable oDetails =       result.GetResult();

Tip 10: A LINQ Multiple Result Sets Example (the Back End)
The next two tips combine some of the LINQ content from the last two tips to build a small web-based example. Because I often use an aging receivables example to illustrate reporting/result set techniques, I'll use that one. I don't use LINQ to SQL nearly as much as I use LINQ to XML/Objects/DataSets; however, I do like the ability to discover stored procedures and SP parameters in a strongly-typed manner.

Suppose you want to:

  1. Write a stored procedure that returns multiple result sets for an aging receivables report.
  2. Create a strongly-typed context to the stored procedure and the result sets.
  3. Define a "composite" class that stores multiple sets in one class (similar to a "DataSet" but without the overhead).
  4. Use the result set classes to design a set of GridViews in ASP.NET.
  5. Use LINQ to SQL to call the stored procedure, return multiple result sets, and bind them to the grid.
Figure 4. Using GridViews: Here's a web page with several formatted GridViews.

In this tip, I'll walk through the back-end steps. In the next tip I'll cover the front-end steps. The end goal is the test web page in Figure 4.

First, in the interest of keeping this article from being too long, I won't include the listing for the stored procedure here. Suffice it to say that the specifics of the stored procedure aren't particularly relevant here?the stored procedure receives an XML string of customer IDs, an "as-of" date, and a flag indicating whether to return only summary or both detail and summary information.

Second, you need to create a strongly-typed context to the stored procedures in the database. You can do this by creating a LINQ to SQL DBML file, or you can use SQLMetal. SQLMetal comes with Visual Studio 2008, and is stored in the Program Files Microsoft SDKs Windows v6.0A BIN folder. You can use these command-line parameters to generate a context for the stored procedures in a database:

   SqlMetal.exe /server:localhost                 /database:ConstructionDemo                 /sprocs                /code:C:ConstructionDemo.cs

After SQLMetal generates the context file, you can add it to your project. Listing 1 shows an excerpt of the generated context?specifically, the generated context for the stored procedure. Notice that the context class contains a method for the stored procedure along with typed references to the parameters and result sets. The four result sets are GetAgingResults1, GetAgingResults2, etc. I'll talk about those names in few moments.

There's something "missing" from this class file, something that you'll need. Can you guess what it is? Well, here's a hint: while there are classes for each of the four result sets (details, summary, brackets, clients), there's no class that combines the four result sets into one class. You can address that by creating a composite/container class that combines (or "pairs") sets of lists for the individual result sets.

Listing 2 shows a class called AgingResultSet that combines multiple List<> class properties for each of the result set names. Eventually, you'll do the following:

  • Populate four list classes for the four result sets.
  • Create an instance of AgingResultSet.
  • Store a List of type GetAgingResultSets1 into the List property DetailResult, GetAgingResultSets2 into SummaryResult, etc. (You'll see this in detail when I cover Listing 3?I'm just giving you a quick look forward.)

One other note about the class in Listing 2?for each of the four result sets, there is a simple GetResults method for each of the four List class results. These are purely for the client-side data-aware controls to "discover" the result set classes, so that you can design against them.

Finally, now that you've built a strongly-typed context to the stored procedure, and you've established a composite class to hold the result sets, you can build a simple data access function to call the stored procedure. Listing 3 uses the technique from Tip 9 to call a stored procedure using the IMultipleResults interface. In essence, Listing 3 does the following:

  • Calls the context method GetAging, and returns an instance of IMultipleResults.
  • Use the IMultipleResult's GetResult method to access each of the four result sets.
  • Create a new instance of the AgingResult composite class (see Listing 2).
  • Assign the four result sets into the instance of the AgingResult class. Note the new object's initialization syntax, where you can specify the properties of the instantiated class without needing a constructor for the class (in Listing 3).
  • Return the instance of the AgingResult class.

Tip 11: A LINQ Multiple Result Sets Example (the Front End)
Part 2 of this process is to build the client-side piece to archive the web page in Figure 4. The goal is to design some basic ASP.NET GridView controls against the result set classes from the context.

Prior to Visual Studio 2008 and ASP.NET 3.5, designing a GridView against any type of result set class could be a bit tricky. Fortunately, the ObjectDataSource control in ASP.NET 3.5 (used for defining the data source) has some improved visibility?this allows you to point the web form GridViews to the result set classes, populate the GridViews with the necessary columns from the result sets, and allows you to customize the output of the GridView controls.

You'll follow these steps:

  1. Create a new ASP.NET web application and add references to any DLL(s) associated with the Listings from the previous tip.
  2. Create a new web page and drop four GridView controls onto the page.
  3. For the first GridView, right-click and select "New Data Source" (see Figure 5).
    Figure 5. Design Model: You can use the GridView Tasks dialog to select a data source for the GridViews.
    Figure 6. Choose Data Source: In the Data Source Configuration Wizard, select an ObjectDataSource, and give it a name.

  4. Visual Studio 2008 will display the ObjectDataSource dialog box (see Figure 6). Specify a meaningful name.
  5. Select the business object and method name (see Figure 7 and Figure 8, respectively) for the corresponding GridView). Repeat this process for each of the four GridViews.
    Figure 7. Choose a Business Object: Select the business object that you'll use to retrieve or update data.
    Figure 8. Choose the Retrieval Method: Select the object method that retrieves data.

  6. ?
    Figure 9. Modify Generated Columns: After you define the GridView's data source, you can modify the generated columns.

    At this point, each of the four GridView controls will have column definitions from the result set classes. You can access the column collection editor (see Figure 9) for each of the GridViews, to customize alignments, change heading text, or format columns for date and currency values.

  7. Finally, you'll need to write some code to call the data access class from the last tip, and to populate the GridView controls. Listing 4 provides an example of this. Note that in the code, you need to set the DataSourceID to null because you're about to override the design-time source with an actual source (of the same type). Also note that I'm creating an instance of the composite class and that I'm using the properties from the class that were populated back in the data access class:
       oResult = oDa.GetAgingData(XmlCustomers,    new DateTime(2006, 1, 1), true);   this.gdvDetails.DataSourceID = null;   this.gdvSummary.DataSourceID = null;   ...   this.gdvDetails.DataSource =       oResult.DetailResult;   this.gdvSummary.DataSource =       oResult.SummaryResult;

Tip 12: Using LINQ to XML to Handle Large XML Files
By now, you've likely seen at least one example of using LINQ to XML. Here's a very simple example for using LINQ syntax to query XML data using C#:

   XElement XmlAddr = new XElement("Addresses",      new XElement("AddressRec",      new XAttribute("EmployeeID", 1),      new XElement("FirstName", "Kevin"),      new XElement("LastName", "Goff"),      new XElement("Address", "111 Main Street"),      new XElement("City", "Philadelphia"),      new XElement("State", "PA"),      new XElement("Zip", "11111")));   XDocument oDoc = new XDocument(XmlAddr);   var xResults =       from oXml in oDoc.Descendants("AddressRec")      where (string)oXml.Element("State") == "PA"      orderby (string)oXml.Element("Zip")      select oXml;

If you use Visual Basic, here's the equivalent code. Note that you can create XML literals in Visual Basic, as follows:

   Dim XmlAddr = _                          Kevin         Goff         
111 Main Street
Philadelphia PA 11111

Then you can use Visual Basic syntax, which is considerably less complicated than the C# equivalent:

   Dim Xdocument As XDocument = _      New XDocument(XmlAddr)   Dim query =       From c In Xdocument.Descendants("AddressRec") _      Where c..Value = "PA" _      Order By c..Value _      Select New With {c..Value}   Me.DataGridView1.DataSource = query.ToList()  

That's certainly "nice", but the proverbial 500-pound elephant in the room is deciding how you would handle large XML files. Certainly, you don't want to load an entire large XML file into memory. What you can do is set up an extension method to perform a streaming function (see Listing 5), and then refer to the extension method in the LINQ to XML IN syntax (see Listing 6 and Listing 7 for C# and Visual Basic, respectively).

Author's Note: The code in Listing 5 comes from the Microsoft XML Team WebLog, where you'll find the code and further explanation. Kudos to the Microsoft XML Team for providing this!

At the beginning of Listing 5, you can refer to the extension method (StreamElements) as your source for the query. The extension method will perform the streaming of the large XML for you:

   from oXML in StreamElements(xmlFile,"CustOrders") 

If you actually set a breakpoint and stepped through the code, you'd observe that the code:

  • Loops through the code in the extension method (in the while (reader.Read()) statement).
  • Creates an individual XElement object, reads an element from the XmlReader, and then returns the XElement object.
  • Comes back to the LINQ to XML code block (specifically the WHERE clause) and evaluates the XElement object (implicitly returned from the extension method) to see if it matches the WHERE statement.

In this context, you can almost think of the extension method as a delegate to handle the element-by-element streaming of the large XML file.

Tip 13: Baker's Dozen Potpourri?Some Miscellaneous Tidbits for SQL Server Reporting Services 2005
Did you just install SharePoint in a server environment where SQL Server Reporting Services 2005 resides? Did you also notice afterwards that SSRS stopped working? That's because SharePoint can take over the port number for the default web site. You can address that situation by going into IIS and changing the port number for your default web site. (I changed mine to 8080.)

Or...are you having problems trying to run a report for a specific parameter, and also trying to send it directly to a PDF file? Here's the "magic" URL syntax (note that I'm using Port 8080!):

   int  MyCustomerID = 1001;   string OutURL =     "http://localhost:8080/ReportServer?/";   OutURL += "MyReportProject/MyReport";   OutURL += "&rs:Command=Render&rs:Format=PDF";   OutURL += "&CustomerID=" +   MyCustomerID.ToString();

Or have you ever tried to drag data from a report DataSet into a report header or footer? SSRS doesn't allow this. You can get around it by doing the following:

  • Define a report parameter associated with the data you want to display in the header/footer.
  • Set the hidden property to true for the parameter.
  • Define the default value for the parameter as the dataset/column associated with the actual database value.
  • Finally, in the report heading, reference the parameter name instead of the dataset name.

Haven't helped you yet? How about this one? You need to develop SSRS reports so that users will only see data associated with their rights. (For example, you might have database rules in the form of tables that define User XYZ as being allowed to see only data for specific product categories or specific regions.) The key is to identify the current user running the report so that you can pass that on to any database query/stored procedure. You can obtain that information either from the User!UserId collection property in SSRS, or from the System_User system variable in SQL Server.

Final Thoughts
The next Baker's Dozen article will present an article I've been planning for a long time and will finally put together: 13 Tips for Implementing Common Design Patterns in C# 3.0. Check my blog for any follow-up tips, additional resources, and notes on this and previous Baker's Dozen articles. You can find the entire source code for this article on my web site.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist