Article Posted: AutoCompleteComboBox for Silverlight

I have posted an article at CodeProject on customization of the Silverlight AutoCompleteBox to be used as a type-ahead ComboBox in LOB applications at CodeProject. The AutoCompleteComboBox can be used in typical Object-to-Object associations (one that we typically encounter when creating associations in Entity Framework) as well as Foreign Key associations (the new association type introduced with Entity Framework 4).

Here’s a sample usage of the control:

  • Object to Object Association:

    Example data structure:

    public class SalesOrderDetail
    {
        Product product;
        public Product Product
        {
            get { return product; }
            set { product = value; }
        }
    }
    

    Example control usage:

     <custom:AutoCompleteComboBox
       SelectedItemBinding="{Binding Product, Mode=TwoWay}"
       ItemsSource="{Binding Path=Products, Source={StaticResource ViewModel}}"
     />
    
  • Foreign Key Association:

    Example data structure:

    public class SalesOrderDetail
    {
        int productID;
        public int ProductID
        {
            get { return productID; }
            set { productID = value; }
        }
    }
    

    Example control usage:

     <custom:AutoCompleteComboBox
       SelectedValue="{Binding ProductID, Mode=TwoWay}"
       SelectedValuePath="ProductID"
       ItemsSource="{Binding Path=Products, Source={StaticResource ViewModel}}"
     />
    

To view the implementation details, you can read the full article at :
http://www.codeproject.com/KB/silverlight/AutoComplete_ComboBox.aspx

T-SQL: Referring computed/calculated columns in the where and group by clause

I previously blogged about how we can write more manageable and readable TSQL queries using CROSS APPLY operator here. This small post is a follow-up and will describe another scenario. Suppose we have a computed column that is a result of some complex calculation and we want to refer it in a where clause (or a group by clause). This is not straight forward since we can’t use the computed column directly in the where clause like this:

select
  column1
  ,column2
  ,computedcolumn =
    Case
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
from SomeTable
where computedColumn < 100

The reason is that the Where part of the query is evaluated before the Select part. For this to work, we need to duplicate the whole computation in the where clause like this:

Where
  (
    Case
      when expression1 = true then column1 * ( column4 - column 5 )
      when expression2 = true then column2 * ( column5 - column 6 )
      when expression3 = true then column3 * ( column6 - column 7 )
      else column4 - column 5
    end
  ) < 100

Luckily, the APPLY operator comes to our rescue again. We just need to move our calculation to a CROSS APPLY section and we can refer to the calculated column by its alias in the where (as well as group by) clause:

select
  column1
  ,column2,
  ,computedcolumn
from SomeTable
cross apply
  (
    Select
      computedColumn =
        Case
          when expression1 = true then column1 * ( column4 - column 5 )
          when expression2 = true then column2 * ( column5 - column 6 )
          when expression3 = true then column3 * ( column6 - column 7 )
          else column4 - column 5
        end
  ) ComputatedColumn1Query
where computedColumn < 100

Great! We can also use a CTE or derived table to accomplish the same:

;With TableWithComputedColumnCalculation as
(
  select
    column1
    ,column2,
    ,computedcolumn =
      Case
        when expression1 = true then column1 * ( column4 - column 5 )
        when expression2 = true then column2 * ( column5 - column 6 )
        when expression3 = true then column3 * ( column6 - column 7 )
        else column4 - column 5
      end
  from SomeTable
)

select *
from TableWithComputedColumnCalculation
where computedColumn < 100

Notice that all the approaches I mentioned here (CROSS APPLY, CTE, derived table) are going to have the same query plan (and hence performance) and it is just a matter of personal preference which one to use.

T-SQL: Calculating cascading computed columns using CROSS APPLY operator

The powerful APPLY operator in Transact SQL is typically used to invoke a table-valued function for each row. Here’s an MSDN article that describes such usage. But a very interesting usage of the cross apply operator is to produce a much readable code in cascading calculations. I was working on an SSRS report a couple of days back and found it really helpful. Let’s have a look how.

Consider this simple data regarding employees, their regular hours, overtime hours, overtime code and regular rate:

With SampleData (Employee, RegHours, OTHours, OTCode, RegRate) as
(
      Select 'Employee1', 100, 10, 'OT1', 10
      union all
      Select 'Employee2', 100, 10, 'OT2', 10
      union all
      Select 'Employee3', 100, 10, 'OT3', 10
)

Select *
from SampleData

/*
Output:

Employee  RegHours    OTHours     OTCode RegRate
--------- ----------- ----------- ------ -----------
Employee1 100         10          OT1    10
Employee2 100         10          OT2    10
Employee3 100         10          OT3    10
*/

Suppose, we need to come up with the following result:

Employee  RegHours  OTHours  OTCode RegRate OTMultiplier OTRate  OTCost  RegCost TotalHours TotalCost EffectiveRate
--------- --------- -------- ------ ------- ------------ ------- ------- ------- ---------- --------- -------------
Employee1 100       10       OT1    10      1.2          12.0    120.0   1000    110        1120.0    10.18
Employee2 100       10       OT2    10      1.4          14.0    140.0   1000    110        1140.0    10.36
Employee3 100       10       OT3    10      1.5          15.0    150.0   1000    110        1150.0    10.45

Here’s how the above columns need to be calculated:

  1. OT Multiplier: To be calculated from OTType
  2. OT Rate: RegRate * OTMultiplier
  3. OT Cost: OTHours * OTCost
  4. Reg Cost: RegHours * RegCost
  5. Total Hours: RegHours + OTHours
  6. Total Cost: RegCost + OTCost
  7. Effective Rate: TotalCost / TotalHours

In the above calculations, the point to be noted is that almost every next column depends upon the result of a previous one. For example, Effective Rate depends upon Total Cost, that depends upon OT Cost, that depends upon OT Rate, that depends upon OT Multiplier, that depends upon the value in OT Type field.

The most straight forward attempt using a simple TSQL query would look something like:

Select *
  ,OTMultiplier =
    Case
      when OTCode='OT1' then 1.2
      when OTCode='OT2' then 1.4
      when OTCode='OT3' then 1.5
    end
  ,OTRate =
    Case
      when OTCode='OT1' then 1.2 * RegRate
      when OTCode='OT2' then 1.4 * RegRate
      when OTCode='OT3' then 1.5 * RegRate
    end
  , OTCost =
    Case
      when OTCode='OT1' then 1.2 * RegRate * OTHours
      when OTCode='OT2' then 1.4 * RegRate * OTHours
      when OTCode='OT3' then 1.5 * RegRate * OTHours
    end
  , RegCost = RegHours * RegRate
  , TotalHours = RegHours + OTHours
  , TotalCost =
    Case
      when OTCode='OT1' then 1.2 * RegRate * OTHours + RegHours * RegRate
      when OTCode='OT2' then 1.4 * RegRate * OTHours + RegHours * RegRate
      when OTCode='OT3' then 1.5 * RegRate * OTHours + RegHours * RegRate
    End
  , EffectiveRate =
    Case
      when OTCode='OT1' then (1.2 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
      when OTCode='OT2' then (1.4 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
      when OTCode='OT3' then (1.5 * RegRate * OTHours + RegHours * RegRate) / (RegHours + OTHours)
    End

From SampleData

The above query is fairly unreadable and difficult to maintain. It is because the effect of OT Type field is propagated to all the columns. What if a new OT Type is added, or any OT Multiplier logic is changed? We will then need to modify a lot of places. A much better code can be achieved if we use several CTEs or derived tables, but even more readable and maintainable code can be produced by using the cross apply operator. Here’s an example:

Select *
from SampleData
cross apply
(
  select OTMultiplier =
    Case
      when OTCode='OT1' then 1.2
      when OTCode='OT2' then 1.4
      when OTCode='OT3' then 1.5
    end
) as ca_otmultiplier
cross apply
(
  select OTRate = RegRate * OTMultiplier
) as ca_otrate
cross apply
(
  select OTCost = OTHours * OTRate
    , RegCost = RegHours * RegRate
) as ca_otcost
cross apply
(
  select TotalHours = OTHours + RegHours
    , TotalCost = RegCost + OTCost
) as ca_totals
cross apply
(
  select EffectiveRate = TotalCost / TotalHours
) as ca_effective

The great advantage is that the calculated result of every cross apply operator can be used into the next one that follows it. Another advantage as pointed by Brad is that such readability and maintainability comes without any extra cost. So the cross apply approach is a great choice if we have many calculated columns, each of which depends upon the result of previously calculated ones.

LINQ: How to get the latest/last record with a group by clause

Sometimes, we need to select a record that has the maximum or minimum value from a group. Some examples are:

  • We have a PersonOrders table and we want to find the last Order for each Person
  • We have a UserOperations table and we need to find the first Operation for each User
  • We have a Persons table and we need to find the person with maximum salary for each Department

Such scenarios are beautifully handled by TSQL’s Ranking_functions over partition by clauses. Here’s a typical example that uses Row_Number function to assign a rank to each row per Person.

;With PersonOrderWithRank
as
(
	Select *, Rnk = ROW_NUMBER() over (partition by PersonID order by OrderDate desc)
	from PersonOrders
)

Select *
from PersonOrderWithRank
where Rnk=1

In LINQ, similar result can be achieved by using the let keyword. Here’s an example:

from p in PersonOrders
//where conditions to be included here
group p by p.PersonID into grp
let MaxOrderDatePerPerson = grp.Max ( g=>g.OrderDate )

from p in grp
where p.OrderDate == MaxOrderDatePerPerson
select p

The above LINQ approach can be used for both LINQ To SQL as well as Entity Framework. Although the SQL and the LINQ approach I described above are not exactly identical (since I used Row_Number() and not Rank() or Dense_Rank() ) but the purpose of this post is to provide an starting point to write similar queries using LINQ.

SSIS: How to handle updates

Handling updates in SQL Server Integration Services isn’t straight forward. If you have developed an ETL (Extract Transform Load) process for a Data Warehouse, then you may have faced a situation where you needed to update existing records. This could be the case when you are implementing Type 1 SCDs. The situation isn’t particular for a Data Warehouse ETL; in routine SSIS tasks, there can be situations when you want to insert a record if it does not exist, or else update the existing record. The problem is that SQL Server Integration Services does not provide direct support for updating records based on some criteria. This post will have a brief look over various solutions:

Ole DB Command

The most basic solution could be to use an Ole DB Command transformation for every input row. For this, our dataflow may look something like.

The most annoying thing in this approach is that Ole DB Command does not support named parameters and we need to write our update queries using “?” like this:

Update Dim_myDimTable
 Set Property1 = ?,
 Property2 = ?
 ....
 Where BusinessKey = ?

This could get tedious to maintain if we have lot of columns in our table since we need to map the ? parameters in the exact order they appeared in the update query.

Also, this method uses a single update query for every matched record and so is not a good choice if the records to be updated are higher in number. Also, when using this approach, we need to make sure that we only update the rows that are changed (the sample dataflow above does not depict this).

Slowly Changing Dimension transformation

This is a nice built-in method that starts a wizard and finally generates a dataflow similar to the one we saw in the first approach if we choose options in the wizard that represent Type 1 SCD with changing attributes. So this option could be regarded as a more maintainable way of approach 1 since it generates all the related data flow components automatically. However, since this approach also updates the records one by one so the performance may degrade if we have a lot of rows to update. To use this option, we need to drag a Slowly Changing Dimension transformation to our data flow and follow the wizard accordingly.

Staging Update Table

This approach gives greater scalablity, maintainablity and flexibility. This option requires us to create a staging table in the database and fill that table with the rows that that already exist in the target table. After that, we can simply write an update query with a join condition to update all the records in one-go. Here’s the dataflow sketch for this option:

After the above dataflow, we will need to create an SQL Script control flow component to write our query and clears the staging update table. This can be done using an sql command like this:

--update dimension rows
 Update D
  Set
   Property1=U.Property1,
   Property2=U.Property2
   ...
  From Dim_myDimTable D
  Inner Join Temp_myDimUpdateTable U
   on D.BusinessKey = U.BusinessKey

  --empty update table
  Truncate table Temp_myDimUpdateTable

Of course, this solution is an overhead if the records to be updated are small in number since it requires an staging table and is a two-pass solution.

Merge Statement

SQL Server 2008 came up with a new Merge command that can automatically insert or update records in the destination table in a single statement. This option is more described in this MSDN BOL article: Using MERGE in Integration Services Packages

That’s all I learned when I wrote an ETL for a data warehouse. I hope that post helps others that are going to write SSIS packages as well. Please have your thoughts propagated using the comments section below.

Control.Invoke and BeginInvoke using lamba and anonymous delegates

Working constantly with LINQ and .NET 3.5 these days, I sometimes forget how to do things without lamda expressions. Today, I was working on a WinForms application and I needed to update the UI from a separate thread, so I wrote the following code:

    this.BeginInvoke( ()=>
        {
            //code to update UI
        });

This gave me the following error:

Cannot convert lambda expression to type 'System.Delegate' because it is not a delegate type

I, then tried the anonymous delegate syntax:

    this.BeginInvoke( delegate ()
        {
            //code to update UI
        });

Still incorrect, as it gave me this error:

Cannot convert anonymous method to type 'System.Delegate' because it is not a delegate type

A quick google revealed that Control.Invoke and Control.BeginInvoke take a System.Delegate as parameter that is not a delegate type and we need to cast our lambas or anonymous delegates to MethodInvoker or Action like this:

    this.BeginInvoke( (Action) (()=>
        {
            this.txtLongestWord.Text = this.longestWord;
        }));

The above code does not look good due to lot of brackets, so let’s use the anonymous delegate syntax:

    this.BeginInvoke( (Action) delegate ()
        {
            //code to update UI
        });

//or

    this.BeginInvoke( (MethodInvoker) delegate ()
        {
            //code to update UI
        });

T-SQL: Using cursor with Common Table Expressions

Cursors aren’t a bad choice for certain scenarios. Few days back, I was writing a stored procedure that was to be scheduled as a SQL agent job. I needed to send emails for each row of a result set and hence concluded that a Fast Forward cursor (Read Only, Forward only cursor) would be a nice choice. I created several CTEs (common table expressions) to reach my final result set and declared a cursor for my final select statement like this:


--declare some CTEs
;With CTE1 as
(
   --CTE1 definitiion
)
,CTE2 as
(
   --CTE2 definitiion
)

--declare a cursor for final select statement
Declare myCursor Cursor Fast_Forward For
   --select query
   Select ... From CTE2

But I was greeted with the following error:

Incorrect syntax near the keyword 'Declare'.

Ok, so my expected syntax is incorrect. After a quick google, I found that the declare cursor statement needs to be on the top of CTE declarations. So, here’s the correct syntax to define cursor with CTEs:


--declare a cursor above the CTE definitions
Declare myCursor Cursor Fast_Forward For

--declare CTEs
With CTE1 as
(
   --CTE1 definitiion
)
,CTE2 as
(
   --CTE2 definitiion
)

--select query as normal
Select ... From CTE2

--now open and use the cursor and don't forget to close and deallocate it in the end

Cool!! That was a nice learning.

Posted in T-SQL. Tags: , , . 2 Comments »

Winning TSQL Challenge 12

It’s a source of great pleasure for me that I am included in the winners for TSQL challenge 12.

Here’s my post describing my solution and here’s the analysis by Jacob Sebastian, the founder and president of TSQL Challenges.

Silverlight: Update service reference for a WCF service generating empty class

My Visual Studio sometimes goes angry. I had a Silverlight 2 – WCF – LINQToSQL application that I recently converted to Silverlight 3. I noticed that sometimes the Update Service Reference does not function properly and instead blanks out the generated reference.cs. It broke once again today and I decided to blog it. Here are the contents of Error window when such abnormal activity happens:

Custom tool error: Failed to generate code for the service reference '..'.  Please check other error and warning messages for details.

The warning tab may have many warnings, some of which are:

Custom tool warning: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Custom tool warning: The type 'System.Collections.ObjectModel.ObservableCollection`1' could not be found.  Ensure that the assembly containing the type is referenced.  If the assembly is part of the current development project, ensure that the project has been built.

Custom tool warning: Cannot import wsdl:portType
Detail: An exception was thrown while running a WSDL import extension: System.ServiceModel.Description.DataContractSerializerMessageContractImporter
Error: Exception has been thrown by the target of an invocation.
XPath to Error Source: //wsdl:definitions[@targetNamespace='']/wsdl:portType[@name=..]

Custom tool warning: Cannot import wsdl:port
Detail: There was an error importing a wsdl:binding that the wsdl:port is dependent on.
XPath to wsdl:binding: //wsdl:definitions[@targetNamespace='..']/wsdl:binding[@name='..']

Custom tool warning: No endpoints compatible with Silverlight 3 were found. The generated client class will not be usable unless endpoint information is provided via the constructor.

Ok. Looks like something is wrong with the Observable Collection reference. This was further confirmed when I brought up the service configuration window (by right clicking the WCF service and selecting Configure Service Reference), and noticed that the ObservableCollection collection type has been replaced by { Custom } :

service-reference-incorrect

For comparison, here’s the normal screenshot for this window:

service-reference-correct

To get rid of this and regenerate service proxy properly, I had to remove the Reuse types in all referenced assemblies (or at least System assembly).

So, if such behavior ever happen to you, try to update the service reference after unchecking the reuse types checkbox. Once done, you can recheck the option and update your service reference again.

T-SQL: Using result of a dynamic SQL query in a variable or table

Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:

sp_ExecuteSql stored procedure

This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:


declare @today datetime
exec sp_executesql
    N'Select @internalVariable = GetDate()', --dynamic query
    N'@internalVariable DateTime output', --query parameters
    @internalVariable = @today output --parameter mapping
select @today

Table variables and Temporary tables

This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:


declare @myTable table
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into @myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from @myTable

Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into #myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from #myTable
drop table #myTable

Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:


create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

--dynamic query
exec sp_executesql
    N'insert into #myTable
        select name, database_id, create_date from sys.databases'

select * from #myTable
drop table #myTable

Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.


declare @myTable table
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into @myTable
    exec sp_helpserver

select * from @myTable

Also, here’s an example to get result of a stored procedure using temporary table:


create table #myTable
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into #myTable
    exec sp_helpserver

select * from #myTable
drop table #myTable

Thats all from me. Let me know if you have any more solutions.