Syed A. Rizvi - Developing Web-based Architectures

Microsoft Virtual PC 2007 - Performance

2010-01-26T11:53:00.007-05:00

Microsoft provides free download for Virtual PC which is a helpful asset when developing applications in team environment as it provides an exclusive environment to develop/debug code without interfering other developers.

Virtual PC environment is big plus for Microsoft SharePoint development which requires regular iisreset command to reflect the GAC changes. This causes other team members to lose productivity if everyone is working on a shared server.

For more details on Microsoft Virtual PC visit: Microsoft Virtual PC Overview

There are numerous blog postings regarding performance of VPC: Andrew Connell, Sahil Malik

After following all these tips my VPC running on Lenovo laptop (host) was still dragging in performance. The VPC was running with Visual Studio 2008 and SQL Server 2005.

Two things did the trick for me:

a) Turn off SQL Server service and other non-important ones like Error reporting service, Wireless service, Windows Update Service etc. I was mainly doing AJAX UI development on this VPC and therefore disabling SQL Server service was safe.

b) I was initially using 1.5GB on VPC as my host machine running on Vista 64 (Intel dual processors) had 4GB of RAM but this did not help in any way regarding performance.

The trick which worked for me was to set the VPC memory to 1GB, this gave me an immediate performance boost!

Dynamics - Virtual Machine Num Lock

2009-06-09T12:26:00.006-04:00

Microsoft provides free download for Dynamics CRM 4.0 virtual demo machine. Microsoft Dynamics is a business management offering for financial, customer relationship management and supply chain.

For more details on Microsoft Dynamics visit: Microsoft Dynamics Overview

Demo virtual machine can be downloaded from the following location.

The virtual machine has Num Lock key enabled by default, which requires the user to manually disable everytime VM starts.

To disable the Num Lock permanently go to the registry [regedit] and navigate to [Hkey_Current_User\Control Panel\Keyboard] and change the [InitialKeyboardIndcators] key to '0'.

If the above step does not work as often when rebooting machine registry key is reverted back to original state, to fix this the other way is using standard Windows command go to [Start\All programs\Accessories\Accessibility\On-Screen Keyboard] and then un-check the 'nlk' key.

Free SharePoint Designer 2007 Training

2009-05-25T19:44:00.017-04:00

Microsoft made SharePoint Designer 2007 a free download as of April 1, 2009. SharePoint Designer 2007 is based on Microsoft FrontPage 2003 with more emphasis towards SharePoint enabled web-sites.

For more details on SharePoint Designer 2007 visit: SharePoint Designer 2007 Overview

Microsoft is offering free downloadable training on SharePoint Designer 2007, you can download the training from the following location.

Silverlight 2.0 - Using Silverlight DataGrid to consume ASMX Web Service

2008-11-02T10:33:00.103-05:00

Microsoft released Silverlight 2.0 on October 13, 2008. Silverlight 2 provides cross-browser rich UI experience which developers can use to author media rich applications using .NET language (C#, Visual Basic etc) of their choice.

For more details on setting up your Visual Studio 2008 development environment and Silverlight 2.0 visit: Silverlight web-site

The example uses NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of Silverlight 2.0 enabled UI by creating an ASP.NET web-form with Silverlight DataGrid control consuming ASMX Web Service.

Silverlight DataGrid control allows structured data to be displayed without requiring any major programming effort, some of the rich UI features like column resizing, column reordering, frozen columns, sorting for data which supports ILIST interface are provided Out-of-the box.

Let us start with our example, we are going to build an ASMX Web Service which will extract data from NorthWind database using LINQ to SQL (see my earlier post), later we will build Silverlight client with DataGrid which will consume this Web Service using LINQ to XML for parsing the Web Service response.

ASMX Web Service Setup:

a. Use O/R designer to map the Database, Tables and Stored procedures, I have added 'Ten_Most_Expensive_Products' Stored procedure to the designer, this will be exposed as a Web Method by the ASMX Web Service:

b. Web Method which exposes Stored procedure to be consumed by Silverlight client application

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml.Linq;

namespace NorthWindWS
{
/// <summary>
/// Summary description for Service1
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class DBWebService : System.Web.Services.WebService
{

[WebMethod]
public string GetTenMostExpensiveProducts()
{
//use DataContext
using (NorthWindDataContext DBContext = new NorthWindDataContext())
{
//LINQ to SQL query
var Categories = from cat in DBContext.Ten_Most_Expensive_Products()
                      where cat.UnitPrice > 0
                      select cat;        

//LINQ to XML query
XDocument xDoc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
                 new XElement("Categories",
                               from cat in Categories
                               select new XElement("Cat",
                               new XAttribute("ProductName", cat.TenMostExpensiveProducts),
                               new XAttribute("UnitPrice", cat.UnitPrice.ToString()))));
//return
return xDoc.ToString();
}
}
}
}

c. Consideration to make Web Service available across Domain Boundaries

Notice clientaccesspolicy.xml & crossdomain.xml files, these 2 files are required for Silverlight client application to access Web Service, for more details refer this article.

Silverlight Client Application:

a. Create New project -> Visual C# -> Silverlight -> Silverlight Application

b. Choose 'Add a new ASP.NET Web project to the solution to host Silverlight', this will give us an ASP.NET application to host and test Silverlight client

c. ASP.NET Web project added to the solution

d. Add ASMX reference to the project

e. Web Service reference added to the DBWebService

f. Add StackPanel, DataGrid & Button controls to the Page.xaml file

<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"  x:Class="MySilver.Page"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Width="800" Height="800">

<StackPanel Background="AliceBlue"  Width="Auto" Height="Auto">
<data:DataGrid Name="myGrid" AutoGenerateColumns="False" GridLinesVisibility="Horizontal"  HeadersVisibility="Column"
RowBackground="Cornsilk" AlternatingRowBackground="LemonChiffon"
Width="500" Height="250" CanUserReorderColumns="True" CanUserSortColumns="True"
IsReadOnly="True" CanUserResizeColumns="True" Visibility="Collapsed"
>
<data:DataGrid.Columns>
<data:DataGridTextColumn Binding="{Binding ProductName}"
Width="300" Header="Product Name"/>
<data:DataGridTextColumn Binding="{Binding UnitPrice}"
Width="200" Header="Unit Price"/>
</data:DataGrid.Columns>
</data:DataGrid>
<TextBlock Text=" "></TextBlock>
<Button Name="myButton" Content="Call Web Service" Click="Button_Click" Width="100" Height="25"></Button>
</StackPanel>
</UserControl>

g. Wire the Button event handler to call the Web Service

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Xml.Linq;

namespace MySilver
{
public partial class Page : UserControl
{
public Page()
{
InitializeComponent();

}

private void Button_Click(object sender, RoutedEventArgs e)
{
MyDBWebService.DBWebServiceSoapClient myService =  new MySilver.MyDBWebService.DBWebServiceSoapClient();
myService.GetTenMostExpensiveProductsCompleted  += new EventHandler<MySilver.MyDBWebService.GetTenMostExpensiveProductsCompletedEventArgs>(myService_GetTenMostExpensiveProductsCompleted);
myService.GetTenMostExpensiveProductsAsync();
}

private void myService_GetTenMostExpensiveProductsCompleted( object sender, MyDBWebService.GetTenMostExpensiveProductsCompletedEventArgs e )
{
//show data
ShowData(e.Result);
}

void ShowData(string xmlData)
{
XDocument xmlCategories = XDocument.Parse(xmlData);
       //LINQ to XML query, to extract response from Web Service
       var categories          = from cat in xmlCategories.Descendants("Cat")
                                 where cat.Attribute("ProductName") != null
                                 select new Product
                                 {
                                    ProductName    = (string)cat.Attribute("ProductName"),
                                    UnitPrice      = (string)cat.Attribute("UnitPrice")
                                 };
//bind to DataGrid
myGrid.Visibility         = Visibility.Visible;
//attach to DataGrid, ToList() is used to enable DataGrid sorting which needs ILIST interface
myGrid.ItemsSource        = categories.ToList();
}
}
}

h. Product.cs class which uses C# 'Automatic properties' to map the Web Service response

using System;

namespace MySilver
{
public class Product
{
public string  ProductName { get; set; }
public string  UnitPrice { get; set; }
}
}

i. Silverlight DataGrid output

Consideration:

Silverlight is a Client-side technology, when calling ASMX Web Service make sure it is invoked 'Asynchronously' and not 'Synchronously'. This is the reason 'ShowData' method (method which binds DataGrid to the Web Service response) is called from 'Completed' event handler.

I will try to provide more working examples of Silverlight controls in the future posts!

LINQ to SQL - Building UI Layer using ASP:LinqDataSource Control

2008-10-27T15:45:00.150-04:00

LINQ stands for Language Integrated Query which was introduced with .NET 3.5. LINQ provides the ability to write query as a first-class language construct using modern programming languages like C# and Visual Basic.

LINQ acts as a bridge between Objects and Data, classic data driven applications expected the programmer to be proficient in both the programming language (C#, Visual Basic etc) and the data source native language (SQL, XQuery etc) to extract the data.

LINQ is designed to work against all data sources possible which includes objects, relational, XML etc.

LINQ allows the developer to write type-safe queries against data sources with their choice of programming languages (C#, Visual Basic). LINQ enjoys full compile-time and intellisense support.

.NET 3.5 framework supports three flavors of LINQ supported by Microsoft:

. LINQ to Objects
. LINQ to XML
. LINQ to ADO.NET [LINQ to SQL, LINQ to Entities and LINQ to DataSet]

For more details on LINQ visit: MSDN

The example uses NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of LINQ to SQL enabled UI by creating an ASP.NET web-form with GridView control bound to the newly introduced LinqDataSource control (.NET 3.5 required).

LinqDataSource control allows the ability to connect data from wide variety of sources like database, collections etc, while maintaining a uniform programming model for data access.

In our example we will see how LinqDataSource control interacts with Database, when LinqDataSource control is used to interact with database it communicates with the database through Entity classes which are generated by O/R Designer (Object Relational Designer).

O/R Designer (Object Relational Designer) can be accessed by adding 'LINQ to SQL classes' from Visual Studio 2008 IDE, the designer usually creates one class mapping the database and one class each for all the tables in the database. The generated classes are typically placed in the App_Code folder of the project.

Let us see the steps involved in adding a LINQ to SQL enabled UI.

a) Use O/R designer to map the database and tables, I have added 'Categories' table to the designer, the sample web-form will use 'Categories' table to demonstrate display/edit/delete functionality:

b) Add a GridView control and click on 'Choose Data Source':

c) Select LINQ from Data Source Type and then provide an ID for the DataSource selected:

d) Choose the DataContext from the drop-down list, this is the name for your O/R designer generated class:

e) Configure Data Source, Pick the 'Categories' table from the Table list, Select '*' for the Columns and then Click 'Advanced':

f) Select all three options to enable Insert, Update & Delete functionality:

g) Select options to Enable Insert, Update & Delete functionality in the GridView control:

h) Web-form is wired with all the controls and is being displayed in 'Display' mode:

i) Web-form is wired with all the controls and is being displayed in 'Edit' mode:

j) Web-form is displayed with warning message showing 'Category Name cannot be blank', we will visit this section in the code walk-through:

k) Web-form is displayed with successful update:

Code Walk-through:

a) Web-form declarative Markup:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="myGrid.aspx.cs" Inherits="myGrid" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Literal runat=server ID="ErrMsg"></asp:Literal>
<asp:GridView ID="GridView1" runat="server" DataSourceID="DBLINQ" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="700px" AllowPaging="True" PagerSettings-Mode=NextPreviousFirstLast PageSize="5" AllowSorting="True"
Height="112px" AutoGenerateColumns="False" DataKeyNames="CategoryID">
<PagerSettings Mode="NumericFirstLast" />
<RowStyle BackColor="#EFF3FB"  Font-Names="Verdana" Font-Size=Small/>
<Columns>
<asp:BoundField DataField="CategoryID"  HeaderStyle-Wrap=false HeaderText="Category ID"
InsertVisible="False" ReadOnly="True" SortExpression="CategoryID" >
</asp:BoundField>
<asp:BoundField DataField="CategoryName" HeaderStyle-Wrap=false HeaderText="Category Name"
SortExpression="CategoryName" >
</asp:BoundField>
<asp:BoundField DataField="Description"  HeaderStyle-Wrap=false HeaderText="Description"
SortExpression="Description" >
</asp:BoundField>
<asp:CommandField HeaderText="Action" ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
<FooterStyle BackColor="#507CD1"  Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF"   Font-Names="Verdana" Font-Size=Small ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#D1DDF1"    Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1"  Font-Names="Verdana" Font-Size=Small Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#FFFFFF"  Font-Names="Verdana" Font-Size=Small/>
<AlternatingRowStyle BackColor="White"  Font-Names="Verdana" Font-Size=Small/>
</asp:GridView>
<asp:LinqDataSource ID="DBLINQ" runat="server"
        ContextTypeName="DataClassesDataContext" EnableDelete="True"
        EnableInsert="True" EnableUpdate="True" TableName="Categories">
    </asp:LinqDataSource>
</form>
</body>
</html>

b) Web-form Code-behind :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


public partial class myGrid : System.Web.UI.Page
{
private Boolean IsError;


protected void Page_Load(object sender, EventArgs e)
{
//wire event handlers
GridView1.RowUpdating      += new
GridViewUpdateEventHandler(GridView1_RowUpdating);
DBLINQ.Updating            += new
EventHandler<LinqDataSourceUpdateEventArgs>(DBLINQ_Updating);
//ini
IsError                    = false;
ErrMsg.Visible             = false;
}


protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//get the row being updated
GridViewRow currRow  = GridView1.Rows[e.RowIndex];
//check for null or empty value
string txtCatName    = ((TextBox)(currRow.Cells[1].Controls[0])).Text;
if (string.IsNullOrEmpty(txtCatName))
{
ErrMsg.Text            = "
<div style='font-family:Verdana;font-size:smaller;font-weight:bolder;
color:red'>* Category Name cannot be blank</br></br></div>";
ErrMsg.Visible         = true;
e.Cancel               = true;
//set the IsError variable to cancel LINQDataSource control update
IsError                = true;
}
}

protected void DBLINQ_Updating(object sender, LinqDataSourceUpdateEventArgs e)
{
//check if error occurred
if (IsError) e.Cancel = true;
}
}

Code highlighted with orange shows how we can add data validation logic to the update routine. This is just a basic example ideally we would have used ASP.NET RequiredFieldValidation control.

Key thing to notice is we need to add custom validation to both the GridView's RowUpdating and LinqDataSource Updating event handlers, as this gives us more granular control towards the validation.

Once the GridView's RowUpdating event handler detects an exception it sets the global variable 'IsError' to True, this variable is then checked by the LinqDataSource Updating event handler before performing the Database update. If the variable is False then the changes are saved to the Database.

Another way of handling the validation logic is to handle it in the LinqDataSource RowUpdated event handler but my personal choice is to use the approach as described above since this approach gives us more flexibility in both handling validation and providing custom feedback to the User.

I will try to provide more working examples of LINQ in the future posts!

ASP.NET/SQL Server 2008 – Using Table-Valued Parameters with .NET Application

2008-10-17T15:00:00.016-04:00

With the release of SQL Server 2008, Microsoft introduced a new parameter type called 'Table-Valued Parameters', which provided the flexibility for the SQL developers to send multiple rows of data to a stored procedure/function or inline T-SQL code.

Table-Valued parameters can be used to transfer multiple rows of data without requiring the developer to construct custom string parsing routines, temporary table or multiple parameters which for a complex stored procedure can become difficult to maintain.

Table-Valued parameters are declared in series of steps:

Create a Table type and define the table structure based on the requirement
Declare a local variable to use this Table type
Initialize the local variable with the data
Pass the local variable to the stored procedure/function to accept this table parameter, make sure the input parameter is 'READONLY'

Table-Valued parameter goes out of scope once the stored procedure/function/T-SQL is executed. Table-Valued parameter can only be modified in the scope it was created, due to this criteria when the parameter is passed to a different stored procedure/function it is passed as READONLY.

For more details on Table-Valued parameters visit: MSDN

Let us consider a real-world example using NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of Table-Valued Parameters by creating a custom stored-procedure and calling the stored-procedure code from the .NET application (.NET 3.5 required).

NorthWind database contains 'Shippers' table, this table stores shipping company contact details used in the Order fulfillment operation.

We are going to insert 2 new Shippers record in the table, using Table-Valued Parameter enabled stored procedure, the stored procedure will be invoked from .NET code.

a) Create User-Defined Table type

CREATE TYPE Shippers AS TABLE (CompanyName nvarchar(80), phone nvarchar(48))

b) Creating the Table-Valued Parameter enabled Stored procedure

CREATE Procedure CreateShippers(@Shippers Shippers READONLY)
AS
BEGIN
INSERT INTO Shippers SELECT CompanyName, Phone FROM @Shippers
END
GO

Notice @Shippers variable of Shippers type is declared with READONLY qualifier.

c) Calling the Stored procedure through .NET application

I have created a test web form to demonstrate how we can call the Table-Valued Parameter enable stored procedure from .NET application.

Web form displays original data in the Shippers table and a 'Load Shippers' button, once the button is clicked 2 new rows of data are inserted into the Shippers table and the web form reloads to display the newly inserted data (I am using hard coded values in the OnClick event handler of the button to initialize data, the code can be easily modified to accept user keyed data by adding text box controls to the web form).

Screen shot showing Web-form with Original and Inserted data:

ASP.NET Web form Code (C#):

protected void Button1_Click(object sender, EventArgs e)
{
string path = Request.CurrentExecutionFilePath;
path = path.Substring(0, path.LastIndexOf('/'));
Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration(path);
ConnectionStringSettings connString = rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];

//create DataTable
DataTable dt = new DataTable("MyData");
DataColumn dbCol1 = new DataColumn("CompanyName", System.Type.GetType("System.String"));
DataColumn dbCol2 = new DataColumn("Phone", System.Type.GetType("System.String"));
//add columns
dt.Columns.Add(dbCol1);
dt.Columns.Add(dbCol2);

//create data
DataRow dr;
dr = dt.NewRow();
dr["CompanyName"] = "ACME Express";
dr["Phone"] = "(503) 111-1111";
dt.Rows.Add(dr);
//new row
dr = dt.NewRow();
dr["CompanyName"] = "Courier Express";
dr["Phone"] = "(503) 222-2222";
dt.Rows.Add(dr);
//save
dt.AcceptChanges();

//open Database connection
using (SqlConnection dbConn = new SqlConnection(connString.ConnectionString))
{
dbConn.Open();
using (SqlCommand dbCommand = new SqlCommand("CreateShippers", dbConn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
//Create a parameter using the new type
SqlParameter param = dbCommand.Parameters.Add("@Shippers", SqlDbType.Structured);
//Set the value of the parameter
param.Value = dt;
//Execute the query
dbCommand.ExecuteNonQuery();
}
}

//get new inserted data
SqlDataSource1.ConnectionString = connString.ConnectionString;
SqlDataSource1.SelectCommand = "Select * from Shippers";
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.Text;
GridView1.DataSourceID = "SqlDataSource1";
//show confirmation
Msg.Visible = true;
//hide button
Button1.Visible = false;
}

ASP.NET - Efficiently Importing DataTable

2008-10-08T17:31:00.033-04:00

ASP.NET developers often use DataTable to store data received from SQL stored procedure, web-service, data access layer (DAL) etc.

DataTable acts like a temporary container where the data is stored, refined (trimming white-spaces, number formatting), transformed (calculating a running total) and then consumed.

For more details on DataTable visit: MSDN

The article will demonstrate how to efficiently create a DataTable from an existing DataTable based on row by row comparison.

Suppose DataTable (A) has 1000 rows of Order data placed by multiple customers of the web-site, our goal is to efficiently create a new DataTable (B) using DataTable (A) to extract Orders having customer ID of 101 & 102.

DataTable (A) is populated by making a call to the DAL, which returns all the Orders placed to date [ideally DAL would provide a method which will return filtered data but for now we are going to go with the assumption of filtering data out of DataTable (A)].

Code:

//DataTable (B) is cloned from DataTable (A)
DataTable B = A.Clone();
//clear DataTable
B.Clear();
//save changes
B.AcceptChanges();

Note we are using DataTable 'Clone' method instead of 'Copy' method as our objective is to only retain the structure of DataTable (A) and not the data.

Now we need to populate DataTable (B) with Orders having customer ID of 101 & 102.

//iterate
foreach(DataRow dr in A.Rows)
{
int customerID = Convert.ToDouble(dr["CustomerID"]);
//match customer ID
if (customerID == 101 || customerID == 102)
{
//customerID matched add it to DataTable (B)
B.ImportRow(dr);
}
}
//save
B.AcceptChanges();

DataTable’s ImportRow method copies the row from DataTable (A) to DataTable (B), performance wise ImportRow is faster than individually adding rows specially when dealing with large amount of data.

SQL Server - Get Nth Highest using ROW_NUMBER() Function

2008-08-27T15:00:00.006-04:00

* Compatibility SQL Server 2005 onwards
ROW_NUMBER function was introduced in SQL Server 2005. ROW_NUMBER as the name suggests is used to calculate row numbers in the query result set.

Prior to ROW_NUMBER developers relied on temporary tables and co-related sub-queries to calculate sequential output, this often resulted in spaghetti code and overall bad query performance.

For more details on ROW_NUMBER visit: MSDN

Let us consider a real-world ROW_NUMBER example using NorthWind database. You can download and install NorthWind database from the following location.

Consider this scenario, we want to run a report which gives us Top Order by sales.

We are not calculating ties here i.e. what if more than one order generated exactly same sales amount, ties can be handled using using RANK() function but for now we will assume sequential result set.

The stored procedure which we are going to see will provide the flexibility to return exact result set i.e. Order with highest sales or range i.e. Top 10 Orders by sales

Stored procedure code:

USE [Northwind]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetHighestSales_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetHighestSales_sp]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetHighestSales_sp
(
@Rank int = NULL,
@FROM int = 1,
@TO int = 1
)
AS
BEGIN
--no extra columns
SET NOCOUNT ON;

/*CTE Declaration block */
WITH CustomerOrders (OrderID, CustomerID, EmployeeID, ContactName, CompanyName, Phone, TotalPurchasePrice, SalesRank) AS
(
SELECT OrderTotal.OrderID As OrderID, C.CustomerID As CustomerID, E.EmployeeID As EmployeeID,
C.ContactName, C.CompanyName, C.Phone, OrderTotal.TotalPurchasePrice,
OrderTotal.SalesRank
FROM
Customers C
INNER JOIN
(
SELECT ORD.OrderID As OrderID, O.CustomerID, O.EmployeeID,
Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money)) AS TotalPurchasePrice,
Row_Number() OVER(ORDER BY (Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money))) DESC) AS SalesRank
FROM
[Order Details] ORD
INNER JOIN ORDERS O ON O.OrderID = ORD.OrderID
GROUP BY ORD.ORDERID, O.CustomerID, O.EmployeeID
) AS OrderTotal ON OrderTotal.CustomerID = C.CustomerID
INNER JOIN Employees E ON OrderTotal.EmployeeID = E.EmployeeID
)

--determine which result to extract
SELECT
OrderID, E.LastName + ', ' + E.FirstName AS EmployeeName, ContactName,
CompanyName, Phone, TotalPurchasePrice, SalesRank
FROM
Employees E
INNER JOIN CustomerOrders CO ON CO.EmployeeID = E.EmployeeID
WHERE
CO.SalesRank between @From AND @To

--reset
SET ROWCOUNT 0
END

(a) Top Order by sales

EXEC dbo.GetHighestSales_sp 1

Query returns Order with highest sales amount.

(b) Top 10 Order by sales

EXEC dbo.GetHighestSales_sp NULL, 1, 10

Query returns Top 10 Orders by sales amount.

MOSS 2007 - Data Caching Vs List Iteration

2008-08-12T18:16:00.116-04:00

MOSS developers most of the time need to retrieve data from SharePoint Lists. In one of the MOSS project I worked there were several provisioned .ASPX Pages which lacked in performance.

The pages took more than usual time to render, on average a page displaying 1000+ List records (we are not discussing Pagination here) was taking approximately 2 minutes! Coming from ASP.NET/SQL background this was just unacceptable.

The code was nicely structured with N-Tier architecture, all the MOSS specific code was residing in Data Access Layer (DAL) and .ASPX pages were consumers.

DAL consisted of function which was returning a DataTable with all the relevant data to be displayed on the .ASPX page.

The problem code was in this function, below is an excerpt of code from this function.

//load datatable

DataTable dt = objDB.GetAllData();

//this happens for 1000+ rows

foreach (DataRowView dr in pages.DefaultView)

{

....

DataRow viewRow = null;
//problem code

viewRow["MyRow"] = objDB.GetNameByID(dr["ID"].ToString())["Name"].ToString();

....

}

//gets Name by ID

public SPListItem GetNameById(string id)

{

int itemId = -1;

if (Int32.TryParse(id, out itemId))

{

using (SPSite site = new SPSite(SiteUrl))

{

using (SPWeb web = site.OpenWeb())

{

SPList list = web.Lists["ListData"];

return list.GetItemById(itemId);

}

return null;

}

Code highlighted with orange shows the problem area, if the outer loop is executing 1000+ times then this lookup function is called 1000+ times along with expensive SPSite, SPWeb and individual lookup which works satisfactorily if you have 100-200 rows but the performance starts to deteriorate exponentially with the increase in number of calls.

Fix:

After the reviewing the code, I recognized the need to avoid calling lookup function 1000+ times and instead have an inline cache to do lookup.

I am not talking about .NET cache here or MOSS 2007 object cache but DataView which can be used as a temporary cache.

//gets Name by ID revised to return DataView

public DataView GetAllNameById()

{

using (SPSite site = new SPSite(SiteUrl))

{

using (SPWeb web = site.OpenWeb())

{

SPList oList = web.Lists["ListData"];

SPQuery oQuery = new SPQuery();

oQuery.Query = "";
oQuery.RowLimit = 0;
DataView dv = new DataView();
dv = oList.GetItems(oQuery).GetDataTable().DefaultView;
//indexes, helps in faster lookup
dv.Sort = "ID";
//return sorted dataview
return dv;
}
}
}

Let us see the revised code excerpt once again:

//cache all data to avoid query MOSS on every request
DataView dv = objDB.GetAllNameById();
//load datatable
DataTable dt = objDB.GetAllData();
//this happens for 1000+ rows
foreach (DataRowView dr in pages.DefaultView)
{
....
....
....
DataRow viewRow = null;
//revised code
DataRowView[] drv = dv.FindRows(dr["Name"].ToString());
if (null != drv)
{
viewRow["MyRow"] = drv[0]["Name"].ToString();
}
....
....
....
}

With this change we are not using expensive SPSite, SPWeb objects and lookup on every iteration but caching the entire lookup List in an indexed DataView, when we are doing the lookups we are not querying a List but querying a DataView which is much Faster!

With this code change the Page rendering time changed from 2 minutes to 7 seconds!

SQL Server - Using EXCEPT Operator

2008-08-08T16:00:00.003-04:00

* Compatibility SQL Server 2005 onwards
EXCEPT operator was introduced in SQL Server 2005. EXCEPT can be used to return distinct records from the left side of a query and which are not located on the right side.

EXCEPT operator has the following behavior:

• EXCEPT by default returns a DISTINCT result set

• EXCEPT requires same column number and order on both side of the query with comparable data type

For more details on EXCEPT visit: MSDN

Let us consider a real-world EXCEPT example using NorthWind database. You can download and install NorthWind database from the following location.

(a) Customers who never placed any Order (Using EXCEPT)

SELECT
CustomerID
FROM
Customers
EXCEPT
SELECT
CustomerID
FROM
Orders

CustomerID
--------------
FISSA
PARIS
….
--------------

Query returns 2 rows, which are unique CustomerID. The above query demonstrated that we can extract unique CustomerID for all the Customers who never placed an Order using EXCEPT operator instead of writing a LEFT OUTER JOIN syntax to accomplish the same output.

(b) Customers who never placed any Order (Using LEFT OUTER JOIN)

SELECT
DISTINCT C.CustomerID
FROM
Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE
O.CustomerID IS NULL

CustomerID
--------------
FISSA
PARIS
….

--------------
Query returns same output as (a) using LEFT OUTER JOIN syntax

SQL Server – Using INTERSECT Operator

2008-08-07T15:14:00.028-04:00

* Compatibility SQL Server 2005 onwards
INTERSECT operator was introduced in SQL Server 2005. INTERSECT can be used to return distinct records from the left and right side of a query.

INTERSECT is often compared with INNER JOINS, it can be said that INTERSECT is almost same as INNER JOIN with the following key differences:

• INTERSECT by default returns a DISTINCT result set whereas INNER JOIN return duplicates unless it is instructed not to do so by using DISTINCT clause to the SELECT statement

• INTERSECT requires same column number and order on both side of the query with comparable data type, INNER JOIN does not require same column number and order condition

For more details on INTERSECT visit: MSDN

Let us consider a real-world INTERSECT example using NorthWind database. You can download and install NorthWind database from the following location.

(a) How many Products have been at least ordered once

SELECT
ProductID
FROM
Products
INTERSECT
SELECT
ProductID
FROM
[Order Details]

ProductID
--------------
1
2
3
4
5
….
….

Query returns 77 rows, which are unique ProductID. The above query demonstrated that we can extract unique ProductID for all the Products purchased using INTERSECT operator instead of writing an INNER JOIN to accomplish the same output

(b) INTERSECT Vs INNER JOIN

SELECT
ProductID
FROM
Products
INTERSECT
SELECT
ProductID
FROM
[Order Details]

ProductID
--------------
1
2
3
4
5
….
….
Returns 77 rows

SELECT
ORD.OrderID
FROM
[Order Details] ORD
INNER JOIN Orders O ON O.OrderID = ORD.OrderID

ProductID
------------
1
2
3
4
5
……
……

Query returns 2155 rows, which are non-unique ProductID. The above query demonstrated the default behavior of INNER JOIN

SELECT
DISTINCT ORD.OrderID
FROM
[Order Details] ORD
INNER JOIN Orders O ON O.OrderID = ORD.OrderID

ProductID
------------
1
2
3
4
5
……
……

Query returns 77 rows, which are unique ProductID as we used DISTINCT keyword to filter out the duplicates.

SQL Server - Common Table Expressions (CTE)

2008-08-05T20:12:00.040-04:00

* Compatibility: SQL Server 2005 onwards
Common Table Expressions (CTE) construct was introduced with Microsoft SQL Server 2005. CTE behaves like a derived table but unlike derived table which has statement level scope CTE offers a procedure level.

CTE helps in writing more modular code, which helps in better readability and reducing complexity of query.

CTE provides following benefits:
. Ability to call block of query (CTE declaration) multiple times like a function call in modern
programming language
. Ability to provide recursion logic (with robust exit condition)
. Ability to use result set (CTE call) in multiple statements or single statement (self join)

CTE is described with the following syntax:

WITH MyCTE1 (OrderID, CustomerID) AS
(
SELECT
OrderID, CustomerID
FROM
MyTable1
)
, MyCTE2 (FirstName, LastName)
(
SELECT
FirstName, LastName
FROM
MyTable2
)
/*Multiple CTE’s can be declared with ‘,’*/

Where
. MyCTE1 is CTE Name used to reference CTE in queries
. Column list (optional), good practice to declare them to avoid confusion
. Query declaration after the AS keyword
. Multiple CTE’s can be declared putting ‘,’ after previous ending declaration

CTE can then be used as following (we will see detailed example later):
SELECT * FROM MyCTE

CTE can be declared in stored procedures, user-defined functions (UDF), triggers, or views.

For more details on CTE’s visit: MSDN

Let us consider a real-world CTE example using NorthWind database. You can download and install NorthWind database from the following location.

Consider this scenario; we want to run a report which gives us Order detail by OrderID, EmployeeID or CustomerID, our sample query will only return Employee name, Contact name, Company name, Phone, Fax and Total purchase price for the order (the objective is to make the stored procedure flexible enough to fetch data for all the input parameters or some).

If we follow conventional T-SQL approach we would have to write multiple T-SQL statements to achieve our objective, but as you can see with one stored procedure using combination of CTE, Dynamic tables and Coalesce statement we made the entire stored procedure multi-functional, we can use the stored procedure to run reports like Sales by Employee, Sales by Customer, Overall Sales total and many more cases depending on how you use the data, of course we can change the query criteria to make our stored procedure even more flexible or extendable.

Stored procedure code:

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[GetCustomerOrders_sp] Script Date: 08/17/2008 21:37:16 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
--all
EXEC dbo.GetCustomerOrders_sp null, null, null
--by OrderID
EXEC dbo.GetCustomerOrders_sp 10682, null, null
--by CustomerID
EXEC dbo.GetCustomerOrders_sp null, null, 'ANTON'
--by EmployeeID
EXEC dbo.GetCustomerOrders_sp null, 9, null
--exact match
EXEC dbo.GetCustomerOrders_sp 10263, 9, 'ERNSH'
*/
ALTER PROCEDURE [dbo].[GetCustomerOrders_sp]
(
@OrderID int = null,
@EmployeeID int = null,
@CustomerID nVarchar(5) = null
)
AS
BEGIN
--no extra columns
SET NOCOUNT ON;

/*CTE Declaration block */
WITH CustomerOrders (OrderID, CustomerID, EmployeeID, ContactName, CompanyName, Phone, Fax, TotalPurchasePrice) AS
(
SELECT OrderTotal.OrderID As OrderID, C.CustomerID As CustomerID, E.EmployeeID As EmployeeID,
C.ContactName, C.CompanyName, C.Phone, C.Fax, OrderTotal.TotalPurchasePrice
FROM
Customers C
INNER JOIN
(
SELECT ORD.OrderID As OrderID, O.CustomerID, O.EmployeeID,
Sum(CAST((UnitPrice * Quantity) * (1-Discount)/100 * 100 AS Money)) AS TotalPurchasePrice
FROM
[Order Details] ORD
INNER JOIN ORDERS O ON O.OrderID = ORD.OrderID
GROUP BY ORD.ORDERID, O.CustomerID, O.EmployeeID
) AS OrderTotal ON OrderTotal.CustomerID = C.CustomerID
INNER JOIN Employees E ON OrderTotal.EmployeeID = E.EmployeeID
),
CustomerDetail (CustomerID, ContactName, CompanyName, Phone, Fax) AS
(
SELECT CustomerID, ContactName, CompanyName, Phone, Fax
FROM Customers
)
/*Note Multiple CTE's can be declared in declaration block*/
--determine which result to extract
SELECT
OrderID, E.LastName + ', ' + E.FirstName AS EmployeeName, E.EmployeeID, ContactName, CompanyName, Phone, Fax, TotalPurchasePrice
FROM
Employees E
INNER JOIN CustomerOrders CO ON CO.EmployeeID = E.EmployeeID
WHERE
CO.EmployeeID LIKE Coalesce(@EmployeeID, CO.EmployeeID)
AND CO.OrderID LIKE Coalesce(@OrderID, CO.OrderID)
AND CO.CustomerID LIKE Coalesce(@CustomerID, CO.CustomerID)
ORDER BY E.EmployeeID ASC
END

Test Scripts result:

Note: Result sets images are adjusted to show the first few records and rest are omitted

--all
EXEC dbo.GetCustomerOrders_sp null, null, null

--by OrderID

EXEC dbo.GetCustomerOrders_sp 10682, null, null

--by CustomerID

EXEC dbo.GetCustomerOrders_sp null, null, 'ANTON'

--by EmployeeID

EXEC dbo.GetCustomerOrders_sp null, 9, null

--exact match

EXEC dbo.GetCustomerOrders_sp 10263, 9, 'ERNSH'

SQL Server – Power of CASE Function

2008-08-01T17:00:00.017-04:00

* Compatibility SQL Server 2000 onwards
CASE statement can be used to introduce conditional logic. CASE can be used to evaluate multiple statements and return one possible output.

CASE can be compared to Switch statement in modern programming languages like C#.

CASE statement provides following benefits:

Ability to format output
Ability to perform conditional logic on a Column
Ability to optimize query by emitting the need to write multiple queries to
return a result set
Ability to perform conditional Update
Ability to provide dynamic Order By in single query

CASE functions are of 2 types:

Simple [Comparison is done with substituted inline values]
Searched [Comparison is done on Column level]

For more details on CASE syntax visit: MSDN

Let us see CASE statement in action for the different type of scenarios:

(a) Formatted output using Simple CASE function:

SELECT
OrderID, OrderDate, ShipVia,
Shipper =
(CASE SHIPVIA
WHEN 1 THEN 'Speedy Express'
WHEN 2 THEN 'United Package'
WHEN 3 THEN 'Federal Shipping'
ELSE 'Unknown'
END)
FROM
Orders

(b) Column level comparison using Searched CASE function:

SELECT
OrderID, OrderDate, Freight,
FreightCategory =
(CASE
WHEN Freight <= 0 THEN 'No Freight'

WHEN (Freight >= 1 AND Freight <= 100) THEN 'Low Freight'

WHEN (Freight >= 100 ) THEN 'High Freight'

ELSE 'Unknown Freight'

END)
FROM
Orders

(c) Dynamic Order By functionality using CASE function:

This is helpful in the scenario when query needs to return the output by multiple columns. CASE function can be used to accomplish this by using Column names as variable which can be evaluated and substituted in the order by clause criteria.

Sample example below shows a query which can be dynamically sorted by LastName, FirstName and EmployeeID (default), the same query can be packaged into a stored procedure where it can take column names as a parameter and return the output accordingly:

--dynamic Order By
Declare @OrderBy Varchar(100)
--order by LastName
SELECT @OrderBy = 'LastName'

SELECT
EmployeeID, LastName, FirstName, Title, BirthDate, HireDate
FROM
Employees
ORDER BY
CASE
WHEN @OrderBy = 'LastName' THEN LastName
WHEN @OrderBy = 'FirstName' THEN FirstName
--default
ELSE Cast(EmployeeID As Varchar)
END

--dynamic Order By
Declare @OrderBy Varchar(100)
--order by FirstName
SELECT @OrderBy = 'FirstName'

SELECT
EmployeeID, LastName, FirstName, Title, BirthDate, HireDate
FROM
Employees
ORDER BY
CASE
WHEN @OrderBy = 'LastName' THEN LastName
WHEN @OrderBy = 'FirstName' THEN FirstName
--default
ELSE Cast(EmployeeID As Varchar)
END

(d) Conditional Update functionality using CASE function:

This is helpful in the scenario when multiple updates are needed for a particular table. CASE function can be used to accomplish the same operation using single update statement instead of writing multiple update statements for each criteria.

Sample example below adds $8 to all the Products which have UnitPrice less than 50, adds $5 to Products having UnitPrice greater than 50 and less than 100, adds $3 to Products having UnitPrice greater than 100 and finally for conditions which are falling in any of the criteria it puts the same UnitPrice (unchanged):

--put in temporary transaction to perform rollback
BEGIN TRAN
UPDATE Products SET UnitPrice =
CASE
WHEN (UnitPrice >= 0 AND UnitPrice <= 50) THEN (UnitPrice + 8)

WHEN (UnitPrice >= 50 AND UnitPrice <= 100) THEN (UnitPrice + 5)

WHEN (UnitPrice >= 100) THEN (UnitPrice + 3)
ELSE UnitPrice
END
--roll back the changes
ROLLBACK