Revindex Storefront

How to use SQL database

Last updated on 2023-09-11 3 mins. to read

Using the custom tax rule, you can calculate tax rate by querying from a database table. Using a database table allows handling complex and large tax dataset.

The following example assumes you have a custom tax table called "Taxes" in your DNN database with these columns:

  • ZipCode - varchar 5 digit zip code
  • Rate - decimal tax rate to multiply against the amount (e.g. "0.10" for 10%")

Since XSLT cannot access the database directly, we will need a middleware code file that will access the database in its place. Follow the steps below:

  1. Create an .aspx file (e.g. TaxRate.aspx") with the following code. You may need to modify the names to match your table and column names:

    <%@ Page Language="C#" AutoEventWireup="true" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Configuration" %>

        // The Zip code will be passed through the query string
        var zipCode = Request.QueryString["zipcode"];

        // Sanitize zip code
        if (!String.IsNullOrEmpty(zipCode))
            zipCode = zipCode.Replace("'", "''");

        // Assume you have a Taxes table with a ZipCode and Rate columns
        var sql = "SELECT Rate FROM Taxes WHERE ZipCode = '" + zipCode + "'";
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SiteSqlServer"].ConnectionString))

            SqlCommand command = new SqlCommand(sql, connection);
            using(SqlDataReader reader = command.ExecuteReader())
                // Return tax rate or zero if not found
                if (reader.Read())

  2. Deploy the file to your Web site's root folder. Make sure the file is reachable by using your browser and passing a valid zip code (e.g https://site/TaxRate.aspx?zipcode=90210)
  3. Under Storefront administration Configuration > Taxes, add a new tax method. Give it a name like "Tax table".
  4. Choose Custom Rate type and Custom Rule for the rate rule.
  5. Paste the following XSL rule in the Rule view.

    <xsl:transform xmlns:xsl="" version="2.0">
      <xsl:template match="/">
        <!-- Retrieve the tax rate by HTTP request -->
        <xsl:variable name="rate" select="unparsed-text(concat('https://site/TaxRate.aspx?zipcode=', substring(/in/salesOrder/billingPostalCode, 1, 5)))"/>
            <xsl:value-of select="$rate * /in/this/salesOrderDetail/(amount + discountAmount)"/>
  6. Modify the XSL rule to replace the example URL with your own URL.
  7. Save and test.


Powered by Revindex Wiki