9:16 AM Edit This 0 Comments »

Understanding Idempotency in .NET Core APIs: Building Reliable and Duplicate-Safe Applications

 

Introduction

In modern distributed systems, network failures, retries, and user actions such as repeatedly clicking a submit button can result in the same request being sent multiple times. Without proper safeguards, duplicate requests may create duplicate records, process payments multiple times, or generate inconsistent data.

This is where idempotency becomes essential.

In this blog, we'll explore what idempotency is, why it matters, and how to implement it effectively in ASP.NET Core Web APIs.

What is Idempotency?

An operation is idempotent if performing it multiple times produces the same result as performing it once.

Example: POST /api/orders

Consider an API that creates an order:

Without idempotency:

  • First request → Order created
  • Retry request → Another order created

Result: Duplicate orders.

With idempotency:

  • First request → Order created
  • Retry request → Previously created order returned

Result: Only one order exists.

 

 

 

 

Why Do We Need Idempotency?

Idempotency helps solve several real-world challenges:

1. Network Timeouts

A client may not receive a response because of network issues and may resend the request.

2. Client Retries

Mobile applications and frontend applications often automatically retry failed requests.

3. User Errors

Users may click the Submit button multiple times.

4. Distributed Systems

Microservices frequently communicate through asynchronous and potentially unreliable networks.

Without idempotency, these scenarios can cause:

  • Duplicate payments
  • Duplicate orders
  • Duplicate database records
  • Inconsistent business data

HTTP Methods and Idempotency

HTTP specification classifies methods based on idempotent behaviour.

Method

Idempotent

GET

Yes

PUT

Yes

DELETE

Yes

HEAD

Yes

POST

No

PATCH

Depends

 

Implementing Idempotency in ASP.NET Core

The most common approach is using an Idempotency Key.

What is an Idempotency Key?

An idempotency key is a unique identifier generated by the client and sent with every request.

The server stores this key along with the response.

If a request arrives with the same key:

  • Server checks existing records
  • Returns the previously stored response
  • Avoids executing business logic again

 

 

 

 

 

Step 1: Create an Idempotency Entity

public class IdempotencyRecord

{

public Guid Id { get; set; }

 

public string Key { get; set; }

 

public string ResponseBody { get; set; }

 

public int StatusCode { get; set; }

 

public DateTime CreatedAt { get; set; }

}

Step 2: Configure DbContext

public class ApplicationDbContext : DbContext

{

public DbSet<IdempotencyRecord> IdempotencyRecords { get; set; }

 

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)

: base(options)

{

}

}

 

 

 

Step 3: Create Idempotency Filter

public class IdempotencyFilter : IAsyncActionFilter

{

private readonly ApplicationDbContext _dbContext;

 

public IdempotencyFilter(ApplicationDbContext dbContext)

{

_dbContext = dbContext;

}

 

public async Task OnActionExecutionAsync(

ActionExecutingContext context,

ActionExecutionDelegate next)

{

if (!context.HttpContext.Request.Headers.TryGetValue(

"Idempotency-Key",

out var key))

{

await next();

return;

              }

 

var existingRecord = await _dbContext.IdempotencyRecords

.FirstOrDefaultAsync(x => x.Key == key);

 

if (existingRecord != null)

{

context.Result = new ContentResult

{

Content = existingRecord.ResponseBody,

StatusCode = existingRecord.StatusCode,

ContentType = "application/json"

};

 

return;

}

 

var executedContext = await next();

 

if (executedContext.Result is ObjectResult result)

{

var response = JsonSerializer.Serialize(result.Value);

 

_dbContext.IdempotencyRecords.Add(

new IdempotencyRecord

{

Key = key,

ResponseBody = response,

StatusCode = result.StatusCode ?? 200,

CreatedAt = DateTime.UtcNow

});

 

await _dbContext.SaveChangesAsync();

}

}

}

Step 4: Register Filter

builder.Services.AddScoped<IdempotencyFilter>();

Step 5: Apply to Controller

[HttpPost]

[ServiceFilter(typeof(IdempotencyFilter))]

public async Task<IActionResult> CreateOrder(CreateOrderRequest request)

{

var order = new Order

{

Id = Guid.NewGuid(),

Amount = request.Amount

};

 

return Ok(order);

}

 

Common Use Cases

·        Payment Processing

·        Order Creation

·        Ticket Booking

Conclusion

Idempotency is a critical design principle for building resilient and reliable APIs. While HTTP methods like GET, PUT, and DELETE are naturally idempotent, POST operations often require additional mechanisms such as Idempotency Keys to prevent duplicate processing.

In ASP.NET Core, implementing idempotency using middleware, action filters, Redis, or database-backed storage helps ensure that retries, network failures, and user errors do not compromise the integrity of your system.

By adopting idempotency, you can build APIs that are more reliable, scalable, and production-ready—especially in domains like payments, e-commerce, booking platforms, and financial services.

·        

 

 

 

 

 

 

 

 

 

 

 

 

 


Temporal Tables with Entity Framework Core

3:37 AM Edit This 0 Comments »

A Deep Dive into Temporal Tables with Entity Framework Core

Modern applications often need to answer questions like:

  • What did this record look like yesterday?
  • Who changed this value and when?
  • Can we restore previous data states?

Traditionally, solving this required complex audit tables, triggers, or manual logging. But with SQL Server Temporal Tables and Entity Framework Core (EF Core), this becomes significantly simpler and more maintainable.

In this article, we’ll explore:

What temporal tables are
How they work internally
How to configure them in EF Core
How to query historical data
Real-world use cases and best practices


📌 What Are Temporal Tables?

A temporal table is a system-versioned table that automatically tracks the full history of data changes.

It consists of two parts:

+-----------------------+        +---------------------------+

|   Current Table       |        |     History Table         |

|-----------------------|        |---------------------------|

| Id | Name | Salary    |        | Id | Name | Salary        |

|    |      |           |        | ValidFrom | ValidTo       |

+-----------------------+        +---------------------------+

🔄 How It Works

Every time a record is:

  • Updated
  • Deleted

SQL Server automatically:

  • Moves the old version to the history table
  • Updates the current table with the new values

    Why Use Temporal Tables?

    • Automatic auditing (no custom code required)
    • Time-travel queries
    • Data recovery
    • Regulatory compliance (SOX, GDPR, etc.)
    • Cleaner design compared to manual audit tables

    Setting Up Temporal Tables in EF Core

    Temporal table support was introduced in EF Core 6+, specifically for SQL Server.


    Step 1: Define Your Entity

    C#

    public class Employee

    {

    public int Id { get; set; }

    public string Name { get; set; }

    public decimal Salary { get; set; }

    }

    Step 2: Configure Temporal Behavior

    Inside your DbContext:

    C#

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

    modelBuilder.Entity<Employee>()

    .ToTable("Employees", tableBuilder => tableBuilder.IsTemporal(temporal =>

    {

    temporal.HasPeriodStart("ValidFrom");

    temporal.HasPeriodEnd("ValidTo");

    temporal.UseHistoryTable("EmployeeHistory");

    }));

    }



    What This Configuration Does

    +----------------------------------------------------+

    | EF Core Configuration                              |

    +----------------------------------------------------+

    | IsTemporal() → Enables system versioning           |

    | HasPeriodStart("ValidFrom") → Start time column    |

    | HasPeriodEnd("ValidTo") → End time column          |

    | UseHistoryTable("EmployeeHistory") → History table |

    +----------------------------------------------------+


    Step 3: Apply Migration


    dotnet ef migrations add EnableTemporal

    dotnet ef database update



    Querying Temporal Data

    This is where temporal tables shine 

    EF Core provides built-in temporal query extensions.


    1. Query Data at a Specific Point in Time


    var employees = context.Employees

    .TemporalAsOf(DateTime.UtcNow.AddDays(-7))

    .ToList();



    2. Retrieve Full History of a Record

    var history = context.Employees

    .TemporalAll()

    .Where(e => e.Id == 1)

    .ToList();



    3. Query Changes Between Dates

    var changes = context.Employees

    .TemporalBetween(startDate, endDate)

    .ToList();

     


    4. Get Records Active Within a Range

    var active = context.Employees

    .TemporalFromTo(startDate, endDate)

    .ToList();



    Performance

    • History tables grow quickly
    • Index period columns:

    SQL

            CREATE INDEX IX_EmployeeHistory_Period

            ON EmployeeHistory (ValidFrom, ValidTo);



    🔄 Updates & Deletes

    • Deletes are not permanent — they move data to history
    • Bulk updates may need extra care

    💡 Real-World Use Cases

    🧾 Audit Tracking

    Track who changed salary, address, or sensitive data.

    🐞 Debugging

    Reproduce bugs by restoring historical data state.

    🏦 Financial Systems

    Maintain legally required transaction history.

    🗂️ Data Recovery

    Restore accidental deletions or incorrect updates.


    Best Practices

    Use clear naming for history tables
    Add indexes on temporal columns
    Avoid excessive historical queries in high-load APIs
    Combine with soft delete if needed
    Periodically archive old history for performance


    When NOT to Use Temporal Tables

    Avoid temporal tables when:

    • You need cross-database support (only SQL Server)
    • You require custom audit logic (e.g., user IDs per change)
    • Data volume grows extremely fast without archiving

    Conclusion

    Temporal tables in EF Core are a powerful, low-effort solution for tracking data history. With just a few lines of configuration, you gain:

    Automatic versioning
    Built-in auditing
    Time-travel queries

    They eliminate the need for complex auditing systems while improving reliability and maintainability.



    2:30 AM Edit This 0 Comments »

    The real reason SELECT * queries are bad: index coverage
    Are SELECT * queries bad? Sure, everyone know that. But, why?
    It's returning too much data, right?
    That's the common answer, but I don't think it's the right one. If you're working with a reasonably normalized database, the actual network traffic difference is pretty small.
    Let's take a look at a sample. The following two queries select 326 rows from the TransactionHistoryArchive table in the AdventureWorks database (which has a total of 89K rows). The first uses a SELECT * query, the second selects a specific column:

    SELECT * FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100
    SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100

    In this case, the difference in network traffic is only 15K, roughly a 10% difference (180K vs. 165K). It's worth fixing, but not a huge difference.
    SELECT * makes the Table / Index Scan Monster come
    Often, the bigger problem with SELECT * is the effect it will have on the execution plan. While SQL Server primarily uses indexes to look up your data, if the index contains all the columns you’re requesting it doesn’t even need to look in the table. That concept is known as index coverage. In the above example, the first query results in a Clustered Index Scan, whereas the second query uses a much more efficient Index Seek. In this case, the Index seek is one hundred times more efficient than the Clustered Index Scan.

    Unless you've indexed every single column in a table (which is almost never a good idea), a SELECT * query can't take advantage of index coverage, and you're likely to get (extremely inefficient) scan operations.
    If you just query the rows you'll actually be using, it's more likely they'll be covered by indexes. And I think that's the biggest performance advantage of ignoring SELECT * queries.
    The Stability Aspect
    SELECT * queries are also bad from an application maintenance point of view as well, since it introduces another outside variable to your code. If a column is added to a table, the results returned to your application will change in structure. Well programmed applications should be referring to columns by name and shouldn't be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.
    Shameless Plug: I go into this (and a lot other important performance tips) in more detail in a soon-to-be-released book for SitePoint.
    Published Wednesday, July 18, 2007 11:56 PM by Jon Galloway
    Filed under: ,
    Comments
    # re: The real reason SELECT * queries are bad: index coverage
    "If a row is added to a table..."
    Should be: "If a column is added to a table..."
    Thursday, July 19, 2007 4:55 AM by Goran
    # re: The real reason SELECT * queries are bad: index coverage
    Great post Jon...
    The point about stability and ordinal position is a very real one, and I strongly agree with the practice of referencing columns by name*. You'll find this out the hard way if you use most database sync applications to migrate changes from one environment to another.
    Even if you don't rely on ordinal position, it's a good idea to have your change scripts drop and recreate a table when columns are added to it, if only for schema consistency reasons.
    Can't wait for the book!
    * ...he says hypocritically, knowing full well that SubSonic relies on the assumption that the name field is in the second ordinal position
    Thursday, July 19, 2007 7:14 AM by Eric Kemp
    # The real reason SELECT * queries are bad: index coverage
    You've been kicked (a good thing) - Trackback from DotNetKicks.com
    Thursday, July 19, 2007 9:53 AM by DotNetKicks.com
    # re: The real reason SELECT * queries are bad: index coverage
    Nice to read the reason why this is pushed so heavily by DBAs.
    I will disagree with your statement that SELECT * makes maintenance of an application more difficult. I use a hybrid OR/M (homegrown) that maps many related tables together and returns all columns so that the developer can decide what to do with the data. If someone is to add a column now, all I have to do is update the code to fill the extra column.
    If I were selecting specific columns, i would still have to update the code, but additional any views and procs that return data to the app.
    Thursday, July 19, 2007 11:08 AM by Jerry
    # re: The real reason SELECT * queries are bad: index coverage
    Another reason to avoid SELECT * is with views that access other views. You'll have to recompile each view in the correct dependency order or you'll get errors. You also run the risk of introducing problems where the new column matches the name of another column in the view and you can get ambiguous results.
    Thursday, July 19, 2007 11:38 AM by Chris Miller
    # SELECT * 的真相: 索引覆盖(index coverage)
    SELECT *的效率很糟糕吗?当然,所有人都知道这一点,但是为什么呢?
    Thursday, July 19, 2007 12:25 PM by Goodspeed
    # re: The real reason SELECT * queries are bad: index coverage
    good one. A good reason to not use Select *
    Thursday, July 19, 2007 1:05 PM by Vikram
    # 为什么使用Select * 查询不好
    今天在asp.net博客上看到一篇好文,探究使用select *在效率和可维护性上的问题,地址如下:weblogs.asp.net/.../the-real-reason-select-queries-are-bad-index-coverage.aspx 大致意思如下: 对于大多数人来说,使用select *的带来的问题直观上是返回了太多的数据,但是经过试验证实这只是小问题。使用select *真正的麻烦来自于索引。由于数据库实际是用索引来查询数据
    Thursday, July 19, 2007 1:17 PM by ikeepSmile
    # re: The real reason SELECT * queries are bad: index coverage
    Good one. Definitely will keep this in mind.
    Question, does it also apply with
    select foo
    from bar
    where exists
    ( select * from tarfu where tarfu.idee = bar.idee)
    or does the query optimizer recognize that it doesn't require any complex index scanning?
    Thursday, July 19, 2007 2:29 PM by mcgurk
    # re: The real reason SELECT * queries are bad: index coverage
    @mcgurk:
    SQL Server's Query Optimizer knows the difference in EXISTS -case as it tests only a boolean value, anyway.
    So it doesn't matter within EXISTS whether you use SELECT * or something else.
    Thursday, July 19, 2007 3:21 PM by Jemm
    # re: The real reason SELECT * queries are bad: index coverage
    I'm curious what other variations you tried. Did you reverse the queries in your batch? Did you add more than one column to your other select? What happens when you explicitly name all of the columns in the table? The reason I ask is that databases are actually really tough to benchmark and profile. It can be nearly impossible when multiple clients are connected as well. Your first query undoubtedly placed all of the pages in RAM that the second query needed so the fact that it ran so much faster doesn't mean much.
    Thursday, July 19, 2007 7:34 PM by Sam Corder
    # re: The real reason SELECT * queries are bad: index coverage
    @Sam Corder
    Great points.
    1) Yes, Index Coverage will only prevent a scan if all the columns are covered. Adding all columns to a select query wouldn't help. However, if SELECT * you're pretty much guaranteed you won't take advantage of index coverage; if you SELECT only the columns you need you (or your DBA) has the opportunity to add selective indexes to frequently used queries.
    You also have the opportunity of adding frequently used columns to indecies via SQL Server 2005's "index with included columns" feature: msdn2.microsoft.com/.../ms190806.aspx
    2) I did test the other order, and I cleared cache between all runs. Try this query, you should see that the SELECT * queries show 50% load and the SELECT column query takes 0% load, regardless of order:
    USE AdventureWorks
    GO
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO
    SELECT * FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100
    GO
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO
    SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100
    GO
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO
    SELECT * FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100
    GO
    DBCC FREESESSIONCACHE
    DBCC FREEPROCCACHE
    DBCC FREESYSTEMCACHE('ALL')
    CHECKPOINT
    DBCC DROPCLEANBUFFERS
    GO
    SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100
    GO
    Thursday, July 19, 2007 8:17 PM by Jon Galloway
    # SELECT * 的真相: 索引覆盖(index coverage) 。
    SELECT * 的真相: 索引覆盖(index coverage) 。
    Friday, July 20, 2007 12:59 AM by 勤勤同学
    # re: The real reason SELECT * queries are bad: index coverage
    Great article Jon,
    Does this still apply even when you want to get all columns from the row? Should you still write out each column name in the select query instead of *?
    thanks
    Friday, July 20, 2007 8:28 AM by Justin
    # re: The real reason SELECT * queries are bad: index coverage
    Indexes, and it is also very simple - you quiery ONLY what you need.
    There's a reason, if you check top 5 internet dating sites, most of them are running on tens of servers (and one on at least a 100), and one - on 3. If you don't get big, it is easy to cover up sloppiness by throwing hardware at it, but that will also cost ya.
    Reason #2 is also obvious - if you need just one value, get a value, not an entire row:
    Select Value1 from Table1 Where Key=123
    is way better than
    Select * from Table1 Where Key=123
    , and use appropriate tools to get it to an application (NOT a Recordset). Writing "Select * " justifies sloppy code in this case as well.
    Friday, July 20, 2007 5:15 PM by SmiLie
    # F??bio Pedrosa » Why SELECT * Queries are bad
    Pingback from F??bio Pedrosa » Why SELECT * Queries are bad
    Friday, July 20, 2007 6:51 PM by F??bio Pedrosa » Why SELECT * Queries are bad
    # re: The real reason SELECT * queries are bad: index coverage
    its really fantastic..thank you verymuch.
    Thursday, July 26, 2007 10:42 AM by kusuma
    # re: The real reason SELECT * queries are bad: index coverage
    Does this is also true when relating to count(*)?
    I mean, if I do something like:
    SELECT count(*) FROM Table

    iis 7.0

    3:34 AM Edit This 0 Comments »

    Introduction

    Visual Studio comes with an inbuilt web server. No doubt the inbuilt web server comes handy during development. However, finally your web site needs to sit inside Internet Information Services (IIS). If you are an ASP.NET developers you are probably familiar with IIS6. The new generations of Windows namely Windows Vista and Windows Server 2008 come with IIS7. The new version of IIS is different than earlier versions in many areas. In fact the entire architecture of IIS has been revamped for the good. In this article I am going to give you a jump start on IIS7. I will confine myself to the features that are most commonly needed by ASP.NET developers. If you wish to deploy your websites on IIS7 then this article should give you a good start.

    New Architecture of IIS7

    As I mentioned earlier, IIS7 has been revamped since its previous versions. The most significant areas of improvement (for developers) are modular architecture, IIS user interface, request processing pipeline and ASP.NET integration. Let's see each of these improvements in brief.

    Modular Architecture

    The new architecture introduced in IIS7 is modular in nature. Individual features of IIS are organized in various functionally related modules. This allows administrators to install only the required features resulting in decreased footprint of the web server. Additionally, they can install patches and upgrades related to installed components only. These modules can be turned on or off using "Windows Features" dialog of Windows Vista.

    Request Processing Pipeline

    In the early versions of IIS there were essentially two request pipelines. One used by IIS and one used by ASP.NET. The request authentication, execution of ISAPI extensions and filters etc. used to happen at IIS level first and then the request used to reach ASP.NET. Then ASP.NET used to run its own authentication and HTTP handlers and modules. As you might have guessed there was some duplication of work and responsibilities. The IIS7 on the other hand provides an integrated requested processing pipeline that combines IIS and ASP.NET processing into a single step.

    ASP.NET integration

    With ASP.NET 2.0 Microsoft added the ASP.NET tab to the IIS application property dialog. Taking this integration further IIS7 adds a lot more integration that makes administrator's job easy.

    IIS User Interface

    IIS user interface has been greatly redesigned for better organization. The following screen shot shows the new user interface of IIS manager.

    Now that you have some idea of what IIS7 has to offer, let's see how some common tasks can be performed. I am going to use Windows Vista for all the discussion below. The concepts remain the same for IIS under Windows Server 2008 also.

    IIS Manager

    The IIS manager can be accessed from Control Panel > System and Maintenance > Administrative Tools > Internet Information Services Manager.

    As shown in the figure above. The IIS manager user interface consists of three panes. The left hand side pane is Connections, the middle pane is Workspace and the right hand side pane is Actions.

    The Connections pane lists application pools and websites. The workspace pane consists of two tabs at the bottom namely Features View and Content View. The Features View allows you to work with the settings of the selected item from Connections pane whereas the Content View displays all the child nodes (content) of the selected item. The following Figure shows these two views for the "Default Web Site"

    Working with Application Pools

    Application pool is a group of IIS applications that are isolated from other application pools. Each application pool runs in its own worker process. Any problem with that process affects the applications residing in it and not the rest of the applications. You can configure application pools individually.

    In order to create a new application pool, select "Application Pools" under Connections pane. Then click on "Add application pool" from Actions pane. This will open a dialog as shown below:

    Specify a name for the new pool to be created. Select .NET framework version that all the applications from the pool will use. Also select pipeline mode. There are two pipeline modes viz. integrated and classic. The integrated mode uses the integrated request processing model whereas the classic mode uses the older request processing model. Click OK to create the application pool.

    Your new application pool will now be displayed in the Workspace pane. To configure the application pool click on the "Advanced Settings" option under Actions pane. The following figure shows many of the configurable properties of an application pool.

    Creating Websites

    One good feature of IIS7 under Vista is that it allows you to create multiple web sites. This feature was missing on Windows XP or Windows 2000 Professional. Server editions of Windows obviously don't have such limitation. To create a new web site, select Web Sites node under Connections pane and then click on "Add Web Site" under Actions pane. This opens a dialog as shown below:

    Here, you can specify properties of the new web site including its application pool and physical location.

    Creating IIS Applications

    Creating an IIS application or a Virtual Directory is quick and simple. Just right click on the web site and choose either "Add Application" or "Add Virtual Directory" to open respective dialogs (see below).

    An existing Virtual directory can be marked as an IIS application by right clicking on it and selecting "Convert to Application".

    Once you create a website or an IIS application, you can then set several ASP.NET related configuration properties via Workspace pane.

    Ok. That's it for now. In the next part I will discuss the hierarchical configuration used by IIS and feature delegation.

    introduction to iis7.0

    3:32 AM Edit This 0 Comments »

    Using the ASP.NET 2.0 ReportViewer in Local Mode

    7:02 AM Posted In Edit This 0 Comments »

    Introduction

    There are a good amount of materials on the net about “SQL Reporting Services in Server Mode” but it took me a while to research on using “Local Mode”, especially when parameters are involved.

    The reason to use “Local Mode” instead of “Server Mode” is that in “Server Mode”, the client makes a report request to the server. The server generates the report and then sends it to the client. While it is more secure, a large report will degrade performance due to transit time from server to browser. In “Local Mode”, reports are generated at the client. No connection to the “SQL Server Reporting Services Server” is needed for local mode. Large reports will not increase wait time.

    So here is an article on how to generate reports using the ASP.NET 2.0 ReportViewer web server control via Local Mode with a parameterized stored procedure. I am using ASP.NET 2.0, Visual Studio 2005, and SQL Server 2005 with Application Block. If you are not using Microsoft Application Block, just call the stored procedure via the SQL Command object without using the SQL Helper class in the example.

    Using the Northwind database, our example will prompt the user for a category from a dropdown list and display all the products under the selected category.

    Step 1: Create a parameterized stored procedure

    ALTER PROCEDURE  ShowProductByCategory(@CategoryName nvarchar(15) )
    AS
    SELECT Categories.CategoryName, Products.ProductName,
    Products.UnitPrice, Products.UnitsInStock
    FROM Categories INNER JOIN Products ON
    Categories.CategoryID = Products.CategoryID
    WHERE CategoryName=@CategoryName
    RETURN

    Step 2: Create a DataTable in a typed DataSet using the DataSet Designer

    Under Solution Explorer, right-click on the App_Code folder. Select “Add New Item”. Select “DataSet”. Name your dataset, e.g., DataSetProducts.xsd, and click Add. The TableAdapter Configuration Wizard should appear automatically, if not, right click anywhere on the DataSet Designer screen and select “Add” from the context menu. Select the “TableAdapter” to bring up the wizard. Follow the wizard to create your data table. I chose “Use existing stored procedures” as the command type and specified “ShowProductByCategory” as the Select command. I also highlighted “CategoryName” as the Select procedure parameter.

    The results from the stored procedure created in step 1 will eventually be placed into this data table created in step 2 (Fig. 1). Report data is provided through a data table.

    Fig. 1 DataSetProducts.xsd contains a DataTable to be used as a report data source.

    Step 3: Create a report definition

    Under Solution Explorer, right-click and select “Add New Item”. Select the “Report” template. I will use the default name Report.rdlc in this example. Click “Add” to add Report.rdlc to your project. “rdl” stands for Report Definition Language. The “c” stands for Client. Hence, the extension .rdl represents a server report. The extension .rdlc represents a local report.

    Drag a “Table” from the Toolbox onto the report designer screen (Fig.2). The Toolbox display here is specific to the report template. It shows controls to be used in a report as opposed to controls to be used in a web form. The “Table” has three bands, the header, detail, and the footer bands.

    A “Table” is a data region. A data region is used to display data-bound report items from underlying datasets. Although a report can have multiple data regions, each data region can display data from only one DataSet. Therefore, use a stored procedure to link multiple tables into a single DataSet to feed the report.

    Fig. 2 Toolbox contains controls specific to the report template.

    Open up the “Website Data Sources” window (Fig.3). Locate the “DataSetProductsDataSet (created in Step 2). Expand to see the columns in the DataTableShowProductByCategory”. The table is named “ShowProductByCategory” because we chose “Use existing stored procedure” in the TableAdapter Configuration Wizard. And our procedure name is “ShowProductByCategory”.

    Drag the column “ProductName” from the “Website Data Sources” window, and drop it in the Detail row (middle row). Drag “UnitPrice” into the middle row-second column and “UnitsInStock” into the last column. The header is automatically displayed. You can right click on any field in the detail row (e.g., right click on “Unit Price”) and bring up the context menu. Select Properties from the context menu. Select Format tab to format the “Unit Price” and “Units In Stock” accordingly.

    Fig 3. Website Data Sources window shows typed datasets in your app and its columns.

    Step 4: Drag a ReportViewer web server control onto an .aspx form

    Drag a DropDownList control onto a new web form (Fig. 4). Use the “Choose Data Source” option from the “DropDownList Task” to bind the CategoryName field from the Category table. Remember to enable autopostback. Users can then make their selection as an input to the stored procedure. While I am using a DropDownList in this example, you can use textboxes and other controls to prompt users for additional input.

    Drag a ReportViewer web server control onto the web form. Set its Visible property to false. Also notice, the ReportViewer web server control in ASP.NET 2.0 provides exporting capability. You can select between Excel format or PDF format. However, I find that what you see on screen is not always what you get from the printer. You will have to experiment with the output format further.

    Fig. 4 Set this web page as the StartUp page.

    Next, bring up the smart tag of the ReportViewer control (Fig. 5). Select “Report.rdlc” in the “Choose Report” dropdown list. “Report.rdlc” was created in Step 3. Local Reports have the extension .rdlc. Server Reports are labeled with .rdc.

    Fig. 5 Associate the report definition file (.rdlc) to the ReportViewer control

    Step 5: Write source code for the “Run Report” button to generate the report based on user selections

    Don’t forget to include the “Microsoft.Reporting.WebForms” namespace in your code-behind file.

    Collapse
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using Microsoft.ApplicationBlocks.Data;
    using Microsoft.Reporting.WebForms;

    public partial class ReportViewerLocalMode : System.Web.UI.Page
    {
    public string thisConnectionString =
    ConfigurationManager.ConnectionStrings[
    "NorthwindConnectionString"].ConnectionString;

    /*I used the following statement to show if you have multiple
    input parameters, declare the parameter with the number
    of parameters in your application, ex. New SqlParameter[4]; */


    public SqlParameter[] SearchValue = new SqlParameter[1];

    protected void RunReportButton_Click(object sender, EventArgs e)
    {
    //ReportViewer1.Visible is set to false in design mode

    ReportViewer1.Visible = true;
    SqlConnection thisConnection = new SqlConnection(thisConnectionString);
    System.Data.DataSet thisDataSet = new System.Data.DataSet();
    SearchValue[0] = new SqlParameter("@CategoryName",
    DropDownList1.SelectedValue);

    /* Put the stored procedure result into a dataset */
    thisDataSet = SqlHelper.ExecuteDataset(thisConnection,
    "ShowProductByCategory", SearchValue);

    /*or thisDataSet = SqlHelper.ExecuteDataset(thisConnection,
    "ShowProductByCategory", dropdownlist1.selectedvalue);
    if you only have 1 input parameter */


    /* Associate thisDataSet (now loaded with the stored
    procedure result) with the ReportViewer datasource */

    ReportDataSource datasource = new
    ReportDataSource("DataSetProducts_ShowProductByCategory",
    thisDataSet.Tables[0]);

    ReportViewer1.LocalReport.DataSources.Clear();
    ReportViewer1.LocalReport.DataSources.Add(datasource);
    if (thisDataSet.Tables[0].Rows.Count == 0)
    {
    lblMessage.Text = "Sorry, no products under this category!";
    }

    ReportViewer1.LocalReport.Refresh();
    }
    }

    Step 6: Build and Run the Report

    Press F5 to run the .aspx. Click on the “Run Report” button to see the list of products based on the selected category from the dropdown list (Fig. 6).

    Fig. 6 Click on the “Run Report” button to generate a local report

    Be sure to add reference of the ReportViewer to your web app, and note that your ReportViewer web server control has registered an HTTP handler in the web.config file. Your web.config file should have the following string:

    <httpHandlers>
    <add path="Reserved.ReportViewerWebControl.axd" verb="*"
    type="Microsoft.Reporting.WebForms.HttpHandler,
    Microsoft.ReportViewer.WebForms,
    Version=8.0.0.0, Culture=neutral,
    PublicKeyToken=?????????????"

    validate="false" />
    </httpHandlers>

    When you use the Visual Studio 2005 ReportViewer web server control in your website, you will need to copy the "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\BootStrapper\Packages\ReportViewer\ReportViewer.exe" to your server and run it before you post those web pages with the ReportViewer control.

    The real reason SELECT * queries are bad:

    5:05 AM Posted In Edit This 0 Comments »

    The real reason SELECT * queries are bad: index coverage

    Are SELECT * queries bad? Sure, everyone know that. But, why?

    It's returning too much data, right?

    That's the common answer, but I don't think it's the right one. If you're working with a reasonably normalized database, the actual network traffic difference is pretty small.

    Let's take a look at a sample. The following two queries select 326 rows from the TransactionHistoryArchive table in the AdventureWorks database (which has a total of 89K rows). The first uses a SELECT * query, the second selects a specific column:

    SELECT * FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100

    SELECT ReferenceOrderLineID FROM Production.TransactionHistoryArchive
    WHERE ReferenceOrderID < 100

    In this case, the difference in network traffic is only 15K, roughly a 10% difference (180K vs. 165K). It's worth fixing, but not a huge difference.

    SELECT * makes the Table / Index Scan Monster come

    Often, the bigger problem with SELECT * is the effect it will have on the execution plan. While SQL Server primarily uses indexes to look up your data, if the index contains all the columns you’re requesting it doesn’t even need to look in the table. That concept is known as index coverage. In the above example, the first query results in a Clustered Index Scan, whereas the second query uses a much more efficient Index Seek. In this case, the Index seek is one hundred times more efficient than the Clustered Index Scan.

    SelectStarQueryPlan

    Unless you've indexed every single column in a table (which is almost never a good idea), a SELECT * query can't take advantage of index coverage, and you're likely to get (extremely inefficient) scan operations.

    If you just query the rows you'll actually be using, it's more likely they'll be covered by indexes. And I think that's the biggest performance advantage of ignoring SELECT * queries.

    The Stability Aspect

    SELECT * queries are also bad from an application maintenance point of view as well, since it introduces another outside variable to your code. If a column is added to a table, the results returned to your application will change in structure. Well programmed applications should be referring to columns by name and shouldn't be affected, but well programmed applications should also minimize the ways in which they are vulnerable to external changes.

    Shameless Plug: I go into this (and a lot other important performance tips) in more detail in a soon-to-be-released book for SitePoint.

    Published Wednesday, July 18, 2007 11:56 PM by Jon Galloway
    Filed under: ,

    Comments

    # re: The real reason SELECT * queries are bad: index coverage

    "If a row is added to a table..."

    Should be: "If a column is added to a table..."

    Thursday, July 19, 2007 4:55 AM by Goran

    # re: The real reason SELECT * queries are bad: index coverage

    Great post Jon...

    The point about stability and ordinal position is a very real one, and I strongly agree with the practice of referencing columns by name*. You'll find this out the hard way if you use most database sync applications to migrate changes from one environment to another.

    Even if you don't rely on ordinal position, it's a good idea to have your change scripts drop and recreate a table when columns are added to it, if only for schema consistency reasons.

    Can't wait for the book!

    * ...he says hypocritically, knowing full well that SubSonic relies on the assumption that the name field is in the second ordinal position