Saturday, August 25, 2007

SQL SERVER : Selecting data from temporary tables & table variables using FOR XML

We can
convert the relational data stored in the table into XML by using the FOR XML
syntax, I am not going to the explain all the features of FOR XML.



Physical table


SELECT * FROM ASPNET_Roles FOR XML AUTO,ROOT ('A'),ELEMENTS

this select statement will give the following output

<A><URR_ASPNET_Roles>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</URR_ASPNET_Roles>
<URR_ASPNET_Roles>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</URR_ASPNET_Roles>
</A>


its is a perfect xml with all the nodes and 'A' as the root element

now consider selecting the data from temporary table & table variable


SELECT *
INTO #TBL_TMP FROM
ASPNET_Roles

the values of the table URR_ASPNET_Roles is inserted into temporary table #TBL_TMP

SELECT *
FROM #TBL_TMP FOR
XML AUTO,ROOT('A'),ELEMENTS

the above select statement will produce the following output

<A>
<_x0023_TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</_x0023_TBL_TMP>
<_x0023_TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</_x0023_TBL_TMP>
</A>


but what is _x0023_
doing in output, from where did it originate ? how can we remove this
addition in the xml tag

consider the following statement

SELECT *
FROM #TBL_TMP TBL_TMP
FOR
XML AUTO,ROOT('A'),ELEMENTS

this will produce the following output

<A>
<TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>89C306A6-2D21-42AB-A42F-5029091CB4E8</RoleId>
<RoleName>SBU
User</RoleName>
<LoweredRoleName>sbu
user</LoweredRoleName>
<USER_TYPE>2</USER_TYPE>
</TBL_TMP>
<TBL_TMP>
<ApplicationId>AFFE3DDD-ABAD-49DF-A144-DA87BE69B90E</ApplicationId>
<RoleId>180BA05D-D2A5-49CA-BED3-3C6F080F6543</RoleId>
<RoleName>Super
User</RoleName>
<LoweredRoleName>super
user</LoweredRoleName>
<USER_TYPE>1</USER_TYPE>
</TBL_TMP>
</A>

the output is clean with no extra additions !!!

How ? in the second statement we have provided an alias for the table which is
displayed in the output

same is the case with table variable's

DECLARE @tblVar TABLE
(ID INT,DATA
VARCHAR(10))

INSERT INTO @tblVar
VALUES (1,'DATA1')

SELECT *
FROM @tblVar FOR
XML AUTO,ROOT('A'),ELEMENTS

<A>
<
_x0040_tblVar>
<ID>1</ID>
<DATA>DATA1</DATA>
</
_x0040_tblVar>
</A>

SELECT *
FROM @tblVar TBLVAR FOR
XML AUTO,ROOT('A'),ELEMENTS

<A>
<TBLVAR>
<ID>1</ID>
<DATA>DATA1</DATA>
</TBLVAR>
</A>


why are such additional unwanted strings displayed in temporary tables and table
variables and not in the case of physical tables

temporary tables and table variables have # & @ attached to their names
respectively, so these special characters are parsed by SQL server and converted
into xml, in both cases we can remove these unwanted additions by using aliases.


Happy programming

ApplicationName" property & its importance while configuring ASP.NET 2.0 role & membership features

Why applicationName ?
applicationName is provided so as to enable us to map different applications to same database

this is a sample configuration for the ASP.net 2.0 Roles



<roleManager>

<providers>

<add name="AspNetSqlRoleProvider"
connectionStringName="LocalSqlServer" applicationName="/"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=
2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a" />


<add name="AspNetWindowsTokenRoleProvider"
applicationName="/"
type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=
2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a" />


</providers>



you develop the site using all the advanced features its works perfectly but when the site is exported / published on another server, these features wont work !!!!

The problem
when we are using the 2.0 features asp.net automatically uses the virtual root folder name of the application if does not find any value for the applicationName value in the .config files.

When we export the same configuration it no longer works because the 2.0 features are configured for some vroot which is not available in the new environment.

Solution:

· Always provide some value to the applicationName (its not a must, but a good practice)

· Provide "/" as the value

Use vb & C# classes in same application (web app)

Use vb & C# classes in same application (web app)

In our application we mainly use only one type of class files say Vb or C#. what if we want to use two or more different language files in one web app ?

in app_code create two folders , let say vb & cs, and place the corresponding class files into it

add this piece of code to your web.config

<compilation debug="true" strict="false" explicit="true">
<codeSubDirectories> <add directoryName="vb"/>
<
add directoryName="cs"/>
</codeSubDirectories>
</compilation>



you can use any folder name instead of VB or CS, the .Net compiler will look into each folder and try to compile all the class files found. But if it finds more than one different language files it throws an exception. the above piece of code will direct the compiler to check additional folders in App_code for class files for compilation.