Asynchronous Web Services with MSMQ

Learn how to overcome a common scalability limitation of Web Services.
Based on an article by Josh Lane

Many of the first Web Services you create are likely to be synchronous, where processing for each service invocation is handled at the time of the invocation. However, Web Services of this sort can have scalability limitations. You can overcome these issues by using Microsoft Message Queue (MSMQ) with Web Services.

Imagine you've decided to implement a help-desk-request logging system for a large IT department as a Web Service. This web service defines a single public SubmitRequest() method. The user request is processed immediately, regardless of current server load or database availability. This can limit scalability and robustness.

Public Function SubmitRequest(ByVal request As HelpDeskRequest) Try Dim oldWriter as New OldSystemWriter oldWriter.SubmitRequest(request) dim newWriter as New NewSystemWriter newWriter.SubmitRequest(request) ContextUtil.SetComplete() Catch ex As Exception EventLog.WriterEntry("HelpDeskRequestProcessor", _ ex.Message,EventLogEntryType.Error ) ContextUtil.SetAbort() End Try

You can solve scalability problems by changing the web service from a synchronous request model to an asynchronous request model, introducing an intermediate component into the request-logging architecture that can take requests rapidly and guarantee their eventual delivery.

Such an asynchronous system can be built using MSMQ, a middleware subsystem that comes pre-installed on all Windows 2000 and upwards. It provides a means of storing messages of arbitrary content for forwarding or retrieval in operating-system-level structures called queues. MSMQ queues are Distributed Transaction Coordinator (DTC)-aware resource managers, meaning that send and receive operations against a queue can operate within a COM+ transaction. You can access the full complement of MSMQ services from the System.Messaging API in .NET.

First, you need to build a component that's triggered when the request message queue on the server receives a help request. This component removes the message from the queue and performs the processing.

The trigger calls this method each time a new message arrives in the target queue. Note that the queue path is passed in as a parameter. Also note that all work is performed inside a COM+ transaction, so the MSMQ receive operation and both SQL Server write operations are treated as a single atomic operation. Public Sub ProcessMessage(ByVal path As String) _ Implements IMsgHandler.ProcessMessage Dim mq As MessageQueue Dim m As Message Dim oldWriter as IRequestWriter Dim newWriter as IRequestWriter Try mq = New MessageQueue( path ) m = mq.Receive() m.Formatter = New XmlMessageFormatter( _ New Type() { GetType( _ VSM_ShareTypes.HelpDeskRequest ) } ) oldWriter = New OldSystemRequestWriter() oldWriter.WriteRequest( m.Body ) newWriter = New NewSystemRequestWriter() newWriter.WriteRequest( m.Body ) ContextUtil.SetComplete() Catch ex As Exception EventLog.WriterEntry("VSM_RequestHandler", _ ex.Message,EventLogEntryType.Error ) ContextUtil.SetAbort() Finally mq.Dispose() End Try End Sub

Create an MSMQ Trigger Rule and register the COM+ components using regsvcs.exe. Trigger rules allow your components to be invoked automatically as a result of messages arriving at the target queue. You can use MSMQ triggers to process queue messages instead of implementing a traditional queue listenener; be sure you understand the usage semantics of each option before choosing one.

New Rule (set up in the Computer Management Console): Invoke COM component Component ProgID: VSM_MessageHandler.HelpDeskRequestHandler Method Name: ProcessRequest

Here the public web service is responsible only for receiving incoming requests and writing them to the target request queue. This simple, fast operation minimizes the work performed by the web server and allows you to maximize precious web server resources and minimize client response times. Note the use of a local transaction instead of a COM+ transaction (which would be overkill since it involves only a single resource manager - the queue).

Public Function SubmitOrder( ByVal order As HelpDeskRequest) Dim mqt As New MessageQueueTransaction() Dim mq As MessageQueue Try mq = New MessageQueue(m_QueuePath) mqt.Begin() mq.Send(order) mqt.Commit() Catch ex As Exception EventLog.WriteEntry( "VSM_RequestTaker", _ ex.Message, EventLogEntryType.Error ) mqt.Abort() Finally mq.Dispose() End Try End Function

Also see Using MSMQ message triggers (PDF file)

Add Reports and Charts to Web Pages

Add Reports and Charts to Web Pages

The ASP.NET 2.0 version of Visual Studio 2005's ReportViewer control and its Report Designer let Web page developers quickly embed, format, and export interactive reports and charts without running a SQL Server Report Server.

March 2006 · by Roger Jennings

It's convenient to be able to embed reports and charts in Windows forms, but you often need to make these same reports and charts available to those who must view them from the Internet or an intranet.

I described how to build Windows form (smart client) applications that use VS 2005's new ReportViewer controls to incorporate embedded reports and charts in a recent issue (see "Build Reports More Easily," VSM November 2005). Now I'll show you how to accomplish the next step, which is using the Report Designer and ASP.NET 2.0 ReportViewer server control to create Web pages that contain reports and charts. I'll also describe how to repurpose the Report Definition Language Client (RDLC) XML files you created for smart client projects in the earlier article for use with the ASP.NET 2.0 ReportViewer server control. You'll also learn how to take advantage of XML InfoSet documents as the data source for smart client and Web-based projects, and overcome fatal "Failed to enable constraints" exceptions in Web reports. Along the way, I'll walk you through the important code-behind, design, and rendering differences between Windows and Web forms reports.

ReportViewer controls let you dispense with SQL Server Reporting Services (SSRS), which now requires a licensed edition of SQL Server 2005. The current version of SQL Server 2005 Express (SSX) doesn't support SSRS, but Microsoft promises a future SSX upgrade with a limited SSRS feature set. Unlike SSRS, which requires SQL Server 2005 or SSX, ReportViewer controls are database agnostic. You can use any database engine that has a native .NET, OLE DB, or ODBC driver that meets ADO.NET 2.0's requirements. You also can create a data source from an XML data document or instances of custom business objects.

This article's downloadable VB 2005 sample code includes Windows and Web form examples of tabular and crosstab reports, and charts that you generate from the AdventureWorks 2005 and Northwind sample databases. You must run the Transact-SQL scripts to install these two databases on your local instance of SQL Server 2005 or SSX to run the sample projects. If you use SSX, change the SQL Server connection string's localhost to localhost\SQLEXPRESS. The sample code installs by default in the current drive's \ReportViewer\ReportSmartClient and \ReportViewer\ReportWebSite folders. If you're running Visual Web Developer Express, install the Report Builder and ReportViewer (SQLServer2005_ReportAddIn.msi) add-ins before running the sample source code (see Additional Resources for links to the required files.)

Designing and embedding a tabular report in a Web form requires four basic steps: embed the ReportViewer server control; specify a report data source with the Table Adapter Configuration Wizard; add and populate a Table control in the Report Designer; and format the report Web page. Together, these steps let you create a simple report Web page. For the sake of simplicity, the example in this article uses the Northwind Employees table.

Embed the ReportViewer
Begin by opening a new file-system Web site and naming it ReportWebTest. Next, open Default.aspx in Design mode and drag a ReportViewer server control from the toolbox's Data category to the form to generate a ReportViewer1 server control (see Figure 1).

Now open ReportViewer1's Report Viewer Tasks smart tag, click on the Design a New Report link to add an empty Report1.rdlc file to the project, and open the Web Site Data Sources window. Next, click on the window's Add New Data Source link to generate a DataSet1.xsd file with an empty TableAdapter and start the Table Adapter Configuration Wizard. Add a new connection to localhost or localhost\SQLEXPRESS with Windows authentication and Northwind as the default database, click on the Next button, and save the connection with its default name, NorthwindConnectionString. Click on the Next button again, accept the default Use SQL Statements option, and click on the Next button to open the Enter a SQL Statement dialog.

Use this T-SQL statement to generate a simple employee roster report:

SELECT EmployeeID, FirstName, LastName, Title,
 Country, Extension, Notes FROM Employees

Click on Advanced Options to open the dialog of the same name, clear the Generate Insert, Update and Delete Statements check box, and click on OK. Note that you don't need DataSet update capability for reports. Click on the Next button, accept the default Fill and Get Data Method Names, clear the Create Methods to Send Updates Directly to the Database check box, and click on the Next and Finish buttons to add DataSet1 to the Website Data Sources window—also called the Field List. You don't need code to populate the DataTable when the page loads.

You're now ready to add and populate a Table control in the Report Designer. Drag a Table control from the Toolbox's Report Items category to the upper left area of Report1.rdlc's empty Body region. The default Table (table1) includes three rows of text boxes—Header, Detail and Footer—and three empty columns. Drag the EmployeeID, LastName, and FirstName field icons from the Website Data Sources window to the text boxes in columns 1, 2 and 3 of the Detail (center) row. Dropping a field in the Detail row adds the formatted field name to the Header text box and an =Fields!FieldName.Value expression to the Detail text box. Right-click on the right-most column header and select Insert Column to the Right to add columns for the Title, Country, and Extension fields (see Figure 2) .

The report width increases automatically as you add columns. (You add the ntext Notes column after formatting the report and page in the next step, because the width of the Notes column depends on available page width.) Expand the width of ReportViewer1 to about 750px, then press F5 to build and load the page in the ASP.NET Development Server (Cassini). Accept the Modify the Web.config File to Enable Debugging option when the Debugging Not Enabled message box opens, and click on the OK button. If the page is empty, close Internet Explorer (IE) 6.0, return to page Design mode, open the Report Viewer Tasks smart tag, and select Report1.rdlc from the Choose Report list. Next, click on the Choose Data Sources link to open the eponymous dialog and verify that the Data Source Instance is ObjectDataSource1 (see Figure 3). Finally, re-run the report. You'll use these features when you repurpose RDLC files from Windows forms later in the article.

Format the Final Report
The fourth and final step for embedding a tabular report in a Web form requires formatting the report to optimize consumption of browser real estate for your users' lowest display resolution or browser-window size, which this article's examples and the downloadable sample projects assume to be 800 by 600 pixels. Start by changing ReportViewer1's width property value from 750px to 100%. Unfortunately, you can't set the ReportViewer control's height in percent and specify XHTML 1.0 standards-mode processing. If you set 100% as the height property value, rendering the report in XHTML 1.0 standards-mode displays only the toolstrip; to view the report with quirks-mode rendering, remove this line from the Default.aspx source code:

Be forewarned: There's no guarantee that IE 7.0 will render quirks-mode reports correctly.

Select table1 in the Report Designer, click on the plain button in the Table's upper left corner to display sizing handles, and drag table1 to the upper left corner of the Body section. Click on the Header button to select the Header row, and click on the toolbar's Bold and Underline buttons to emphasize text. Change the Employee ID header to ID, and reduce the column width to 0.25 in.; reduce the Last Name and First Name column width to 0.88 in.; change the Title column width to 1.75 in.; change the Country and Extension headers to Ctry and Ext and the column widths to 0.38 in. Add a column and drag the Notes field to its Details text box; set the column width to 3.13 in. and drag the right edge of the Body section to the right edge of table1. Next, replace Untitled Page with Northwind Traders Employee Roster in the Default.aspx Source window, and press F5 to build and run the project to test your work so far.

You can delete the report footer row if you want, but that's not required. If you want to add a page header, page footer, or both, simply choose Report | Page Header or Report | Page Footer and add text or expressions. Most Web reports are single-page, so you can simplify the toolstrip by setting ReportViewer1's ShowPageNavigationControls property to False. You can add green-striping to the report by selecting Report1.rdlc's Details row (TableRow2), opening its Properties window, selecting the BackgroundColor property to open the color picker, clicking on the Web tab, and selecting to open the Edit Expression dialog. Now type this VBA expression in the text box (see Table 1):

=IIf(RowNumber(Nothing) Mod 2,
 "PaleGreen", "White")

Click on OK, then press F5 to build and render the page in IE (see Figure 4). Typing a search term in the text box or selecting a report export format enables the associated link buttons (see Figure 5). Unlike the Windows toolstrip, the Web form toolstrip has no print-related buttons. You set margins and print the report by executing IE's File | Page Setup, File | Print Preview, and File | Print commands.

Clone Web Report Pages
Report-oriented Web sites usually have multiple pages, enabling users to navigate easily between related reports without moving to another URL. This design model is similar to a Windows form project, such as ReportSmartClient.sln, that uses pages of a Tab control to display different reports (see Figure 6). Cloning pages for multiple reports saves substantial developer/designer time and effort. After you create a workable starting page, such as NWEmployees.aspx, you can copy and paste the page to the project, and then rename the cloned pages. For example, four of the five pages of the sample ReportWebSite project—NWOrders1997.aspx, NWOrdersChart.aspx, AWEmployees.aspx and AWCategories.aspx—began as clones of NWEmployees.aspx.

You can copy and paste RDLC files for the original and cloned pages from a corresponding Windows form project. The NWOrders1997Win.rdlc, NWOrdersChartWin.rdlc, AWEmployeesWin.rdlc, and AWCategoriesWin.rdlc files originate in the ReportSmartClient.sln project. Data source instances for Web-based reports differ considerably from their Windows form counterparts, so you must recreate the ObjectDataSource for each report page except AWCategories.aspx. However, you can create the required ObjectDataSource by copying the DataSetName.xsd file from the Windows form project to the Web site's App_Code folder.

Recreating an ObjectDataSource for a cloned page is simple. Open the ReportViewer Tasks smart tag and select the appropriate RDLC file from the Choose Report list. Most cloned pages have an existing ObjectDataSource1 instance, in which case selecting the new RDLC file adds an ObjectDataSource2 instance. Delete the added instance, open ObjectDataSource1's Tasks smart tag, click on the Configure Data Source link, and verify or change the object name in the Object Data Source Wizard's first dialog. Complete the Wizard's steps, click on the ReportViewer Tasks smart tag's Choose Data Sources link, and verify or change the Data Source Instance to ObjectDataSource1. This technique applies to conventional report pages, such as the ReportWebSite's NWEmployees.aspx, NWOrders1997.aspx, and NWOrdersChart.aspx.

The AWCategories.aspx page, like the AWCategories tab page of the Windows form project, requires code-behind to create and populate a runtime DataSet's DataTable from an XML data document (see Listing 1). The code to create a runtime, untyped DataSet for the Page_Load event handler is almost identical to that for the Form_Load event handler (see Listings 2 and 3). AWCategories.aspx illustrates a complex page that includes a background image, a complex matrix report that resembles an Excel PivotTable, and multiple detail charts. Unlike the corresponding Windows form report, the detail charts are bitmaps, so zooming the charts to 75% or smaller makes the charts impossible to read.

---

Use an XML InfoSet Document as a Web ReportViewer Data Source Listing 1. ReportViewer controls require a typed or untyped DataSet with a DataTable to serve as the report's data source. This code behind the AWCategories.aspx page loads Listing 3's XML data into an untyped, runtime DataSet that's assigned to the ReportViewer's LocalReportDataSources(0) object. The bold attribute emphasizes the modifications required to the corresponding Windows form code. Note that you can't use the "|DataDirectory|" connection string macro, which points to the project's \App_Data folder, in file path statements. Imports System Imports System.Data Imports Microsoft.Reporting.WebForms Partial Class AWCategories Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Page.IsPostBack Then Return End If With Me.ReportViewer1 'Untyped DataSet for the report's DataSource Dim dsAwCats As New DataSet 'Read the XML data into the DataSet Dim strPath As String = _ "\ReportViewer\ReportWebSite\App_Data\" dsAwCats.ReadXml(strPath + "AWCategories.xml") 'Assign the DataTable as the DataSource .LocalReport.DataSources.Add(New + _ ReportDataSource("Sales", dsAwCats.Tables(0))) 'Refresh the report with the new data .DataBind() End With End Sub End Class

AWEmployees.aspx demonstrates an undocumented problem with data sources that execute SELECT queries. They generate this nasty exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." You can use the DataTable.GetErrors( ) method to find out what rows and columns have errors, but you might find that eliminating the errors is impossible. Windows forms let you bypass this problem by adding an AdventureWorksDataset.EnforceConstraints = False statement before the Me.AwEmployeesTableAdapter.Fill(Me.AdventureWorksDataset.AWEmployees) method call. Unfortunately, this simple approach doesn't work for Web form reports because their DataSets don't expose the EnforceConstraints property. The upshot of this omission is that you need to create a new AdventureWorksDataset instance and set its EnforceConstraints property value to False, and then generate and fill a new TableAdapter instance (see Listing 4). Issue a ReportViewer1.LocalReport.DataSources.Clear( ) instruction before adding the new DataSet instance with the ReportViewer1.LocalReport.DataSources.Add( ) method. If you don't clear the DataSources, autogenerated C# DataSet designer code will raise the error when processing the required, but unusable, ObjectDataSet.

---

Eliminate Web Forms' "Failed to enable constraints" Exceptions Listing 4. Some SELECT queries, such as that for the AWEmployees.rdlc report's data source, throw this exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Windows forms let you ignore the exception by adding a DataSetName.EnforceConstraints = False instruction before you invoke the TableAdapterName.Fill( ) method. However, setting the EnforceConstraints property value in a Web form requires creating a new DataSet instance, which requires creating and filling a new TableAdapter. Imports System Imports System.Data Imports Microsoft.Reporting.WebForms Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Page.IsPostBack Then Return End If Dim dsAdvWorks As New AdventureWorksDataSet Dim taEmployees As New AdventureWorksDataSetTableAdapters. _ AWEmployeesTableAdapter dsAdvWorks.EnforceConstraints = False taEmployees.Fill(dsAdvWorks.AWEmployees) With Me.ReportViewer1 'Remove ObjectDataSource1 .LocalReport.DataSources.Clear() .LocalReport.DataSources.Add(New ReportDataSource("AdventureWorks"+_ "Dataset_AWEmployees", dsAdvWorks.Tables(0))) 'Refresh the report with the new data .DataBind() End With End Sub End Class

The Report Designer and ReportViewer combination lets you quickly integrate tabular and crosstab reports, as well as charts and graphs, in your .NET 2.0 Windows and Web forms projects. Migrating Windows form RDLC files to corresponding Web-based reports is easy and most cloned Web reports don't require adding code. A limitation of Web reports is the poor readability of zoomed charts and graphs. But the primary ReportViewer drawback for .NET developers is the fact that Microsoft provides even less documentation for Web-based reports than Windows-based reports. Apart from these drawbacks, the .NET 2.0 ReportViewer reports are ready for primetime in Windows and Web forms.

About the Author

Roger Jennings is an independent architect/database developer and writer with more than 1.25 million English copies printed. Roger's latest .NET book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He''s also a Visual Studio Magazine contributing editor and online columnist, and manages the OakLeaf Systems blog. His Code of Federal Regulations Web services won Microsoft''s 2002 .NET Best Horizontal Solution Award. Reach him at roger_jennings@compuserve.com.

C# - MultiDropdownFilterService

This class implements a filtering plug-in service that will allow the user to filter one or more SqlDataSources, GridViews and/or FormViews by the value selected in one of more DropDownLists.

Full text here.

VB.NET and C# Comparison

This is a quick reference guide to highlight some key syntactical differences between VB.NET (version 2) and C#. Very useful!

Check out the Java (J2SE 5.0) and C# comparison too!

Two articles on datetimes

Two articles on datetimes

The ultimate guide to the datetime datatypes

Frequently Asked Questions about Calendars, by Claus Tøndering

ASP.NET Performance Tuning Tips

ASP.NET Performance Tuning Tips

Any programming model has its common performance pitfalls, and ASP.NET is no exception. This section describes some of the ways in which you can avoid performance bottlenecks in your code.

Disable Session State when not in use: Not all applications or pages require per-user session state. If it is not required, disable it completely. This is easily accomplished using a page-level directive, such as the following:


<%@ Page EnableSessionState="false" %>


Note: If a page requires access to session variables but does not create or modify them, set the value of the directive to ReadOnly. Session State can also be disabled for XML Web service methods. See Using Objects and Intrinsics in the XML Web services section.


Choose your Session State provider carefully: ASP.NET provides three distinct ways to store session data for your application: in-process session state, out-of-process session state as a Windows Service, and out-of-process session state in a SQL database. Each has its advantages, but in-process session state is by far the fastest solution. If you are only storing small amounts of volatile data in session state you should use the in-process provider. The out-of-process solutions are primarily useful in Web garden and Web farm scenarios or in situations in which data cannot be lost in the event of a server/process restart.

Avoid excessive round trips to the server: The Web Forms page framework is one of the best features of ASP.NET, because it can dramatically reduce the amount of code you need to write to accomplish a task. Programmatic access to page elements using server controls and the postback event handling model are arguably the most time-saving features. However, there are appropriate and inappropriate ways to use these features, and it is important to know when it is appropriate to use them.
An application typically needs to make a round trip to the server only when retrieving data or storing data. Most data manipulations can take place on the client between round trips. For example, validating form entries can often take place on the client before the user submits data. In general, if you do not need to relay information back to the server, then you should not make a round trip to the server.

If you are writing your own server controls, consider having them render client-side code for up-level (ECMAScript-capable) browsers. By employing "smart" controls, you can dramatically reduce the number of unecessary hits to your Web server.


Use Page.IsPostback to avoid extra work on a round trip: If you are handling server control postbacks, you often need to execute different code the first time the page is requested from the code you do use for the round trip when an event is fired. If you check the Page.IsPostBack property, your code can execute conditionally, depending on whether there is an initial request for the page or a responce to a server control event. It might seem obvious to do this, but in practice it is possible to omit this check without changing the behavior of the page. For example:


<script language="C#" runat="server">

public DataSet ds;
...

void Page_Load(Object sender, EventArgs e) {
// ...set up a connection and command here...
if (!Page.IsPostBack) {
String query = "select * from Authors where FirstName like '%JUSTIN%'";
myCommand.Fill(ds, "Authors");
myDataGrid.DataBind();
}
}

void Button_Click(Object sender, EventArgs e) {
String query = "select * from Authors where FirstName like '%BRAD%'";
myCommand.Fill(ds, "Authors");
myDataGrid.DataBind();
}

</script>

<form runat="server">
<sp:datagrid datasource='<%# ds.DefaultView %>' runat="server"/><br>
<sp:button onclick="Button_Click" runat="server"/>
</form>



The Page_Load event executes on every request, so we checked Page.IsPostBack so that the first query does not execute when we process the Button_Click event postback. Note that even without this check our page would behave identically, since the binding from the first query would be overturned by the call to DataBind in the event handler. Keep in mind that it can be easy to overlook this simple performance improvement when you write your pages.


Use server controls sparingly and appropriately: Even though it is extremely easy to use, a server control might not always be the best choice. In many cases, a simple rendering or databinding substitution will accomplish the same thing. For example:


<script language="C#" runat="server">

public String imagePath;
void Page_Load(Object sender, EventArgs e) {
//...retrieve data for imagePath here...
DataBind();
}

</script>

<%-- the span and img server controls are unecessary...--%>
The path to the image is: <span innerhtml='<%# imagePath %>' runat="server"/><br>
<img src='<%# imagePath %>' runat="server"/>

<br><br>

<%-- use databinding to substitute literals instead...--%>
The path to the image is: <%# imagePath %><br>
<img src='<%# imagePath %>' />

<br><br>

<%-- or a simple rendering expression...--%>
The path to the image is: <%= imagePath %><br>
<img src='<%= imagePath %>' />




In this example, a server control is not needed to substitute values into the resulting HTML sent back to the client. There are many other cases where this technique works just fine, even in server control templates. However, if you want to programmatically manipulate the control's properties, handle events from it, or take advantage of its state preservation, then a server control would be appropriate. You should examine your use of server controls and look for code you can optimize.


Avoid excessive server control view state: Automatic state management is a feature that enables server controls to re-populate their values on a round trip without requiring you to write any code. This feature is not free however, since the state of a control is passed to and from the server in a hidden form field. You should be aware of when ViewState is helping you and when it is not. For example, if you are binding a control to data on every round trip (as in the datagrid example in tip #4), then you do not need the control to maintain it's view state, since you will wipe out any re-populated data in any case.
ViewState is enabled for all server controls by default. To disable it, set the EnableViewState property of the control to false, as in the following example:


<sp:datagrid EnableViewState="false" datasource="..." runat="server"/>


You can also turn ViewState off at the page level. This is useful when you do not post back from a page at all, as in the following example:


<%@ Page EnableViewState="false" %>


Note that this attribute is also supported by the User Control directive. To analyze the amount of view state used by the server controls on your page, enable tracing and look at the View State column of the Control Hierarchy table. For more information about the Trace feature and how to enable it, see the Application-level Trace Logging feature.

Use Response.Write for String concatenation: Use the HttpResponse.Write method in your pages or user controls for string concatenation. This method offers buffering and concatenation services that are very efficient. If you are performing extensive concatenation, however, the technique in the following example, using multiple calls to Response.Write, is faster than concatenating a string with a single call to the Response.Write method.


Response.Write("a");
Response.Write(myString);
Response.Write("b");
Response.Write(myObj.ToString());
Response.Write("c");
Response.Write(myString2);
Response.Write("d");


Do not rely on exceptions in your code: Exceptions are very expensive and should rarely occur in your code. You should never use exceptions as a way to control normal program flow. If it is possible to detect in code a condition that would cause an exception, you should do that instead of waiting to catch the exception before handling that condition. Common scenarios include checking for null, assigning to a string that will be parsed into a numeric value, or checking for specific values before applying math operations. For example:


// Consider changing this:

try {
result = 100 / num;
}
catch (Exception e) {
result = 0;
}

// To this:

if (num != 0)
result = 100 / num;
else
result = 0;


Use early binding in Visual Basic or JScript code: One of the advantages of Visual Basic, VBScript, and JScript is their typeless nature. Variables can be created simply by using them and need no explicit type declaration. When assigning from one type to another, conversions are performed automatically, as well. This can be both an advantage and a disadvantage, since late binding is a very expensive convenience in terms of performance.
The Visual Basic language now supports type-safe programming through the use of a special Option Strict compiler directive. For backward compatibility, ASP.NET does not enable Option Strict by default. However, for optimal perfomance, you should enable Option Strict for your pages by using a Strict attribute on the page or Control directive:


<%@ Page Language="VB" Strict="true" %>

<%

Dim B
Dim C As String

' This causes a compiler error:
A = "Hello"

' This causes a compiler error:
B = "World"

' This does not:
C = "!!!!!!"

' But this does:
C = 0

%>


JScript also supports typeless programming, though it offers no compiler directive to force early binding. A variable is late-bound if:
It is declared explicitly as an object.
It is a field of a class with no type declaration.
It is a private function/method member with no explicit type declaration and the type cannot be inferred from its use.
The last distinction is complicated. The JScript compiler optimizes if it can figure out the type, based on how a variable is used. In the following example, the variable A is early-bound but the variable B is late-bound:


var A;
var B;

A = "Hello";
B = "World";
B = 0;


For the best performance, declare your JScript variables as having a type. For example, "var A : String".


Port call-intensive COM components to managed code: The .NET Framework provides a remarkably easy way to interoperate with traditional COM components. The benefit is that you can take advantage of the new platform while preserving your existing code. However, there are some circumstances in which the performance cost of keeping your old components is greater than the cost to migrate your components to managed code. Every situation is unique, and the best way to decide what needs to be changed is to measure site performance. In general, however, the performance impact of COM interoperability is proportional to the number of function calls made or the amount of data marshaled from unmanaged to managed code. A component that requires a high volume of calls to interact with it is called "chatty," due to the number of communications between layers. You should consider porting such components to fully managed code to benefit from the performance gains provided by the .NET platform. Alternatively, you might consider redesigning your component to require fewer calls or to marshal more data at once.

Use SQL stored procedures for data access: Of all the data access methods provided by the .NET Framework, SQL-based data access is the best choice for building scalable web applications with the best performance. When using the managed SQL provider, you can get an additional performance boost by using compiled stored procedures instead of ad hoc queries. For an example of using SQL stored procedures, refer to the Server-Side Data Access section of this tutorial.

Use SqlDataReader for a fast-forward, read-only data cursor: A SqlDataReader object provides a forward, read-only cursor over data retrieved from a SQL database. SqlDataReader is a more performant option than using a DataSet if it can be used for your scenario. Because SqlDataReader supports the IEnumerable interface, you can even bind server controls, as well. For an example of using SqlDataReader, see the Server-Side Data Access section of this tutorial.

Cache data and output wherever possible: The ASP.NET programming model provides a simple mechanism for caching page output or data when it does not need to be dynamically computed for every request. You can design your pages with caching in mind to optimize those places in your application that you expect to have the most traffic. More than any feature of the .NET Framework, the appropriate use of caching can enhance the performance of your site, sometimes by an order of magnitude or more. For more information about how to use caching, see the Cache Services section of this tutorial.

Enable Web gardening for multiprocessor computers: The ASP.NET process model helps enable scalability on multiprocessor machines by distributing the work to several processes, one for each CPU, each with processor affinity set to its CPU. The technique is called Web gardening, and can dramatically improve the performance of some applications. To learn how to enable Web gardening, refer to the Using the Process Model section.

Do not forget to disable Debug mode: The <compilation> section in ASP.NET configuration controls whether an application is compiled in Debug mode, or not. Debug mode degrades performance significantly. Always remember to disable Debug mode before you deploy a production application or measure performance. For more information about Debug mode, refer to the section entitled The SDK Debugger.

A Short History of the Calendar (and Julian dates)

A Short History of the Calendar
Excerpt from Joe Celko's Data and Databases: Concepts in Practice

The Western world is and has been on a solar calendar. That means a year is defined as one revolution of the earth around the sun. Unfortunately, this revolution is not an even number of days (one solar year = 365.2422 days) and therefore solar calendars drift out of alignment unless corrected.

The Egyptian calendar drifted completely around approximately every 1,461 years and made two complete cycles from its introduction to the time of Julius Caesar. As a result, this calendar was useless for agriculture. The Egyptians relied on the stars to predict the flooding of the Nile. Julius Caesar decreed that the year 708 AUC (ab urbis conditae from the founding of the city of Rome, or 46 BCE) had 445 days in order to realign the calendar with the seasons. Leap years were referred to as bissextile years.

Julius Caesar, on the advice of Sosigenes of Alexandria, also introduced leap years in 708 AUC (they did not exist in solar calendars prior to then). This calendar became known as the Julian calendar. The year 46 BCE was called the Year of Confusion by the Romans.

Over the next several centuries, months were added to the year, and days were added and subtracted from the months by assorted Romans, until the Christians had control over the calendar. The problem with the Julian calendar was that it used a simple four-year leap year cycle. It drifted by approximately 3 days every 400 years and had gotten 10 days out of step with the seasons by 1582.

However, you might want to remember that a Roman calendar without a leap year would have drifted ‘completely around’ slightly more than once between 708 AUC and 2335 AUC (1582 CE). The summer solstice, so important to planting crops, had no relationship to June 21 by 1582, so Pope Gregory took two weeks out of the month of October in 1582 to realign things. (A thousand years before Pope Gregory, the Aztecs and Mayans knew the number of days in a solar year to three decimal places.)

The Gregorian calendar is now properly known as the Common Era calendar to avoid religious references that might confuse non-Christians. What used to be AD and BC are now properly abbreviated as CE and BCE.

The transition from the old Julian calendar to the Gregorian calendar took place at different times in different countries:

The Italian duchies and kingdoms, Catholic parts of Switzerland, Spain, and Portugal (at that time under the same king, Philip II), including their colonies, skipped from 1582 October 4 to 1582 October 15.

France (including its colonies) skipped from 1582 December 9 to 1582 December 20.

Poland skipped from 1582 October 4 to 1582 October 15.

German-Roman Empire (the Habsburgs in Austria): in 1583.

German duchies with Catholic confession: in 1583.

German duchies with Protestant confession skipped from 1700 February 18 to 1700 March 1.

Netherlands: in 1700.

Protestant parts of Switzerland: in 1701.

Denmark and Norway skipped from 1700 February 18 to 1700 March 1.

Sweden and Finland skipped from 1753 February 17 to 1753 March 1, but were one day apart from the old calendar between 1700 and 1712! (that is, 1700 February 28 was followed by 1700 March 1, and 1712 February 29 was followed by 1712 February 30, which was followed by 1712 March 1).

Great Britain and its colonies skipped from 1752 September 2 to 1752 September 14.

Russia and the former Soviet Union skipped from 1918 January 18 to 1918 February 1 (therefore, the October Revolution took place 1917 November 7 in the Gregorian calendar).

The Balkan nations switched between 1918 and 1924.

The Julian Date

If you’re going to use universal dates, think big and use Julian dates. The Julian date is a number set up by astronomers that currently is seven digits long. It ranges from 4713 January 01 BCE through 27,378 CE, which ought to be enough for any database application. The use of the starting date of 4713 January 01 BCE is related to solar and lunar cycles. This format avoids some problems, such as finding the day of the week (take the Julian date modulo 7) and leap year conversions. Durations are calculated by simple subtraction and addition. Finding the phase of the moon, lunar eclipses, solar eclipses, and other astronomical facts is fairly simple algebra which is why astronomers use it. The downside is that existing systems, both legal and computer, would need to be rewritten.

Unneeded Services on Windows XP

Unneeded Services on Windows XP

Windows & .NET Magazine December 2003 pg. 88
Author(s) Michael Otey

The code-bloat problem seems to get worse with every release of Windows—each new release seems to run slower than the preceding one. In most cases, the problem doesn't stem from the base OS code but from the fact that each new release tends to incorporate more functionality. One key area that continues to expand is services: Windows XP automatically starts 36 services. Few users need all those services, however, and by trimming back unused services, you can make your system run more efficiently.

To disable a service, open the Control Panel Services applet and double-click the service to open its Properties sheet. On the General tab, click the Startup type drop-down box and select Disabled. If you discover that you've lost important functionality, restart the service by resetting its Startup type to Automatic or Manual. Here are 10 XP services that you can consider turning off.

10. Automatic Updates service—Some users depend on Microsoft's Automatic Updates to keep their systems up-to-date and will want to leave this service enabled. Personally, I like to be in control of the updates that are applied to my systems, so I turn off Automatic Updates.

9. Messenger service—The Messenger service sends and receives messages that the Net Send command or the Alerter service has transmitted. If you don't use the Net Send function or receive Alerter messages, you can safely disable this service.

8.TCP/IP NetBIOS Helper service—If you're still running WINS and NetBIOS on your network, you'll want the TCP/IP NetBios Helper service to remain enabled. However, if you run only TCP/IP, you can probably eliminate this service.

7. Wireless Zero Configuration service—As its name suggests, the Wireless Zero Configuration service supports automatic configuration of 802.11 wireless connections. Mobile users of laptops and tablet PCs should probably leave this service active, but networked client systems usually have no need for wireless connections and can safely disable the service.

6. Upload Manager service—The Upload Manager service performs asynchronous file transfers. This service lets your system send Microsoft information that's used to search for drivers for your system. I prefer to explicitly manage the drivers I use, so I disable the Upload Manager service.

5. Task Scheduler service—The Task Scheduler lets your system automatically run programs and scripts at a prescheduled time. Some third-party virus scanners and backup utilities use this service; others install their own scheduling service. To see whether anything on your system uses this service, open the Scheduled Tasks folder in Control Panel. If the folder is empty, you probably can disable Task Scheduler without sacrificing functionality.

4. Error Reporting service—The Error Reporting service contacts Microsoft when applications encounter an error. At first, I thought this service was cool, but after taking the time to send an error report to Microsoft several dozen times for a variety of problems with no visible result, I gave up on this service as more trouble than it's worth.

3. Remote Registry service—The Remote Registry service lets you access and manipulate the registry on other networked systems. This service can be useful on administrative workstations, but it can also be a potentially serious security exposure on end users' network clients. I recommend disabling the Remote Registry service on most client systems.

2. Server service—The Server service provides remote procedure call (RPC) support as well as support for file and print serving. Although this service is necessary on server systems, it can pose a security risk on network clients that don't need to provide file and print serving.

1. Computer Browser service—The Computer Browser service maintains and publishes to network clients a list of computers that are on the network. Although this service is useful on one or two key servers, network clients usually shouldn't run this service.

Good Articles about passing many arguments to a stored procedure

Good Articles about passing many arguments to a stored procedure


Have you ever wanted to pass a DataTable, or array, containing tens or tens of thousands (or even millions) of records, into a SQL Server2000 stored procedure in just one database call? Ever wanted to pass a list of IDs of records to select or delete into a SP? Then read on.

Passing an array or DataTable into a stored procedure

How to pass a list of values or array to SQL Server stored procedure

Multi-table insert/update using XML

Simulating dynamic SQL in ASE

Simulating dynamic SQL in ASE


With a certain regularity, Sybase users ask questions like "How can I use dynamic SQL in ASE ?" in the Sybase-related newsgroups. There are a number of different answers to this question.

The elegant way: execute-immediate (ASE 12.0+)

First, if you're running ASE 12.0 or later, you can use the execute immediate feature, which gives you full dynamic SQL capabilites, as it lets you execute dynamically generated SQL command strings. A simple example:
declare @c varchar(25)
select @c = "select getdate()"
exec (@c)

While this example is hardly something to get excited about, execute-immediate has opened a new dimension to the sort of thing you can do with T-SQL. The most important characteristic is that you can now create stored procedures (and triggers) that are independent of the schema of tables -- a highly useful thing to do.

The clumsy way: simulate dynamic SQL(ASE pre-12.0)

First, If you don't have ASE 12.0, (so you're on version 11.9.x or before), then your ASE version won't support dynamic SQL. However, you can still simulate certain types of dynamic SQL statements through a variety of tricks. These will work in ASE 11.9.x and earlier versions, and even in old stuff such as version 4.9.x.

These are different ways of doing this:

If you're using ASE version 11.5.x or 11.9.x, you can simulate dynamic SQL by using the CIS-related stored procedure sp_remotesql. You'll need to use a backdoor trick which has some drawbacks, but otherwise works very well. It also provides almost as much functionality as the execute immediate in ASE 12.0, but it's quite a bit more complicated to get it all set up. Go here to get details on how to set this up and download some examples.

For any version of ASE, including 4.9.x, 10, 11.0, 11.5 and 11.9, you can achieve quite a few things by creatively employing existing commands and procedures. However, note the following: while offering some possibly useful ideas, there are significant limitations on what's possible with this approach, so this is mainly aimed at inspiration rather than at solving a wide range of real-life problems.


--------------------------------------------------------------------------------

Creating a table with a variable name

While the pre-12.0 Sybase Transact-SQL syntax does not allow you to use a variable for the table name in the create table statement, it is possible to achieve the same result with the following sequence:

create table some_unused_name
( column_1 int, column_2 varchar(80) )

exec sp_rename "some_unused_name",
"the_final_table_name"

By putting these statements in a stored procedure, you can effectively create a table with a variable name.
Note that you can also rename the columns of the newly created table:

exec sp_rename "final_table_name.column_1",
"Column_Number_One"


CHECK MORE OF THIS ON http://www.sypron.nl/dynsql.html


Simulating dynamic SQL through CIS

This is a demo of how you can use certain CIS (Component Integration Services) features in ASE 11.5 or 11.9 to simulate dynamic SQL.

ASE 12.0 contains a new feature called execute immediate which lets you execute a text string containing dynamically created SQL queries; however, this isn't possible in ASE 11.5.x or 11.9.x. This demo shows how to achieve basically the same functionality in ASE 11.5.x / 11.9.x.

Below on this page, you can download SQL scripts which will create a stored procedure named sp_exec_dynsql. This procedure takes a text string as an argument and executes the SQL statements contained in the text string. Here's an example: the below procedure myproc takes two arguments, being a column name and a table name. It will then select the specified column from the specified table using sp_exec_dynsql:

create procedure myproc
@col_name varchar(32),
@tab_name varchar(70)
as
begin
declare @cmd varchar(255)
select @cmd = "select " + @col_name +
"from " + @tab_name
exec sp_exec_dynsql @cmd
end

You can then do things like:
1> exec myproc "pub_name", "pubs2..publishers"
2> go

pub_name
----------------------------------------
New Age Books
Binnet & Hardley
Algodata Infosystems

(3 rows affected)

Behind the scenes, this works as follows: using a CIS-related trick, you can access the local server as if it were a remote server. You can then use sp_remotesql to execute any command string in that not-so-remote server. Because sp_remotesql takes a text string as an argument, you can create any SQL statement dynamically and then execute it.
Note that you must be running ASE 11.5 or later (earlier versions do not have the CIS functionality included), and you must run set up the server for these tricks to work.

CHECK MORE OF THIS ON http://www.sypron.nl/dynsqlcis.html

Converting SQL result sets to XML in Sybase ASE

Converting SQL result sets to XML in Sybase ASE


There is a growing interest among ASE users for combining SQL and XML. Perhaps the most common requirement is to represent an SQL result set in XML format. There are a number of ways of doing this:
ASE 12.5.1: ASE 12.5.1 comes with a completely new implementation of XML inside ASE. Unlike earlier attempts at XML-in-ASE, 12.5.1 does not use Java-in-ASE this new 'native' XML functionality (actually, it does use Java for some of the minor features, but the main functionality doesn't).

Among many other things (like XPath/XQuery support), this new feature lets you turn an SQL result set into an XML document by simply adding the for xml clause to a select statement:
1> select pub_id, pub_name from publishers for xml
2> go



0736
New Age Books


0877
Binnet & Hardley


1389
Algodata Infosystems



To extract elements from an XML document, use the xmlextract() function:
-- first create some test data
create table MyXMLTab (id int, xmldoc text)
go

insert MyXMLTab values (1,
'0736
New Age Books')

insert MyXMLTab values (2,
'1389
Algodata Infosystems')
go

-- now retrieve the data
1> select
2> pub_id = xmlextract("//pubs/pub_id/text()", xmldoc),
3> pub_id_tag = xmlextract("//pubs/pub_id", xmldoc)
4> from MyXMLTab where id = 2
5> go

pub_id pub_id_tag
------ ---------------------
0877 0877

Note how the text() function strips the XML tags from the XML element. Also note that the returned value is still a text string, so if conversion to a numeric datatype is desired, this should still be done explicitly with convert().

This is only a brief example of the XML functionality in 12.5.1+. Many more functions exist; full details can be found in the ASE product documentation.
The XML stuff in 12.5.1 is a licensable feature, but fortunately, it is included in the free Developer's Edition of 12.5.1. ASE 12.5.1 also supports web services, which is based on this XML functionality.
ASE pre-12.5.1: In pre-12.5.1, ASE supports XML/XQL based on the Java functionality in ASE. This works well provided you have XML documents already stored in the database and you want to parse/query these using XQL. Unfortunately, there isn't an easy way of converting between SQL format and XML (this can be implemented with some of the included Java classes, but this requires implementation work on your part). Also, you'll need to license the ASE_JAVA option to use this functionality.
Full details are described here.
ASE 12.0+: In 12.0+, you can also turn the contents of a table into XML format using the stored procedure sp_tab2xml, described below. This is quite a simple tool (though it uses rather advanced T-SQL programming tricks), and does not require any licensable options; yet, it is surprisingly useful.
Lastly, Thomas Gagne's 'is' tool is basically an isql replacement that can return a result set as XML (or HTML); it works well, but you'll have to build the tool from the source code your self - YMMV...

--------------------------------------------------------------------------------

sp_tab2xml: a simple SQL-to-XML tool for Sybase ASE
I have repeatedly received requests from ASE users who simply want to present some of their relational data in XML format, but don't want to bother with Java licenses or custom software development. Basically, many ASE users seem to need a simple way to present a result set as XML.

For those requirements, I wrote a (rather straightforward) stored procedure named sp_tab2xml which presents the rows in a database table (or view) as XML documents, with tags corresponding to the column names. You can download this stored procedure here.

Let's just display the contents of the table pubs3..publishers as XML:
1> use pubs3
2> go
1> sp_tab2xml publishers
2> go



0736
New Age Books
Boston
MA



0877
Binnet & Hardley
Washington
DC



1389
Algodata Infosystems
Berkeley
CA



(return status = 0)

The column-list parameter can be used to include only certain columns:
1> sp_tab2xml publishers, NULL, "pub_id, city"
2> go



0736
Boston



0877
Washington



1389
Berkeley



(return status = 0)

Expressions are also allowed, and XML tags can be renamed:
1> sp_tab2xml publishers, NULL,
2> "Town=upper(city), Publisher=pub_id"
3> go



BOSTON
0736



WASHINGTON
0877



BERKELEY
1389



(return status = 0)

Finally, the where and order-by clause can be used to filter and order the rows:
1> sp_tab2xml publishers, NULL, NULL,
2> "where city like 'B%' order by state"
3> go



1389
Algodata Infosystems
Berkeley
CA



0736
New Age Books
Boston
MA



(return status = 0)


CHECK OUT MORE OF THIS ON http://www.sypron.nl/xml.html

Querying the system catalog for tables, columns and constraints

Querying the system catalog for tables, columns and constraints
--------------------------------------------------------------------------------
// show tables and their columns in their original order
SELECT so.name, sc.name, st.name, sc.length, sc.prec, sc.scale, sc.status
FROM dbo.sysobjects so, dbo.syscolumns sc, dbo.systypes st
WHERE so.type IN ( 'V', 'U' )
AND so.id = sc.id
AND sc.type = st.type
AND st.usertype NOT IN ( 18, 25 )
AND so.name = "RTI_IMPORT_PROVRVU"
ORDER BY sc.colid

// show referential integrity constraints
SELECT so.name table_name,
sc.name column_name ,
constr_obj.name constraint_name,
ref_obj.name referenced_table_name,
ref_col.name referenced_column_name
FROM sysobjects so,
sysobjects constr_obj,
sysreferences sr,
sysobjects ref_obj,
syscolumns sc,
syscolumns ref_col
WHERE constr_obj.id = sr.constrid
AND sr.tableid = so.id
AND sr.reftabid = ref_obj.id
AND sr.fokey1 = sc.colid
AND sr.tableid = sc.id
AND sr.refkey1 = ref_col.colid
AND ref_obj.id = ref_col.id

How to get the nth highest salary

How to get the nth highest salary
--------------------------------------------------------------------------------

This SQL will find the employee with the nth highest salary from the 'employee' table defined as
emp_id Integer
salary decimal(12,0)

SELECT emp_id, salary
FROM employee a
WHERE ( SELECT COUNT( * )
FROM employee b
WHERE b.salary > a.salary ) = ( n - 1 )

To list all the employees in the 'top - n salary bracket' use:

SELECT emp_id, salary
FROM employee a
WHERE ( SELECT COUNT( * )
FROM employee b
WHERE b.salary > a.salary ) <= ( n - 1 )

How to concatenate all the values from a column and return a single row?

How to concatenate all the values from a column and return a single row?
----------------------------------------------------------------------------------

It was possible to concatenate a series of strings to return a single column, in a sort of analogous manner to sum summing all of the numbers in a column. However, this was not a 'feature' but a bug, so if you are running an EBF that has the fix for CR 210688, this is no longer possible.

(Obsolete:) Use a case statement, a la,

DECLARE @string_var varchar(255)
SELECT @string_var = ""
SELECT @string_var = @string_var +
( CASE 1 WHEN 1 THEN char_col
END )
FROM tbl_a

PRINT "%1!", @string_var
GO

How to execute dynamic SQL in the server (within stored procedures and triggers)

How to execute dynamic SQL in the server (within stored procedures and triggers)
----------------------------------------------------------------------------------

ASE System 12 solution:

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT(*) FROM master..sysobjects"
EXEC (@sqlstring)
GO

ASE 11.5 and 11.9 solution utilizing the CIS features:

Firstly define your local server to be a remote server using
sp_addserver LOCALSRV,sql_server[,INTERFACENAME]
GO

Enable CIS
sp_configure "enable cis",1
GO

Finally, use sp_remotesql, sending the sql to the server defined in point 1.
DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "SELECT COUNT (*) FROM master..sysobjects"
sp_remotesql LOCALSRV,@sqlstring
GO

Remember to ensure that all of the databases referred to in the SQL string are fully qualified since the call to sp_remotesql places you back in your default database.

ASE 10 solution (undocumented feature using stored procedures):

DECLARE @sqlstring varchar(255)
SELECT @sqlstring = "sp_who"
EXEC @sqlstring
GO

How to avoid divide by zero

How to avoid divide by zero
----------------------------------------------------------------------------------

SELECT CASE WHEN field2 = 0 THEN 0 ELSE field1 / field2
FROM table

or

SELECT field1 / ( field2 * CONVERT( int, SUBSTRING( '1', 1, ABS( SIGN( field2 ) ) ) ) )
FROM table

How to implement if-then-else comparing strings in a select clause

How to implement if-then-else comparing strings in a select clause
----------------------------------------------------------------------------------

SELECT ISNULL( SUBSTRING( 'Petit', CHARINDEX( 'SMALL', PRODUCT_SIZE ), 255 ), 'Grand' )
FROM PRODUCTS

How to emulate the Oracle decode function/crosstab with boolean logic

How to emulate the Oracle decode function/crosstab with boolean logic
----------------------------------------------------------------------------------

From the Rozenshteins book, "Advanced SQL", there is the concept of characteristic functions: the delta function. For a Boolean expression such as A = B is written delta[A = B]. Its definition is that delta[A = B] = 1 whenever A = B is true and delta[A = B] = 0 otherwise. For delta[ au_ord = 1 ] , the translation is ( 1 - abs( sign( au_ord - 1 ) ) ).

If you want to test a field to see if it is equal to a value, say 100, use the following code:

SELECT ( 1 - ABS( SIGN( ISNULL( 100 - , 1 ) ) ) )

The innermost function will return 1 when the field is null, a positive value if the field <> 100 and will return 0 if the field = 100. This example is for Sybase or Microsoft SQL server, but other servers should support most of these functions or the COALESCE() function, which is the ANSI equivalent to ISNULL.


The SIGN() function returns zero for a zero value, -1 for a negative value, 1 for a positive value The ABS() function returns zero for a zero value, and > 1 for any non-zero value. In this case it will return 0 or 1 since the argument is the function SIGN(), thus acting as a binary switch.

Put it all together and you get '0' if the value match, and '1' if they don't. This is not that useful, so we subtract this return value from '1' to invert it, giving us a TRUE value of '1' and a false value of '0'. These return values can then be multiplied by the value of another column, or used within the parameters of another function like SUBSTRING() to return a conditional text value.

This is a neat way to use boolean logic to perform cross-tab or rotation queries easily, and very efficiently. Using the aggregate 'Group By' clause in a query and the ISNULL(), SIGN(), ABS(), SUBSTRING() and CHARINDEX() functions, you can create queries and views to perform all kinds of summarizations but this technique does not produce easily understood SQL statements.

How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)

How to implement if-then-else or emulate the Oracle decode function/crosstab (ASE 11.5 +)
----------------------------------------------------------------------------------

If you are using ASE version 11.5 or later, the simplest way to implement if-then-else or implement the Oracle decode is with the CASE statement.

SELECT STUDENT_ID,
(CASE WHEN COURSE_ID = 101 THEN 1 ELSE 0 END) AS COURSE_101,
(CASE WHEN COURSE_ID = 105 THEN 1 ELSE 0 END) AS COURSE_105,
(CASE WHEN COURSE_ID = 201 THEN 1 ELSE 0 END) AS COURSE_201,
(CASE WHEN COURSE_ID = 210 THEN 1 ELSE 0 END) AS COURSE_210,
(CASE WHEN COURSE_ID = 300 THEN 1 ELSE 0 END) AS COURSE_300
GROUP BY STUDENT_ID
ORDER BY STUDENT_ID

SELECT
CASE WHEN PRODUCT_SIZE = 'SMALL' THEN 'Petit'
ELSE 'Grand'
FROM PRODUCTS