Saturday, August 22, 2015

RadWindow cause RadComboBox to open popup unexpectedly and workaround

RadWindow and RadComboBox that I am talking about here  are both from Telerik(www.telerik.com) Silverlight controls, we use it quite a lot in our project.

Recently , I notice an issue  when using RadComboBox inside RadWindow , which somehow cause the RadComboBox to open it's dropdown  unexpectedly. Obviously it is quite annoying.

Originally I was suspect it is caused by our program related to the way how we use RadWindow and RadComboBox, but it turns out it is not ,because I created an isolated project and I can reproduce the same issue.  Just create a UserControl, and then drop a few RadComboBox in it.  Set the IsEditable=True, OpenDropDownOnFocus = true.


Then use code to open a RadWindow, Let's call it window1, and set the UserControl we created above as the content.  Inside the UserControl , provide a button , when click the button , open another RadWindow, let's call it window 2,  Then  leave window 2 open as it is, and click one of the RadComboBox on the  window 1, you will find out that all the combobox has it's dropdown open.

it will looks like the following snapshot.


I attach the sample project here.download

I also submit this issue to Telerik with the sample project , they confirm it is an issue, but no good solution yet.

But I work out a workaround that will collapse the dropdown when it is open without focus. 

I use a behaviour to achieve this, cause behaviour is nice , it can be easily add to the control without messing up the view's code behind. We are quite strictly using MVVM. So  there is very minimal code in the view's code behind.

using System.Windows.Interactivity;
 using Telerik.Windows.Controls;
 using System.Windows;
 /// <summary>
 /// Behavior to close unexpected dropdown behavior
 /// </summary>
 public class RadComboBoxCloseUnexpectedDropdownBehavior : Behavior<RadComboBox>
 {
  /// <summary>
  /// Default Constructor.
  /// </summary>
  public RadComboBoxCloseUnexpectedDropdownBehavior()
  {
   
  }
 
  /// <summary>
  /// When the behavior get attached into Visual Tree.
  /// </summary>
  protected override void OnAttached()
  {
   base.OnAttached();
   if (null == this.AssociatedObject) return;
   this.AssociatedObject.DropDownOpened += new SystemEventHandler(AssociatedObject_DropDownOpened);
 
  }
  void AssociatedObject_DropDownOpened(object sender, SystemEventArgs e)
  {
   if(!this.AssociatedObject.IsEnabled || !this.AssociatedObject.IsFocused)
    this.AssociatedObject.IsDropDownOpen = false;
  }
 
 
  /// <summary>
  /// Check whether keyboard focus is in this control.
  /// </summary>
  /// <param name="element"></param>
  /// <returns></returns>
  bool IsKeyboardFocusWithin(UIElement element)
  {
   UIElement uiElement = FocusManagerHelper.GetFocusedElement((DependencyObject)element) as UIElement;
   if (uiElement != null)
    return ParentOfTypeExtensions.IsAncestorOf((DependencyObject)element, (DependencyObject)uiElement);
   return false;
  }
  /// <summary>
  /// When behavior get removed
  /// </summary>
  protected override void OnDetaching()
  {
   this.AssociatedObject.DropDownOpened -= AssociatedObject_DropDownOpened;
   base.OnDetaching();
  }
 }

In order to use this behavior conveniently in Style, I also create attached property

/// <summary>
    /// Extensions on RadComboBox.
    /// </summary>
    public static class RadComboBoxExtensions
    {
        #region CloseDropdown
        /// <summary>
        /// Gets the <see cref="CloseUnexpectedDropdownProperty"/>
        /// </summary>
        public static readonly DependencyProperty CloseUnexpectedDropdownProperty = DependencyProperty.RegisterAttached(
            "CloseUnexpectedDropdown",
            typeof(bool),
            typeof(RadComboBox), //Note MUST be DepOb to support use in styles
            new PropertyMetadata(OnCloseUnexpectedDropdownChanged));
        
        /// <summary>
        /// Sets the DropOnFocus value
        /// </summary>
        /// <param name="element">Element</param>
        /// <param name="value">Value</param>
        public static void SetCloseUnexpectedDropdown(RadComboBox element, bool value) { element.SetValue(CloseUnexpectedDropdownProperty, value); }
        /// <summary>
        /// Gets the DropOnFocus value
        /// </summary>
        /// <param name="element">Element</param>
        /// <returns>Bool</returns>
        public static bool GetCloseUnexpectedDropdown(RadComboBox element) { return (bool)element.GetValue(CloseUnexpectedDropdownProperty); }
        static void OnCloseUnexpectedDropdownChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var behaviours = Interaction.GetBehaviors(d);
 
            var existing = behaviours.OfType<RadComboBoxCloseUnexpectedDropdownBehavior>().ToList();
 
            if ((e.NewValue is bool) && (bool)e.NewValue)
            {
                if (existing.Count <= 0) behaviours.Add(new RadComboBoxCloseUnexpectedDropdownBehavior());
            }
            else
            {
                foreach (var item in existing) { behaviours.Remove(item); }
            }
 
        }
        #endregion
    }

 I didn't dig into telerik's RadWindow source code to see what actually cause this, but I suspect the window is trying to to set the focus appropriately when it get activated,but I hope Telerik can fix this issue appropriately in their future release.

Thursday, August 13, 2015

Import data from SQL2012 to SQL2008

SQL Server is the database that we use in many of our products. And as you know there are also many versions of SQL Server out there as well.  At the moment our core product is still using SQL Server 2008 R2 unfortunately.  

Quite a while ago we need to import data from a legacy application into core product, which is using SQL Server 2008 R2.  however the legacy database is using some very old technology.  During that time the guy who worked on the data migration project decided to migrate the database to SQL 2012 assume that we will have upgrade our product to SQL2012 by the time when he finish, also there are some nice features in SQL2012 made his life a little bit easier.

So , here comes the problem, as time goes by , the  person who worked on data migration is long gone and our core product fail to upgrade to SQL 2012, cause we never get the time. I was ask to import some data from a client to our production core system. Which is still using SQL 2008 R2. 

That's where the problem comes in.  Update database from lower version to higher version is nice and easy, SQL Server have very good support. Not too much need to be done.  However if I need to downgrade a database from Higher version to lower version, the tool doesn't support by default. I can't restore a database from SQL2012 to SQL2008.

The Data Compare feature in my Visual Studio 2010 unfortunately doesn’t support database version higher than SQL 2008 R2 either.

I can also use SQL server's data import/export to do this as well, but since we use Timestamp field in our database, and use that to do concurrency check, which make it is very tedious to change the each single one of table setting.

Thus I did a bit search. And decide to create a script to achieve this goal. Here is what I did, in case someone else need to do similar task. And also for future myself , just in case I need to do similar things later on.

The following few conditions are unique in my situation, which is also the foundation that this solution is based on. 
  1. The database in SQL 2012 and SQL 2008 have exactly the same schema, table structure etc.
  2. We don't use any fancy features of SQL Server, no triggers, no  SSB , no Queue etc. just basic table.
  3. Our primary key is unique comprise with two part, one part is using the database auto-generated , Identity or Guid, the other part is maintained by our application based on setting. Thus we won't violate any primary key constrain.

First of all, I need to create a linked server my SQL 2008 R2  , so as I can access the database in 2012 format. You will need to have access to both SQL2008 and SQL2012 databases.



Click "New Linked Server…" option.  Assume I have the SQL Server 2012 and SQL Server 2008 R2 installed on the same machine.
And the instance name of SQL 2012 is "SQL2012"
Also go to security tab to specify logons that you need to use to access SQL2012
After these , I can access my SQL2012 database  easily.

A few things I need to do in my script.
  1. Need to traverse every table in the database. To check whether I have data there need to move across to SQL2008
  2. If the table contains Identity column ,then I need to SET IDENTITY_INSERT ON , before I insert data.
  3. If the table has foreign key constrains , I need to turn off the constrian check , because the order to insert data is undetermined
  4. After insert data, need to SET IDENTITY_INSERT OFF .
  5. We are using Timestamp column in our database which is readonly , so I need to specifically exclude Timestamp column from the script.

Here is the script that I used to generate the data import script.

USE [Your database name]

GO
IF OBJECT_ID('SQLStatement') IS NOT NULLBEGIN
   DROP TABLE
SQLStatementENDGOCREATE TABLE SQLStatement([SQL] NVARCHAR(MAX),Id INT NOT NULL IDENTITY)GOSET NOCOUNT ON

DECLARE
@TenantId INT
DECLARE
@FromTable NVARCHAR(255)DECLARE @ColumnList NVARCHAR(MAX)DECLARE @SourceTable NVARCHAR(255)DECLARE @SourceTablePreFix NVARCHAR(255)DECLARE @SQLPrepareSource NVARCHAR(MAX)--SET @SourceTablePreFix = '[eVisionRelease].'SET @SourceTablePreFix = '.\sql2012.[Your database name].'SET @TenantId = 3SET @SQLPrepareSource = 'insert #rectables select ''?'',count(*) rec_count from ? 'CREATE TABLE #rectables (tbl_name VARCHAR(100), rec_count INT)EXEC sp_MSforeachtable @SQLPrepareSourceDECLARE cTable CURSOR FOR
  SELECT
tbl_name FROM #rectables WHERE rec_count > 0
OPEN cTable
FETCH NEXT FROM cTable INTO @FromTableWHILE @@FETCH_STATUS = 0BEGIN

   DECLARE
@cols AS NVARCHAR(MAX),  @query  AS NVARCHAR(MAX)
  
SET @SourceTable =  @SourceTablePreFix + @FromTable
  
--get all columns, except timestamp
  
SELECT name AS ColumnName,column_id AS ColumnId INTO #ColumnSource FROM sys.columns WHERE OBJECT_ID=OBJECT_ID(@FromTable) AND system_type_id != 189
  
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
                      
FROM #ColumnSource
                      
GROUP BY ColumnName, ColumnId
                      
ORDER BY ColumnId
              
FOR XML PATH(''), TYPE
              
).value('.', 'NVARCHAR(MAX)')
           ,
1,1,'')
  
DECLARE @HasIdentityColumn INT
   SET
@HasIdentityColumn =  (SELECT COUNT(*) FROM sys.identity_columns WHERE [object_id] = OBJECT_ID(@FromTable))
  
  
INSERT INTO SQLStatement ([SQL]) SELECT 'ALTER TABLE ' + @FromTable + ' NOCHECK CONSTRAINT ALL'
  
IF @HasIdentityColumn > 0
      
INSERT INTO SQLStatement ([SQL]) SELECT 'SET IDENTITY_INSERT ' + @FromTable + ' ON'
  
INSERT INTO SQLStatement ([SQL]) SELECT 'DELETE FROM ' +@FromTable +  ' WHERE TenantId=@TenantId'
  
INSERT INTO SQLStatement ([SQL]) SELECT 'INSERT INTO ' + @FromTable + '(' + @cols + ') SELECT ' + @cols +' FROM ' + @SourceTable + ' WHERE TenantId=@TenantId'
  
IF @HasIdentityColumn > 0 INSERT INTO SQLStatement ([SQL]) SELECT 'SET IDENTITY_INSERT ' + @FromTable + ' OFF'
  
DROP TABLE #ColumnSource

  
FETCH NEXT FROM cTable INTO @FromTable
  
END
CLOSE
cTableDEALLOCATE cTable DROP TABLE #rectablesSELECT [SQL] FROM SQLStatement ORDER BY Id



------------------------------------------------------------
After execute this script, it will generate the final script that we will need to execute to import data. Run the script in SQL2008 database. The one we need to import data into.

 Finger cross!

Final step, as we turn off the constrain check on foreign key, after we done with the import we need to turn it back on.

USE [Your databasename]
EXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'


That's it...

Be aware this solution can be only executed on an isolated and offline environment, if you trying to do this on your live database, DBA will certainly kill you….