Free SharePoint Designer 2007 Training

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

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

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

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

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

* 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

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!