Insert into a table from dynamic sql

I have had many situations to put the dynamic sql data to a temp table but first thought was to pass the temp table as parameter or create temp table on dynamic sql itself. But none of these methods would work on SQL. When we create a temp table on dynamic sql, it will not be available outside of dynamic sql scope.


Try this out

DECLARE @tablename VARCHAR(10)
SET @tablename = 'tk'
DECLARE @strQuery NVARCHAR(1000)

SET @strQuery = 'select name from sys.tables where name like ''' + @tablename + '%'''
DECLARE @tablenames TABLE (tblname VARCHAR(100))


INSERT INTO @tablenames
EXEC
sp_executesql @strQuery

SELECT * FROM @tablenames

Easy method to dump all your data from dynamic sql!

What is wrong with WCF HTTP Activation?

Could not load type ‘System.ServiceModel.Activation.HttpModule’ from assembly ‘System.ServiceModel, Version 3.0.0.0, Culture=neutral….


This error started occurring all of a sudden. I was using IIS 7 and .net 4.0. The page was working fine and it broke when I checked it next day. What is wrong with my asp.net pages? Well, it’s not problem with asp.net pages or IIS. It looks like a problem when you install WCF HTTP activation module after you install .net 4.0. I installed WCF HTTP activation module to try something else which caused this problem. Take a look at http://msdn.microsoft.com/en-us/library/aa751852.aspx

Fix: Run aspnet_regiis.exe -i –enable on VS 2010 command prompt.

Forms authentication integrated with IIS7

If you have worked on ASP.net application, you would have definitely used forms authentication in your applications. The new IIS7 brings a new feature which integrates forms authentication with IIS. On IIS 6 you could see authentication options of anonymous, Basic, Digest and Windows. On IIS7 you could also find Forms authentication when you have ASP.Net components installed. Let us look at how to make use of this for our application.


Forms Authentication and Membership providers:

In a brief, forms authentication and membership providers are integrated from the .net 2.0 version. When you set up your application to use membership provider, it take care of all membership related functionalities also the member validations.

I will set up a sample app to use the forms authentication with IIS7 integrated pipeline and see how we can take advantage of this feature.

Lets create a Web Project.


Create 3 pages: About.aspx, Login.aspx, Default.aspx
Set up the application to use sql Membership provider. The detailed explanation on how to set up sql membership provider is available on http://msdn.microsoft.com/en-us/library/ff649314.aspx

On Our login page, lets add a login control

<div>
<asp:LoginView ID="LoginView" runat="server">
<AnonymousTemplate>
<asp:Login ID="Login" runat="server" DestinationPageURL="About.aspx"
onloggedin="Login_LoggedIn" />
</AnonymousTemplate>
<LoggedInTemplate>
<asp:LoginName ID="LoginName" runat="server" />
<asp:LoginStatus ID="Logout" runat="server" />
<asp:ChangePassword ID="ChangePwd" runat="server" />
</LoggedInTemplate>
</asp:LoginView>
</div>

When you login using login.aspx, the login control internally invoke sql membership provider ValidateUser method and set the authentication cookie. These are all done by ASP.NET and you don’t have to do anything from your code. If you need any custoim logic to be appended on login process, then you need to inherit MemberShip class and build your own custom Membership provider. For now let us just use the sql DB provider.

OK. We are all set with the application side to use the forms authentication. With IIS 6, we had to use Anonymous access, so that any user can hit the page and ASP.Net would validate the cookie and authenticate the users. With IIS7 this part is moved over to IIS itself. Here is how we can do it.


Go to IIS manager

Select Authentication
 

Here keep forms authentication as enabled and disable rest of the authentication methods. Now if you try to browse any of your pages, you will get below error

So even if you have configured login.aspx as your login page, the IIS doesn’t allow you to go to login.aspx since IIS cannot find authentication token for any request and anonymous access is restricted.

Let us allow login.aspx for anonymous access. Go to content view, select login.aspx . Right click and Select Switch to feature view and allow anonymous access

 

 
Now browse the site, you will be redirected to login.aspx if you have not logged in to the site. If you have logged in, the site will allow you to browse other pages.

Another factor you need to remember to get this working is that you need to select integrated mode on application pool for the web site which is configured.

There is couple of disadvantages when we plan to deploy it on different server and use the application on load balancer. The authentication ticket which is created by Asp.net and used by IIS to determine the authentication of user is actually encrypted using machine key. So if the request is sent to different server, the token will not be valid. The only way to get that working on different server is to keep the machinekey same.

Comparison XPathDocument, XmlReader, XLINQ, XmlDocument

From the days of .net 1.0 many of us use XmlDocument for parsing and writing xml in our .Net code. If you ask anyone who started with .Net 3.0 or above, they might say Xlinq is the best method to work with XML. Well, when we conclude on the method that we are going to use in our applications, we also need to see how it will impact on our application or if there is any better method to use.

Let us consider an XMl which I need to just parse and get the data out of it. If I am using .Net 3.5 or 4.0, first thought that comes to mind is that use Xlinq. I did a small test just to check which method would be better to read the xml and parse through the value and the result was really surprising.

Here is my Test program

class Program

{

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

string xmlinput = "<Customer><Item desc=\"item1 desc\" id=\"1\" /><Item desc=\"item2 desc\" id=\"2\" /><Item desc=\"item3 desc\" id=\"3\" /><Item desc=\"item4 desc\" id=\"4\" /><Item desc=\"item5 desc\" id=\"5\" /><Item desc=\"item1 desc\" id=\"1\" /><Item desc=\"item2 desc\" id=\"2\" /><Item desc=\"item3 desc\" id=\"3\" /><Item desc=\"item4 desc\" id=\"4\" /><Item desc=\"item5 desc\" id=\"5\" /><Item desc=\"item1 desc\" id=\"1\" /><Item desc=\"item2 desc\" id=\"2\" /><Item desc=\"item3 desc\" id=\"3\" /><Item desc=\"item4 desc\" id=\"4\" /><Item desc=\"item5 desc\" id=\"5\" /><Item desc=\"item1 desc\" id=\"1\" /><Item desc=\"item2 desc\" id=\"2\" /><Item desc=\"item3 desc\" id=\"3\" /><Item desc=\"item4 desc\" id=\"4\" /><Item desc=\"item5 desc\" id=\"5\" /></Customer>";


XmlReader objReader = System.Xml.XmlReader.Create(new System.IO.StringReader(xmlinput));


sw.Start();
for (int i = 0; i < 1000; i++)
{
 Program.NavigateXPathNavigate(objReader);
}
sw.Stop();
Console.WriteLine("XPATH ----- " + sw.ElapsedTicks.ToString());
Console.WriteLine(" ");
sw.Reset();
sw.Start();
for (int i = 0; i < 1000; i++)
{
Program.NavigateXmlDocument(xmlinput);
}
sw.Stop();
Console.WriteLine("XMLDOC ------ " + sw.ElapsedTicks.ToString());
Console.WriteLine(" ");
sw.Reset();
sw.Start();
for (int i = 0; i < 1000; i++)
{
Program.NavigateXmlReader(xmlinput);
}
sw.Stop();
Console.WriteLine("XML Reader ------ " + sw.ElapsedTicks.ToString());
Console.WriteLine(" ");
sw.Reset();
sw.Start();
for (int i = 0; i < 1000; i++)
{
Program.NavigateXlinq(xmlinput);
}
sw.Stop();
Console.WriteLine("XLINQ ------ " + sw.ElapsedTicks.ToString());
Console.WriteLine(" ");
Console.ReadKey();
}






private static void NavigateXPathNavigate(XmlReader xmlinput)
{
XPathDocument xpathDoc = new XPathDocument(xmlinput);
XPathNavigator xpathNavig = xpathDoc.CreateNavigator();
XPathNodeIterator pgItereator = xpathNavig.Select("/Customer/Item");
foreach (XPathNavigator n in pgItereator)
{
 string val = n.GetAttribute("id", "");
}
}




private static void NavigateXmlReader(string xmlinput)
{
XmlReader objReader = System.Xml.XmlReader.Create(new System.IO.StringReader(xmlinput));
while (objReader.Read())
{
if (objReader.NodeType == XmlNodeType.Element)
{
if (objReader.Name == "Item")
{
 objReader.MoveToAttribute("id");
 string id = objReader.Value;
}
}
}
}






private static void NavigateXmlDocument(string xmlinput)
{
XmlDocument objdoc = new XmlDocument();
objdoc.LoadXml(xmlinput);
XmlNodeList objlist = objdoc.SelectNodes("/Customer/Item");
foreach (XmlNode n in objlist)
{
  string val = n.Attributes["id"].Value.ToString();
}
}






private static void NavigateXlinq(string xmlinput)
{
XElement obj = XElement.Parse(xmlinput);
IEnumerable oldItem = (from c in obj.Elements("Item")
select c);
foreach (XElement ele in oldItem)
{
  string val = ele.Attribute("id").Value;
}
}
}


I have kept the watcher for each method to get the time taken to process the method. Here is the output of my program

The XPathDocument was always much lower than any other methods. And the performance of the method always fall into below order


• XPATHDocument
• XMlReader
• XLinq
• XMLDocument


This is purely based on my sample test and it may differ based on the situation. For any enterprise applications, where the number hits and concurrent users are huge, we need to definitely consider on these methods as you can see a drastic difference on the process time.

IIS 7.0 Managed pipeline Mode

If you have configured any application on IIS 6 or IIS 7 you might have configured application pools as well. One of the new things added on IIS 7 which makes it much different for ASP.NET application is managed pipeline mode. I had never observed it till these days and was not aware of the difference. If you are hosting your ASP.Net application on IIS 7 setting this feature appropriately would give you lot of boost on your application performance.

In above image you can see Integrated and Classic as Managed Pipleline Mode. What exactly the difference between these two and how these two works? Here are few things I found on my search


Classic mode:

When the application pool is made as classic, it works exactly like IIS 6.0. ASP.NET requests first go through native processing steps in IIS and are then routed to Aspnet_isapi.dll for processing of managed code in the managed runtime. Finally, the request is routed back through IIS to send the response. This separation of IIS and ASP.Net request processing results in duplication of some processing steps like authentication and authorization. Here are the steps that IIS will follow on classic mode

• IIS core received the incoming HTTP request
• The request is processed through ISAPI.
• The request is passed to ASP.NET.
• The request passes back through ISAPI.
• The request passes back through the IIS core -> HTTP response delivered

Integrated mode:

The request-processing models of IIS and ASP.NET are integrated into a unified process model. This model eliminates steps that were previously duplicated in IIS and ASP.NET, such as authentication. Additionally, Integrated mode enables the availability of managed features to all content types

Here are the steps

• The incoming HTTP request is received through the IIS core and ASP.NET.
• The appropriate handler executes the request and delivers the HTTP response