Saturday 15 March 2014

Getting previous page URL using ASP.NET with C#

string referer = Request.UrlReferrer.ToString();

Using Request.UrlReferrer, we can get the Previous page URL of the current request. Previous Page information is available in the Request.UrlReferrer property only if user is redirected to the current page from some other page and is available in the !Page.IsPostBack of Page Load event of the form. 

One thing to remember is that UrlReferrer will change to your pagename when you post back to the server.. so you should maybe store the value in ViewState on the first page load of the page if you are wanting to redirect the user to the previous page after a couple postbacks to the same page.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack) //check if the webpage is loaded for the first time.
        {
            ViewState["PreviousPageURL"] = Request.UrlReferrer; 
            //Saves the Previous page url in ViewState
        }
    }

If you want to reach the previous page ,

if (ViewState["PreviousPage"] != null) //Check if the ViewState contains Previous page URL
        {
            Response.Redirect(ViewState["PreviousPage"].ToString());
           //Redirect to Previous page by retrieving the PreviousPage Url from ViewState.
        }

ViewState is used for retaining values between multiple requests for the same page. This is the default method that the page uses to preserve page and control property values between round trips.

The situations where it does work include the following methods of a browser loading a URL:
  • clicking on a straight HTML <a href> link;
  • submitting a form, using POST or GET, from a submit button, <input type=image> or client-side script (form.submit())

The situations where it doesn't work:
  • using Response.Redirect / Server.Transfer;
  • clicking on a Favorite, History, or the recently-typed URLs list;
  • clicking on 'Home' in IE's toolbar, or an item in IE's 'Links' toolbar;
  • using location.href or location.replace() in client-side JScript/JavaScript/VBScript;
  • using HierMenus (details);
  • typing the URL directly in the browser and hitting Enter or clicking 'Go';
  • launching a clickable URL from an e-mail or MS Office document;
  • using Response.AddHeader  or <meta http-equiv=refresh> to redirect;
  • loading the URL with XML

Wednesday 29 January 2014

Dynamic SQL Performance comparison in SQL Server

Dynamic SQL statements

A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements. It can be useful to construct these statements dynamically when you need to decide at run time what fields to bring back from SELECT statements; the different criteria for your queries; and perhaps different tables to query based on different conditions.

These SQL strings are not parsed for errors because they are generated at execution time, and they may introduce security vulnerabilities into your database. Also, SQL strings can be a nightmare to debug, which is why I have never been a big fan of dynamically built SQL statements; however, sometimes they are perfect for certain scenarios.

NOTE: Most importantly, the Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed.

Declare a string variable, something like:

declare @sql varchar(5000)
Set that variable to be the completed SQL string you want (as a string, and not actually querying... so you embed the row-name you want using string concatenation).

Then call: exec(@sql)
 
There have been a lot of discussions on the usage of dynamic SQL in SQL server. Most of them were stressing on SQL injection. Will dynamic SQL cause any performance difference? Interesting question!! Shall we check with an example?

Following are two simple procedures created in Northwnd database to fetch company names. One using dynamic SQL and the other one without dynamic SQL.































The following SQL Server script that could be useful to collect the measures-
  

Does it mean that dynamic SQL is something that should be avoided ? Never meant that !! The tests were not exhaustive enough to prove that, and sp_executesql is not at all counted in these tests. The only point to emphasize here is that there could be performance implications while using dynamic SQL, and this should be known and counted during application development.

Tuesday 28 January 2014

Multiple JOINs with Missing Operator Issue in MS Access

Join is used to retrieve rows from two or more tables by matching a field value that is common between the tables. The fields you join on must have similar data types, and you cannot join on MEMO or OLEOBJECT data types

if you want to do more than one JOIN, you have to use parenthesis in the FROM clause.

SELECT ...
FROM MainTable
JOIN JointTable1 ON ...
JOIN JointTable2 ON ...
JOIN JointTable3 ON ...

you would have to do the following:

SELECT ...
FROM ((MainTable
JOIN JointTable1 ON ...)
JOIN JointTable2 ON ...)
JOIN JointTable3 ON ...

the joins must be nested by using parentheses

Otherwise, you get a "Missing Operator" error.