Tuesday, October 1, 2013

Issue with XSD element in ASP.Net when there is Inheritance

In one of my project I was consuming an web service which had some Inheritance where the parent Class is EmployeeProfile and the child Class is FieldEmployeeProfile. When I was calling a method returning Child Class object Array, I was not getting the Child Class specific Properties (not those inherited from Parent) set. Instead, I found a property in Parent Class called "Any' which is an Array of XMLElement and having the missing Child Object Property equivalent Name-Values in the XML element array.

I will try to explain the problem using a simpler example I created and the resolution.

Server Side Classes
    public class Shape
    {
        //[System.Xml.Serialization.XmlTypeAttribute("Name")]
        [System.Xml.Serialization.XmlElement(Order = 0)]
        public string Name { get; set; }

        [System.Xml.Serialization.XmlElement(Order = 1)]
        public virtual int Area { get; set; }

        [System.Xml.Serialization.XmlAnyElementAttribute(Order = 2)]
        public System.Xml.XmlElement[] AnyElement
        {
            get;
            set;
        }
    }

    public class Rectangle : Shape
    {
        [System.Xml.Serialization.XmlElement(Order = 0)]
        public int Side1 { get; set; }

        [System.Xml.Serialization.XmlElement(Order = 1)]
        public int Side2 { get; set; }

        [System.Xml.Serialization.XmlElement(Order = 2)]
        public override int Area
        {
            get
            {
                return Side1 * Side2;
            }
        }
        [System.Xml.Serialization.XmlAnyElement(Order = 3)]
        public System.Xml.XmlElement[] AnyChildElement
        {
            get;
            set;
        }
    }

Note the AnyElement in Parent Class and special attention to Order value. I will explain it later.

The WSDL generated for these classes as as below


<s:complexType name="Shape">
  <s:sequence>
    <s:element minOccurs="0" maxOccurs="1" name="Name" type="s:string" />
    <s:element minOccurs="1" maxOccurs="1" name="Area" type="s:int" />
    <s:any minOccurs="0" maxOccurs="unbounded" />
  </s:sequence>
</s:complexType>

<s:complexType name="Rectangle">
  <s:complexContent mixed="false">
    <s:extension base="tns:Shape">
      <s:sequence>
        <s:element minOccurs="1" maxOccurs="1" name="Side1" type="s:int" />
        <s:element minOccurs="1" maxOccurs="1" name="Side2" type="s:int" />
        <s:any minOccurs="0" maxOccurs="unbounded" />
      </s:sequence>
    </s:extension>
  </s:complexContent>
</s:complexType> 

When my web method returns Rectangle I get it as below with Side1 and Side2 property values as 0 and the base Any Property is having those missing property related XMLElements.



To fix this the easiest way is to remove the "Any" property related code from the Base Class shape in "Reference.Cs" file as below (you need to click on "Show All Files" in Solution explorer top to show Reference.cs file in solution explorer).

The alternate approach is to update the reference.cs file for the code generated for child class Rectangle as below:
[System.Xml.Serialization.XmlElementAttribute(Order = 0)]
        public int Side1
        {
            get
            {
                if (this.side1Field == 0)
                {
                    foreach (System.Xml.XmlElement xe in base.Any)
                    {
                        if (xe.Name == "Side1")
                        {
                            int.TryParse(xe.InnerText, out this.side1Field);
                        }
                    }
                }
                return this.side1Field;
            }
            set
            {
                this.side1Field = value;
            }
        }

        ///
        [System.Xml.Serialization.XmlElementAttribute(Order = 1)]
        public int Side2
        {
            get
            {
                if (this.side2Field == 0)
                {
                    foreach (System.Xml.XmlElement xe in base.Any)
                    {
                        if (xe.Name == "Side2")
                        {
                            int.TryParse(xe.InnerText, out this.side2Field);
                        }
                    }
                }
                return this.side2Field;
            }
            set
            {
                this.side2Field = value;
            }

        }

Note the tag is for expending the request & response payload for an web service in future without changing the WSDL. So you may require to actually utilize the Any property. So you shouldn't delete the property from reference.cs file if your code needs to handle any additional node without breaking it's logic.

I previously mentioned to pay special attention to Order value in the server side code for the classes. The reason is that all the properties following the Any element, will not be populated if you are not taking special care. It will have same null/0 value as the Child class properties.

Saturday, June 1, 2013

C# Predicate, Anonymous Delegate & Lambda Expression usage to get same result

Predicate:

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
    	List list = new List { 1, 2, 3 };

    	Predicate predicate = new Predicate(greaterThanTwo);

    	List newList = list.FindAll(predicate);
    }

    static bool greaterThanTwo(int arg)
    {
    	return arg > 2;
    }
}
 

Anonymous Delegate:

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        List list = new List { 1, 2, 3 };

        List newList = list.FindAll(delegate(int arg)
                           {
                               return arg> 2;
                           });
    }
}

Lambda Expression:

using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
    	List list = new List { 1, 2, 3 };

    	List newList = list.FindAll(i => i > 2);
    }
} 
 
Reference 

Tuesday, October 16, 2012

Oracle Time Zone Conversion

I have to prepare a report where the start date and end date was stored in GMT and I had to show the no of days it took to complete.
The logic is very simple no matter what it the time, just consider the date. Like if it has Start date as 2012-Dec-21 10:30:00 PM and End Date 2012-Dec-22 12:30:00 AM, it should be shown as 1 day even though it has taken just 2 and half hours.
So I started very cool as below

Select (trunc(end_date) - trunc(begin_date)) from wf_tasks

Now came the timezone into picture. Most of the records were correct but very few was having some issue as this is related to tasks and people doesn't work late at office :).

Consider the scenario when an user started a task on 9:30AM ET on Dec/21/2012 and completed the task (end_task column) at 9:30PM ET on Dec/22/2012. So in database the converted date saved as
Start Date (begin_date): 21-12-2012 2:30:00 PM
End Date(end_date): 23-12-2012 2:30:00 AM
(consider daylight saving off).
So when you run below query, it will give you 2 days but actuall the user started on 21st and completed on 22nd as per his Eastern time zone. So it should have shown 1 days.

Select
(
  TRUNC(TO_DATE('2012-12-23 2:30:00 AM','YYYY-MM-DD HH:MI:SS AM'))
  -
  TRUNC(TO_DATE('2012-12-21 2:30:00 PM','YYYY-MM-DD HH:MI:SS AM') )
)as No_Of_Days
from dual
--Returns 2 days

Resolution:-
I came to know about FROM_TZ function in oracle which Return Values TIMESTAMP_TZ
Syntax
FROM_TZ (timestamp_value , time_zone_value).


Rewriting the query considering the time zone:

Select 
(
  TRUNC(cast((FROM_TZ(CAST(TO_DATE('2012-12-23 2:30:00 AM','YYYY-MM-DD HH:MI:SS AM') AS TIMESTAMP),'GMT') AT TIME ZONE 'America/New_York')as Date))
  - 
  TRUNC(cast((FROM_TZ(CAST(TO_DATE('2012-12-21 2:30:00 PM','YYYY-MM-DD HH:MI:SS AM') AS TIMESTAMP),'GMT') AT TIME ZONE 'America/New_York')as Date))
)as No_Of_Days
from dual 

--Returns 1 Day

My Final Query looked like below with the columns storing date values.

Select
(
  TRUNC(cast((FROM_TZ(cast( end_date as Timestamp),'GMT') AT TIME ZONE 'America/New_York')as Date))
  -
  TRUNC(cast((FROM_TZ(cast( begin_date as Timestamp),'GMT') AT TIME ZONE 'America/New_York')as Date))
)as No_Of_Days
from wf_tasks

To get the zones abbreviation run below query:
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES
SELECT UNIQUE tzname FROM V$TIMEZONE_NAMES;

Refrence:
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1088.htm

Tuesday, October 13, 2009

Custom paging in Gridview With ObjectDataSource

Many times we use paging with GridView with AllowPaging = "True" and binding the GridView with some ObjectDataSource. It actually fetches all row from the database and depending on the no of records it fetched, it generates the Pager section of the page and according to the current page index, it shows the set of record.
This approach is good for when you have limited no of record in the database. But there are many situations where there is thousands of rows in the database and you have to display only 25 records(PageSize) out of that thousands of records. So it is not good practice to fetch all record through object datasource to display only 25 records.
Here we should use custom paging to fetch only the 25 rows starting from the startindex which is
CurrentPageIndex*PageSize (As Index is 0 Based).
Calculated automatically by objectdatasource.
Here is the change you have to do

  • Set EnablePaging="True" for ObjectDataSource

  • Set The SelectMethod Property

  • Set value for following Properties of ObjecDataSource StartRowIndexParameterName, SortParameterName, MaximumRowsParameterName
    These parameters needs to be present in the method attached for selecting the result for objectdatasource.

  • Set SelectCountMethod to the name of method returning the total no of rows.


Here is my ObjectDataSourceCode


<asp:objectdatasource id="objDS" runat="server" enablepaging="True" selectmethod="SearchResult" typename="PRP.BusinessLogic.TpilTblNsefaoscripmaster" startrowindexparametername="paramStartRowIndex" sortparametername="paramSortExpression" maximumrowsparametername="paramPageSize" selectcountmethod="GetNoOfRows">
<selectparameters>
<asp:parameter name="paramSymbol" type="String">
<asp:parameter direction="Output" name="paramTotalNoOfRows" type="Int32">
</asp:parameter>
</asp:parameter>
</selectparameters>
</asp:objectdatasource>


The SearchResult & GetNoOfRows Function


public DataTable SearchResult(string paramSymbol,
int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
TpilTblNsefaoscripmasterHelper.SearchResult(paramSymbol, paramStartRowIndex, paramStartRowIndex + paramPageSize, paramSortExpression, ref paramTotalNoOfRows);
this.totalNoOfRows = paramTotalNoOfRows;
}
int totalNoOfRows = 0;
public int GetNoOfRows(string paramSymbol,
int paramStartRowIndex, int paramPageSize, string paramSortExpression, out int paramTotalNoOfRows)
{
paramTotalNoOfRows = totalNoOfRows;
return totalNoOfRows;
}


The parameter list for both the functions are same but the second function retuns int no of rows. But i don't have used two function call for getting the total noofrows. Inside select function i have set the totalNoOfRows parameter which is accessed inside GetNoOfRows method to get the no of rows. This works as objectdatasource calls both the method on same object instance of the class and none of the methods and the class variable is static and the select methos is called first and then the row count method is called.
Now check the stored procedure used for this purpose


Create PROCEDURE [dbo].[USP_TPIL_tbl_NSEFAOScripSelectForSearch]
@Symbol varchar(50),
@StartIndex int = 0,
@EndIndex int =25,
@SortExpression varchar(50),
@TotalNoOfRows int OUTPUT
AS
SET NOCOUNT ON
Select * from
(
SELECT m.Instrument,m.Symbol,m.SymbolDescription,ExpiryDate,
Row_Number() OVER (Order By
CASE WHEN @SortExpression='EXPIRYDATE' OR @SortExpression='EXPIRYDATE ASC' THEN m.EXPIRYDATE END DESC,
CASE WHEN @SortExpression='EXPIRYDATE DESC' THEN m.EXPIRYDATE END ASC ,
CASE WHEN @SortExpression='SYMBOL' OR @SortExpression='SYMBOL ASC' THEN m.SYMBOL END DESC,
CASE WHEN @SortExpression='SYMBOL DESC' THEN m.SYMBOL END ASC
) as RowNumber
FROM
[dbo].[TPILl_NSEFAOScripMaster] m
WHERE
(m.[Symbol] LIKE @Symbol OR @Symbol = '')
)t
Where RowNumber>@StartIndex AND RowNumber<=@EndIndex --return SELECT @TotalNoOfRows = Count(*) FROM [dbo].[TPILl_NSEFAOScripMaster] m WHERE (m.[Symbol] LIKE @Symbol OR @Symbol = '')


As i am using Sql Server 2005, i used ROW_Number() function. The value of output parameter "TotalNoOfRows " is set to the variable "totalNoOfRows" inside "SearchResult" function.
To know about how to get Row Number in Sql Server 2000 please check the bottom of this link.

Thursday, April 16, 2009

Focus the first control of your page

In all pages we have to put the focus on the first control of the page. It can be a textbox,a radio button, a checkbox, a select field, a textarea or a button. For my current project i have created a function which i have placed inside the masterpage and it will traverse through the page starting from a particular control (inside the control) and will take the first occurance of the above types of control and will put the cursor focus on the control. Below is the javascript code i used to achieve this

function selectFirstControl()
{
var objIP = getFocusableControl(document.getElementById('tdContentBody'));
if(objIP!=null)
{
objIP.focus();
return;
}
}

function getFocusableControl(parentCtrl)
{
if(!$(parentCtrl).visible())
return null;
if(parentCtrl.tagName == "INPUT")
{
if(parentCtrl.getAttribute("type")=="hidden")
{
return null;
}
return parentCtrl;
}
else
{
if(parentCtrl.tagName == "SELECT")
{
return parentCtrl;
}
}
var children = $(parentCtrl).childElements();
for(var i = 0;i<children.length;i++)
{
var resCtrl = getFocusableControl(children[i]);
if(resCtrl != null)
return resCtrl;
}
}


Call the function selectFirstControl(); from the bottom of your page.
Here i have called the function "getFocusableControl" recursively to get the control. It is checking the tagname of the node, if it is "Input" or "Select" then it is excluding the hidden fields by checking the type property for for inputs and if it is not hidden, then it is putting the focus on the control.
Iam checking if the parent control visible or not with $(parentCtrl).visible() which is a function from prototypejs to check the visibility. But this checks for the inline "display" property of the element, if "display :none" it returns false. But it cannot check if you are using the hidden property of hiding the control with stylesheet class.
If you are not using prototypejs, you can check the direct style property of the control.

Tuesday, April 7, 2009

Trigger Html/Javascript Event from Javascript

In many situation i had to trigger JavaScript Event from code. Like click on a button when something happened to post the page. It mainly comes into picture when i use UpdatePanel or using modalpopup for click on a button inside grid/list/gridview ect. This creation of event is very much dependent on the browser. Here is the code to trigger a button click event
function clickButton()
{
if( document.createEvent )
{
var evObj = document.createEvent('MouseEvents');
evObj.initMouseEvent( 'click', true, false, window, 0, 12, 345, 7, 220, false, false, true, false, 0, null );
$(' btnTransRefresh').dispatchEvent(evObj);
}
else
if( document.createEventObject )
{
$('btnTransRefresh').fireEvent('onChange');
}
}

Here i am using Prototyprjs framework, so you see the "$". You can replace those with document.getElementById('') syntax.
initMouseEvent takes the following parameters.
initMouseEvent( 'type', bubbles, cancelable, windowObject, detail, screenX, screenY, clientX, clientY, ctrlKey, altKey, shiftKey, metaKey, button, relatedTarget )

In many scenarios we do some javascript calculation on change of value in some particular textboxes. But in some scenarios, the value in the textbox on change of which we need to calculate is done through some javascript. Like in my project i was using calender from ajax control toolkit which is inside a project specific control names PRPCalender. In some pages i had to do some calculations only when the value in calender is changed. So i could not use the javascript events associated with the calender control (ajax). So i decided to go with triggering onchange event as the textbox showing the calender control doesn't fire the onchange event as the value is changed through JavaScript.
string methodName = "prpCalenderDateChanged" + txtCalendar.ClientID;
calendarExtender.OnClientDateSelectionChanged = methodName;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.AppendFormat(@"
function {0}(sender,eArg)
{{
//alert('Calender Date Changed');
var tbId = '{1}';
//$(tbId).blur();
//$(tbId).focus();
if( document.createEvent )
{{
var evObj = document.createEvent('HTMLEvents');
evObj.initEvent( 'onchange', true, false);
$(tbId).dispatchEvent(evObj);
}}
else
if( document.createEventObject )
{{
$(tbId).change();
}}
}}
", methodName, txtCalendar.ClientID);
ScriptManager.RegisterStartupScript(this, this.GetType(), "textChanged" + txtCalendar.ClientID, sb.ToString(), true);
Here inside my calender control PRPCalender, i am registering a function which is unique for each calender control inside any page (i am using the client id as concatined to generate the method name) which fires the onchange event on the textbox.
calendarExtender.OnClientDateSelectionChanged = methodName;
which is called by toolkit JS when the date value is changed.
Some Useful Link Link1

Thursday, March 26, 2009

Trigger not working when importing data with SSIS package

I am very new to SSIS package. Actually i am not a hardcode SQL guy, so i never needed to work with SSIS before. In my current project i needed to import data after fetching from a server with FTP and then extract the ZIP files and insert it into respective tables. For eatracting ZIP files i used "SharpZipLib" which is open source library for dot net. Then i created a temp table in which i first inserted to remove duplicate. Remember, i created the temp table on the fly inside the "Execute SQL Task" with create table statement and after insert to the original table i deleted the table. In this scenario you have to change the "DelayValidation" property of the "Data Flow Task" to "true" (By default it is false). Otherwise you will get some validation error as the temp table in which you are going to insert data is not present now, it is created on the fly and deleted after insert is completed.
Now in my case i had a trigger which need to fire each time i instered some data into the table. Actually, i was updating the stock rate and needed to copy the rate by trigger. But to my surprise, the trigger was not firing when i insert/update the data by the SSIS. But the trigger fires when i do the update by SQL.
The problem is with the settings in "OleDB Destination" inside "Data Flow Task". Here is how i solved the issue
Right click on th "OldeDB Destination" and you will find "Show Advanced Editor" and open the Advanced editor.

In the editor check the "FastLoadOptions" which is by default "TABLOCK,CHECK_CONSTRAINTS". You need to add one more option "FIRE_TRIGGERS" . And thats it. But remember the trigger runs once for each update/insert. Not for every update/insert. So in case where we are doing batch update, you must write the trigger code in such a way that it will be able to handle the situation for multiple row sets. Here is a sample statement
CREATE TRIGGER UpdateStockRate
ON INV_tbl_StockMaster
AFTER INSERT,Update
AS

UPDATE t
SET CMP = i.LastTradedPrice
FROM INV_tbl_Holding t
JOIN INSERTED i
ON i.Symbol = t.Symbol