Fransiscus Setiawan | EV Charging & Azure Solution Architect | Sydney

Technical Insights: Azure, .NET, Dynamics 365 & EV Charging Architecture

Author: fransiscuss Page 17 of 19

Temporary View using Common Table Expression In Sql server 2005

By

On August 29, 2008

In SQL Server

In this post, I will discuss on how to optimize join. Based on my experience. When you join record you need to join as fewer as possible otherwise it will result in performance. And please make sure you put the index on the key that you are using for join.

Within SQL Server 2005, You can use temporary view, It is not a table variable and it is not a temporary table. You also need to try to avoid cursor as well if possible.

First sample is a traditional join where I joined all the tables that required. In this sample EvenResult table has 20 million rows and I need to count the evenresult table based on the EventID that I get from the join criteria.

1. Join all tables./Traditional Way and it takes about 21secs

    SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            INNER JOIN
                EventResults er
            ON
                E.EventID = er.EventID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))

2. Using Temporary Views, In this query. I try to filter down all the required eventID based on the criteria and insert it into a temporary view(tblEvents). Once i get the filtered result then I joined it with EventResults table and It takes only 16secs considering the number of records.

WITH tblEvents(EventID) AS
(
            SELECT
                E.EventID
            FROM
                Events E
            INNER JOIN
                Venues V
            ON
                E.VenueID = V.VenueID
            INNER JOIN
                Regions R
            ON
                R.RegionID = V.RegionID
            WHERE
                (DATEDIFF(d, E.EventDate, @DateFrom) = 0)
            AND
                ((@StateID IS NULL) OR (R.StateID = @StateID))
            AND
                ((@RegionID IS NULL) OR (R.RegionID = @RegionID))
            AND
                ((@VenueID IS NULL) OR (V.VenueID = @VenueID))
            AND
                ((@EventScheduleID IS NULL) OR (E.EventScheduleID = @EventScheduleID))
)

            SELECT
                COUNT(*) As Total
            FROM
                tblEvents te
            INNER JOIN
                EventResults er
            ON
                te.EventID = er.EventID
            GROUP BY
                er.MemberID
            HAVING
                COUNT(*) >= @MinGame
            AND
                COUNT(*) <= @MaxGame

Error System.NotSupportedException: The given path’s format is not supported

By

On August 29, 2008

In ASP.NET, C#

I got this exception when i tried to uploading a file to a web and try to save it as a different name

System.NotSupportedException: The given path’s format is not supported.
at System.Security.Util.StringExpressionSet.CanonicalizePath(String path, Boolean needFullPath)
at

By using code below it will make sure that you will get the real file name without any trailing path, fuCSV is a file upload control and there is a property file name but it doesn’t guarantee that you will get the actual file name. When i tried to debug it, it also give me the file path. The best way of doing this is to use GetFileName method from System.IO.Path

It was:

string filename = fuCSV.PostedFile.FileName;

Fix

string filename = System.IO.Path.GetFileName(fuCSV.PostedFile.FileName);

Record/Row Filter in DataTable

By

On August 29, 2008

In .NET, ASP.NET, C#

When I have a smaller rows return from my sql stored procedure, I tend to think of not recreating another stored procedure to do filtering or to use optional parameter. My idea is to do filtering from the code base and not recreating/adding the stored procedure.

The idea was to create a generic stored procedure without any filter and apply the filter from the code base. The example given is by applying filter to dataset and then after that add the datatable to dataset and then cast it back again to the datatable.

  public tdsEvent.EventSummaryDataTable TodayEventSummaryFilter(string filter)
        {
            tdsEvent.EventSummaryDataTable table = TodayEventSummaryCache();
            DataRow[] rows = table.Select(filter); //apply the filter first

            if (rows.Length != 0)
            {
                DataSet ds = new DataSet();

                tdsEvent.EventSummaryDataTable eventTable = new tdsEvent.EventSummaryDataTable();
                ds.Tables.Add(eventTable); // add to the filter
                ds.Merge(rows, false, MissingSchemaAction.Ignore);
                // cast it back to the data table
                table = ds.Tables[0] as tdsEvent.EventSummaryDataTable;
            }
            else
            {
                table = new tdsEvent.EventSummaryDataTable();
            }

            return table;
        }

This is the alternative code which doing the same thing as above

Dim table as tdsEvent.EventSummaryDataTable = TodayEventSummaryCache()
//create dataview instance based on datatable

dim dv as DataView = new DataView(table)

//create the filter to select the valid only
dv.RowFilter = "Valid = 1"

//bind to the grid or repeater
rptEvent.Datasource = dv
rptEvent.DataBind()

ORDER BY in UNION

By

On August 29, 2008

In SQL Server

I was having a problem this morning in using a ORDER BY in UNION Clause. The solution is to use keyword of “TOP 100 PERCENT” on the related ORDER BY SELECT query.

CREATE PROCEDURE MyAPLRank
@MemberID int
AS
      SET NOCOUNT ON;
BEGIN
      SELECT r.ResultName,r.PlayerOfTheYearID,r.MemberID,r.RankID,r.memberCode,
            r.FirstName,r.Surname,r.State,r.NumEvents,r.Points,r.AvgPoints,
                  r.MeetsRequirements,r.Valid,r.IsLeaderBoard,r.RowNum
      FROM
      (
            SELECT
                  'Player of The Year' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,1 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NULL AND s.WeekID IS NULL AND s.QuarterID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Month' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,2 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID AND MeetsRequirements = 1
            UNION
            SELECT
                  'Player of The Week' As ResultName,PlayerOfTheYearID,MemberID,
                  RankID,memberCode,FirstName,Surname,State,NumEvents,Points,
                  AvgPoints,MeetsRequirements,Valid,'0' as IsLeaderBoard,3 as RowNum
            FROM
                  PlayerOfTheYearQuery
            WHERE
                  PlayerOfTheYearID
                  =
                  (
                        SELECT
                              TOP 1 poy.PlayerOfTheYearID
                        FROM
                              PlayerOfTheYear poy
                        INNER JOIN
                              Schedule s
                        ON
                              (s.ScheduleID = poy.ScheduleID)
                        WHERE
                              (s.MonthID IS NOT NULL AND s.WeekID IS NOT NULL)
                        ORDER BY
                              s.ScheduleID
                        DESC
                  ) AND MemberID = @MemberID  AND MeetsRequirements = 1
      UNION

                SELECT TOP 100 PERCENT
                         v.venue As ResultName,l.leaderboardID as PlayerOfTheYearID,lq.MemberID,
                         lq.RankID,lq.membercode,lq.name as FirstName,lq.name as Surname,s.State,
                        lq.NumEvents,lq.Points,lq.points as AvgPoints,lq.valid as MeetsRequirements,
                        lq.Valid, '1' as IsLeaderBoard,4 as RowNum
                FROM
                        leaderboardquery lq
                INNER JOIN
                        leaderboard l
                ON
                        (
                                l.leaderboardid = lq.leaderboardid
                            AND
                                l.venueid IS NOT NULL
                            AND
                                lq.MemberID = @MemberID
                            AND
                                l.scheduleID =
                                        (
                                         SELECT    ScheduleID
                                         FROM    Schedule
                                         WHERE    GETDATE() BETWEEN FromDate AND ToDate
                                                AND MonthID IS NOT NULL
                                        )
                        )
                INNER JOIN
                        venues v
                ON
                        (v.venueID = l.venueid)
                INNER JOIN
                        states s
                ON
                        l.StateID = s.StateID
                ORDER BY
                    l.ScheduleID
                DESC
      ) r
ORDER BY
      RowNum
END

Casting DataItem in repeater to strongly typed dataset

By

On August 29, 2008

In ASP.NET, VB.NET

Most of the time when you use repeater, you want to be able to cast the datasource/dataitem, you don’t really want to do DataBinder.Eval(e.Item.DataItem, “Description”) but you want to do row.Description. It’s not hard to do that, this is the snippet

Protected Sub rptEvents_Repeater(ByVal sender As Object, ByVal e As RepeaterItemEventArgs) Handles rptEvents.ItemDataBound
        If (e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem) Then

             'you need to cast eh dataitem into datarowview and cast them again to strongly type dataset

            Dim view As DataRowView = DirectCast(e.Item.DataItem, DataRowView)
            Dim row As tdsEvent.EventSummaryRow = DirectCast(view.Row, tdsEvent.EventSummaryRow)

            If Not (row Is Nothing) Then

                Dim lblVenue As Label = DirectCast(e.Item.FindControl("lblVenue"), Label)
                Dim lblEvent As HyperLink = DirectCast(e.Item.FindControl("lblEvent"), HyperLink)
                Dim lblState As Label = DirectCast(e.Item.FindControl("lblState"), Label)
                Dim lblTime As Label = DirectCast(e.Item.FindControl("lblTime"), Label)
                Dim lblType As Label = DirectCast(e.Item.FindControl("lblType"), Label)

                Dim eventClass As String = row.ColorCode ' don't set free

                ' if event is not normal, set it...
                If Not (row.IsVenueNull()) Then
                    lblVenue.Text = row.Venue
                End If

                If Not (row.IsEventNull()) Then
                    lblEvent.Text = Left(row.Event, 25) + "..."
                    lblEvent.NavigateUrl = String.Format("~/playpoker/view-event.aspx?EventID={0}&EventTypeID={1}", row.EventID.ToString(), row.EventTypeID.ToString())
                End If

                If Not (row.IsStateNull()) Then
                    lblState.Text = row.State
                End If

                If Not (row.IsEventTypeNull()) Then
                    lblType.Text = row.EventType
                End If

                If Not (row.IsRegistrationTimeNull()) Then
                    lblTime.Text = row.RegistrationTime.ToString("hh:mm tt")
                End If

                'bind all the data...

                'set class's
                If Not (String.IsNullOrEmpty(eventClass)) Then
                    lblVenue.CssClass = eventClass
                    lblEvent.CssClass = eventClass
                    lblState.CssClass = eventClass
                    lblTime.CssClass = eventClass
                    lblType.CssClass = eventClass
                End If

            End If
        ElseIf (e.Item.ItemType = ListItemType.Header) Then

            Dim lnk As LinkButton = DirectCast(e.Item.FindControl("lnkVenue"), LinkButton)

            If Not (lnk Is Nothing) Then
                lnk.CommandName = "SortCommand"
                lnk.CommandArgument = "Venue"
            End If

            lnk = DirectCast(e.Item.FindControl("lnkEvent"), LinkButton)

            If Not (lnk Is Nothing) Then
                lnk.CommandName = "SortCommand"
                lnk.CommandArgument = "Event"
            End If

            lnk = DirectCast(e.Item.FindControl("lnkState"), LinkButton)

            If Not (lnk Is Nothing) Then
                lnk.CommandName = "SortCommand"
                lnk.CommandArgument = "State"
            End If

            lnk = DirectCast(e.Item.FindControl("lnkTime"), LinkButton)

            If Not (lnk Is Nothing) Then
                lnk.CommandName = "SortCommand"
                lnk.CommandArgument = "lnkTime"
            End If

            lnk = DirectCast(e.Item.FindControl("lnkType"), LinkButton)

            If Not (lnk Is Nothing) Then
                lnk.CommandName = "SortCommand"
                lnk.CommandArgument = "EventType"
            End If

        End If
    End Sub

How to filter and sort in Dataset

By

On August 29, 2008

In ASP.NET, VB.NET

Here is the way to filter or to sort dataset and then you can bind into your datagrid

              If intEventID = 0 Then
                ds = DataAccess.Events.GetAllEventsByRegionID(Me.radStartDate.SelectedDate, Me.radEndDate.SelectedDate, intRegionID, intVenueID)

            Else
                ds = DataAccess.Events.GetEventByIDDS(intEventID)
            End If

            'this is used to show the valid event only
            Dim eventView As DataView
            eventView = ds.Tables(0).DefaultView
            eventView.RowFilter = "Valid = true"

            'this is used to sort
            eventView.Sort = "EventID Desc"

           grdEvents.DataSource = eventView

Get current page name in ASP.NET

By

On August 29, 2008

In ASP.NET, C#, VB.NET

In order to get current page name , we need to get it from server variables of “ScriptName” but that’s not enough since it will bring up the whole path. you need to use System.IO in order to get the file name

C# version: 

string strCurrentPage = System.IO.Path.GetFileName(Request.ServerVariables ["SCRIPT_NAME"])

VB.NET version:

 Dim strCurrentPage As String = System.IO.Path.GetFileName(Request.ServerVariables("SCRIPT_NAME"))

XML http object cross browser

By

On August 29, 2008

In ASP.NET, Javascript

I believe this will be useful for everyone who wants to implement AJAX manually and make it compatible cross browser.



           // cross-browser method to retrieve an XMLHttp object for asynchronous requests & responses
            function GetHTTPRequest()
            {
                var http_request = false;

                if (window.XMLHttpRequest)
                {
                    // Mozilla, Safari,...
                    http_request = new XMLHttpRequest();

                    if (http_request.overrideMimeType)
                    {
                        http_request.overrideMimeType("text/xml");
                    }
                }
                else if (window.ActiveXObject)
                {
                    // IE
                    try
                    {
                        http_request = new ActiveXObject("Msxml2.XMLHTTP");
                    }
                    catch (e)
                    {
                        try
                        {
                            http_request = new ActiveXObject("Microsoft.XMLHTTP");
                        }
                        catch (e) {}
                    }
                }

                return http_request;
            }

SYS is undefined in AJAX Update Panel

By

On August 29, 2008

In ASP.NET

I tried to implement AJAX on my website by using AJAX extension on visual studio .NET. I tried to drag update panel to my page and I’ve put my button event handler to the trigger collection in update panel. I was expecting when i click that particular button then it will not refresh or load up the page, but in fact it reloads the page which showing me that the AJAX panel does not work, and my firebug detects that there are errors related with sys is undefined.

This happens because I haven’t AJAX config on my web.config. The config is related with HTTP handler, you can try to put this in your web.config. Please make sure it should be inside “”

Set/Find item in Dropdownlist based on its list item value in ASP.NET

By

On August 29, 2008

In ASP.NET, C#

this is a very simple trick on how to select an item in pre binding/pre populated dropdown list from database in asp.net, you can use “FindByValue” method from dropdown list to return you a list item then you can use index of to find the index no of that list item and then set the selected index.

ddlSMSRate.SelectedIndex = ddlSMSRate.Items.IndexOf(ddlSMSRate.Items.FindByValue(drCompany.SMSRate));

Page 17 of 19

Previous

Next

Powered by WordPress & Theme by Anders Norén