(ZipPackage)myWordDocument

Fun fact: MSOffice 2013+ documents are compressed archives, a.k.a. zip files. They’re built and manipulated using the same System.IO.Packaging namespace that’s used by SQL Server Data Tools to make dacpacs.

Advertisements

Copy an Open Word/Excel File

I had a requirement to disallow saving a file over a certain size. Simple! Wait…how do I find out what size it is without saving it?

Make a copy of the file and check the size of the copy. Wait…I want to check the size of the in-memory edits. Curses! Foiled again.

Luckily, the Excel object model has the answer: Workbook.SaveAsCopy. This method does exactly what I want. So:

  1. Get a temporary file path.
  2. .SaveAsCopy.
  3. Check the size of the copy.
  4. Delete the copy.

And now we copy & paste for Word…which doesn’t have that method.

!

But a Word Document does implement the IPersistFile interface, which implements the Save method, which is the same thing. So cast your Document to IPersistFile and save away.

So now I’ve got all the pieces.

  1. Intercept the save.
  2. Copy the in-memory document.
  3. Check the size.
  4. Delete the copy.
  5. Warn the user that the document is too large.
  6. Don’t save.
  7. Happy dance.

Create a Database Snapshot

Sometimes it’s handy to be able to quickly revert to a previous version of a database. During testing, it’s handy to set up your test data, snapshot the database, and test over and over again without having to restage the data each time. Backups can be time-consuming in a large database. Snapshots to the rescue!

Prerequisites:

  1. Obviously, you’ll need to be using a version of SQL Server that supports snapshots.
  2. You’ll need the appropriate permissions on the database server. The fixed server “sysadmin” role will suffice, naturally.
  3. You’ll need the appropriate permissions on the directory(ies) where your database files are located. If not, you can always change the script to use a different directory.

Set your SSMS to output results to text (Ctrl + T) and execute the following:

SET NOCOUNT ON

DECLARE @currdate VARCHAR(20)
SET @currdate = CONVERT(VARCHAR(20), GETDATE(), 120)
SET @currdate = REPLACE(@currdate, ':', '')
SET @currdate = REPLACE(@currdate, '-', '')
SET @currdate = REPLACE(@currdate, ' ', '_')
SET @currdate = SUBSTRING(@currdate, 0, 14)

DECLARE @SnapshotName varchar(100)
DECLARE @CreateSQL VARCHAR(MAX) = ''
DECLARE @RestoreSQL VARCHAR(MAX) = ''
DECLARE @names table ([Name] varchar(100), [Physical_Name] varchar(200))
DECLARE @DatabaseName varchar(100) = 'DATABASE NAME GOES HERE'

------------------------

SET @SnapshotName = @DatabaseName + '-' + @currdate + '-1.ss'
DELETE @names

INSERT @names ([Name], [Physical_Name])
SELECT name, physical_name
FROM sys.master_files
WHERE type = 0
AND (database_id = DB_ID(@DatabaseName))

IF (SELECT COUNT(*) FROM @names) > 0
BEGIN
    SELECT @CreateSQL = @CreateSQL + 'CREATE DATABASE ' + QUOTENAME(@SnapshotName) + ' ON ' 

    SELECT @CreateSQL = @CreateSQL + CHAR(13) + CHAR(10) + 
            '(NAME = ' + QUOTENAME([Name]) 
            + ', FILENAME = ''' + [Physical_Name] + '-' + @currdate + '.snap''),' 
    FROM @names

    SELECT @CreateSQL = LEFT(@CreateSQL,len(@CreateSQL)-1) + CHAR(13) + CHAR(10)  + 'AS SNAPSHOT OF [' + @DatabaseName + ']
'
    SELECT @RestoreSQL = @RestoreSQL + 'RESTORE DATABASE [' + @DatabaseName + '] FROM DATABASE_SNAPSHOT = ''' + @SnapshotName + ''';
'
END  

PRINT '/*'

PRINT @CreateSQL 
PRINT '*/

/*'
PRINT @RestoreSQL 

PRINT '*/'

You’ll get an output that looks like the following (with names and locations specific to your server):

/*
CREATE DATABASE [{databasename}-1.ss] ON 
(NAME = [{filename01}], FILENAME = '{filepath01}-20170419_0802.snap'),
(NAME = [{filename02}], FILENAME = '{filepath02}-20170419_0802.snap'),
(NAME = [{filename03}], FILENAME = '{filepath03}-20170419_0802.snap')
AS SNAPSHOT OF [{databasename}]
*/

/*
RESTORE DATABASE [{databasename}] FROM DATABASE_SNAPSHOT = '{databasename}-20170419_0802-1.ss';
*/

Nothing will actually execute yet. I leave that up to you.

Be careful. Don’t play with snapshots unless you understand the possible ramifications, namely, you could inadvertently trash your databases. There’s a good article with lots more details on MSDN here: Create a Database Snapshot. Enjoy!

Unpivot

None of the “simple” examples for an SQL UNPIVOT were in any way simple, so here’s as simple as I can make it.

I’m using the following table definition for this discussion:

CREATE TABLE [dbo].[MyTable](
    [MyHeaderColumn] [varchar](100) NULL,
    [MyColumn1] [int] NULL,
    [MyColumn2] [int] NULL,
    [MyColumn3] [int] NULL
) 

It contains the following data:

output1

But I don’t want to output the data in that format. I want to output all three integer columns in a single column, thusly:

output2

I could use UNION statements, but that’s inefficient and decidedly uncool. UNPIVOT to the rescue!

I’m going to build it up one step at a time, rather than throw it all at you at once. I’m going to be very explicit and verbose. You can slim it down at your leisure.

First you need the source of the data. Simple enough.

    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T

Then you need to encapsulate it so it looks like a single object. In this context, the AS [{name}] is required, since all objects need a name.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]

Then you add the UNPIVOT shell, again with the required alias:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(

) AS TR

Then you add the column that will contain the consolidated output values. Note: you’re defining the column here; it doesn’t have to match anything in the source, but it can if you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn]
) AS TR

Then we define another output column that’s going to tell us which source column the output value came from. Again, you’re defining the column; it can be named whatever you like.

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (

                                            )
) AS TR

Then you enumerate the source columns that will be combined in the single output column:

(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

At this point, we’ve only defined a data source, so now we need actually define the projection. Note: any columns that you included in the UNPIVOT cannot be included in the output. Think of the UNPIVOT like an aggregate: you can’t output an unaggregated or ungrouped column.

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
(
    SELECT 
    T.[MyHeaderColumn], 
    T.[MyColumn1],
    T.[MyColumn2],
    T.[MyColumn3]
    FROM [dbo].[MyTable] T
) AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And here’s the result:

output3

Notice the contents of the [OutputColumnIdentifier] column. That’s the column the value was retrieved from! Neat.

If you’re not manipulating the original source, you can simplify by just specifying the table directly. This produces the identical output:

SELECT
TR.[MyHeaderColumn], 
TR.[OutputColumn], 
TR.[OutputColumnIdentifier] 
FROM 
[dbo].[MyTable] AS [Source]
UNPIVOT
(
    [OutputColumn] FOR [OutputColumnIdentifier] IN (
                                            [Source].[MyColumn1], 
                                            [Source].[MyColumn2], 
                                            [Source].[MyColumn3]
                                            )
) AS TR

And there you have it! Combining multiple columns into a single column using SQL UNPIVOT.

More information is available in TechNet: Using PIVOT and UNPIVOT, FROM (Transact-SQL).

.NET Assembly Binding

99.999% of the time the framework loads assemblies seamlessly and transparently.  The other 0.001% of the time is so painful that it more than makes up for the rest.  One of the more common troublemakers is the Newtonsoft.Json.dll.  There are still Microsoft assemblies (System.Net.Http.Formatting!  Yup, I called you out!) that reference years-old versions of the Newtonsoft assembly.  Any other time you can’t get msbuild to perform indirect reference chaining to save your life, but it does love to look several references back and grab an older version of Newtonsoft.  How does that work?  Why does it do that?

This topic has been covered ad nauseum on many other forums, so I’ll just present a list of references I’ve used.

How the Runtime Locates Assemblies on MSDN.

Redirecting Assembly Versions on MSDN.

Assembly Binding Log Viewer on MSDN.  Also known as the Fusion Log Viewer.  This is your best reference for learning exactly what happened when the framework tried to load your assembly.  The viewer is already installed on your machine if you have Visual Studio installed.  Open the developer command prompt with Admin permissions and type “fuslogvw”.  A lot of the older blogs talk about making changes directly to your registry.  Don’t do that.  Use the utility.

If everything is working properly, including a configured binding redirect, you’ll see something like the image below.

The framework wanted version 6.0, but found a redirect in the config file that told it to use 9.0 instead.

fusionlog

Recently I had an issue that turned out to be malformed XML in the config file.  This manifested as the fusion log showing me that the framework was apparently ignoring the configured redirect.  It can’t do that, so I dug into the XML and discovered the malformation.  The fusion log was invaluable for troubleshooting that issue.

Scott Hanselman has some good tips here.

Some good tips on stackoverflow here and here.

The MSBuild output is always a wealth of information if you dig through it.  In Visual Studio, type “verb” in your Quick Launch to bring up the Build and Run section in the Options dialog.  Change “MSBuild project build output verbosity” to Detailed.  Rebuild your solution and you’ll get plenty of information that will (hopefully) provide some clues as to the path being followed to get to the assembly.


Things to watch out for:

  • Assembly binding redirect configurations.
  • Use the log viewer to be sure the framework is using the config file you think it should be using.

Things to remember:

  • It’s not magic.  It’s not random.  The framework follows a very specific set of steps to determine which assembly to load.  Understanding that process will make you seem like a dark wizard to the muggles.

Handy C# Shortcuts

Rather than walking down an object model validating that each level is not null, like this:

if ((this.MyThing != null) && (this.MyThing.MySubThing != null))
{
    // do stuff
}

You can shortcut with a Null-conditional Operator:

if (this.MyThing?.MySubThing != null)
{
    // do stuff
}

Rather than explicitly checking and initializing a backing variable, like this:

public Thing MyThing
{
    get
    {
        if (_myThing == null)
        {
            _myThing = new Thing();
        }
        return _myThing;
    }
    set { _myThing = value; }
}

You can use a Null-coalescing Operator and make use of the fact that assigning a value to a variable also returns the value:

public Thing MyThing
{
    get { return _myThing ?? (_myThing = new Thing()); }
    set { _myThing = value; }
}

Rather than creating a read-only property without a setter, like this:

public Thing MyOtherThing
{
    get
    {
        return _myOtherThing;
    }
}

You can just use a Lambda Operator to return the backing variable:

public Thing MyOtherThing => _myOtherThing;