Tuesday, December 18, 2012

DNN UserController.GetUsersByProfileProperty

Today while traversing through DNN code I found that method UserController.GetUsersByProfileProperty returns all available records if we pass -1 as pageIndex!

DNN handles pageIndex -1 at AspNetMembershipProvider class. Below is its implementation present in that class.

Public Overrides Function GetUsersByProfileProperty(ByVal portalId As Integer, ByVal propertyName As String, ByVal propertyValue As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ArrayList
            If pageIndex = -1 Then
                pageIndex = 0
                pageSize = Integer.MaxValue
            End If


            Return UserController.FillUserCollection(portalId, dataProvider.GetUsersByProfileProperty(portalId, propertyName, propertyValue, pageIndex, pageSize), totalRecords)
        End Function

Tuesday, December 11, 2012

SQL Server Template Explorer

Today while working I created a new Stored Procedure using SSMS 2008, through 
Database => Programmabilityv=> Procedures => Right Click Select New procedures 

This returned me a well defined new SP template as usual. Here I was supposed to add some extra lines of comment like modified by, date, purpose etc. This prompted me to think how nice it would have been if I could get these automatically written for me every time I create a new SP. So I googled and straightaway found a stack-overflow link telling about Template Explorer in SSMS. From template explorer you can view all the default SSMS templates and can edit any of them. 

I edited template for new stored procedure to add new comment lines as per my requirement and also changed number and type of default parameters.

You can also use it to add some other commonly used lines like TRY/CATCH block :)

Shortcut to open template explorer: CTRL + ALT + T

These templates are stored in your file system as plain SQL files which you can edit without SSMS as well. Location for it is below:

On my XP machine having SQL Server 2005 Express:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql

On my windows 7 machine having SQL Server 2008 R2 Developer Edition:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

Saturday, October 27, 2012

Rawan Dahan

Just for fun (working best in chrome and safari, works but is slow in IE and Firefox)

Missiles Fired: 0
Click bombs to drop
*
*
*
*
*
*
*
*
*
*
Rawan
ravan's head' ravan's head' ravan's head' ravan's head' ravan's head'

A simple game made just for fun.

How to Play:
  • Click missiles to fire them, time them such that they fall on Rawan's main head
  • You get 10 missiles by default and you have to strike rawan's head 5 times to win
  • Click "Reload drones" button to reset missiles, this will not restore number of rawan's killed
  • Click "Reset Game" button to reset game to initial values
  • Try this on Chrome or safari, as game speed is slow on Firefox and IE.
Images used here are not of mine.
Rawan's image can be found here.
Drone's image can be found here.

Saturday, October 20, 2012

windows service couldn’t be started

A few days back while working on a windows service, I used below line for debugging purpose:

System.Diagnostics.Debugger.Launch();

This line worked fine and I got my service debugged. Then I tried to deploy service on our server machine. I used below two commands for installing my service

InstallUtil.exe -i 
NET START

InstallUtil worked fine but when I fired NET START, it failed saying, service couldn’t be started. I didn’t have any proper explanation of this but by hit and trial I found that line I used for diagnosing (System.Diagnostics.Debugger.Launch()) was creating problem somehow, I just commented that line and everything just worked fine.

If you have some windows service that is having some trouble in starting, then try to check this case.

Thanks.

Disable hyperlinks/javascript when exporting SSRS report

"Disable hyperlinks/javascript when exporting SSRS report", this is a very common question and I was expecting a straight forward answer from google like enabling/disabling a property at report or server level. But I was unable to find any such thing and hence I took below turnaround to solve my problem.

Problem:
In my case I had a column where some names were coming and they were linked to some external URL, this was fine and as required when rendered through report-viewer control on my web-application's page but when I export this to report PDF/Excel I didn't want them as then these links become non-functional and show error message like invalid URL.

Solution:
I duplicated my column containing link, and removed action from newly created column. Now I set their visibility with below expressions.

Visibilty of column with action link associated:
=IIF(Globals!RenderFormat.Name = "PDF" Or Globals!RenderFormat.Name = "EXCEL", true, false) 

Visibilty of column without action link associated:
=IIF(Globals!RenderFormat.Name = "PDF" Or Globals!RenderFormat.Name = "EXCEL", false, true)

Please consider: This might not work for older versions of SSRS

Hope this will save a few minutes of someone. If you have some better approach for this issue, please add it in comments and I'll update that in this post with due credits. Thanks.

Thursday, July 26, 2012

SQL Server Temp table vs Table variable

Hi All,

Some time back I was in an interview where one of the question was "Whats the difference between Temporary tables and Table variables".

What I knew at that time was:
  • Table variables are a newly introduced concept  and are faster.
  • Table variable exists only exist in current session.
So after interview I googled a bit and as-usual found answer on SQL Authority spread in couple of articles.  These articles have a lot of comments and some interesting points, so I thought to summarize them here for those who want it quick:
  • In case of large amount of data temp table is advised because SQL Server creates, maintains and uses the statistics of temp table while generating the execution plan. Besides this we can create index in temp tables if that is needed.
  • Table variables performs good only for small record-set. Because there data statistics is not used while creating the execution plan and because of that sometimes fully optimized plan are not created. Table variable should be used where they are joined with small tables only.
  • Temp table has the global option which is very useful too when having two connections open and running multiple tests at the same time.
  • Table variables are not transactional and do not roll back. Temp tables are transactional and do roll back.
For more details, please visit below links:

http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/

Thursday, May 24, 2012

using variables in css - CSS Pre-Processors

Have you ever felt that CSS should be more powerful, having variables, expressions, code reuse capability between classes and other such stuff. If yes then here is an option to do all that. I stumbled to it a few days back and found it to be quite impressive.
Less - The dynamic stylesheet language, lets you to specify widely used values in a single place, and then re-use them throughout the style sheet.

Below is a simple example of LESS code:

// LESS
@color: #4D926F;
#header
{
     color: @color;
}
h2
{
     color: @color;
}
/* Compiled CSS */
#header
{
      color: #4D926F;
}
h2
{
     color: #4D926F;
}

 LESS offers a lot more than this. To know more check their website...

Edit 28th Oct, 2012:

I just found that Less is not alone and its one of the members of family called CSS Pre-processors. I also read a very nice blog post from Miller H. Borges Medeiros illustrating some drawbacks of using these.

Tuesday, May 1, 2012

Report Server Model generation error: Table has a non-primary data source

A lil bit unorthodox solution!!

I faced this error (Report Server Model generation error: Table has a non-primary data source) yesterday while working on SSRS. I was trying to create a Report Model (SMDL) on SQL Server Buisness Development Studio and was getting this error repeatedly in final step of “Report Model Wizard”.

Query which I was using in Data Source View was somewhat like below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1

I googled this error a bit but found no solution and hence I adopted this lil bit unorthodox option.

My solution was to add an artificial column to my resultset and then using it as logical primary key. So I changed my query as below:

SELECT Table1.Col1, Table1.Col2, Table2.Col2, Table2.Col3 ', NEWID()AS Id
FROM Table1
JOIN Table2
ON Table1.Col1 = Table2.col1

And then I used this new column as logical primary key, as shown in below image. 



This is working like charm for me but I am yet to test this fully so use this solution for you after through testing. Hope this helps.

Thursday, March 29, 2012

search as you type functionality for ASP.NET site using JQuery and ASMX web services

This post is for search as you type functionality (on demand loading) for ASP.NET site using JQuery and ASMX web services.

Approach:
  • Step 1: Handle textbox keydown event to get user input(‘keyup’ could have been easier choice but it has a problem, will tell u that later in this post)
  • Step 2: Send an AJAX hit to server to get search results corresponding to user input.
  • Step 3: Catch and display result on client side
  • Step 4: Separate handling for down/up key for traversing between search results, enter and tab keys for selecting specific search result and escape key to remove search result.
Cool things to look for:
  • CSS stuff (z-index, etc.) that give search results google search type floating feel (exaggerating a bit to keep your interest here ;)
  • AJAX calls to ASMX web service.
 
 Code blocks: (Download Code)

ASPX (Just a textbox to type and a div to hold results)  
< span style="color:White;">Search As You Type:
        < input id="txtTest" type="text" style="width: 400px;" />
        < div id="divPredictedResultsContainer" class="divPredictedResultsContainer" >


Jquery (Code to handle keydown event and make Ajax hits with entered text)
<script type="text/javascript">
        $(document).ready(function() {
            var selectedResultCounter = 1;
            $("#txtTest").keydown(function(event) {
                //check if key pressed is alphabet, a number or backspace
                if ((event.keyCode > 64 && event.keyCode < 91) || (event.keyCode > 47 && event.keyCode < 58) || (event.keyCode == 8)) { //handle alphabets, numbers and backspace
                    var currentValue = ''
                    if (event.keyCode == 8 && $('#txtTest').val().length > 0) {
                        currentValue = $('#txtTest').val().substring(0, $('#txtTest').val().length - 1);
                    } else {
                        currentValue = $('#txtTest').val() + String.fromCharCode(event.keyCode);
                    }

                    selectedResultCounter = 1;
                    $.ajax({
                        type: "POST",
                        url: "GetData.asmx/GetData",
                        data: "{'startsWith':'" + currentValue + "'}",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function(msg) {
                            $("#divPredictedResultsContainer").html(msg.d);
                            if (msg.d.indexOf("divPredictedResultStyle") > -1) {
                                $("#divPredictedResultsContainer").addClass("roundedCorners");
                            }
                        },
                        error: function(xhr, ajaxOptions, thrownError) {
                            alert(xhr.status);
                            alert(thrownError);
                        }
                    });
                } else if (event.keyCode == 40) {       //handle down key
                    var resultCount = $(".divPredictedResultStyle").size();
                    if (selectedResultCounter == resultCount + 1) {
                        selectedResultCounter = 1;
                        $("#divPredictedResult" + resultCount).removeClass("divPredictedResultSelected");
                    }
                    $("#divPredictedResult" + selectedResultCounter).addClass("divPredictedResultSelected");
                    $("#divPredictedResult" + (selectedResultCounter - 1)).removeClass("divPredictedResultSelected");
                    selectedResultCounter = selectedResultCounter + 1;

                } else if (event.keyCode == 38) {       //handle up key
                    selectedResultCounter = selectedResultCounter - 1;
                    var resultCount = $(".divPredictedResultStyle").size();
                    if (selectedResultCounter == 1) {
                        selectedResultCounter = resultCount + 1;
                        $("#divPredictedResult1").removeClass("divPredictedResultSelected");
                    }
                    $("#divPredictedResult" + (selectedResultCounter - 1)).addClass("divPredictedResultSelected");
                    $("#divPredictedResult" + selectedResultCounter).removeClass("divPredictedResultSelected");
                } else if (event.keyCode == 9 || event.keyCode == 13) {       //handle tab key and enter key
                    if (event.preventDefault) {                               //this is to select one of the records
                        event.preventDefault();                               //from search results
                        event.stopPropagation();
                    }
                    $('#txtTest').val($(".divPredictedResultSelected").html());
                    return false;
                } else if (event.keyCode == 27) {                           //handle escape key
                    $(".divPredictedResults").hide();                       //hide results div, if escape key is pressed
                    $("#divPredictedResultsContainer").removeClass("roundedCorners");
                }
            });
        });
       
    script>
Points to ponder (read these points only if u r not in hurry, else move to ASMX code block :)
      • ·         Here if I would have used keyup event in place of keydown, It wouldn’t have been possible to select result by pressing tab or enter key as in that case, event has already been occurred thus prevent default can’t work.
      • ·         Code in red (see above) is a known bug :( u can c it as coding exercise): This code block handles backspace key but assumes that user will press it end of text only and not from between. I have this problem because on keydown I don’t have final value of textbox, I just have the keycode of key pressed, so I am forced to assume that key pressed is at end only(I’ll update this post once I find the proper solution).
      • ·         Code in green is heart of this all, it sends request to an ASMX service and captures response coming from there.
      • ·         In code blocks that handle up/down key there is logic to keep selecting search result records in loop, means if down key is pressed and last result found was currently selected then automatically selection will get changed to first search result.
 
ASMX Service code (To serve results from server)
< WebMethod() > _
    Public Function GetData(ByVal startsWith As String) As String
        Dim strResults As New StringBuilder(String.Empty)
        Dim dtResults As DataTable = GetDataForIntellisense(startsWith)
        Dim counter As Integer = 1
        strResults.Append("< div class='divPredictedResults'>")
        If dtResults.Rows.Count > 0 Then
            For Each dr As DataRow In dtResults.Rows
                strResults.Append("< div class='divPredictedResultStyle' id='divPredictedResult" + counter.ToString() + "'>" + dr("code").ToString() + "")
                counter += 1
            Next
        Else
            strResults.Append("No results found.")
        End If
        strResults.Append("")
        Return strResults.ToString()
    End Function

There is nothing typical in above ASMX code, its just about fetching records from database and then generating html corresponding to records retreived. One thing worth noticing is that one div is created corresponding to each search result and they have been given ids in incremental order. Other thing is method ‘GetDataForIntellisense’, implementation of this is upon you. Current code expects this method to return a datable with a column named “code”.


CSS (last but not least)
In above css, class ‘divPredictedResultsContainer’ has two lesser known attributes, namely z-index and border-radius.
Z-index is to specify stack order of an element. It lets results to float above other contents and thus you don’t need to reserve space for search results and can show search results without disturbing other contents of site.
Border-radius property is for giving rounded corners effect to div. This might not work in older browsers.


Download sample code from here.

About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!