Tuesday, June 2, 2015

Finding which event fired(button clicked) in Page_Load or Page_Init/Page_PreInit

I was working on an app and I wanted to turn off Event Validations when a certain action was performed to Render the HTML. Keeping them on won't let me render my UserControl to an HTML file. So I wanted to capture the Event right in Page_PreInit so that I can disable Event Validation of Page. Note that this can only be done either programmatically before Page is Initialized, or as a Page directive in aspx file.

Request.Form["__EVENTTARGET"] is the variable that will capture the information for the button clicked.

One additional aspect to note is to have UseSubmitBehavior="false" for the Button Web Control to generate the value for "__EVENTTARGET".

So in your html:

<asp:Button runat="server" ID="btnDownload" UseSubmitBehavior="false" ClientIDMode="Static" Text="Download" style="display:none;" OnClick="btnDownload_Click" />

In code behind, this is what you would have:


protected void Page_PreInit(object sender, EventArgs e) 
 { 
if (Request.Form["__EVENTTARGET"] != null && Request.Form["__EVENTTARGET"].Contains("btnDownload"))
Page.EnableEventValidation = false;//(NO INTELLISENSE!)disable event validation when the button is clicked for downloading file
}

Friday, May 15, 2015

Filtering/Grouping by multiple columns in sql subquery

I have been struggling for a while to have multiple columns in subquery and then filtering out the result.

Often with composite keys in the staging table, you want to check if there are any duplicates.

Taking an example of a fictitious "sales" table:

select * from sales where order_number in
(select order_number from sales
group by order_number, customer_id
having count(*)>1)

Above query might not give the desired results if I am looking for particular results with filters. The reason being I did not apply all the fields in the select query for the ones used in group by.

An extensible solution below takes care of that:

--Use CTE to store result for further filtering
with cte as (select s1.field1, s1.field2, s1.field3 from sales as s1
where exists
--Have a subquery for multiple column grouping. Compare it with the fields of the parent query, which would yield 1 corresponding record
(select s2.field1, s2.field2, sd.field3  from sales as s2
where s2.field1=sd1.field1 and s2.field2=s1.field2 and sd.field3=s1.field3
group by field1,field2,field3
) --Have your extra condition here for filtering
)
select * from cte where reached_target='Y'  -- Get all those qualified records needed

In this case, Common Table Expression (CTE) does come in very handy to filter out the final resultset.

Tuesday, April 21, 2015

Defining and using Server variables in the html head section of page

My browser was caching the javasscripts and css and it gets annoying when you are multiple design changes to code. So I decided to do cache busting. I defined a Random number variable in code behind and wanted to use it in html <head> so that every time a request is made, the browser takes it as a new request to the server with a fresh copy of styles and javascript files. The solution is a little quirky, but works:

     <link href="mystyle.css?rn=<%="" +Rand %>" type="text/css" rel="stylesheet"/>

where:
rn : is variable for Random Number
Rand : is server side generated Random number

Considering the Random number generated as 1234, the output would be like below:

     <link href="mystyle.css?rn=1234" type="text/css" rel="stylesheet"/>


Note: Use this for development only. Caching is always good to have in production to speed up loading times. With any new build of css/javascript, a version number can be assigned to get a fresh copy.