SQL Server XML Questions You Were Too Shy To Ask

by Robert Sheldon

Sometimes, XML seems a bewildering convention that offers solutions to problems that the average database user doesn't have. To make things worse, XML experts generally lack the wherewithal to provide simple answers to  even the simplest questions. Rob Sheldon, in contrast, can answer even those questions we somehow feel silly asking in public, and think twice about doing so.

The Questions

• Why store XML data in a SQL Server database? Why not just store the XML as documents in a file system?
• What makes an XML document different from a regular text file?.
• What is an XML instance in SQL Server, as opposed to an XML document?.
• When storing XML documents in a SQL Server database, should you always use the xml data type?
• What does it mean for an xml column to be typed?
• How do I convert an XML value to a string value in SQL Server?
• Is it worth indexing an xml column in a SQL Server database?
• What’s this I hear about selective XML indexes in SQL Server?
• How do I retrieve an individual value from a specific element within an XML instance?
• Why can't I just explore XML data using SELECT statements?
• How do I update an individual value for a specific element in an XML instance?
• Is it possible to retrieve relational data in a SQL Server database as XML?

Why store XML data in a SQL Server database? Why not just store the XML as documents in a file system?

In some cases, the file system might be fine, if you don’t plan to associate the files with any data in your SQL Server database and you don’t want to take advantage of any of the XML-specific features that SQL Server provides, such as being able to associate relational data with data in an XML instance. For example, suppose you have a database that stores product information. For each product, you maintain an XML document that provides details about that product. By storing the document within the database, you can create queries that retrieve both relational data and specific information within the XML instance related to that data.

In SQL Server, you usually store XML data in a column configured with the xml data type. The data type supports several methods that let you query and modify individual elements, attributes, and their values directly within the XML instance, rather than having to work with that instance as a whole. In addition, the xml data type ensures that each XML instance is, at the very least, well formed according to ISO standards. And if you associate a schema collection with your xmlcolumn, you can further validate the structure and its data.

SQL Server also provides several options for indexing your xml column, with expanded features starting in SQL Server 2012. In addition, storing your XML in a SQL Server database lets you take advantage of SQL Server’s administrative capabilities and data access mechanisms, such as OLE DB and ADO.NET. Even if you don’t store data in an xmlcolumn, you can still leverage the other SQL Server storage and access capabilities. That said, if you don’t need any of these features, you might not need SQL Server. Take a look at how you plan to access and modify your XML files, and then decide.

What makes an XML document different from a regular text file?

An XML document is one that conforms to the ISO formatting rules governing the eXtensible Markup Language (XML). As the name suggests, XML is a text-based language similar to HTML. It uses tags to describe the nature of the data and how the data should be formatted. Unlike HTML, however, XML lets you define your own tags, thus its extensible nature, which is why XML is often referred to as a self-describing language. Because it has been so universally adopted, you can use it to share a wide range of data across heterogeneous systems, as long as the XML conforms to ISO standards.

An XML document includes two basic types of information: the data itself and the tags that organize and describe the data. The following XML demonstrates the basic components of an XML document:

<Cars>

  <Car id="1234">

    <Make>Volkswagen</Make>

    <Model>Eurovan</Model>

    <Year>2003</Year>

    <Color>White</Color>

  </Car>

  <Car id="5678">

    <Make>Honda</Make>

    <Model>CRV</Model>

    <Year>2009</Year>

    <Color>Black</Color>

    <Mileage>35,600</Mileage>

  </Car>

</Cars>

Elements represent the building blocks of in an XML instance. They include the tags enclosed in angle brackets and the data between those tags. All elements contain an opening tag and an end tag, such as <Cars> and </Cars>, respectively. Between the opening and end tags, you’ll find child elements, data, or both. For example, the <Car>elements are embedded in the <Cars> element, with child elements such as <Make> and <Model> containing the actual data.

In addition, an element’s opening tag can include attributes, such as id="1234", which is a combination of an attribute name (id) and it’s value (1234). You’ll also notice that the XML begins with a special type of tag called a declaration. The declaration specifies which XML version is being used and can optionally include encoding information. In this case, the declaration indicates that the data is stored as a sequence of 8-bit Unicode characters.

This basic example also points out the hierarchical nature of XML and its self-describing nature. We could have just as easily created an XML structure that describes people, places, or other things. Although XML is nothing but simple text, its self-describing format lets it pass data to a variety of systems. As long as applications such as SQL Server have the capacity to parse the XML and that XML conforms to ISO standards, there’s no limit to the type of data you can pass between heterogeneous systems. Its universal acceptance and standardized methodology make it a simple yet effective way to move data between applications that serve different functions and are based on different technologies.

What is an XML instance in SQL Server, as opposed to an XML document?

You’ll often find that SQL Server documentation refers to the XML stored in an xml object (column, variable, or parameter) as an XML instance. This is because SQL Server supports XML documents and XML fragments, both of which are considered XML instances. Although you’ll often see the terms XML instance and XML document used interchangeable, strictly speaking, they are different, with a document being a type of instance, as is a fragment.

You can store either an XML document or fragment in an xml object. However, if you associate that object with a schema collection, you can specify that only documents be permitted. To be considered a document, an XML instance must have only one root element, with no text specified at the top level. Consequently, the following XML instance is considered a document:

<Cars>

  <Car id="1234">

    <Make>Volkswagen</Make>

    <Model>Eurovan</Model>

    <Year>2003</Year>

    <Color>White</Color>

  </Car>

  <Car id="5678">

    <Make>Honda</Make>

    <Model>CRV</Model>

    <Year>2009</Year>

    <Color>Black</Color>

    <Mileage>35,600</Mileage>

  </Car>

</Cars>

However, if we were to strip out the <Cars> element, we would have only two <Car> elements with no root, which would leave us with an XML fragment, as shown in the following example:

<Car id="1234">

  <Make>Volkswagen</Make>

  <Model>Eurovan</Model>

  <Year>2003</Year>

  <Color>White</Color>

</Car>

<Car id="5678">

  <Make>Honda</Make>

  <Model>CRV</Model>

  <Year>2009</Year>

  <Color>Black</Color>

  <Mileage>35,600</Mileage>

</Car>

Both examples are considered XML instances, but only the first is a document.

When storing XML documents in a SQL Server database, should you always use the xml data type?

No, you should not. In some cases, you should use large object storage such as varchar(max) or nvarchar(max). It all depends on how you’ll be accessing and updating the XML instances and whether you want to validate the XML structure.

The xml data type requires that all XML be well formed according to ISO standards, which specify how the XML elements must be formatted. You can even take this a step further by associating a schema collection with your xml columns. A schema collection contains one or more XML schemas that define the types of elements and attributes an XML instance can contain. Schemas also more tightly define the type of values that can be associated with an element or attribute.

In addition, the xml data type supports a set of methods that let you query specific elements and attributes within an XML instance. For example, suppose your xml column stores all the contact information associated with each customer. The data might include email addresses, phone numbers, social networking handles, and physical addresses. From this column, you can retrieve an email address or add a cell phone number or change a home address. Plus, you can define an XML index on an xml column if your query workload commonly includes that column.

In some cases, however, you don’t need any of the features that the xml data type provides. For example, you might be storing product manuals that are accessed only in their entirety. If the manual is modified, the xml value itself is replaced with the new document. You never need to query or modify individual components, and you don’t care about the XML being validated. In a situation such as this, you should use a large object data type to avoid the extra validating processes that the database engine performs for an xml column. In general, whenever your application is simply storing and retrieving the entire XML instance or you want to preserve an XML instance in its original form, as might be the case with legal documents, you should stick with large object storage.

What does it mean for an xml column to be typed?

A typed xml column (or variable or parameter) refers to one that is bound to an XML schema collection within SQL Server. A schema collection is a set of one or more XML schemas that determine the permitted structure of the XML instances stored within that column.

The schema specifies the types of elements and attributes permitted in the XML and how those components should be ordered and hierarchically preserved. A schema also determines the type of values permitted in an element or attribute (such as string or integer) and the number to times an element can be included. For example, the schema might specify that there must be exactly one <FirstName> child element within each <Customer> element and the <FirstName>value must be a string.

An xml column not associated with a schema collection is considered untyped. However, the XML instance must still be well formed accorded to ISO standards. For example, each element must be defined by opening and end tags, as in the following example:

<FirstName>Elizabeth</FirstName>

Notice that the two tags are identical, except that the end tag also includes a slash to distinguish it from the opening tag. In addition, the opening and end tag names must have identical capitalization.

When you insert an XML instance into an untyped xml column, the database engine validates the XML to ensure that it is well formed. When you insert an XML instance into a typed column, the engine ensures that the XML is well formed andthat it adheres to the more rigid structure defined in the associated schema.

You should use typed xml columns whenever you have the necessary schema collections and you want SQL Server to validate the XML against that schema. If you don’t have a schema collection or don’t want to associate an xml column with the collection you do have, then use an untyped column.

How do I convert an XML value to a string value in SQL Server?

Unlike many types of conversions in SQL Server, you cannot implicitly convert an xml value to a string value. Although SQL Server supports many types of implicit data type conversions, including string data to XML, it does not support implicit XML-to-string conversions. For that, you must use either the CAST or CONVERT function. (If you do try to do an implicit conversion, you’ll receive an error.)

Converting XML to a string is a fairly straightforward process. The following example shows you how to use the CASTfunction to convert an xml variable varchar(max):

DECLARE @xmlCar xml;

DECLARE @strCar varchar(max);

SET @xmlCar =

'<Car id="1234">

  <Make>Volkswagen</Make>

  <Model>Eurovan</Model>

  <Year>2003</Year>

  <Color>White</Color>

</Car>'

SET @strCar = CAST(@xmlCar AS varchar(max));

SELECT @strCar;

In the example, I’ve simply assigned the XML to the @xmlCar variable and used the CAST function to convert it to the string data type. If you were to run the SELECT statement, you would receive a single line of text that contains your XML instance:

<Car id="1234"><Make>Volkswagen</Make><Model>Eurovan</Model><Year>2003</Year><Color>White</Color></Car>

 If you want to instead use the CONVERT function, the final SET statement would look as follows:

SET @strCar = CONVERT(varchar(max), @xmlCar);

The function would again convert the XML instance to the varchar(max) data type, and the SELECT statement would return the same single line of text.

When using the CONVERT function, be aware that it’s specific to SQL Server and does not port to other systems. However, unlike CAST, the CONVERT function supports additional options for converting data. For example, if your XML instance has been saved with the white space and line breaks preserved, you can add a third argument to the function to preserve them when converting the instance to a string. Even so, if there’s any possibility that you’ll be porting your database to another system, you should probably stick with CAST

Is it worth indexing an xml column in a SQL Server database?

In some cases, yes. That’s why XML indexes  are available. Keep in mind that SQL Server stores XML data as large binary objects (BLOBS). When there is no index, the database engine shreds the XML at runtime when evaluating the query. In some cases, this can represent a fairly time-consuming process. If you plan to query the xml column routinely, then you should consider an XML index.

Also, you should consider an XML index if your XML instances are relatively large compared to the parts you’re retrieving. With an index, the engine doesn’t have to parse the entire instance at runtime, and queries can benefit from how the tags, values, and paths have been indexed. But be sure to take into account your data manipulation operations. XML indexes receiving heavy data modifications can severely affect query performance.

SQL Server supports two types of XML indexes: primary and secondary. The first index on an XML column must be a primary index, which stores a shredded representation of the XML instance. The index includes all tags, paths, and values, as well as other details about the instance, in order to support queries that target specific components. Queries that retrieve the entire instance do not use the index.

Secondary indexes are specific to the paths, values, or properties within the XML instance and support queries that target those specific elements. You can define any combination of one or more secondary index on you xml column, as long as you first define your primary index. For example, you might define a secondary index on the XML paths and another on the XML values because your queries frequently target both types of components within your XML instances.

What’s this I hear about selective XML indexes in SQL Server?

The selective index is new to SQL Server 2012 SP1, although it’s received relatively little press. The new feature addresses one of the main limitations of the original XML index, that is, it includes the entire XML instance, which can lead to unnecessary hits on performance and index maintenance.

The selective index feature lets you create primary and secondary XML indexes that contain only specific paths within your XML instances. In other words, you can create XML indexes based on a subset of nodes, ideally those you most often query. Selective indexes can improve query performance as well as reduce storage and maintenance costs. (A secondary selective index is based on a single path that is already included in an existing primary selective XML index.)

When you create a selective XML index, you specify the paths that should be included. However, you cannot index nodes with complex or binary XS types. In addition, you can index only child, attribute, or descendent axes. In general, you should avoid using selective XML indexes if you must index a large number of nodes.

In order to create a selective XML index, you must first enable the feature on your database. To do so, use thesp_db_selective_xml_index system stored procedure.

How do I retrieve an individual value from a specific element within an XML instance?

The simplest way to retrieve an individual value from an XML instance is to use the value() method, one of the five methods available to the xml data type. When using the value() method, you call it by first specifying the name of thexml object that contains the target data and then providing two arguments, an XQuery expression and the data type of the returned value, as shown in the following syntax:

xml_object.value('xquery_exp', 'data_type')

 XQuery is a scripting language used to access XML data. SQL Server supports a subset of the language that you can use in the expression you pass into the value() method and other xml methods. Although a full discussion of XQuery, as it is implemented in SQL Server, is way beyond what we can cover here, you can find more information in the MSDN XQuery Language Reference.

As you can see in the syntax, you must include the XQuery expression as the first argument to the value() method. In most cases, that expression will be a fairly straightforward path that points to the XML value you want to retrieve. If you're querying a typed column, that expression will also include a reference to the schema.

Let’s look at an example to get a sense of how the value() method works against an untyped variable. The following T-SQL declares the @cars variable with the xml data type and assigns an XML document to the variable:

DECLARE @cars xml;

SET @cars =

'<?xml version="1.0" encoding="UTF-8"?>

<Cars>

  <Car id="1234">

    <Make>Volkswagen</Make>

    <Model>Eurovan</Model>

    <Year>2003</Year>

    <Color>White</Color>

  </Car>

  <Car id="5678">

    <Make>Honda</Make>

    <Model>CRV</Model>

    <Year>2009</Year>

    <Color>Black</Color>

    <Mileage>35,600</Mileage>

  </Car>

</Cars>';

SELECT

  @cars.value('(/Cars/Car[@id=5678]/Make)[1]', 'varchar(15)')

    AS Make,

  @cars.value('(/Cars/Car[@id=5678]/Model)[1]', 'varchar(15)')

    AS Model;

After we declare and set the @cars variable, we can use it to retrieve the specific value we want. (It works the same way for an xml column.) When we call the variable, we add the value() method and pass in the two arguments. The first argument is the XQuery expression that points to the path where our data is located. The second argument specifies that the value be returned as a varchar(15) string.

In this case, we’re specifying that we retrieve the <Make> and <Model> values for the <Car> element whose idattribute is 5678. Notice that, although we’re referencing a specific <Car> element, we must still include the [1] after the XQuery expression. The value() method requires a scalar value so you must specify the [1] to ensure that a single value is being returned, whether or not it’s needed.

As to be expected, the SELECT statement returns the value Honda from the <Make> element and returns CRV from the<Model> element. However, by modifying the XQuery, we can retrieve whatever individual values we need.

Why can't I just explore XML data using SELECT statements?

You can. There is an excellent function published by Jacob Sebastian  called XMLTable to get you started. If you don't feel like writing your own queries to explore XML documents, you can use this to see what data is in a document, and the path to specific values within the document. If, on the other hand, you wish to know more, it is well worth investigating the source to see how it is done.

If, for example, you want to investigate the XPaths and values in an XML fragment, you can do this

DECLARE @cars xml;

SET @cars =

'<?xml version="1.0" encoding="UTF-8"?>

<Cars>

  <Car id="1234">

    <Make>Volkswagen</Make>

    <Model>Eurovan</Model>

    <Year>2003</Year>

    <Color>White</Color>

  </Car>

  <Car id="5678">

    <Make>Honda</Make>

    <Model>CRV</Model>

    <Year>2009</Year>

    <Color>Black</Color>

    <Mileage>35,600</Mileage>

  </Car>

</Cars>';

SELECT xpath, value FROM dbo.xmlTable(@cars)

WHERE value is not null ORDER BY xpath

This would give the result of …

xpath                      Value

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

Cars[1]/Car[1]/@id         1234

Cars[1]/Car[1]/Color[1]    White

Cars[1]/Car[1]/Make[1]     Volkswagen

Cars[1]/Car[1]/Model[1]    Eurovan

Cars[1]/Car[1]/Year[1]     2003

Cars[1]/Car[2]/@id         5678

Cars[1]/Car[2]/Color[1]    Black

Cars[1]/Car[2]/Make[1]     Honda

Cars[1]/Car[2]/Mileage[1]  35,600

Cars[1]/Car[2]/Model[1]    CRV

Cars[1]/Car[2]/Year[1]     2009

You could then use the XPaths supplied in order to select individual values from the XML document. For example:

SELECT @cars.value('Cars[1]/Car[2]/Make[1]','VARCHAR(20)')+' '+@cars.value('Cars[1]/Car[2]/Model[1]','VARCHAR(20)')  AS CarType

... giving ...

CarType

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

Honda CRV

Or then,  to select all the cars in the XML file you can use the XPath in the .nodes method.

SELECT  t.value('Make[1]','NVARCHAR(MAX)')+' '+ t.value('Model[1]','NVARCHAR(MAX)') AS CarType

  from @cars.nodes('/Cars/Car') x(t)

giving …

CarType

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

Volkswagen Eurovan

Honda CRV

How do I update an individual value for a specific element in an XML instance?

Another method that the xml data type supports is modify(), which uses the XML Data Modification Language (XML DML) to update, insert, and delete individual components within an XML instance. The XML DML language is a SQL Server extension to the XQuery language. Like XQuery, XML DML supports a number of different elements. For example, the second SET statement in the following T-SQL uses XML DML to update the Eurovan’s <Year> value to 2001:

DECLARE @cars xml;

SET @cars =

'<?xml version="1.0" encoding="UTF-8"?>

<Cars>

  <Car id="1234">

    <Make>Volkswagen</Make>

    <Model>Eurovan</Model>

    <Year>2003</Year>

    <Color>White</Color>

  </Car>

  <Car id="5678">

    <Make>Honda</Make>

    <Model>CRV</Model>

    <Year>2009</Year>

    <Color>Black</Color>

    <Mileage>35,600</Mileage>

  </Car>

</Cars>';

SET @cars.modify(

 'replace value of

  (/Cars/Car[@id=1234]/Year/text())[1]

  with "2001" ');

SELECT @cars;

As in the previous example, we declare the @cars variable and assign an XML value. Then we issue a second SETstatement that again specifies the variable, but this time calls the modify() method. In this case, the method takes only one argument enclosed in singe quotes, but that argument includes several components.

The argument starts with the replace value of keywords, which indicates that we’re updating data. This is followed by an XQuery path expression, in parentheses and followed by [1], which specifies what value we want to update. In this case, the path points to the <Year> node for car 1234. Notice, however, that the XQuery path expression also includes the text() function, which specifically targets the path’s value. We then follow with the with keyword and then the new value, 2001, enclosed in double quotes. When we run the SELECT statement, we’ll find that the <Year> value has been updated, but nothing else has been touched.

This, of course, is only one simple example of the modify() method. It can do a lot more than just update a single value. Be sure to refer to SQL Server Books Online more details about this and all the methods supported by the xml data type.

Is it possible to retrieve relational data in a SQL Server database as XML?

Yes. As a matter of fact, it’s fairly easy. Just add the FOR XML clause to your SELECT statement and specify a mode for how the data should be returned. Rather than returning a typical rowset, the SELECT statement will return the data as XML. You can add the FOR XML clause to your top-level SELECT statements as well as to subqueries embedded inSELECT, INSERT, UPDATE and DELETE statements.

When you include the FOR XML clause in your SELECT statement, you must also specify one of four nodes: RAW, AUTO,EXPLICIT, or PATH. The RAW mode returns a single element for each row in the result set. The element uses the<row> tag and treats each non-null column as an attribute. For example, the following SELECT statement joins several tables and returns a column from each:

USE AdventureWorks2012;

GO

SELECT TOP 2

  Sales.SalesOrderID AS SalesID,

  Territory.name AS TerritoryName,

  Country.name AS CountryName

FROM Sales.SalesOrderHeader Sales

  INNER JOIN Sales.SalesTerritory Territory

    ON Sales.TerritoryID = Territory.TerritoryID

  INNER JOIN Person.CountryRegion Country

    ON Territory.CountryRegionCode = Country.CountryRegionCode

FOR XML RAW;

Because the FOR XML clause is included, the data will be returned as XML. You can see this in the following results, in which each row returned is its own <row> element with the columns treated as attributes:

  <row SalesID="43664" TerritoryName="Northwest" CountryName="United States" />

  <row SalesID="43665" TerritoryName="Northwest" CountryName="United States" />

Unlike the RAW mode, the AUTO mode returns the results as nested XML elements, forming a hierarchy based on the order of the columns in the SELECT list. If we were to specify the AUTO keyword instead of RAW in our example SELECTstatement, the results would look quite different:

  <Sales SalesID="43659">

    <Territory TerritoryName="Southeast">

      <Country CountryName="United States" />

    </Territory>

  </Sales>

  <Sales SalesID="43660">

    <Territory TerritoryName="Southeast">

      <Country CountryName="United States" />

    </Territory>

  </Sales>

As you can see, the XML has fewer attributes and more elements than the preceding results. However, the form they take depends on how you construct your SELECT statement. It’s also worth noting that both modes support additional options, although neither one provide much control over how you form your XML. For that, we need to turn to the EXPLICIT andPATH modes.

The EXPLICIT mode provides much more control over the XML, but it can be more difficult to use because it requires some cumbersome workarounds to get your query to return the results in the proper form. The PATH mode provides much of the same control as the EXPLICIT mode, but it is far simpler to use, so let’s look at how that one works:

SELECT TOP 2

  Sales.SalesOrderID AS "@SalesID",

  Territory.name AS TerritoryName,

  Country.name AS CountryName

FROM Sales.SalesOrderHeader Sales

  INNER JOIN Sales.SalesTerritory Territory

    ON Sales.TerritoryID = Territory.TerritoryID

  INNER JOIN Person.CountryRegion Country

    ON Territory.CountryRegionCode = Country.CountryRegionCode

WHERE SalesOrderID < 43662

FOR XML PATH('Sale'), ROOT('Sales');

Again, we’re running the same SELECT statement as the previous examples, but this time around, our FOR XML clause specifies the PATH mode and a name for each row element, in this case, <Sale>. The mode name is then followed by the ROOT directive, which specifies that our root node should be <Sales>. The statement now returns the following results:

  <Sales>

    <Sale SalesID="43659">

      <TerritoryName>Southeast</TerritoryName>

      <CountryName>United States</CountryName>

    </Sale>

    <Sale SalesID="43660">

      <TerritoryName>Southeast</TerritoryName>

      <CountryName>United States</CountryName>

    </Sale>

  </Sales>

As you can see, the PATH mode gives us a great deal of control over how we format the XML output. Of course, these examples are just a simple overview of how the FOR XML clause and its modes work, but they give you the idea of the many options available to you for outputting your queries to XML.

Implementing Transactions in SQL Server – Part II

BY AMIT CHAUDHARY

One of the regular tasks of a DBA is to generate database schema change scripts, and then deploy the scripts to SQL databases. If an organization is not using a third-party tool, as is common, then Database Professional (which is part of Visual Studio from Microsoft) is normally used to accomplish this task. In many companies, the process of generating schema change scripts is a daily routine. Create DB scripts are generated on from TFS, a schema compare is performed between the previous and the latest build, and the schema update script is generated. The only issue is they do not run as a single transaction. The reason is that the scripts that are generated do not have explicit transactions defined. Unfortunately it is not simply a matter of defining a transaction using BEGIN TRANSACTION, and based on the @@TRANCOUNT variable in the end of the script either perform a Rollback or a Commit. This is due to the fact that after every DDL statement, DBPro inserts a GO statement, causing each statement to run as a batch, and explicit transactions do not span multiple batches. A sample script that is generated by DBPro would be similar to this:

PRINT N'Creating [dbo].[test1]'
GO
CREATE TABLE [dbo].[Test1]
(
[Col1] [bigint] NOT NULL IDENTITY(1, 1),
[Col2] [int] NOT NULL,
[Col3] [varchar] (50) NOT NULL,
[Col4] [varchar] (50) NOT NULL
)
GO
PRINT N'Creating primary key [PK_Test1] on [dbo].[Test1]'
GO
ALTER TABLE [dbo].[Test1] ADD CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  ([Col1])
GO
PRINT N'Creating [dbo].[usp_SP1]'
GO
CREATE PROCEDURE [dbo].[usp_SP1]
AS
…..
ALTER TABLE [dbo].[Test2] ADD [Col1] VarChar(100)NULL
GO

If there are no syntax errors in any of the DDL statements, then all the statements will run successfully. If, however, any of the statements results in an error then we have issues. Let’s say, the sample script above fails at the following statement because the table dbo.Test2 doesn’t yet exist on the database:

ALTER
TABLE [dbo].[Test2]
ADD [Col1] VarChar(100)NULL
GO

In this case, all the statements before this statement in the script would have run successfully, and committed the schema changes on the database, and the remaining statements after this statement including this one will not update the database leaving it in an unstable state. One solution is to fix this statement and run only the remainder of the script. This is a manual step, and is fine if scripts are deployed manually to the database. But what if there are a series of scripts being deployed at a time using an automated process?

There are several options to address this:

Option 1 : Remove all the GO statements from the scripts and then wrap the entire script within a single explicit Transaction. This involves manually editing the files which may not be feasible if the files are very large in size with numerous GO statements.

Option 2 : Implement transactions. Didn’t I mention earlier it is not possible because of the GO statements? Actually it is possible with a little bit of tweak, and the use of a SET option in the script. In SQL Server, there is a SET option called SET XACT_ABORT. This option specifies whether SQL Server automatically terminates and rolls back a Transaction if a T-SQL statement raises a runtime error. The default option is OFF. But, if it is set to ON, the entire transaction is terminated and rolled back.

To avail of this the above above sample script can be rewritten as below:

:On Error Exit
SET XACT_ABORT ON
GO
Begin Transaction
      PRINT N'Creating [dbo].[test1]'
      GO
      CREATE TABLE [dbo].[Test1]
      (
      [Col1] [bigint] NOT NULL IDENTITY(1, 1),
      [Col2] [int] NOT NULL,
      [Col3] [varchar] (50) NOT NULL,
      [Col4] [varchar] (50) NOT NULL
      )
      GO
      PRINT N'Creating primary key [PK_Test1] on [dbo].[Test1]'
      GO
      ALTER TABLE [dbo].[Test1] ADD CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  ([Col1])
      GO
      PRINT N'Creating [dbo].[usp_SP1]'
      GO
      CREATE PROCEDURE [dbo].[usp_SP1]
      AS
      …..
      ALTER TABLE [dbo].[Test2] ADD [Col1] VarChar(100)NULL
      GO
If Xact_State()=1
Begin
      Print 'Committing Tranaction...'
      Commit tran
End
Else If Xact_State()=-1
Begin
      Print 'Rolling Back Transaction...'
      RollBack Tran
End

Please note the first four lines and the last ten lines in the script.

:On Error Exit

This command causes sqlcmd to exit the sql script upon encountering an error.

SET
XACT_ABORT ON

With this statement, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Transactions in SQL Server Part I – An Introduction

BY AMIT CHAUDHARY

A basic requirements in most SQL Server based applications is implementing a database operation (DDL or DML) in a single unit of work. The DDL requirement is more from a DBA’s perspective rather than the application, since any application would perform only INSERTs, UPDATEs and DELETEs on the existing data, and a DBA would be performing DDL statements on the existing database as part of schema updates. The default behavior of SQL Server is to implement implicit transactions, meaning every T-SQL Statement is committed as soon as it is executed. This works fine as long as there is only one query being executed. But this is seldom the reality in large applications.

Enterprise applications normally entail executing large transactions, ie. a large number of T-SQL statements to be executed as a single unit of work. This means, either all the statements run successfully or they all need to be rolled back. This is also one of the ACID properties of transactions – Atomic. This ensures that the state of the database is stable even in case of failure, and any subsequent operation can be performed gracefully. The implementation of these transactions becomes important in any multi-tier application where the user interface makes a call to the database in response to user actions. In the first part of this article, I will discuss the basics of SQL Server Transactions from a beginner developer’s perspective. Below is an example of how to define an explicit transaction:

BEGIN TRANSACTION;
BEGIN TRY
--  T-SQL Statements
END TRY
BEGIN CATCH
-- error catching SQL
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

The first statement in this example is BEGIN TRANSACTION. This statement marks the beginning of an explicit transaction. Beginning with SQL Server 2005 onwards, a new block of T-SQL statements called BEGIN TRY… END TRY and BEGIN CATCH… END CATCH was introduced. These blocks make it easy for error handling. The main block, BEGIN TR… END TRY contains the statements to be executed. BEGIN CATCH… END CATCH is the error handling portion of the construct.

The BEGIN TRANSACTION statement marks the beginning of an explicit transaction. This statement should be the first statement in any block of code (Stored Procedure, Function). The BEGIN TRY… END TRY defines a block of code where the actual T-SQL Statements go. If all the statements within this block run successfully, then control is transferred to the first statement after the BEGIN CATCH… END CATCH block. If, however, any of the statements results in an error, then control is transferred to the BEGIN CATCH… END CATCH block.

Since we have defined an explicit transaction, we need to either roll it back, or commit it. The Rollback would occur in the BEGIN CATCH… END CATCH block. The Commit should be immediately after this block. The statement that rolls back a transaction is ROLLBACK TRANSACTION, and the statement that commits is COMMIT TRANSACTION.

In the example code above, I have checked for the value of the system variable @@TRANCOUNT before taking a decision of rolling back or committing a transaction. This variable indicates whether there are any transactions open in the current session, and if yes, we need to do something about them. Therefore, in the BEGIN CATCH..END CATCH block, if the value of the @@TRANCOUNT variable is greater than 0, it means we need to roll back the transaction because since control was transferred to this block when errors were encountered in the BEGIN TRY..END TRY block of statements.

Similarly, after the BEGIN CATCH..END CATCH block, we need to check the @@TRANCOUNT variable to determine if there are any transactions open, and these will need to be committed them when no errors were encountered in the BEGIN TRY block.

This is a very basic demonstration of the use of SQL Server Transactions which should be implemented in stored procedures and functions that are the basic blocks of T-SQL code, and form the backbone of any transaction based application. In the next article, I will discuss more advanced topics related to using and implementing transactions.

New Features in ASP.NET Web API 2

Ref: http://weblogs.asp.net/dwahlin/archive/2013/11/11/new-features-in-asp-net-web-api-2-part-i.aspx

Attribute Routing

Routing has been a core feature of Web API since it’s initial release and something that’s built into new Web API projects out-of-the-box. However, there are a few scenarios where defining routes can be challenging such as nested routes (more on that in a moment) and any situation where a lot of custom routes have to be defined. For this example, let’s assume that you’d like to define the following nested route:

/customers/1/orders

This type of route would select a customer with an Id of 1 and then return all of their orders. Defining this type of route in the standard WebApiConfig class is certainly possible, but it isn’t the easiest thing to do for people who don’t understand routing well. Here’s an example of how the route shown above could be defined:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        config.Routes.MapHttpRoute(
            name: "CustomerOrdersApiGet",
            routeTemplate: "api/customers/{custID}/orders",
            defaults: new { custID = 0, controller = "Customers", action = "Orders" }
        );

        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );

        GlobalConfiguration.Configuration.Formatters.Insert(0, new JsonpFormatter());
    }
}

With attribute based routing, defining these types of nested routes is greatly simplified. To get started you first need to make a call to the new MapHttpAttributeRoutes() method in the standard WebApiConfig class (or a custom class that you may have created that defines your routes) as shown next:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {

        // Allow for attribute based routes
        config.MapHttpAttributeRoutes();

        config.Routes.MapHttpRoute(
            name: "DefaultApi",
            routeTemplate: "api/{controller}/{id}",
            defaults: new { id = RouteParameter.Optional }
        );
    }
}

Once attribute based routes are configured, you can apply the Route attribute to one or more controller actions. Here’s an example:

[HttpGet]
[Route("customers/{custId:int}/orders")]
public List<Order> Orders(int custId)
{
    var orders = _Repository.GetOrders(custId);
    if (orders == null)
    {
        throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
    }
    return orders;
}

This example maps the custId route parameter to the custId parameter in the Orders() method and also ensures that the route parameter is typed as an integer. The Orders() method can be called using the following route:

/customers/2/orders

While this is extremely easy to use and gets the job done, it doesn’t include the default “api” string on the front of the route that you might be used to seeing. You could add “api” in front of the route and make it “api/customers/{custId:int}/orders” but then you’d have to repeat that across other attribute-based routes as well. To simply this type of task you can add the RoutePrefix attribute above the controller class as shown next so that “api” (or whatever the custom starting point of your route is) is applied to all attribute routes:

[RoutePrefix("api")]
public class CustomersController : ApiController
{
    [HttpGet]
    [Route("customers/{custId:int}/orders")]
    public List<Order> Orders(int custId)
    {
        var orders = _Repository.GetOrders(custId);
        if (orders == null)
        {
            throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
        }
        return orders;
    }
}

There’s much more that you can do with attribute-based routing in ASP.NET. Check out the following post by Mike Wasson for more details.

Returning Responses with IHttpActionResult 

The first version of Web API provided a way to return custom HttpResponseMessage objects which were pretty easy to use overall. However, Web API 2 now wraps some of the functionality available in version 1 to simplify the process even more.

A new interface named IHttpActionResult (similar to ActionResult in ASP.NET MVC) has been introduced which can be used as the return type for Web API controller actions. To return a custom response you can use new helper methods exposed through ApiController such as:

• Ok
• NotFound
• Exception
• Unauthorized
• BadRequest
• Conflict
• Redirect
• InvalidModelState

Here’s an example of how IHttpActionResult and the helper methods can be used to cleanup code. This is the typical way to return a custom HTTP response in version 1:

public HttpResponseMessage Delete(int id)
{
    var status = _Repository.DeleteCustomer(id);
    if (status)
    {
        return new HttpResponseMessage(HttpStatusCode.OK);
    }
    else
    {
        throw new HttpResponseException(HttpStatusCode.NotFound);
    }
}

With version 2 we can replace HttpResponseMessage with IHttpActionResult and simplify the code quite a bit:

public IHttpActionResult Delete(int id)
{
    var status = _Repository.DeleteCustomer(id);
    if (status)
    {
        //return new HttpResponseMessage(HttpStatusCode.OK);
        return Ok();
    }
    else
    {
        //throw new HttpResponseException(HttpStatusCode.NotFound);
        return NotFound();
    }
}

You can also cleanup post (insert) operations as well using the helper methods. Here’s a version 1 post action:

public HttpResponseMessage Post([FromBody]Customer cust)
{
    var newCust = _Repository.InsertCustomer(cust);
    if (newCust != null)
    {
        var msg = new HttpResponseMessage(HttpStatusCode.Created);
        msg.Headers.Location = new Uri(Request.RequestUri + newCust.ID.ToString());
        return msg;
    }
    else
    {
        throw new HttpResponseException(HttpStatusCode.Conflict);
    }
}

This is what the code looks like in version 2:

public IHttpActionResult Post([FromBody]Customer cust)
{
    var newCust = _Repository.InsertCustomer(cust);
    if (newCust != null)
    {
        return Created<Customer>(Request.RequestUri + newCust.ID.ToString(), newCust);
    }
    else
    {
        return Conflict();
    }
}

More details on IHttpActionResult and the different helper methods provided by the ApiController base class can be found here.

Conclusion 

Although there are several additional features available in Web API 2 that I could cover (CORS supportfor example), this post focused on two of my favorites features. If you have .NET 4.5 available then I definitely recommend checking the new features out. Additional articles that cover features in ASP.NET Web API 2 can be found here

ASP.NET MVC 4 Highlights: Bundling and Minification

By: John V. Petersen

In the first installment of this series, I explored a few of the new features in ASP.NET MVC 4, including the new default project templates, mobile templates, and display modes. Since that article, ASP.NET MVC 4 has been released to beta. For brevity’s sake, when I refer to MVC the design pattern, I’m referring to the ASP.NET implementation of the pattern. In this installment, I’m going to focus on one of MVC’s most useful features: integrated JavaScript and CSS bundling and minification.

One of the most important considerations in any Web application is the size of the content rendered to the browser. Bundling and minification handle two important tasks. First, all of the disparate JavaScript and CSS files are combined into one or more files. Second, the JavaScript and CSS code is minified by means of removing all of the carriage returns and line feeds as well as verbose variable names in favor of shorter (less verbose) alternatives.

With bundling, there are fewer resources that need to be rendered to the browser. With minification, the size of such resources is much smaller, often by as much as 70% or more!

These are not new concepts. Tools like JSMin and YUI have been around for several years. You have always been able to incorporate these bundling and minification solutions. What’s new are the integrated features baked into the MVC framework that handle these bundling and minification tasks out of the box.

Bundling and Minification “Out of the Box”

Figure 1 illustrates what you get out of the box for bundling and minification. Like all ASP.NET applications, everything starts in the Global.asax Application_Start() event handling code.

Figure 1: These code windows represent the out-of-the-box bundling and minification implementation in Global.asax and _Layout.cshtml.

In that code, there’s a call to:

BundleTable.Bundles.RegisterTemplateBundles();

The code for this method is burned into the System.Web.Optimization.dll. Figure 2 illustrates what that code looks like.

Figure 2: This code represents the out-of-the-box implementation code for RegisterTemplateBundles in System.Web.Optimization.

The code is straightforward in that bundles are created and files are added to the bundles. There are two ways to add files. One way is to refer to the file specifically. The second way is to specify a directory along with a file pattern search string. In the second method, the code can selectively traverse subdirectories to add content.

Each method has their respective pros and cons. When you specify the files, you know exactly what is getting into your bundled and minified files. This method requires more code. When you add directories, you don’t have to write as much code, but the possibility exists for unwanted code in the bundled and minified file. Choosing between adding files, directories or some combinatin thereof is a matter personal preference.

To use this functionality, there are a few concepts you must be familiar with:

• Bundle Class: Encapsulates one or more files to be bundled and minified
• AddFile() Method: Adds a specifically named file to the bundle
• AddDirectory() Method: Adds files that match a file search string within the specified directory
• Transform Property: An instance of IBundlerTransform, the object in this property performs the minification task
• Path Property: The string that identifies the bundle within the Bundles Member in BundleTables

As you will see in a moment, as is custom in MVC, if you don’t like the default out of the box functionality in MVC, you can substitute your own functionality. First, take a look at what the default functionality gives you. Figure 3 illustrates how the disparate javaScript (JS) and Cascading Style Sheet (CSS) files are rendered to the browser.

Figure 3: The network view lists the bundled and minified CSS and JS files (with a query string parameter that is explained in the next section).

Note the file paths:

/Content/css/Content/themes/base/css/Scripts/js

These are the paths specified when the bundles were created. Figure 4 illustrates how the minified JavaScript appears.

Figure 4: This is the minified and bundled JavaScript content as rendered to the browser.

Why Is a Query String Parameter Added to the JavaScript and CSS Files?

Every time the page is refreshed, the server-side code is executed, causing the _Layout.cshtml Razor Template to evaluate. You always want the client to recognize the latest JavaScript and CSS content. If the file were always named the same, the browser may reference its cache. This behavior can always be configured at the browser level. However, that is not usually a feasible solution because it requires each computer to be specifically configured. If you have the chance to control behaviors that are essential to your applications at the server level, take it! With the addition of the query string, the client will interpret this to be a new file and therefore, rely on its cache.

You might be thinking that the call to ResolveBundleUrl is required for the bundling and minification to work. It’s not. To illustrate, I’ll replace the code for /Content/css with the following:

<link href="~/Content/css" rel="stylesheet" type="text/css" />

Figure 5 illustrates that bundling and minification still takes place. The only difference is the file name. Without the call to ResolveBundleUrl, the query string parameter is not added.

Figure 5: If the ResolveBundleUrl call is omitted, a query string parameter will not be added.

Essentially, that’s it! That’s what we get for free. But what about debugging? I don’t want my code minified in those cases. Am I stuck?

Of course not! In MVC, with almost no exception, you can override the default out-of-the-box behavior for your own behavior. In the next section, you will see how to create your own custom bundler and minification class.

Creating a Custom Bundler If there are limitations to how the bundling and minification feature is implemented in the beta, these two would be at the top of the list: The code to create the bundles is burned in a dll. There is no way to make the default functionality sensitive to debug vs. release modes. While bundling is something you probably always want, minification isn’t. While debugging, you need the unminified code to be rendered. Out of the box, using the default bundler, you cannot conditionally bundle. Let’s solve that problem now. The custom bundler code in Listing 1 solves the problem. What’s the Second Bool Variable in AddFile()? The second Bool Variable is the throwIfNotExist parameter. If the specified file does not exist, you can elect to have an exception thrown. In the default beta implementation, this parameter is set to false. If you don’t want to be explicit with your files, the code could be simplified to what is illustrated in Listing 2. The AddDirectory() method has four parameters: directoryVirtualPath: the root directory used to search for files to bundle and minify searchPattern: specifies the pattern to limit which files are included in the bundle searchSubDirectories: if true, the process recursively searches all contained subdirectories under the directoryVirtualPath throwIfNotExist: if true, the process throws an exception if the specified directoryVirtualPath does not exist In this simplified code, every js file under /Scripts and every CSS file under /Content is included. In both cases, a compiler directive is added to specify the transformer used to drive the minification process. Out of the box, there is a class called NoTransform. As the name implies, this class does not minify. You need such a class because the bundler instance requires a transformer: var bundle = new Bundle("~/Scripts/js", jstransformer); The bundler does not care what the transformer does. As long as it gets an instance that conforms to IBundleTransformer, the Bundle instance will be happy. Listing 3 shows what the NoTransform class is. I Like the YUI Minifier; Can I Use That? The nice thing about the way bundling and minification was implemented in ASP.NET MVC is that you don’t have to give up using utilities that you are currently using. The implementation works very much like the way IDependencyResolver works. In Version 3, an inversion of control container adapter was added to abstract away the details of any specific IoC container from the framework. The bundling and minification process illustrated here works very much the same way. The process begins with creating a custom instance of IbundlerTransform, as shown in Listing 4. To take advantage of the YUICompressor Transform Class, the code in Listing 2 that loads the proper transformer must be changed to the following: bool isDebug; #if DEBUG isDebug = true; #endif if (isDebug) { jstransformer = new NoTransform("text/javascript"); csstransformer = new NoTransform("text/css"); } else { jstransformer = new YUITransform(contentType.javascript); csstransformer = new YUITransform(contentType.css); } I changed the debug-checking process slightly, because I am gradually moving to a solution that is testable. The next step involves creating a custom abstraction over the base Bundle Class that begins to abstract away the details of which files to add. I’ll leave that exercise to you to explore. File Ordering within a Bundle By default, JavaScript files are first ordered alphabetically within a bundle. Then, the files are restacked around known libraries. For example, jQuery - related files occur first in the bundle. Within the jQuery group, the files are sorted alphabetically. For CSS files, the files are first sorted alphabetically. Then, if the files reset.css or normalize.css exist, that content appears at the top of the bundled CSS file. Figure 6 illustrates this behavior in the bundled CSS file. Like everything else, this behavior is completely customizable. The Bundle Class has an Orderer property that conforms to the IBundleOrderer interface. Figure 6: The Bundle.Orderer Property controls how files are ordered within the bundle. Conclusion With each release, the ASP.NET MVC Framework gets better and better. Bundling and minification is an essential process that needs to be in every production Web app that relies on significant JavaScript and CSS resources. Out of the box, the functionality is pretty good. There are, however, some missing pieces. Fortunately, the process was designed with customization and extensibility in mind. With a little effort, it was easy to toggle minification based on whether or not the application was being run under debug mode. One final point, this article was based on beta software. The usual disclaimer applies - this functionality may change when the product is released to manufacturing (RTM). John V. Petersen	&		SPONSORED SIDEBAR: Learn ASP.NET MVC in a Day! ASP.NET is one of the world’s most popular Web development environments. We can help you with all ASP.NET projects as well as related technologies, such as HTML (4 and 5), JavaScript, jQuery, CSS, AJAX, services, and many more. (Learn more from www.codemag.com/consulting.) That’s why CODE Training is offering a full day of training in ASP.NET MVC from CODE Consultants, experts in Web development. CODE Training and EPS Software will be holding an intensive one-day lecture, June 5, 2012, on ASP.NET MVC specifically designed for developers of business applications. Learn how, when and why to use ASP.NET MVC for the best result in your projects. Only $399! Visit www.codemag.com/training to find out a little bit more about the class, or send an e-mail to info@codemag.com for more information.
Listing 1: Custom bundler class using System;using System.Linq;using System.Web.Optimization; namespace MVC4BundleUI{ public class MyBundler { public static void init() { IBundleTransform jstransformer; IBundleTransform csstransformer; #if DEBUGjstransformer = new NoTransform("text/javascript"); csstransformer = new NoTransform("text/css"); #else jstransformer = new JsMinify(); csstransformer = new CssMinify(); #endif var bundle = new Bundle("~/Scripts/js", jstransformer); bundle.AddFile("~/Scripts/jquery-1.6.2.js", true); bundle.AddFile("~/Scripts/jquery-ui-1.8.11.js", true); bundle.AddFile("~/Scripts/jquery.validate.unobtrusive.js", true); bundle.AddFile("~/Scripts/jquery.unobtrusive-ajax.js", true); bundle.AddFile("~/Scripts/jquery.validate.js", true); bundle.AddFile("~/Scripts/modernizr-2.0.6-development-only.js", true); bundle.AddFile("~/Scripts/AjaxLogin.js", true); bundle.AddFile("~/Scripts/knockout-2.0.0.debug.js", true); BundleTable.Bundles.Add(bundle); bundle = new Bundle("~/Content/css", csstransformer); bundle.AddFile("~/Content/site.css", true); BundleTable.Bundles.Add(bundle); bundle = new Bundle("~/Content/themes/base/css", csstransformer); bundle.AddFile("~/Content/themes/base/jquery.ui.core.css", true); bundle.AddFile("~/Content/themes/base/jquery.ui.resizable.css", true); bundle.AddFile("~/Content/themes/base/jquery.ui.selectable.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.accordion.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.autocomplete.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.autocomplete.css", true); bundle.AddFile("~/Content/themes/base/jquery.ui.dialog.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.slider.css", true); bundle.AddFile("~/Content/themes/base/jquery.ui.tabs.css", true); bundle.AddFile("~/Content/themes/base/jquery.ui.datepicker.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.progressbar.css",true); bundle.AddFile("~/Content/themes/base/jquery.ui.theme.css", true); BundleTable.Bundles.Add(bundle); } } } Listing 2: Simplied custom bundler code using the AddDirectory() method public class MyBundler { public static void init() { IBundleTransform jstransformer; IBundleTransform csstransformer; #if DEBUGjstransformer = new NoTransform("text/javascript"); csstransformer = new NoTransform("text/css"); #else jstransformer = new JsMinify(); csstransformer = new CssMinify(); #endif var bundle = new Bundle("~/Scripts/js", jstransformer); bundle.AddDirectory("~/Scripts/","*.js",true, true); BundleTable.Bundles.Add(bundle); bundle = new Bundle("~/Content/css", csstransformer); bundle.AddDirectory("~/Content/", "*.css", true, true); BundleTable.Bundles.Add(bundle); } } Listing 3: NoTransform transformation Class public class NoTransform : IBundleTransform { readonly string _contentType; public NoTransform(string contentType) { this._contentType = contentType; } public void Process(BundleContext context, BundleResponse response) { response.ContentType = this._contentType; } } Listing 4: YUI Compressor transformation class using System.IO;using System.Web.Optimization;using Yahoo.Yui.Compressor; namespace Bundler.Utilities{ public enum contentType { javascript, css } public class YUITransform : IBundleTransform { readonly string _contentType = string.Empty; public YUITransform(contentType contentType) { if (contentType == contentType.css) { this._contentType = "text/css"; } else { this._contentType = "text/javascript"; } } public void Process(BundleContext context, BundleResponse bundle) { bundle.ContentType = this._contentType; string content = string.Empty; foreach (FileInfo file in bundle.Files) { using (StreamReader fileReader = new StreamReader(file.FullName)) { content += fileReader.ReadToEnd(); fileReader.Close(); } } bundle.Content = Compress(content); } string Compress(string content) { if (_contentType == "text/javascript") { return JavaScriptCompressor.Compress(content); } else { return CssCompressor.Compress(content, CssCompressionType.StockYuiCompressor); } } }}