Tuesday, June 5, 2012

Using APPLY Operator

The Apply operator available in SQL Server 2005 onwards is used to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

The Apply operator is similar to that of Joins but has no conditions in the query.

There are two forms of Apply operator:
  • Cross Apply
    The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. It's similar to that of Inner Join or Cross Join.
  • Outer Apply
    The OUTER APPLY, on the other hand, returns all rows from the outer table, even if the function produces no results. Thus, it generates NULL values for those records whose result set is not returned. It's similar to that of Outer Join.

Consider an example below to demonstrate Apply operator:

GO

CREATE TABLE [dbo].[Departments](
 [DepartmentId] [int] NOT NULL PRIMARY KEY,
 [DeparttmentName] [varchar](50) NULL,
);

GO

INSERT INTO [dbo].[Departments] VALUES(1, 'HR')
INSERT INTO [dbo].[Departments] VALUES(2, 'Marketing')
INSERT INTO [dbo].[Departments] VALUES(3, 'Finance')
INSERT INTO [dbo].[Departments] VALUES(4, 'R&D')
INSERT INTO [dbo].[Departments] VALUES(5, 'Training')
INSERT INTO [dbo].[Departments] VALUES(6, 'Gardening')


GO

CREATE TABLE [dbo].[Employees](
 [EmployeeId] [int] NOT NULL PRIMARY KEY,
 [EmployeeName] [varchar](50) NULL,
 [DepartmentId] [int] NULL REFERENCES [dbo].[Departments],
);

GO

INSERT INTO [dbo].[Employees] VALUES(1, 'Jeet', 1)
INSERT INTO [dbo].[Employees] VALUES(2, 'Sagar', 2)
INSERT INTO [dbo].[Employees] VALUES(3, 'Chirag', 2)
INSERT INTO [dbo].[Employees] VALUES(4, 'Jayesh', 3)
INSERT INTO [dbo].[Employees] VALUES(5, 'Ketan', 4)
INSERT INTO [dbo].[Employees] VALUES(6, 'Rikin', 4)
INSERT INTO [dbo].[Employees] VALUES(7, 'Pratik', 6)

GO

In above example we can see list of Departments and Employees related to departments (if any) in appropriate tables respectively.

As mentioned above, Apply operator works with table-values function (also known as TVF), consider the below example of it.
GO
CREATE FUNCTION [dbo].[GetEmployees]
(
 @DepartmentId int 
)
RETURNS 
@ResultSet TABLE 
(
 EmployeeId int,
 EmployeeName varchar(20)
)
AS
BEGIN
 INSERT INTO @ResultSet 
  SELECT EmployeeId,EmployeeName 
  FROM Employees 
  WHERE DepartmentId = @DepartmentId
 
 RETURN 
END

GO


Using CROSS APPLY
SELECT * FROM Departments
 CROSS APPLY GetEmployees(Departments.DepartmentId)

The above query returns the rows with employees assigned to departments, whereas the department wherein the employees are not assigned does not appear. Thus we can see the department "Training" is missing.


Using OUTER APPLY
SELECT * FROM Departments 
 OUTER APPLY GetEmployees(Departments.DepartmentId)

The above query returns all the rows with the outer table, whether or not the function returns data for a specific row. Thus we can see that employee value of "Training" department has NULL values.

Learn by diving in Programming Ocean...
Happy Programming!!!

Thursday, April 12, 2012

Customizing items of RadGrid page size combo based on total records

Telerik provides the easy to use RadGrid control with inbuilt paging feature.

By default paging combo includes 10, 20 and 50 page sizes, regardless of number of records displayed in the grid (i.e. if records counts to 11 then also page size combo displays 10, 20 and 50).

If we want to customize the page size combo items to show
  •     Records count > 10 then page size items should show 10 and 20 
  •     Records count >= 20 then page size items should show 10, 20 and 50
In order to achieve the above functionality, we will be using RadGrid ItemCreated and ItemEvent events, refer the below code
private int totalItemCount;
protected void RadGrid1_ItemEvent(object sender, GridItemEventArgs e)
        {
            if (e.EventInfo is GridInitializePagerItem)
            {
                totalItemCount = (e.EventInfo as GridInitializePagerItem).PagingManager.DataSourceCount;
            }
        }

protected void RadGrid1_ItemCreated(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            if (e.Item is GridPagerItem)
            {
                GridPagerItem pagerItem = (GridPagerItem)e.Item;
                RadComboBox pageSizeCombo = (RadComboBox)pagerItem.FindControl("PageSizeComboBox");

                if (totalItemCount > 10 && totalItemCount < 20)
                {
                    pageSizeCombo.Items.Clear();
                    RadComboBoxItem item = new RadComboBoxItem();
                    item = new RadComboBoxItem("10", "10");
                    item.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                    pageSizeCombo.Items.Add(item);

                    item = new RadComboBoxItem("20", "20");
                    item.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                    pageSizeCombo.Items.Add(item);
                }


                if (totalItemCount >= 20)
                {
                    if (pageSizeCombo.Items.Where(i => i.Value == "50").Count() == 0)
                    {
                        RadComboBoxItem item = new RadComboBoxItem();
                        item = new RadComboBoxItem("50", "50");
                        item.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
                        pageSizeCombo.Items.Add(item);
                    }
                }

            }
        }

Displaying page combo always regardless of number of records, refer below code

                         
          
                    . . . .
     


Showing "Show All" option in page size combo, refer below combo
private int totalItemCount;
protected void RadGrid1_ItemEvent(object sender, GridItemEventArgs e)
        {
            if (e.EventInfo is GridInitializePagerItem)
            {
                totalItemCount = (e.EventInfo as GridInitializePagerItem).PagingManager.DataSourceCount;
            }
        }

protected void RadGrid1_ItemCreated(object sender, Telerik.Web.UI.GridItemEventArgs e)
        {
            if (e.Item is GridPagerItem)
            {
                GridPagerItem pagerItem = (GridPagerItem)e.Item;
                RadComboBox pageSizeCombo = (RadComboBox)pagerItem.FindControl("PageSizeComboBox");

              GridPagerItem pagerItem = (GridPagerItem)e.Item;
 RadComboBox PageSizeCombo = (RadComboBox)pagerItem.FindControl("PageSizeComboBox");
             RadComboBoxItem item1 = new RadComboBoxItem();
             item1 = new RadComboBoxItem("Show All", totalItemCount.ToString());
             item1.Attributes.Add("ownerTableViewId", e.Item.OwnerTableView.ClientID);
            PageSizeCombo.Items.Add(item1);

            }
        }
Thus we are done...

Learn by diving in Programming Ocean...
Happy Programming!!!

Thursday, December 22, 2011

Let it snow...

Its Christmas and New Year holiday time. Both Google & Microsoft seems to be playing same "Let it snow" competition.

1. Go to google.com and search for "Let it snow". Press enter.
2. Go to URL - http://ie.microsoft.com/testdrive/performance/letitsnow/ . Also see other test drive by clicking on “Return to test drive” on right top corner.

Learn by diving in Programming Ocean...
Happy Programming!!!