Creating cross tab queries and pivot tables in SQL

Sometimes, you just absolutely have to generate a cross tab in SQL. It won't do to have the reporting system do it, nor is it feasible to build that functionality into the application. For example:

• You may be using a reporting solution that doesn't provide this functionality.
• You are using a legacy application that you'd rather not fiddle with.
• You'd like to export some data, already set out in the required format, to a text file.

It is for these exceptional cases that I decided to write a dynamic cross tab stored procedure.

The exception rather than the rule

There is a general rule which states that data manipulation of this sort is best left to the application or reporting levels of the system, and for good reason. The SQL database engine's primary role is the storage and retrieval of information, not the complex processing of it. Anyone who has tried to pound data in SQL into a meaningful set of information, using a complicated set of business rules, will probably agree that SQL tends to discourage you from doing so, and the more fancy and creative you try to make your solution, the stronger that discouragement becomes.

It has also been said that just because you can do something, it doesn't mean you should. True, but I for one think that the opposite is also applicable. Just because it seems that you can't do something, it doesn't mean you shouldn't. It's a balancing act that demands careful consideration. I have found some applications for which this stored procedure was the ideal solution – I hinted at these in the first paragraph. However, there are just as many, if not more, where it shouldn't be used. The stored procedure can have an adverse affect on performance if not used correctly, or used on an expensive or large data source. I leave you with the advice that the script described here should be used carefully and sparingly, and not sprinkled willy-nilly about your databases.

Requirements

All of my demonstration code will use the trusty Northwind sample database. It comes with SQL Server 2000 by default, but if you've gotten rid of it, or if you're running Server 2005, you can download it from the Microsoft website.

Once Northwind has been downloaded and attached, create the sys_CrossTab stored procedure in the database and you're on your way.

A simple cross tab query

The Northwind database has a table called Categories, which is used to partition the full compliment of products into eight distinct groups, namely Beverages, Condiments, Confections, Dairy Products, Grains/Cereals, Meat/Poultry, Produce and Seafood. If the North Wind Trading Company were a real entity, it would not be inconceivable for one of the bean counters to request a report listing the total value of orders placed, by year, by category. This would be the perfect opportunity to try out a cross tab query. The simplest way to do this is to use the CASE function.

SELECT      YEAR(ord.OrderDate) YEAR,  
            SUM(CASE prod.CategoryID WHEN 1 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) Beverages, 
            SUM(CASE prod.CategoryID WHEN 2 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) Condiments, 
            SUM(CASE prod.CategoryID WHEN 3 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) Confections, 
            SUM(CASE prod.CategoryID WHEN 4 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) [Dairy Products], 
            SUM(CASE prod.CategoryID WHEN 5 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) [Grains/Cereals], 
            SUM(CASE prod.CategoryID WHEN 6 THEN    
                    det.UnitPrice * det.Quantity ELSE 0 END) [Meat/Poultry], 
            SUM(CASE prod.CategoryID WHEN 7 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) Produce, 
            SUM(CASE prod.CategoryID WHEN 8 THEN 
                    det.UnitPrice * det.Quantity ELSE 0 END) Seafood 

FROM        Orders ord 

INNER JOIN  [Order Details] det 
ON          det.OrderID = ord.OrderID 

INNER JOIN  Products prod 
ON          prod.ProductID = det.ProductID 

GROUP BY    YEAR(ord.OrderDate) 

ORDER BY    YEAR(ord.OrderDate)

This will return

So, you quickly type up the query, you show the accountant how to import the data into an Excel spreadsheet, and you're off for a pint to celebrate your ingenuity.

Shortly thereafter, the chap decides that the data report is not quite granular enough, and would like a similar report split by product name rather than category. There are 77 products, so it involves a few more CASE statements. You grumble to yourself quietly while demonstrating your cut-and-paste proficiency and generate the new report, showing the breakdown by product.

Thanks to your report, the company decides that a few of the product lines are not generating the revenue that they should, so they drop those products and add a few new ones. The accountant is dismayed to discover that the report you wrote for him still shows the old products, and has not included the new products into the report. This is where your quick solution starts to go south.

Enter the dynamic cross tab

There comes a point when maintaining all of these 'hard-coded' cross tabs is more effort than spending some time developing a more generic, permanent solution. The solution I arrived at still essentially uses the CASE function to cross tab the data. The only real difference is that the list of CASE statements is built up dynamically, based on the data that you wish to use to describe the columns.

The stored procedure I created started as a simple dynamic CASE statement builder, using sp_executesql. It immediately became useful and soon people were asking, "How do I get it to do..." questions. Bit by bit, it evolved to the monster it is today. The intention has always been to have a procedure that was so generic and portable, that it could be added to anyone's database and cross tabs could be created immediately without any further setup or change in SQL code. Although simplicity of use may have suffered a little, I feel that the primary objective has been achieved.

Using the stored procedure

For starters, let's generate a cross tab result set giving a list of companies in the first column, the name of the contact at the company in the second column and a list of the stocked products from column three onwards. Inside the grid, we'll give the total value of the orders placed by that company, for that product. It must be sorted by company name.

The SQL query that returns the source data that we require is

SELECT          cus.CompanyName, cus.ContactName, prod.ProductID,  
                prod.ProductName, det.UnitPrice, det.Quantity 
       
FROM            Orders ord 

INNER JOIN      [Order Details] det 
ON              det.OrderID         = ord.OrderID 

INNER JOIN      Products prod 
ON              prod.ProductID      = det.ProductID 

INNER JOIN      Customers cus 
ON              cus.CustomerID      = ord.CustomerID
 
And here's how we'll do it:
 
EXEC sys_CrossTab 
    'Orders ord 
    inner join      [Order Details] det 
    on              det.OrderID         = ord.OrderID 
    inner join      Products prod 
    on              prod.ProductID      = det.ProductID 
    inner join      Customers cus 
    on              cus.CustomerID      = ord.CustomerID', --  @SQLSource     
    'prod.ProductID',                                      --  @ColFieldID    
    'prod.ProductName',                                    --  @ColFieldName  
    'prod.ProductName',                                    --  @ColFieldOrder 
    'det.UnitPrice * det.Quantity',                        --  @CalcFieldName 
    'cus.CompanyName, cus.ContactName',                    --  @RowFieldNames 
    NULL,                                                  --  @TempTableName 
    'sum',                                                 --  @CalcOperation 
    0,                                                     --  @Debug         
    NULL,                                                  --  @SourceFilter  
    0,                                                     --  @NumColOrdering
    'Total',                                               --  @RowTotals    
    NULL,                                                  --  @ColTotals    
    'CompanyName',                                         --  @OrderBy      
    'int'                                                  --  @CalcFieldType

The first few rows and columns returned will be

Structure of the stored procedure

If you wish to fine-tune the procedure, make it more efficient, maybe adapt it to your individual needs and cut out some of the functionality you'll never use, you may be interested in how it was put together. If you've ideas of a better way of doing things, then please do share it with all of us. The stored procedure is fairly well documented and you should be able to find your way around the code.

You'll notice that there are a good few varchar(8000) variable declarations right up front. Very early into the project, I found that varchar(8000) just wasn't large enough for anything beyond the most trivial query. The only way around this storage problem was to create a range of these variables, and as the first one filled up, I'd start adding information into the next. A range of variables have been declared for each portion of the final query that we are building, namely the CASE statements, the select field list, the totals and so on.

The first order of business is to determine the names of the columns of the cross tab. This will be the first of two queries on your source data. We insert all distinct column names into a memory table (#Columns), in the order that they should appear in the cross tab. If you've chosen to show column totals, these will be calculated and stored at this point.

Next, any prefixes from the row fields are stripped out. This is important, as we'll be grouping by these fields and the aliases, or table references, can complicate the generated query.

I then define a cursor that runs over the items that were inserted into the #Columns memory table. This generates the CASE statements that are used to perform the aggregate functions on the source data. Some work is also done on the generation of the SQL statement portions for row and column totals, as well as the insert statement into the target temporary table, if these options were selected.

Once we've built up the bits and pieces, we string them together and run the query. If you look into the stored procedure code, you'll see that I've identified eight different scenarios, based on whether or not we've elected to save to a temp table or use row and column totals. The applicable scenario is determined and the final SQL statement is then pieced together appropriately, along with the debug version if debugging was enabled. This will be the second query on your data source.

It would be difficult to describe the stored procedure in more detail than this, without getting terribly long winded about it. However, I do feel that the code is adequately commented and you shouldn't have too much hassle making modifications should you choose to do so. The best advice I have to offer is to make use of the debugging facility, as you'll immediately see the effect of your change on the generated SQL code.

The stored procedure parameters, explained

The prototype of the stored procedure is as follows:

CREATE PROC [dbo].[sys_CrossTab] 
    @SQLSource        varchar(8000),
    @ColFieldID       varchar(8000),
    @ColFieldName     varchar(8000),
    @ColFieldOrder    varchar(8000),
    @CalcFieldName    varchar(8000),
    @RowFieldNames    varchar(8000),
    @TempTableName    varchar(200) = null,
    @CalcOperation    varchar(50) = 'sum',    
    @Debug            bit = 0,
    @SourceFilter     varchar(8000) = null,
    @NumColOrdering   bit = 0,
    @RowTotals        varchar(100) = null,
    @ColTotals        varchar(100) = null,
    @OrderBy          varchar(8000) = null,
    @CalcFieldType    varchar(100) = 'int'

My original application didn't have need of nvarchars, and I really needed the extra storage space, so I decided to use the varchar data type. I would recommend that you alter these to nvarchars if you want code that is culture-safe.

Some detail of the purpose and usage of each parameter is given. If my description is a little too vague for you, have a look at the example script above and the output it generated, or even better, run the script for yourself and experiment with it.

@SQLSource

The first parameter, @SQLSource, is just that; the source of the data you wish to generate the cross tab from. This can be a table name, view name, function name or even the FROM clause of a SELECT statement, as we've used in the example. Have another look at the SQL statement I presented, and compare it to the text used for the @SQLSource parameter. It's basically the portion of the SQL statement from after the FROM keyword, up to but not including the WHERE clause, if one exists. If you wish to use a table, view or function, use just the name and possibly its alias - leave out the SELECT keyword.

@ColFieldID

We need to decide, for each row in the source data, which column to assign the values to. The @ColFieldID parameter is used to select the column to be used for this function. The ProductID field is used in our example. The number of distinct values that this column has in the source data will tell you how many columns will be used in the cross tab. This is an important consideration, especially if you wish to use the results of the cross tab in an Excel spreadsheet, as Excel puts an upper limit on the number of columns that it can handle.

@ColFieldName

Use @ColFieldName to provide the name of the field that will contain the captions for each column of the cross tab. It can be the same field as used for @ColFieldID.

@ColFieldOrder

If you require the columns to be sorted, you can specify a field by which the ordering should occur. The @ColFieldOrder parameter should hold the name of this ordering field. This too can be the same field as @ColFieldID. You might also want to set the @NumColOrdering parameter if the ordering is important. By default, the columns will be sorted alphanumerically. If you require then to be sorted numerically, set @NumColOrdering to 1. The description of that parameter will give a little more detail.

@CalcFieldName

@CalcFieldName should contain the name of the field that will be used to create the data within the cross tab grid. This will be the base data of the count, sum, average or whichever aggregate function you choose. Naturally, you should ensure that the data type of this field matches the operation you wish to perform. You cannot perform a SUM operation on varchar field, although a COUNT operation is perfectly acceptable.

@RowFieldNames

Here you will provide a comma-separated list, consisting of one or more field names, to be used as the first few columns of the grid. The aggregate function that you intend to perform will be carried out as a function of the grouping of the fields you specify here, so choose them wisely.

@TempTableName

Occasionally, the cross tab is not the final result, but a means to an end. Maybe you'd like to perform further queries on the cross tab data generated, or you'd like to join it to other tables. The @TempTableName parameter was added for this reason. It provides a way for the cross tab data to be inserted into a temporary table that you can then use for further processing.

There are a number of caveats here though. Firstly, you'll need to create the temp table before you call the cross tab stored procedure (because of SQL's scoping rules). When creating a table, you'll need to provide at least one column though. The simplest is to do something like

CREATE TABLE #CrossTab (Dummy TINYINT NULL)

You will then pass in the name of the temp table (#CrossTab in this case) to the stored procedure. Once the cross tab generation has completed, your temp table will contain the cross tab information in addition to your Dummy field. If, like me, you feel that the dummy field is 'wasted', you can declare it as an identity field, thereby adding a sequence number to your table.

CREATE TABLE #CrossTab (Sequence INT IDENTITY(1,1))

The users of your query are a lot less likely to be perturbed by a sequence number than an empty, useless column at the front of the result set.

@CalcOperation

Here we tell the stored procedure what to do with the source data we're providing. Acceptable values for this parameter are any of SQL's aggregate functions, namely AVG, SUM, COUNT, MIN, MAX and their ilk. Make sure that you match the operation to the data type, i.e. no SUMming of varchar data.

@Debug

The @Debug parameter, switched off by default, can be quite handy. When enabled (set to 1), it will print out the SQL code used to generate the cross tab. If you're not expecting the columns of your cross tab to alter, you can run the SQL printed out by the debugging code instead of using the stored procedure, which will be considerably more efficient. In this way, you can use the stored procedure as a SQL generation tool.

Take note that the row totals will not be calculated by the debug SQL. The stored procedure will 'hardcode' the totals that it calculated at the time that it was run.

@SourceFilter

@SourceFilter lets you input some SQL code to filter the source data prior to it been cross tabbed. This would be the code of the WHERE clause to match that of the SELECT clause as given to the @SQLSource parameter. There is no reason why you can't include a WHERE clause as part of the data given to @SQLSource, although I find it easier and more maintainable to specify it separately.

@NumColOrdering

If you intend for your columns to be arranged in a particular order, you'll give the field name to order them by to the @ColFieldOrder parameter, and you'll use the @NumColOrdering field to specify how the ordering is to take place. A value of 0 (the default) will cause the data to be sorted alphanumerically, and a value of 1 will sort in numerically.

If you're not sure about the difference between the two, consider the following list: 2, 1, 10, 11, 20, 100. When this is sorted numerically, it will be 1, 2, 10, 11, 20, 100. However, sorting it alphanumerically will result in 1, 10, 100, 11, 2, 20. Naturally, alphanumeric sorting will also handle A's, B's and C's, whereas numeric sorting will cause a type mismatch error to be raised.

@RowTotals

If this parameter is set to something other than NULL, an additional column will be added as the final column of the result set (the column name being the value given here), and will contain the sum of the cross tab values for each row.

@ColTotals

If set to something other than NULL, an additional row will be added as the final row of the result set, and will contain the sum of the cross tab values for the each column. There are a number of things to look out for with this one though. Firstly, you'll need to pass the field names already wrapped in quotes into the parameter. For example, if you wish the line to be marked as Total, you'll need to set @ColTotals to '''Total'''. Secondly, you'll need to provide as many values as fields that you've specified in the @RowFieldNames parameter. In our example, we've used two fields, so we need to provide two values to @ColTotals. Lastly, this total row may not necessarily appear at the bottom of the result set, depending on whether you've given an @OrderBy parameter value. The totals are added prior to the cross tab being sorted.

If you've enabled the debug printing option, the SQL code given to you will also not calculate the column totals dynamically. The totals will have been determined during the initial execution of the stored procedure, and these fixed values are then joined onto the rest of the result set.

@OrderBy

The @OrderBy parameter allows you to provide an ORDER BY clause. If used, this must be one or more of the fields used in the @RowFieldnames parameter. If you're using @ColTotals, keep in mind that the column totals row will be considered part of the cross tab data, and will be ordered along with the other rows.

@CalcFieldType

The data type of the calculated fields in the cross tab grid can be specified by the @CalcFieldType parameter. This will be INT types by default. Set the type to one that is appropriate for the type of operation being performed, and the type of data you expect to see in the cross tab.

The challenge!

If you're going to try the stored procedure out, you may as well get something for your effort. The Simple-Talk editor, Tony Davis, has kindly offered to sponsor a prize for the first three correct responses to the challenge. It is also based on the Northwind database, and you'll need to do the following:

Compile a cross tab report that displays the order value by customer, by quarter. You should also group the clients by the country in which they are based. Sort the list by country, and then by company name. Show both row and column totals, to appear at the right and bottom of the report respectively. I've included a screen shot so that you can see what the report should look like.

Post the source code for your solution in the comments to this article, (or send it to Tony at editor@simple-talk.com).

In conclusion

You'll find that once you've done one cross tab, you've pretty much done them all. The greatest difficulty is in actually deciding what you want displayed, and then collecting the source data for the stored procedure. The actual generation of the cross tab is then simply a matter of matching the field names to the input parameters.

I hope that you'll find this stored procedure as helpful as I have - it's one of the more valuable items in my toolbox. If you discover some novel use for it, or a new idea on how to improve it a little, please share it with us. I for one would be interested to hear about it.

C++ Programming Lab - Program 2

/ * Program to find the sum of two Surface points */

 

#include<iostream.h>

void main()

{

struct point

{

int x,y;

 

};

 

point p1,p2, p3;

cout<<" Enter coordinates for P1:";

cin>>p1.x>> p1.y;

cout<<"Enter coordinates for P2:";

cin>>p2.x>>p2.y;

 

p3.x=p1.x+p2.x;

p3.y=p1.y+p2.y;

cout<<"coordinates of P1 + P2:"<<p3.x<<","<<p3.y;

}

C++ Programming Lab - Program 1

/*      Raising a number n to a power p */

#include <iostream.h>

void power(double n,int p)

{

double pow=1;

int i=1;

while (i<=p)

{

pow=pow*n;

i=i+1;

}

cout<<"N Raise to power p="<<pow;

}

void main

{

double n;

int p;

cout<<"enter any no";

cin>>n;

cout<<"Enter power (p):";

cin>>p;

power(n,p);

 

}

Convert number to words.

Add button and textbox on the design page and on click event of the button on the coding page write this code.

private void button1_Click(object sender, EventArgs e)
{
Class2 obj = new Class2();



MessageBox.Show(obj.NumberToWords(Convert.ToInt32(textBox1.Text)) +" ruppees);
}

Create Class with the same name and call the function.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WindowsFormsApplication2
{
class Class2
{
private string[] _smallNumbers = new string[]
{"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight",
"Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen",
"Sixteen", "Seventeen", "Eighteen", "Nineteen"};

// Tens number names from twenty upwards
private string[] _tens = new string[]
{"", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty",
"Ninety"};

// Scale number names for use during recombination
private string[] _scaleNumbers = new string[]
{"", "Thousand", "Million", "Billion"};



public String NumberToWords(int number)
{
if (number == 0)
{
return _smallNumbers[0];
}
int[] digitGroups = new int[4];

// Ensure a positive number to extract from
int positive = Math.Abs(number);

// Extract the three-digit groups
for (int i = 0; i < grouptext =" new" i =" 0;" combined =" groupText[0];" appendand =" (digitGroups[0]"> 0) && (digitGroups[0] < i =" 1;" prefix =" groupText[i]" appendand =" false;" combined =" prefix" combined = "Negative " grouptext = "" hundreds =" threeDigits" tensunits =" threeDigits" tens =" tensUnits" units =" tensUnits">= 2)
{
groupText += _tens[tens];
if (units != 0)
{
groupText += " " + _smallNumbers[units];
}
}
else if (tensUnits != 0)
groupText += _smallNumbers[tensUnits];

return groupText;
}

}
}

How to use Microsoft SQL Server Reporting Services in ASP.NET

There are times in a developer's life when you get to use great new tools.  Recently, at a client site, I was
asked to create a few web based reports that would fit into the application that I was working on.  I was
given my options; create them using a repeater control, Crystal Reports or Microsoft SQL Server
Reporting Services (SSRS).

Each of these comes with their own benefits and detractions straight from the top. 

The repeater control is easy to get the data formatted, but getting the pages to break in the correct spot is
notoriously painful.

Crystal Reports is standard, a little painful, but easy to develop.  However, in production there would have
been some licensing difficulties.  I was "encouraged" to not use Crystal.

SSRS are great when they are installed on a SQL Server.  However, this client did not have them installed
and had no intention of installing them on the SQL Server 2008 database that the application would run
upon.

SSRS was the preferred option, not only by the client, but by me as well. Then the dilemma struck me,
I had never used SSRS locally. Thus began my local SSRS adventure.

The Scenario

Before we get started on actually writing our local report, we should delve into the database that we will be
using. As with most example applications we will be working with the Northwind database.  We will write
a report based on the Customers and Orders Tables. I will be demonstrating this using parameters and
without using parameters. The basic query that I will be using in both instances is below.

Listing 1

Select OrderID,        OrderDate,        ShippedDate,        CompanyName,        ContactName,        ShipName,        ShipAddress,        ShipCity,        ShipRegion,        ShipPostalCode,        ShipCountry from   Customers cst inner join Orders od on       od.CustomerID = cst.CustomerID 

First, I will create a new website called SSRSExample.  I will be using Visual Basic 2010 as my
development language. Also, since this is an example, I am going to go light on the design and focus
more on the process of creating the report.

Figure 1

The first thing that we want to do, now that the project is created, is to add the Bin and App_Code folders
to the project.

Figure 2

Now that the Bin and App_Code folders exist in the project, we need to add a dataset in the App_Code
folder.  Do this by right clicking on the App_Code folder and selecting Add New Item.  Select DataSet,
change the name, and click Add.

Figure 3

The Table Adaptor Configuration Wizard will be a pop-up that will ask you to define the data that will be
used to populate the dataset.  First, select your database server from the list or create a new connection to
the server then click Next.

Figure 4

You will be asked to save your connection in the web.config file. I will say yes; it will make adding the
next DataSet a little easier. Then Click Next.

Figure 5

This first example will use the Use SQL Statements option. As you can see you can create a stored
procedure from here or use an existing one.  In my development I use existing stored procedures. They
give me more flexibility and control over the application. For the example though, we will just "Use SQL
Statements."  Click Next.

Figure 6

This next page of the wizard will allow you to enter the SQL Statement with which you want to populate
the DataSet.  Click Next, Next, then Finish.  No options need changed on the following two screens.

Figure 7

After finishing you will then see the following screen.

Figure 8

Now we will open up the Default.aspx page and add a ReportViewer control to the page.

Figure 9

A blank report viewer will be added to the page, resize to fit.  I will make this one 800x600 from properties
of Report Viewer control.

Figure 10

Now, right click on the project name and Add New Item.  Select Report and change the name.  Click Add.

Figure 11

A blank report will be added to the project and a new control tab will be added with the Toolbox and Server
Explorer tabs.  This tab holds the controls for the dataset we just created.

Figure 12

Now that the report is open, add a Table to the report.

Figure 13

Start adding boxes to accommodate the data that we want to display. Some formatting may need to take
place to fit everything into the 800 x 600 format that we have created on the Report Viewer that we placed
on Default.aspx.  I have added 5 boxes and dragged and dropped the OrderID, CompanyName, OrderDate
, ShippedDate and ShipAddress into the fields.  I have added formatting to the two date fields and some
concatenation to the Address field. The next few images will show how the formatting was done.

Figure 14

Now that the report has been created we need to link it to the ReportViewer on Default.aspx.  Click on
the to open up the ReportViewer Tasks box and select the report from the drop down list. This will
add an ObjectDataSource to the page.

Figure 15

Rename the ObjectDataSource (odsExample1) and click on the again to choose the data source to use
with the report.

Figure 16

At this point you can run the application! So hit F5 and watch the magic happen.

Figure 17

Now, granted, this is a simplistic report, there is no filtering that has taken place, just a mess of a report. 
The question now becomes, how do we create a report that will accept parameters and give a little more
meaningful results? That is a good question, and I will show you.

We will use the same project; just add two new pages, a new report and a new DataSet.  I will only
highlight the differences between the two.  The two pages that will be added will be a criteria selection

page and a report page. The new DataSet will accept a parameter (CustomerID).

Here is the new query in the Example2 DataSet, notice that I added a where clause and a parameter
@CustomerID.

Figure 18

The report that I created for this one looks identical to the one in example 1.  However, it is now pointing
to the new DataSet.

This is the basic, very basic form for the Example2Select.aspx page.  Another feature of the .NET
Framework 4.0 that I find very useful is Cross Page Post Backs.  I will explain a little about them now,
since I am using them for the page submissions.

Figure 19 

To do a cross page post back, you have to set the PostBackURL property of the Submit Button.  I have
set the property to ~/Example2.aspx.  When the button is clicked this will send the post back straight to
the page referenced in the PostBackURL property.  In order to make this as easy and as powerful as
possible I have also added the following public read only property to the Example2Select.aspx page:

Listing 2

Public ReadOnly Property CustID() As String  Get   Return tCustID.Text     End Get End Property

When setting up the page that is being submitted to, the following code needs placed in the .aspx page markup:

Listing 3

<%@ PreviousPageType VirtualPath="~/Example2Select.aspx" %>

This tag sets up a reference to the page that is posting to this page, in this case, Example2Select.aspx. The
power of this tag will become clear with the next piece of code that runs before this page is rendered. This
codes job is to reference Example2Select.aspx and get the CustID property that was entered and then pass
it into the odsExample2 ObjectDataSource. The power in setting the above tag is in how the CustID
property is referenced, PreviousPage.CustID.  Without the PreviousPageType being explicitely set,
conversions would have to be done just to get the CustomerID.  This allows for a Strongly Typed Reference
to the CustID property.

Listing 4

Protected Sub odsExample2_Selecting(ByVal sender As Object, _         
ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) _  
       Handles odsExample2.Selecting        
   e.InputParameters("CustomerID") = PreviousPage.CustID 

End Sub

These are the only real differences in the two processes.  So, with no further ado, the screen shots of the
application running with parameters.

Applying Themes in ASP.net At RunTime

Step 1: Under the App_Code folder, we add a class file named Theme.cs:

public class Theme
{
private string _name;
public string Name
{
get { return _name; }
set { _name = value; }
}
public Theme(string name)
{
Name = name;
}
}

Step 2: Under the App_Code folder, we add a ThemeManager class file named ThemeManager.cs. This will list all the available themes under the /App_Themes folder.

using System;
using System.Data;
using System.Configuration;
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 System.Collections;
using System.Collections.Generic;
using System.IO;
public class ThemeManager
{ #region Theme-Related Method public static List GetThemes()
{
DirectoryInfo dInfo = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("App_Themes"));
DirectoryInfo[] dArrInfo = dInfo.GetDirectories();
List list = new List();
foreach (DirectoryInfo sDirectory in dArrInfo)
{
Theme temp = new Theme(sDirectory.Name);
list.Add(temp);
}
return list;
}
#endregion
}

Step 3: Comment out any pre-defined themes such as in the web.config. You don't need this because the application level default theme will be specified in the BasePage class in Step 6.

Step 4: In you master page, such as Default.master, add a data source and a radiobutton list. You can use a dropdownlist if you would prefer that.



Step 5: In the master page code-behind, such as Default.master.cs, add these methods:

protected void strTheme_DataBound(object sender, EventArgs e) { strTheme.SelectedValue = Page.Theme; } protected void strTheme_SelectedIndexChanged(object sender, EventArgs e) { Session.Add("MyTheme", strTheme.SelectedValue); Server.Transfer(Request.FilePath); }


Step 6: Add the BasePage class under App_Code, and specify the default theme. Here, we use "White".

using System;
using System.Data;
using System.Configuration;
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;
public class BasePage : System.Web.UI.Page
{
protected override void OnPreInit(EventArgs e)
{ base.OnPreInit(e);
if (Session["MyTheme"] == null)
{ Session.Add("MyTheme", "White");
Page.Theme = ((string)Session["MyTheme"]);
}
else
{ Page.Theme = ((string)Session["MyTheme"]); }
}
}

Step 7: Inherit all the pages using the dynamic theme from BasePage:

CAST Data-Type Conversions in SQL

 

May be you have  covered the data types that SQL recognizes and supports. Ideally, each column in a
database table has a perfect choice of data type. In this non-ideal world, however, exactly what that
perfect choice may be isn't always clear. In defining a database table, suppose you assign a data type
to a column that works perfectly for your current application. Later, you want to expand your
application's scope or write an entirely new application that uses the data differently. This new use
could require a data type different from the one you originally chose.

You may want to compare a column of one type in one table with a column of a different type in a
different table. For example, you could have dates stored columns contain the same things (dates, for
example), the fact that the types are different may prevent you from making the comparison. In SQL-86
and SQL-89, type incompatibility posed a big problem. SQL-92, however, introduced an easy-to-use
solution in the CAST expression. The CAST expression converts table data or host variables of one type
to another type. After you make the conversion, you can proceed with the operation or analysis that you
originally envisioned.

Naturally, you face some restrictions when using the CAST expression. You can't just indiscriminately

convert data of any type into any other type. The data that you're converting must be compatible with
the new data type. You can, for example, use CAST to convert the CHAR(10) character string '1998-04-26'
to the DATE type. But you can't use CAST to convert the CHAR(10) character string 'rhinoceros' to the
DATE type. You can't convert an INTEGER to the SMALLINT type if the former exceeds the maximum

size of a SMALLINT.

You can convert an item of any character type to any other type (such as numeric or date) provided that
the item's value has the form of a literal of the new type. Conversely, you can convert an item of any
type to any of the character types, provided that the value of the item has the form of a literal of the

original type.

The following list describes some additional conversions you can make:

� Any numeric type to any other numeric type. If converting to a type of less fractional precision, the
    system rounds or truncates the result.

� Any exact numeric type to a single component interval, such as INTERVAL DAY or INTERVAL SECOND.

� Any DATE to a TIMESTAMP. The time part of the TIMESTAMP fills in with zeros.

� Any TIME to a TIME with a different fractional-seconds precision or a TIMESTAMP. The date part of the
    TIMESTAMP fills in with the current date.

� Any TIMESTAMP to a DATE, a TIME, or a TIMESTAMP with a different fractional-seconds precision.

� Any year-month INTERVAL to an exact numeric type or another yearmonth INTERVAL with different
    leading-field precision.

� Any day-time INTERVAL to an exact numeric type or another day-time INTERVAL with different leading-
    field precision.

Using CAST within SQL

Suppose that you work for a sales company that keeps track of prospective employees as well as
employees whom you've actually hired. You list the prospective employees in a table named PROSPECT,
and you distinguish them by their Social Security numbers, which you store as a CHAR(9) type. You list

the employees in a table named EMPLOYEE, and you distinguish them by their Social Security numbers,
which are of the INTEGER type. You now want to generate a list of all people who appear in both tables.
You can use CAST to perform the task, as follows:

SELECT * FROM EMPLOYEE

WHERE EMPLOYEE.SSN =

CAST(PROSPECT.SSN AS INTEGER) ;

Using CAST between SQL and the host language
The key use of CAST is to deal with data types that are in SQL but not in the host language that you use.
The following list offers some examples of these data types:

� SQL has DECIMAL and NUMERIC, but FORTRAN and Pascal don't.

� SQL has FLOAT and REAL, but standard COBOL doesn't.

� SQL has DATETIME, which no other language has.

Suppose that you want to use FORTRAN or Pascal to access tables with DECIMAL(5,3) columns, and
you don't want the inaccuracies that result from converting those values to the REAL data type of
FORTRAN and Pascal. You can perform this task by CASTing the data to and from characterstring

host variables. You retrieve a numeric salary of 198.37 as a CHAR(10) value of '0000198.37'. Then if
you want to update that salary to 203.74, you can place that value in a CHAR(10) as '0000203.74'.
First, you use CAST to change the SQL DECIMAL(5,3) data type to the CHAR(10) type for the employee
whose ID number you're storing in the host variable :emp_id_var, as follows:

SELECT CAST(Salary AS CHAR(10)) INTO :salary_var

FROM EMP

WHERE EmpID = :emp_id_var ;

Then the application examines the resulting character string value in :salary_var, possibly sets the string
to a new value of '000203.74', and then updates the database by using the following SQL code:

UPDATE EMP

SET Salary = CAST(:salary_var AS DECIMAL(5,3))

WHERE EmpID = :emp_id_var ;

Dealing with character-string values like '000198.37' is awkward in FORTRAN or Pascal, but you can write
a set of subroutines to do the necessary manipulations. You can then retrieve and update any SQL data
from any host language and get and set exact values.

The general idea is that CAST is most valuable for converting between host types and the database rather
than for converting within the database.

 

an Simple example of conversion is as:

use NIR

declare @as int;

select @as= dbo.emp.Salary from dbo.emp where dbo.emp.Id=1;

 

declare @ds varchar(22);

set @ds= CAST(@as as varchar(22));

print @ds;