C# - Retrieve Excel Workbook Sheet Names.

Introduction

There are many examples using ADO.NET to query an Excel Workbook but they all have a limitation, you must know the worksheet name. This might work if you know the name of the worksheet, but what if you don't? If your program is dynamic and your sheet names differ for each Excel workbook, you need a way to extract the names of the sheets. The code example provides you with a way to retrieve the work sheet names.

The following method returns a string array containing the names of the sheets. The method also shows how to loop through the array. The method has one input parameter excelFile which is the location of the Excel file.

Code Example

/// <summary>



/// This mehtod retrieves the excel sheet names from



/// an excel workbook.



/// </summary>



/// <param name="excelFile">The excel file.</param>



/// <returns>String[]</returns>



private String[] GetExcelSheetNames(string excelFile)

{

  OleDbConnection objConn = null;

  System.Data.DataTable dt = null;



  try

  {

    // Connection String. Change the excel file to the file you



    // will search.



    String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 

        "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";

    // Create connection object by using the preceding connection string.



    objConn = new OleDbConnection(connString);

    // Open connection with the database.



    objConn.Open();

    // Get the data table containg the schema guid.



    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

 

    if(dt == null)

    {

      return null;

    }



    String[] excelSheets = new String[dt.Rows.Count];

    int i = 0;



    // Add the sheet name to the string array.



    foreach(DataRow row in dt.Rows)

    {

      excelSheets[i] = row["TABLE_NAME"].ToString();

      i++;

    }



    // Loop through all of the sheets if you want too...



    for(int j=0; j <>

    {

      // Query each excel sheet.



    }



    return excelSheets;

  }

  catch(Exception ex)

  {

    return null;

  }

  finally

  {

    // Clean up.



    if(objConn != null)

    {

      objConn.Close();

      objConn.Dispose();

    }

    if(dt != null)

    {

      dt.Dispose();

    }

  }

}

Referenced from: http://www.codeproject.com/KB/aspnet/getsheetnames.aspx

Language Integrated Query (LINQ) to SQL

Introducing LINQ to Relational Data

LINQ introduced the concept of a unified data access technique to eliminate challenges of accessing data from a disparity of sources. LINQ has been extended to include the ability to access relational data through LINQ to SQL and LINQ to Entities. LINQ to SQL offers a direct mapping to the Microsoft SQL Server family of databases and allows you to query and manipulate objects associated with database tables. LINQ to Entities offers a more flexible mapping to relational data stored in other databases beyond just SQL Server. You build against a conceptual schema that is mapped to an actual schema. The remainder of this article will concentrate on LINQ to SQL.

LINQ to SQL Object Model

LINQ to SQL allows you to query and manipulate objects associated with database tables. Creating an object model involves creating a typed object that represents the database connection along with classes that map to database entities. The following list of objects are involved:

* DataContext - A base class that is a disposable type that gets extended to represent a strongly typed connection to the database.
* Classes and Collections - Represent objects and collection of objects that map to database entities.
* Methods - Are used to extend classes or to represent stored procedure calls.

There are a few different ways to create an object model. The two most straight forward approaches are to use the provided designer within Visual Studio 2008 to auto-generate the objects and mappings or adding attributes to existing objects. For this example I downloaded the Northwind sample database and loaded it in my SQL Server so that I could retrieve data from it for testing. I created a new solution and added a console application. I then added a LINQ to SQL data class to the console application. I used the Server Explorer to setup a new connection to the Northwind database my local SQL Server, and then dragged all of the tables from the Server Explorer on to the design surface for the data class. Finally I right clicked on the design surface and chose "Layout Diagram" to auto arrange all of the newly added tables and relationships. Refer to Figure 1 for an example of the completed design surface

Test Driving LINQ to SQL through Examples

Now that we've covered the background let's use a couple of examples of LINQ to SQL. Our examples will demonstrate the use of the DataContext object along with an example of querying the Northwind sample database using expressions.
Querying Data
The following example code uses LINQ syntax to query data from the database and then loop through it to print it to the console. You'll notice how the query expression syntax is the same as what you would find whether it be LINQ to XML or another form of LINQ. Also notice how the NorthwindDataClassesDataContext object is wrapped within a using construct. The NorthwindDataClassesDataContext is an object that extends the DataContext class and represents a strongly typed connection to our database. The NorthwindDataClassesDataContext is a disposable type, so wrapping it in a using block ensures it is properly disposed after use.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
var results = from customers in context.Customers where customers.City == "London" orderby customers.CompanyName select customers;
foreach (var customer in results)
{
Console.WriteLine("Company is {0} and contact is {1}", customer.CompanyName, customer.ContactName
);
} // Pause to see the output Console.ReadLine(); }

Modifying Data Using the Created Object Model

The previous example focused on the retrieval of data. We'll now examine the basic data manipulation, create, update, and delete operations and how LINQ to SQL makes them simple. The object model used to manipulate the data is the model generated in one of the earlier sections. After each operation we'll use the SubmitChanges() method to save the changes to the database. We'll execute some simple query expressions with the Count() method to verify the modifications were made as desired. As the example will show, you can use instance of objects, make modifications to them, and the changes that you make are tracked in a change set and pushed back to the database.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Add a new record and verify it exists through Count
var customer = new Customer()
{
CompanyName = "Drama Cafe", CustomerID = "DRACA", ContactName = "Tom Smith", City = "Beverly Hills", Address = "123 Melrose Place", PostalCode = "90210"
};
context.Customers.InsertOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count()); // Modify the record and verify it is changed through Count customer.ContactName = "Joe Smith";
context.SubmitChanges();
Console.WriteLine("Number of Joe Smith records: {0}", context.Customers.Where(c => c.ContactName == "Joe Smith").Count()); // Delete a record and verify it is removed through Count context.Customers.DeleteOnSubmit(customer);
context.SubmitChanges();
Console.WriteLine("Number of DRACA records: {0}", context.Customers.Where(c => c.CustomerID == "DRACA").Count());
// Pause to see the output Console.ReadLine();

Calling Stored Procedures

The previous retrieve, create, update, and delete operations involved dynamically generated SQL statements. It is also possible to call stored procedures in place of dynamically generated SQL. The sample code below demonstrates how to call stored procedures. The Northwind database has a stored procedure called "Ten Most Expensive Products" we will use for this example. Once again using the Server Explorer navigate to the Stored Procedures in the tree view and drag the stored procedure to the design surface. You'll want to right click on the design surface and select "Show Methods Pane" if it isn't already available. You should now see a method, Ten_Most_Expensive_Products(), that has been created and we'll use in our next code example to get the list of top products and display the prices to the console.

using (NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext()) {
// Use the ten most expensive products stored procedure var results = from products in context.Ten_Most_Expensive_Products() select products;
foreach (var product in results)
{
Console.WriteLine("Product price is {0}", product.UnitPrice);
} // Pause to see the output Console.ReadLine();

LINQ To SQL

Introduction

LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.

These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.

I recommend that you read 101 LINQ Samples if you would like to learn more.


// Basic
// Select * From Products
var query1 = from p in db.Products select p;
// Select ProductID, ProductName, UnitPrice From Products
var query2 = from p in db.Products select new
{
p.ProductID, p.ProductName, p.UnitPrice
};

Note: query2 will create a new class which contains three properties that map the ProductId, ProductName, and UnitPrice.

// Where // Select * From Products Where ProductID = 1
var query3 = from p in db.Products where p.ProductID == 1 select p;

// Select * From Products Where SupplierId =5 and UnitPrice > 20
var query4 = from p in db.Products where p.SupplierID == 5 && p.UnitPrice > 20 select p;

// Select * From Products Where SupplierId =5 Or SupplierId=6
var query5 = from p in db.Products where p.SupplierID == 5 || p.SupplierID == 6 select p;

Note: The condition in the where block is a logical express, a boolean value is returned just like in if().

// Order By
// Select * From Products Order By ProductId
var query6 = from p in db.Products
orderby p.ProductID
select p;

// Select * From Products Order By ProductId Desc
var query7 = from p in db.Products
orderby p.ProductID descending
select p;

// Select * From Products Order By CategoryId, UnitPrice Desc
var query8 = from p in db.Products
orderby p.CategoryID, p.UnitPrice descending
select p;

Note: The default order is ascending, the order by p.ProductID is same as order by p.ProductID ascending, just like in T-SQL.

// Top
// Select Top 10 * From Products
var query9 = (from p in db.Products
select p).Take(10);

// Select Top 1 * From Products
var query10 = (from p in db.Products
select p).Take(1);
// or
var query11 = (from p in db.Products
select p).First();

Note: If it just returns one record, I recommend using First instead of Take(1).

// Top with Order By
// Select Top 10 * From Products Order By ProductId
var query12 = (from p in db.Products
orderby p.ProductID
select p).Take(10);

// Distinct
// Select Distinct CategoryId From Products
var query13 = (from p in db.Products
select p.CategoryID).Distinct();

// Group By
// Select CategoryId, Count(CategoryID) As NewField
// From Products Group By CategoryId
var query14 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Count()
};

// Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
var query15 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Average(k => k.UnitPrice)
};

// Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
var query16 = from p in db.Products
group p by p.CategoryID into g
select new {
CategoryId = g.Key,
NewField = g.Sum(k => k.UnitPrice )
};

// Union
// Select * From Products Where CategoryId =1 union Select *
// From Products Where CategoryId = 2
var query17 = (from p in db.Products
where p.CategoryID == 1
select p).Union(
from m in db.Products
where m.CategoryID == 2
select m
);

// Two tables
// Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName
// From Products A, Categories B
// Where A.CategoryID = B.CategoryID and A.SupplierId =1
var query18 = from p in db.Products
from m in db.Categories
where p.CategoryID == m.CategoryID && p.SupplierID == 1
select new {
p.ProductID,
p.ProductName,
m.CategoryID,
m.CategoryName
};

Introduction To silverLight Controls

Figure 6-1

Width=”500” Text=”Using the TextBlock with runs…”>


Second Line with Verdana


Text=”3rd line with Times New Roman” />



168
Part II: Developing ASP.NET Applications with Silverlight
Introduction to Silverlight Controls
Silverlight 2 provides more than 25 controls that can be grouped into four general categories including
user input controls, layout controls (see Chapter 5), items controls, and media controls. Figure 6-1 shows
what some of these controls look like in the Visual Studio Toolbox.
Figure 6-1
User input controls include common controls found in many other frameworks such as TextBox, Button,
and CheckBox, as well as some nonstandard controls such as ToggleButton and RepeatButton. Layout
controls include Canvas, Border, Grid, and StackPanel, and item controls (used to show collections
of items) include DataGrid, ListBox, and ComboBox. Finally, media controls include MediaElement,
Image, and MultiScaleImage. Additional supporting controls such as GridViewSplitter and
ScrollViewer exist as well.
All of the controls available in Silverlight 2 can be defined declaratively in XAML or dynamically in
code like ASP.NET controls. In fact, if you come from an ASP.NET or WPF background, you will find
the concept of defining controls in XAML very straightforward. If you’re new to the concept of defining
controls declaratively, you’ll see that it’s simple once you know the fundamentals.


169
Chapter 6: Silverlight Controls
Defining Controls in XAML
In Chapter 3, you were provided with an introduction to Extensible Application Markup Language
(XAML) and shown how XML elements and attributes could be defined in XAML files. If you’re used
to defining controls in ASP.NET Web Forms, you’ll quickly discover that XAML isn’t quite as forgiving
with syntax issues. When you’re defining controls in XAML, there are three key points to keep in mind.
First, XAML is case-sensitive, so it’s important that you case your control names and associated attributes
properly. Visual Studio allows you to drag and drop controls from the toolbox; thus in many cases, you
can avoid manually typing controls into XAML files. Second, attribute values must be quoted. ASP.NET
doesn’t have this requirement (although you should quote your attributes there as well when defining
controls) and is quite forgiving when you don’t include quotes around attribute values. Finally, opening
tags must always have corresponding closing tags. If you forget to close a tag, you’ll have compilation
issues.
Short-cut close tags are allowed when a particular control has no content defined. By using short-cut tags
where appropriate, you can minimize typing as well as the size of the XAML file. Here’s an example of a
short-cut close tag for a TextBlock control. Notice that no closing tag is required since
the control has no content between the start and end tags and only defines attributes.

With those rules in mind, here’s an example of defining a Grid control inside a UserControl using XAML:




Looking at the code, you’ll notice that the control defines a Name attribute, which is prefixed with the
x namespace prefix and sets the Background to a value of White. The x prefix is defined on the User
Control element and points to a unique Uniform Resource Identifier (URI) value of http://schemas
.microsoft.com/winfx/2006/xaml. You’ll use x:Name rather than id when defining a control name
that you may want to access through code. As with ASP.NET controls, all control names within a XAML
file must be unique, start with an alphabetic character or underscore, and contain only alphanumeric
characters or underscores.
Silverlight controls that derive from FrameworkElement expose a Name property
that provides a convenient way to set the XAML-defined x:Name attribute. You can
use x:Name or Name to define the name of a control in a XAML file.
In addition to the attributes defined on the Grid element, you’ll see that the beginning Grid control
element has a matching ending element defined and that the case of both elements matches exactly.

1.3. XAML
The eXtensible Application Markup Language (XAML) is a declarative language that enables you to initialize objects in XML format. This format enables you to easily visualize a hierarchy of elements while, at the same time, separating content from code. This separation is possible because each XAML element maps to a .NET type. Each attribute within an element corresponds to a property within a .NET type. This concept is probably best illustrated by figure 1.7.
Figure 1.7. Any object that you create in XAML can also be created within code.

The illustration in this figure shows three code equivalents to a segment of XAML. Notice that the TextBlock element in the XAML code corresponds to an initialization statement within the code segments. This initialization occurs because, each time an element is created in XAML, the corresponding .NET type's default constructor is called behind the scenes. Also occurring behind the scenes is the executable code defined within the code-behind files.
1.3.1. Code behind
Much like ASP.NET, XAML pages support the concept of code-behind pages. Code-behind pages enable you to separate code from design by placing the UI-related code in the XAML and the executable code within a linked source file. This relationship between XAML and source code is best exemplified by figure 1.8.
Figure 1.8. The relationship between an XAML file and a code-behind file within a C# project

As this figure illustrates, the XAML code is stored within a .xaml file while the code-behind class definition is stored within a .xaml.cs file. (The extension will vary based upon what language you're using.) In all honesty, the class definition is stored within two files: a file with the .xaml.cs suffix and a file with the .xaml.g.cs suffix, which is automatically created by Visual Studio. The .xaml.g.cs file provides the variable and method declaration for ease of development. In fact, the InitializeComponent method from figure 1.8 is stored within the .xaml.g.cs file. Your coding efforts should take place within the .xaml.cs file though; for this reason, we'll focus on that file.
The XAML file references the code-behind file through the x:Class attribute. This class definition is compiled and stored within an assembly that gets placed within a directory relative to the application called ClientBin. The class definition is primarily used to handle the events triggered through the user interface defined within the corresponding XAML file. As shown in figure 1.8, through the Loaded attribute, you can specify the name of the event-handling method within the XAML code. When using this approach, the compiler expects a method named after the value of the attribute within the code-behind file. You must ensure that the method accepts the correct number and type of parameters.
1.3.2. Namespaces
A namespace provides a way of organizing related objects within a common grouping. These groupings, or namespaces, give you a way to define where the compiler should look for a type. To specify where to look, you reference a namespace within the root element of an XAML file. Snippet 1.1 illustrates the use of two namespaces.
Snippet 1.1. XAML: A basic XAML file referencing two namespaces





As this snippet illustrates, you're permitted to summon multiple namespaces within a single XAML file. When you reference multiple namespaces, each namespace must be uniquely prefaced. For instance, the x prefix in this example is used in association with the http://schemas.microsoft.com/winfx/2006/xaml namespace. At the same time, the http://schemas.microsoft.com/winfx/2006/xaml/presentation namespace doesn't use a prefix.
These two namespaces we just mentioned will be used in almost every Silverlight application you work with or see. These namespaces are generally defined in the following manner and expose these features to your Silverlight applications:
http://schemas.microsoft.com/winfx/2006/xaml/presentation— Provides your applications with the core Silverlight elements. For this reason, this namespace generally omits a prefix, making it the default namespace within the page. This approach enables you to reference elements within this specific namespace without having to include the prefix.
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"— Provides functionality that's common across XAML. It's important to remember that XAML is used by other technologies, such as WPF and Windows Workflow Foundation (WF), all of which need access to common features such as Name, Key, and Class properties.
A tale of two namespaces
In addition to the http://Schemas.microsoft.com/winfx/2006/xaml/presentation namespace, the Silverlight runtime supports http://schemas.micro-soft.com/client/2007 as the default namespace. But, you should use http://schemas.microsoft.com/winfx/2006/xaml/presentation as the default namespace because Visual Studio and Blend recognize this namespace. This namespace also makes it easier to promote your Silverlight applications to WPF applications if you need to at a later point.
In addition to these two commonly used namespaces, Silverlight gives you the flexibility to reference other namespaces including those within your own custom assemblies. When another assembly is referenced, it gets copied into a subdirectory of your Silverlight application called ClientBin. In fact, when you compile your Silverlight application, it itself gets compiled into an assembly that gets placed in this directory. We'll discuss the application model itself a little bit later; for now, in order to reference these assemblies, you need to define a new namespace, which includes a prefix, namespace, and assembly. Snippet 1.2 illustrates this concept.
Snippet 1.2. XAML: This example references a custom assembly called MyAssembly.dll that contains a namespace called MyNamespace. To access the elements within MyNamespace, the my prefix is defined.

As this snippet illustrates, referencing other elements, including custom elements, only requires you to provide a couple more details.
1.3.3. Compound properties
Although the illustration in figure 1.6 shows the relationship between XAML and .NET types and properties, we did omit one detail pertaining to properties. Properties within XAML may consist of elements significantly more complex and detailed than primitive types. For instance, the XAML in snippet 1.3 uses another kind of .NET object, called a LinearGradientBrush, to define the background of a Grid.
Snippet 1.3. XAML: A Grid with a gradient background











The concept of a GradientBrush is discussed but, as you can see, you define the Background element inside the XAML hierarchy. This approach is enabled by a compound property.
A compound property enables you to use the syntax of TypeName.PropertyName within an element to define more complex items within an element. This powerful feature gives you the ability to easily see the hierarchy of an object structure. In addition, it gives you a significant amount of flexibility when you're creating control templates, a topic we'll discuss . But first, another kind of property is as equally important as the compound property.
1.3.4. Attached properties
An attached property is a property specified within an element other than the element that references it. Although this seems like a mouthful, it really isn't. You can easily recognize an attached property by its consistent syntax of Attached-Element.PropertyName. These special attributes are generally used within the context of layout panels, which we discuss .
As an example, let's pretend you need to define a Rectangle within a Canvas. This Rectangle will have an offset of 10 pixels from the top and 10 pixels from the left of the Canvas. The subject (Rectangle) is presented relative to the parent (Canvas), so, as snippet 1.4 shows, the subject is attached to the parent.

Snippet 1.4. XAML: An example showing the use of two attached properties in action


This snippet uses the Canvas.Left and Canvas.Top attached properties to position the Rectangle within the Canvas. As you can see, the attached properties are set just like traditional properties.

How to get the Primary Key from different DataBases

For SQL Server We Can Use

SELECT cu.CONSTRAINT_NAME, cu.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE
tc.CONSTRAINT_CATALOG = 'psycinfo_NewProcess' AND tc.TABLE_NAME = 'Users' AND
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )


For Oracle

You can retrieve primary key information with the following SQL statement:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

If you knew the table name that you were looking for, you could modify the SQL as follows:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;


Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase.

Let's quickly explain the output from this query.

table_name is the name of the table (stored in uppercase).

column_name is the name of the column that is a part of the primary key. (also stored in uppercase)

position is the position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important.

status indicates whether the primary key is currently enabled or disabled.

owner indicates the schema that owns the table.


For Mysql is


SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema='db'
AND t.table_name='tbl';

replace 'db' with databaseName
and 'tbl' with table Name

Constructing Web Interfaces on the Fly

Web applications are dynamic- many requiring unique content and interfaces for each user. There are a myriad of ways to take user content out of a database and present it in a browser. This article focuses on the different ways it can be done in the Microsoft world (specifically in ASP.NET), why we chose XSLT for our own product, and finally a look at our technical implementation.

Planning for Dynamic Content

Whether you are in the process of building a new business web application or planning to build one, you most likely need to address how your web application will handle displaying different content for different users.

Considerations

When considering the different options for displaying dynamic content you generally need to take into account the following aspects:

• Usability
  Usability might be one of the most critical aspects in the success (or lack thereof) of your application.
• Development Time
  This includes the total amount of development time involved in satisfying your current application requirements.
• Flexibility
  Regardless of how comprehensive your current requirements are, applications tend to evolve over time. It’s important to evaluate the development effort and skill sets required to accommodate changes.
• Support, Maintenance & Ongoing Enhancements
  Commonly ignored by many when planning new development projects, it is generally responsible for a good chunk of the total cost of an application over the span of its life. This includes bug fixes, client customizations, minor application enhancements and of course, QA and testing.

The Options in ASP.NET

Generally web applications that use ASP.NET have two main options for displaying dynamic content:

• Server Controls
  • With Binding – retrieving the relevant data and binding it to the appropriate ASP.Net server controls on a web form
  • In Code – populating the appropriate ASP.Net server controls in code
• HTML
  • In Code – constructing the HTML to display in code based on the information retrieved from the database
  • With XSLT – retrieving the database information in XML format and then transforming it into HTML with XSLT

A third option is Silverlight, a new technology introduced as an option for web applications by Microsoft about a year ago. Silverlight provides a very rich GUI with the power of the .Net platform (a subset, actually) and tools that make web application development similar to the XAML interfaces found in the latest Windows application development. Silverlight is outside the scope of this particular article though.

Comparing the Different Options

Before you decide on the best route to take to display dynamic content you need to evaluate the impact of each approach on your application. You might find the table below helpful when making this decision.

Approach	Pros & Cons	When to Use
Server Controls
With Binding	The quickest approach in terms of development time but also the least flexible.	Best suited for applications with low data complexity and that are not expected to change often.
In Code	Hooking up the controls in code takes longer in terms of development but is much more flexible than binding. This approach also requires a higher level of support and maintenance.	Best suited for applications with high data complexity and that are not expected to change often.
HTML
In Code	Constructing HTML in code gives a good deal of flexibility over the markup that’s created but is time-consuming and extraordinarily brittle. This approach requires extensive testing and support.	I wouldn’t recommend this to be used as a general strategy for web applications but at times this might be used for particular sections depending on a unique set of requirements.
Using XSLT	Offers high flexibility and the least amount of ongoing maintenance and testing. The development time might be longer depending on your team’s skill set.	Best suited for applications with high data complexity and ones that are expected to change significantly and frequently. In addition, this allows for a lot of room for creativity as far as interface design goes.
Silverlight	Provides the richest GUI possibilities and the complete separation of interface and code. The time to develop, test and support are currently higher than other options.	Best suited for applications that require very rich user interfaces, and for development teams that primarily focus on Windows development.

Real-World Use Case: Scopings

When we started evaluating the requirements for Scopings, our homegrown recruiting platform, we needed a way to present complex content with a unique look and feel, a high level of usability, an infrastructure that can easily adapt to frequent and substantial changes, and built-in capabilities for globalization.

Very quickly we realized that although we can use ASP.Net server controls to build the first revision of Scopings in a relatively short period of time, this wouldn’t adapt well to the frequent changes we expected to be an inevitable part of the product lifecycle. This option would substantially increase our total cost of ownership.

After much analysis and many discussions, it became clear to us that designing our own infrastructure for constructing HTML using XML and XSLT would satisfy all of our requirements. We sat down and started to design an infrastructure that has ultimately been used for more than 80% of the functionality on Scopings.

Constructing the HTML

The idea behind the Scopings infrastructure was to allow us to make substantial changes to our user interface, while eliminating the need for any code changes and development staff involvement, and substantially decreasing the amount of QA required following any changes to the user interface.

To accommodate these requirements the Scopings infrastructure was built to be completely indifferent to the information retrieved from our database and to the way the information is ultimately displayed.

To achieve this we designed the infrastructure as follows:

• A separate stored procedure was built against each web content page to be displayed, and was designed to only return XML back to the calling code.
• Upon loading a web page, the code behind would load the XML for the current user. The XSLT would then transform the XML to an HTML interface for the user.
• The web page would be responsible for all functionality, both on the server in the code-behind or on the client using Javascript.
• All styling was handled with CSS.

Given the above mechanism, any change to the way we display data, or to the content of the page would only involve the following:

• If any additional data is needed we would modify the stored procedure to include the new data required. If no additional data is needed, no change to the stored procedure will be made.
• Modify the XSLT to include any new data to be displayed, and any changes to the display.
• Modify the corresponding CSS files to make any changes required for styling.
• Test display changes and specific page functionality if any.

This allows for content and interface changes that require little to no development efforts and can be done very quickly with minimal testing.

Constructing Web Interfaces with XSLT

In this section I’m going to provide the technical details needed to build a simple infrastructure for retrieving dynamic content from a SQL Server database and displaying it on a web page using XSLT.

As discussed above, the process of displaying dynamic content includes the following steps:

• Extracting Database Content – building a stored procedure to output all web page content as XML.
• Retrieving the XML from the Database – building a function to call the stored procedure and retrieve its data.
• Transforming XML into HTML using XSLT – developing the XSLT required to transform the XML retrieved from the database into HTML.
• Displaying Content on the Web Page – configuring the web page to retrieve the XML, convert it to HTML and finally display the interface to the user.

Step 1 - Extracting Database Content

The most flexible and powerful way to extract database content is simply to create stored procedures that output XML. This way all the data needed to for the interface can be easily extracted with one database call. A future change to the content only requires slight adjustments of the appropriate stored procedure without any changes to the calling code.

The following is a sample stored procedure that builds an XML document with a specific user’s first and last name, and a list of tasks this particular user needs to accomplish:

CREATE procedure [dbo].[Get_User_Profile_XML]
@User_ID varchar(10)
AS
SET NOCOUNT ON;
SELECT    Users.[User_ID] AS [@id],
      Users.First_Name AS [@first],
      Users.Last_Name AS [@last],

      --all user tasks
      (
          SELECT    Tasks_ID     AS [@id],
              Task_Description    AS [@description]
          FROM    dbo.Users_Tasks
          WHERE    Users_Tasks.[User_ID] = Users.[User_ID]
          FOR XML PATH('task'),TYPE, ROOT('tasks')
      )
FROM    dbo.Users
WHERE    Users.[User_ID] = @User_ID  
FOR XML PATH('user'), TYPE;

Note: This was written against SQL Server 2005 and can be easily adjusted to use FOR XML EXPLICIT for use with SQL Server 2000.

The output XML of the above stored procedure might look as follows:

<user id="U12" first="John" last="Smith">
<tasks>
  <task id="T34" description="Annual review"/>
  <task id="T56" description="File my taxes"/>
tasks>
user>

Step 2 – Retrieving the XML Content from the Database

After building the appropriate stored procedure, we need to develop the code that can call this procedure on runtime and extract the appropriate data for the current user.

As increasing flexibility and lowering long-term maintenance costs are our primary goals, it is often best to keep the code needed to retrieve that database information almost entirely indifferent of the content it gets back. Extracting XML directly from the stored procedure gives us just that. We can keep changing the XML structure and content without requiring any changes to the calling code.

The following function might be used to extract the XML data from the stored procedure above and send the XML (as a string) back to the client:

private string GetUserProfileXML(string userId)
{
 const string MY_CONNECTION_STRING = "YOUR CONNECTION STRING";
 const string PROCEDURE = "dbo.Get_User_Profile_XML";
        

 using (SqlConnection connection = new SqlConnection(MY_CONNECTION_STRING))
 using (SqlCommand command = new SqlCommand())
 {
  connection.Open();

  command.Connection = connection;
  command.CommandText = PROCEDURE;
  command.CommandType = CommandType.StoredProcedure;

  SqlParameter param = new SqlParameter("@User_ID", SqlDbType.VarChar, 10);
  param.Value = new SqlString(userId);
  command.Parameters.Add(param);

  using (XmlReader reader = command.ExecuteXmlReader())
  {
    reader.MoveToContent();
    string resultXml = reader.ReadOuterXml();
    return resultXml;
  }
}
}

Note: This opens a connection to SQL server, configures the command object and its @ID parameter, and reads the XML as a string from the XmlReader.

Step 3 –Transforming XML into HTML using XSLT

Our next task is to transform the XML into HTML that can be displayed to the current user.

XSLT can be quite handy and an ideal solution when it comes to transforming XML into HTML. XSLT offers enormous flexibility, as it can be quickly adjusted to handle any changes to the XML content or changes to new requirements.

XSLT files can be easily included as part of any web application, and although this is beyond the scope of this article, development teams can choose to slightly adjust this approach and globalize their web applications by developing different XSLT files per language supported.

The following XSLT might be used to process the XML data above:

xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html"/>

<xsl:template match="user">
  <div class="userInfo">
    <xsl:value-of select="concat('Tasks for ', @first,' ',@last,':')"/>
  div>

  <div class="userTasks">
    <xsl:for-each select="tasks/task">
      <div class="userTask">
        <a>
          <xsl:attribute name="href">
            <xsl:value-of select="concat('taskinfo.aspx?id=',@id)"/>
          xsl:attribute>

          <xsl:value-of select="@description"/>
        a>
      div>
    xsl:for-each>
  div>
xsl:template>
xsl:stylesheet>

Note: This outputs the heading for the page (first and last name) and then outputs every associated user task.

The HTML result (without any applied CSS) might look like the following:

Tasks for John Smith:
Annual review
File my taxes

Step 4 – Displaying Content on the Web Page

The final step is to create the web page needed to process and display the appropriate user content. As described at the beginning of the article, we need to retrieve the XML from the database by calling the "GetUserProfileXML" function we developed earlier, transform the XML into HTML using the XSLT file above, and then finally to display the HTML to the user.

We first create an ASPX page with a server side DIV to ultimately contain the HTML. The page might look like the following:

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

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>My User Profile Pagetitle>
head>
<body>
  <form id="form1" runat="server">
  <div id="_divData" runat="server">
  div>
  form>
body>
html>

Note: The DIV is set to run on the server so the HTML can be assigned on the server.

Next, we need to develop the function that takes XML and transforms it into HTML using an XSLT file.

A function that accepts an XSLT file name and the XML data, and performs the transformation might look like the following:

private string GetPageHTML(string xsltFileName,string xmlData)
{
string fullXsltFilePath = Server.MapPath("~/" + xsltFileName);
using (XmlReader dataReader = LoadXMLToReader(xmlData))
{
  XslCompiledTransform xslTrans = new XslCompiledTransform();
  xslTrans.Load(fullXsltFilePath);
  using (MemoryStream outputStream = new MemoryStream())
  {
    xslTrans.Transform(dataReader, null, outputStream);
    outputStream.Position = 0;
    using (StreamReader sr = new StreamReader(outputStream, Encoding.UTF8))
    {
      string resultHtml = sr.ReadToEnd();
      return resultHtml;
    }
  }
}
}

Note: This function gets the full path to the XSLT file, loads the XML data into an XmlReader, and then uses XslCompiledTransform object to transform the XML into HTML and return the HTML as string to the calling code. Also, XslCompiledTransform is thread-safe object, so I would highly recommend caching it to further increase website performance.

You can use the following helper utility to load an XML string into an XmlReader object:

private XmlReader LoadXMLToReader(string inputXML)
{
byte[] xmlData = Encoding.UTF8.GetBytes(inputXML);
MemoryStream xmlStream = new MemoryStream(xmlData);
xmlStream.Position = 0;

XmlReader reader = XmlReader.Create(xmlStream);
reader.Read();
return reader;
}

Finally, to wrap everything up, here is what the Page_Load function on the web page might look like:

protected void Page_Load(object sender, EventArgs e)
{
const string XSLT_FILE_NAME = "UserProfile.xslt";
    
//gets the current user id
string userId = "U12";

//loads the xml data from the database
string xmlData = GetUserProfileXML(userId);

//transform the XML into HTML
string html = GetPageHTML(XSLT_FILE_NAME, xmlData);

//shows the html to the user
_divData.InnerHtml = html;
}

Note: The User ID is hard-coded but it should be retrieved dynamically based on the current logged-in user.

Conclusion

Dynamic content for web applications can be produced in several ways but if you are looking to build a web application with great flexibility, a high level of usability, and low total cost of ownership- using XSLT to transform XML into an HTML interface might be the right approach.