Fast Row Count for All Tables

November 12, 2013 Leave a comment

 

Using Count(*) to obtain a row count can be very slow on large tables. This is the only truly reliable way to obtain an accurate row count but, post SQL Server 2000, the mess that was sys.indexes, etc. was largely cleaned up and nowadays the row count can be obtained using the following code. Microsoft still states that the row count in sys.partitions is an “Approximate number of rows in this partition”, but it appears to be stable and accurate with appropriate caveats with regards to uncommitted transactions.

 

Select      s.Name
           ,t.Name
           ,Sum(p.rows) As Rows
From        sys.tables t
Inner Join  sys.schemas s On (s.schema_id = t.schema_id)
Inner Join  sys.indexes i On (i.object_id = t.object_id and i.index_id < 2)
Inner Join  sys.partitions p On (p.object_id = t.object_id)
Where       p.index_id In (0, 1)
Group By    s.Name
           ,t.Name
Order By    s.name
           ,t.name
Categories: SQL Server, T-SQL Tags: , , ,

Mark Items as Default in Reference Data … and Ensure There is Only One

November 6, 2013 Leave a comment

 

Often it is convenient to be able to default values in where they are not specified, or to not require items to be specified to make life easier.
For maintainability it is best to mark default values in the data itself, rather than in some code somewhere, and where better than in the source reference table.
By adding a bit column ‘IsDefault’ it is simple to mark a particular record as the default, however it is also easy to mark several items as default by mistake.
But with careful use of a filtered unique index it is possible to apply a constraint such that there can be only one default record as below.

 

Create Unique NonClustered Index IX_UNIQUE_IS_DEFAULT On MyReferenceTable
(
	IsDefault Asc
)
Where (IsDefault=(1))

 

By filtering, we don’t index the ‘false’ values so that more than one record can be marked as “not the default”, and by making it unique we ensure that we only have a single record marked as default.

Generate Strong Password in T-SQL

October 30, 2013 Leave a comment

 

Recently I needed to create a stored procedure to automate the creation of user logins within SQL Server and required a way to generate strong passwords. The following is the resultant code. It is fairly simplistic, but flexible and convenient.
It uses a recursive common table expression to create a number list and then uses the Crypt_Gen_Random function to generate a random number in the binary variable which is then used to dip into the character string to pull out the characters to build up the password. Due to the use of “For XML Path” characters which would be HTML encoded have been excluded.

 

Declare     @length int = 10
Declare     @chars char(92)
           ,@bytes binary(128)
           ,@password varchar(128)

Set         @chars = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!"£$%^*()_+-={[}]:;@~#,.?/'
Set         @bytes = Crypt_Gen_Random(128)
  
;With cte_numbers (number) As
(
    Select      Cast(1 As int)
    Union All
    Select      number + 1
    From        cte_numbers
    Where       number < @length
)
Select      @password = 
            (
                Select      SubString(@chars, (SubString(@bytes, n.Number, 1) % Len(@chars)) + 1, 1)
                From        cte_numbers n
                For         XML Path ('')
            )
            
-- display the resultant password
Select      @password
Categories: SQL Server, T-SQL

Listing duplicates from the same table without repetition

December 8, 2011 Leave a comment

 

I recently came across what was apparently a simple problem, but which had me initially stumped. The problem was, how to list out duplicates within the same table without repetition. For example if rows A and B in a table are deemed to have matched, then selecting from the table joined to itself will give two results, A matches B and B matches A. Both are valid results, but it effectively doubles the number of duplicates reported. I only needed one of the two results.
The solution turned out to be simple, of course, but since I spent some time thinking it through I thought it worth creating a quick post.

Walking through a simple example, create the following sample table.

CREATE TABLE dbo.DuplicateSample(
    id int IDENTITY(1,1) NOT NULL,
    name varchar(50) NOT NULL
)

 

Throw a load of sample data into it.

Insert dbo.DuplicateSample (name) Values('Bob')
Insert dbo.DuplicateSample (name) Values('Paul')
Insert dbo.DuplicateSample (name) Values('James')
Insert dbo.DuplicateSample (name) Values('Claire')
Insert dbo.DuplicateSample (name) Values('Alex')
Insert dbo.DuplicateSample (name) Values('Bob')
Insert dbo.DuplicateSample (name) Values('Rebecca')
Insert dbo.DuplicateSample (name) Values('Paul')
Insert dbo.DuplicateSample (name) Values('Andrew')
Insert dbo.DuplicateSample (name) Values('Giles')
Insert dbo.DuplicateSample (name) Values('Terry')
Insert dbo.DuplicateSample (name) Values('Robert')
Insert dbo.DuplicateSample (name) Values('Ralph')
Insert dbo.DuplicateSample (name) Values('Alex')
Insert dbo.DuplicateSample (name) Values('Heather')
Insert dbo.DuplicateSample (name) Values('Alice')
Insert dbo.DuplicateSample (name) Values('Heather')
Insert dbo.DuplicateSample (name) Values('Oliver')
Insert dbo.DuplicateSample (name) Values('Jack')
Insert dbo.DuplicateSample (name) Values('Harry')
Insert dbo.DuplicateSample (name) Values('Alfie')
Insert dbo.DuplicateSample (name) Values('Charlie')
Insert dbo.DuplicateSample (name) Values('Thomas')
Insert dbo.DuplicateSample (name) Values('William')
Insert dbo.DuplicateSample (name) Values('Joshua')
Insert dbo.DuplicateSample (name) Values('George')
Insert dbo.DuplicateSample (name) Values('James')

 

Now our initial attempt at selecting duplicates might be something like the following.

Select          ds1.id As id1
               ,ds1.name As name1
               ,ds2.id As id2
               ,ds2.name As name2
From            DuplicateSample ds1
Inner Join      DuplicateSample ds2 On (ds1.name = ds2.name And ds1.id != ds2.id)

 

This will give us the following results.

id1 name1 id2 name2
6 Bob 1 Bob
8 Paul 2 Paul
27 James 3 James
14 Alex 5 Alex
1 Bob 6 Bob
2 Paul 8 Paul
5 Alex 14 Alex
17 Heather 15 Heather
15 Heather 17 Heather
3 James 27 James

 

Note that all the rows are shown twice, but switched round, i.e. Bob 1 matches Bob 6, but also Bob 6 matches Bob 1.

So, how do we solve this? By adding in an order to the rows and only matching ahead.

;With cte_DuplicateSample As (
    Select          Row_Number() Over (Order By id) As row_number
                   ,id
                   ,name
    From            DuplicateSample
)
Select          ds1.id As id1
               ,ds1.name As name1
               ,ds2.id As id2
               ,ds2.name As name2
From            cte_DuplicateSample ds1
Inner Join      cte_DuplicateSample ds2 On (ds1.name = ds2.name And ds1.row_number < ds2.row_number)

 

This will give us the following, much better, results.

id1 name1 id2 name2
1 Bob 6 Bob
2 Paul 8 Paul
5 Alex 14 Alex
15 Heather 17 Heather
3 James 27 James

 

The CTE selects out the same source data, but adds in a row_number, then in the joining clause we only match for rows that are ahead of the current row, i.e. when the row_number is greater that the current row_number.

Simple in the end.

Categories: SQL Server Tags: , ,

SSIS Package Logging

December 5, 2011 1 comment

 

Thank you to Adam Aspin, my much respected friend, who introduced me to, and inspires me in, all things SQL.

SSIS packages, especially once loaded onto a production server are very opaque. There is very little information provided about the progress of the package and no stats are available regarding which tasks are taking the most time, therefore some form of logging is essential.

In this post I’ll outline a very simple and easy to implement approach to logging that can be applied to the SSIS package as well as stored procedures that are called within it.

In order to maintain separation of log info from any particular application it would be preferable to create a separate logging database, however it is not essential. In this post I’ll create a ‘LOG’ schema and create the tables and stored procedures within this schema.

In brief we will:

  1. Create a reference table and data related to logging.
  2. Create a table to store the log entries.
  3. Create stored procedures to facilitate the logging.
  4. Create events within an SSIS package to add logging information.
  5. Annotate a sample stored procedure to include logging.
  6. Review the logging output.

 

Create a reference table and data related to logging

The only reference table we need is to define the type of each log entry. Primarily we need start and stop entries for each type of item we are going to track. Additionally I have added an extra two types, one for information and one for errors.

By using negative numbers mirroring the positive numbers it makes it much easier to see elements start and stop when reviewing a log.

CREATE TABLE LOG.LogEntryType (
    log_entry_type_id smallint NOT NULL,
    name varchar(50) NOT NULL,
    CONSTRAINT PK_LogStepType PRIMARY KEY CLUSTERED (log_entry_type_id ASC)
)
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (1, 'SSIS Package Start')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (-1, 'SSIS Package Finish')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (2, 'SSIS Task Start')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (-2, 'SSIS Task Finish')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (3, 'Stored Procedure Start')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (-3, 'Stored Procedure Finish')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (4, 'Stored Procedure Step Start')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (-4, 'Stored Procedure Step Finish')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (99, 'Information')
INSERT LOG.LogEntryType (log_entry_type_id, name) VALUES (100, 'Error')

 

Create a table to store the log entries

CREATE TABLE LOG.LogEntry(
    log_entry_id bigint IDENTITY(1,1) NOT NULL,
    log_entry_type_id smallint NOT NULL,
    date_created datetime NOT NULL,
    entity_id varchar(50) NULL,
    entity_name varchar(2000) NOT NULL,
    parent_entity_id varchar(50) NULL,
    parent_entity_name varchar(2000) NULL,
    value bigint NULL,
    description varchar(2000) NULL,
    CONSTRAINT PK_LogEntry PRIMARY KEY NONCLUSTERED (log_entry_id ASC)
)

ALTER TABLE LOG.LogEntry WITH CHECK 
ADD CONSTRAINT FK_LogEntry_LogEntryType FOREIGN KEY(log_entry_type_id)
REFERENCES LOG.LogEntryType (log_entry_type_id)

ALTER TABLE LOG.LogEntry CHECK CONSTRAINT FK_LogEntry_LogEntryType

 

Create stored procedures to facilitate the logging.

The code below creates the stored procedure required to add a simple log entry. The parameters will become clear as we work on adding logging to an SSIS package.

CREATE Procedure LOG.AddLogEntry
/******************************************************************************
Description:
    Adds an entry to the log.
History:
    05-Oct-2011 S.Wilber
        Created
******************************************************************************/
    @log_entry_type_id smallint
   ,@entity_id varchar(50)
   ,@entity_name varchar(2000)
   ,@parent_entity_id varchar(50) = Null
   ,@parent_entity_name varchar(2000) = Null
   ,@error_code int = Null
   ,@error_description varchar(2000) = Null
   ,@value bigint = Null
   ,@value_description varchar(2000) = Null
As

Insert          LOG.LogEntry 
                (
                    log_entry_type_id
                   ,date_created
                   ,entity_id
                   ,entity_name
                   ,parent_entity_id
                   ,parent_entity_name
                   ,value
                   ,description
                )
Values          (
                    @log_entry_type_id
                   ,GetDate()
                   ,@entity_id
                   ,@entity_name
                   ,@parent_entity_id
                   ,@parent_entity_name
                   ,Case When @error_code Is Not Null Then Convert(bigint, @error_code) Else @value End
                   ,Case When @error_code Is Not Null Then @error_description Else @value_description End
                )

 

The following code creates the stored procedure that we are going to use to add logging to our own stored procedures. This code is a little more complex as I want to minimise the logging code that I will need to add to any stored procedure. Rather than adding a log entry to start and stop each step within a store procedure I just want to log the start; the code below will automatically add the stop for the previous step as well as closing off the last step when finishing the stored procedure.

CREATE Procedure LOG.AddProcedureLogEntry
/******************************************************************************
Description:
    Adds an entry to the log. This is a wrapper to AddLogEntry but tailored
    for stored procedures.
    Depending on the combination of parameters it closes the previous step
    and starts the current step
History:
    05-Oct-2011 S.Wilber
        Created
******************************************************************************/
    @proc_id int
   ,@step_number smallint = Null Output
   ,@step_description varchar(2000) = Null
   ,@close_only bit = 0
   ,@parent_entity_id varchar(50) = Null
   ,@parent_entity_name varchar(2000) = Null
As


Declare         @log_entry_type_id smallint
               ,@entity_id varchar(50) = Convert(varchar(10), @proc_id) + '-' + Convert(varchar(10), @@spid)
               ,@entity_name varchar(2000) = Object_Name(@proc_id)
               ,@error_code int = Null
               ,@error_description varchar(2000) = Null
               ,@value bigint
               ,@value_description varchar(2000)
               ,@log_entry_type_proc_start smallint = 3
               ,@log_entry_type_proc_end smallint = -3
               ,@log_entry_type_proc_step_start smallint = 4
               ,@log_entry_type_proc_step_end smallint = -4


-- if this is for a step - close off the previous step and then make a new one
If @step_number Is Not Null
  Begin
    If @step_number != 0
      Begin
        Set             @log_entry_type_id = @log_entry_type_proc_step_end
        Set             @value = Convert(bigint, @step_number)
        
        -- get the last description
        Select Top 1    @value_description = description
        From            Log.LogEntry
        Where           log_entry_type_id = @log_entry_type_proc_step_start
        And             entity_id = @entity_id
        And             value = @value
        Order By        log_entry_id desc
        
        Exec LOG.AddLogEntry
           @log_entry_type_id
          ,@entity_id
          ,@entity_name
          ,@parent_entity_id
          ,@parent_entity_name
          ,@error_code
          ,@error_description
          ,@value
          ,@value_description
      End
        
    If @close_only != 1
      Begin
        -- increment the step number
        Set             @step_number = @step_number + 1
        Set             @log_entry_type_id = @log_entry_type_proc_step_start
        Set             @value = Convert(bigint, @step_number)
        Set             @value_description = IsNull(@step_description, 'Step: ' + Convert(varchar(10), @step_number))

        Exec LOG.AddLogEntry
           @log_entry_type_id
          ,@entity_id
          ,@entity_name
          ,@parent_entity_id
          ,@parent_entity_name
          ,@error_code
          ,@error_description
          ,@value
          ,@value_description
        
      End  
  End
Else
  Begin

    -- check that the last step has been closed
    Select Top 1    @value_description = description
                   ,@step_number = Convert(smallint, value)
                   ,@log_entry_type_id = log_entry_type_id
    From            Log.LogEntry
    Where           entity_id = @entity_id
    Order By        log_entry_id desc
    
    If @log_entry_type_id = @log_entry_type_proc_step_start
      Begin
        Set             @log_entry_type_id = @log_entry_type_proc_step_end
        Set             @value = Convert(bigint, @step_number)
        
        Exec LOG.AddLogEntry
           @log_entry_type_id
          ,@entity_id
          ,@entity_name
          ,@parent_entity_id
          ,@parent_entity_name
          ,@error_code
          ,@error_description
          ,@value
          ,@value_description
      End

    -- now just write the procedure start/end              
    Set @log_entry_type_id = Case When @close_only = 0 Then @log_entry_type_proc_start Else @log_entry_type_proc_end End
    Set @value = Null
    Set @value_description = Null
    
    Exec LOG.AddLogEntry
       @log_entry_type_id
      ,@entity_id
      ,@entity_name
      ,@parent_entity_id
      ,@parent_entity_name
      ,@error_code
      ,@error_description
      ,@value
      ,@value_description

  End    

 

Create events within an SSIS package to add logging information.

Logging within SSIS is performed by carrying out actions triggered by ‘events’.

The events we are interested in here are:

  • OnPreExecute – triggered just before the package or a task within the package starts to execute.
  • OnPostExecute – triggered just after the package or a task within the package has executed.
  • OnError – trigged when an error occurs.

Now very conveniently, if the events are created at the package scope, i.e. click on the background of your package before clicking on the ‘Event Handlers’ tab, then the events fire both for the package starting and stopping, but also for each and every task starting and stopping. Therefore if we called our stored procedure ‘LOG.AddLogEntry’ from within the events, then the whole package would be logged.

However, before you dive in, we will need to be able to differentiate between events triggered off for tasks and for the package, and we’ll do this by using a few little variables evaluated from expressions.

The first step is to create three variables, in the scope of the event, that will contain some of the values that we will send to the ‘LOG.AddLogEntry’ stored procedure. The details of the variables are given below, but note that in the properties for the variable, set ‘EvaluateAsExpression’ to ‘True’.

Name Type Expression
LogEntryID String @[System::SourceID]
LogEntryName String @[System::SourceID] == @[System::PackageID] ? @[System::PackageName] : @[System::SourceName]
LogEntryTypeID Int16 (DT_I2)(@[System::SourceID] == @[System::PackageID] ? 1 : 2)

The screenshot below shows the ‘OnPreExecute’ event, which is the same as on the ‘OnPostExecute’ event. You can see the three variables required on the left hand side (note their scope) and also the Execute SQL Task that will call the stored procedure to add the log entry.

Screenshot - OnPreExecute

 

For the execution of the stored procedure to add the log entry I’ve used an ADO.NET connection due to the clarity of the parameter mapping.

The Execute SQL Task merely calls the stored procedure ‘LOG.AddLogEntry’ as shown in the screenshot below.

Screenshot - Execute SQL Task

 

The parameters are mapped as shown in the screenshot below.

Screenshot - Execute SQL Task Parameters

 

The PostExecute event is very similar to the PreExecute event and I won’t go into further details here.

I also like to catch the OnError event, and its implementation is very similar. The ‘LogEntryTypeID’ variable is still require, but it does not need to be an expression and its value can just be set to 100.

Screenshot - OnError

 

The parameters require are slightly different and are shown in the following screenshot.

Screenshot - OnError Parameters

 

Annotate a sample stored procedure to include logging.

Adding logging to long and complex stored procedures can be a really valuable aid in clearly identifying long running sections or for pinning down errors or unexpected flow.

Since we have already created the stored procedure for logging from stored procedures above, here I’ll quickly go through the changes that need to be made to add logging.

The principle behind the code for logging from a stored procedure is to minimise the impact to the logged stored procedure in terms of additional code. Thus, each time the LOG.AddProcedureLogEntry is called potentially two entries are added to the log, one to close of the previous entry, i.e. to mark the previous step as finished and a second to mark the current step as starting.

Three additional parameters can be added to your stored procedure. These will become self-explanatory below.

@log bit = 1
@log_parent_entity_id varchar(50) = null
@log_parent_entity_name varchar(2000) = null

 

Additional to the parameters you will need one variable to record the current step number.

Declare @log_proc_step smallint = 0

 

Finally I add three types of log calls within the stored procedures. Note, you can dispense with the ‘if’ and the additional parameter if you always want logging to be switched on. Most of the parameters are fairly clear, but @close_only is used to instruct the procedure, in the case of a step, to only close off the previous step and not to create a new step entry, and also to differentiate between the stored procedure header/footer logging.

 

Stored procedure start – marking the start of the execution of the stored procedure

If @log = 1 Exec LOG.AddProcedureLogEntry @proc_id = @@ProcID, @step_number = Null, @close_only = 0, @parent_entity_id = @log_parent_entity_id, @parent_entity_name = @log_parent_entity_name

 

Note that @step_number is null, so this is for either the header/footer and @close_only = 0, meaning that this is for the header.

 

Step – marking the start of each step

If @log = 1 Exec LOG.AddProcedureLogEntry @proc_id = @@ProcID, @step_number = @log_proc_step Output, @step_description = 'Fetch Process Run IDs' ,@close_only = 0, @parent_entity_id = @log_parent_entity_id, @parent_entity_name = @log_parent_entity_name

Note the @step_description which allows you to add a note for the step. This makes reviewing the log so much more simple and I would strongly recommend going to the effort to write this note.

 

Stored Procedure finish – marking the end of the execution of the stored procedure

If @log = 1 Exec LOG.AddProcedureLogEntry @proc_id = @@ProcID, @step_number = Null, @close_only = 1, @parent_entity_id = @log_parent_entity_id, @parent_entity_name = @log_parent_entity_name

Note that @step_number is null, so this is for either the header/footer and @close_only = 1, meaning that this is for the footer.

 

Review the logging output.

So, now all the logging is in place, what does the output look like. The screenshot below shows a section from a current project copied to Excel. The data is very simplistic, but you can write additional stored procedures to report out the logging information in a more informative way, including highlighting the % time that each step has used overall, using historical runs to identify the progress in terms of % of a current run, finding the top 10 slowest steps, etc.

Just for clarification I’ve coloured logging that has come from a stored procedures in pale purple.

Screenshot - Sample Log Results in Excel

Pluralization Services

 

Sometimes it is useful to allow a user to define their own entities or rename existing entities within an application. For example, in a contact management system, one could envisage the user wanting to tailor the software to change, say, ‘customer’ to ‘client’, or ‘lead’ to ‘prospect’, as well as defining new entities, such as contact methods, e.g. ‘network meeting’, ‘mailshot’, etc.

One issue when giving end users freedom like this is how to pluralise the words, e.g. “You have 3 new lead”. One way is to just add an ‘s’ in brackets, such as “you have 3 new lead(s)”, but this falls down with some words, such as ‘entity’, ‘person’, ‘child’, etc. where the plurals don’t follow the simple “add an ‘s’” rule.

Luckily, but for English only, .Net includes a pluralisation service which was required for the development of Entity Framework, and we can access it.

The screenshot below shows a very simple demonstration application (which can be downloaded here).

 

 

The code extract below is the key to using the service, and it is very simple.

private void buttonPluralize_Click(object sender, EventArgs e)
{
  if (!String.IsNullOrWhiteSpace(textBoxSingular.Text)) {
    textBoxPlural.Text = PluralizationService.CreateService(CultureInfo.GetCultureInfo("en-us")).Pluralize(textBoxSingular.Text);
  } else {
    textBoxPlural.Text = String.Empty;
  }
}

 

Unfortunately, the only cultures supported at the minute are English based. If you attempt to use anything else you will be met with the lovely message “We don’t support locales other than english yet”.

To utilise the PluralizationService you need to reference the following two DLLs.

System.Data.Entity.dll
System.Data.Entity.Design.dll

On my machine, these can both be found in the following folder:
C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\

The second DLL, ‘System.Data.Entity.Design.dll’, is not in the .Net 4 Client framework, but rather the full framework.

You will also require the following ‘using’ statements.

using System.Data.Entity.Design.PluralizationServices;
using System.Globalization;

 

So, play around and have fun. It would be great to hear what people are doing with the service, so please let me know if you have time.

Downloads

Pluralization.zip

XamlParseException – ‘The invocation of the constructor on type…’ – A common bug

June 3, 2011 7 comments

There is an exception which many people hit when first creating dependency properties.

System.Windows.Markup.XamlParseException: The invocation of the constructor on type ‘[type name here]‘ that matches the specified binding constraints threw an exception.

Don’t worry – it’s very easy to fix and also very commonly made.

You first dependency property may look something like this, especially if you use the code snippet “propdp“.

        public long MyProperty
        {
            get { return (long)GetValue(MyPropertyProperty); }
            set { SetValue(MyPropertyProperty, value); }
        }

        public static readonly DependencyProperty MyPropertyProperty =
            DependencyProperty.Register(
                "MyProperty",
                typeof(long),
                typeof(MyClass),
                new PropertyMetadata(0));

If you created your code using the code snippet it will compile just fine and the world will be a happy place. The problem lies in the constructor of the PropertyMetadata on line 12 above where you set the default value for the property.

The definition of the constructor is:

public PropertyMetadata(object defaultValue);

The type of the parameter ‘defaultValue‘ is ‘object‘, this means that your default value will be boxed as an object type.

The error comes when the default value is needed and is unboxed. If you didn’t carefully force the type of the value going in then the most sensible type will be chosen by the compiler, which may differ from the type you specified on line 10, i.e. the underlying type you wish to use for your dependency property.

Using the above as an example, the default value is set to ‘0‘ – the compiler will interpret ‘0‘ to be an Int32 and then the constructor will store that Int32 typed value as the default value in the defaultValue object.

When you come to need the default value the code that is executed behind the scenes will attempt to turn your default value back to the type specified for your dependency property, i.e.

long value = (long)defaultValue;

Much to many people’s surprise, this is not possible and will throw the exception above. Why? Well, you need to unbox back to the underlying type before you cast it to another type.

Take the following code, which you can paste into a console project to check out:

var myInt = 0;
Console.WriteLine(myInt.GetType().Name); // produces Int32

object myObj = myInt;
Console.WriteLine(myObj.GetType().Name); // produces Int32 - the underlying type

// no problem
long myLong1 = (long)myInt;

// no problem - unboxing to int and then casting to long
long myLong2 = (long)(int)myObj;

// exception - can't unbox to a different type
long myLong3 = (long)myObj;

Note that on line 1 I declared the variable using var instead of int, however we can still see that the compiler has decided that Int32 is the best way to go with this value.

So, back to the exception, and how to correct it. Simple really, make sure that the compiler is aware of the type of value that you are passing in and make sure that it matches the type you are declaring for your dependency property.

In the example above we only need to replace line 12 with the following using an explicit cast.

                new PropertyMetadata((long)0));

Alternatively we could have used a type suffixes as a shortcut.

 new PropertyMetadata(0l));

Other type suffices are:
u – uint
l – long
ul – ulong
f – float
d – double
m – decimal

If you are interested in a more depth article on boxing and unboxing and casting in general, see Eric Lippert’s excellent blog post.

Follow

Get every new post delivered to your Inbox.