Making Xceed excel export use foreign key lookups

Why does print functionality use foreign key values and excel export doesn't? Because Xceed hates you.

There is a workaround by rolling your own excel export class that extends Xceed's ExcelExporter base class though.

Here is it: <download>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Xceed.Wpf.DataGrid;
using Xceed.Wpf.DataGrid.Export;

namespace sfc.Controls.Custom
{
    public class CustomExcelExporter : ExcelExporter
    {
        private DataGridControl _dgc;

        public CustomExcelExporter(DataGridControl dgc)
            : base(dgc)
        {
            _dgc = dgc;
        }

        protected override void StartDataItemField(ExportContext exportContext, object fieldValue)
        {
            // Attempt to find the export column in the master section of the DataGrid
            var column = (Column)_dgc.Columns[exportContext.Column.FieldName];

            // If the column cannot be found in the master section, search the columns of the detail configurations
            if (column == null)
            {
                foreach (DetailConfiguration detailConfiguration in _dgc.DetailConfigurations)
                {
                    if ((column = (Column)detailConfiguration.Columns[exportContext.Column.FieldName]) != null)
                    {
                        break;
                    }
                }
            }

            if (column != null)
            {
                ForeignKeyConfiguration fkc = column.ForeignKeyConfiguration;

                // Check if the column being exported has a ForeignKeyConfiguration and export the mapped value if so
                if (fkc != null && fkc.ForeignKeyConverter != null)
                {
                    fieldValue = fkc.ForeignKeyConverter.GetValueFromKey(fieldValue, fkc);
                }
            }

            base.StartDataItemField(exportContext, fieldValue);
        }
    }
}

One gotacha I found when writing this is that if the foreign key lookup returns a null its going to corrupt your excel document.

Execute all SQL Scripts in a Folder

How to quickly execute a collection of SQL Scripts against a SQL Server Database.

The simplest solution I have found is to use a quick batch script and leverage the osql command. The osql utility is a Microsoft® Win32® command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts.


To use the script you will need to:
1. download it from here, This script will execute every .sql in the folder it is executed in.
2. open it up in a text editor of your choice and substitute your connection information for the dummy "[Your XX]" placeholders.
3. Close the file and double click on it to execute.

RunScript.bat

FOR %%x IN (*.sql) DO OSQL -U [Your UserName] -P [Your Password] -S [My Server] -d [My Database] -i "%%x" >> Log.txt

%%x is a variable for the current file. (*.sql) is what selects the files in the folder. Everything after the Do is what is executed for every file.

Note: scripts will execute in alphabetical order. As a bonus the script logs the output to Log.txt

How to know when the sort is changed on the Xceed WPF grid

There is no easy to use sort event on the on the Xceed WPF grid itself however there is a not so obvious way that you can detect when the sort is changed by wiring up the following code.

DataGridCollectionView view = grid.ItemsSource as DataGridCollectionView;

      if (view != null)

      {

            ((INotifyCollectionChanged)view.SortDescriptions).CollectionChanged -=

                                                      new NotifyCollectionChangedEventHandler(SortCollectionChanged);

}

      private void SortCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)

      {

            //do stuff

      }

AppInfoHandler: ASP.NET application and environment diagnostic tool

This is a cool little tool that I wrote a few years ago. At the time I was working for a government department and our web applications where deployed to a multi-tier web farm with ISA acting as a load balancer. Direct traffic to an individual box was not allowed so it was quite difficult to determine which server you were hitting, Making it next to impossible to diagnose intermittent deployment problems.

Enter the AppInfoHandler. AppInfoHandler is a diagnostic tool that when hit spews out a tonne of information about the application such as.

General
    Machine Name
    Server IP
    IIS Version 
    IIS Priority
    IIS Up Time
    .Net Version
    OS Version
    Service Identity
    Trust Level
    Server Name
    App Domain Id
    Physical Application Path
    Virtual Application Path
    Application Temp Path
    Temp Path
    Authenticated
    Secure Connection (https)
    User Identity Name
    User Host Address
    Impersonation Level
    Server Time
    Server Culture
    Server UI Culture
    Server Cores
    Server Memory
Loaded Assemblies
Server Variables (these are dynamically loaded from the request)
    ALL_HTTP
    ALL_RAW
    APPL_MD_PATH 
    APPL_PHYSICAL_PATH
    AUTH_TYPE
    AUTH_USER
    AUTH_PASSWORD 
    LOGON_USER
    REMOTE_USER
    CERT_COOKIE 
    CERT_FLAGS 
    CERT_ISSUER 
    CERT_KEYSIZE 
    CERT_SECRETKEYSIZE 
    CERT_SERIALNUMBER 
    CERT_SERVER_ISSUER 
    CERT_SERVER_SUBJECT 
    CERT_SUBJECT 
    CONTENT_LENGTH
    CONTENT_TYPE 
    GATEWAY_INTERFACE 
    HTTPS 
    HTTPS_KEYSIZE 
    HTTPS_SECRETKEYSIZE 
    HTTPS_SERVER_ISSUER 
    HTTPS_SERVER_SUBJECT 
    INSTANCE_ID 
    INSTANCE_META_PATH 
    LOCAL_ADDR
    PATH_INFO
    PATH_TRANSLATED
    QUERY_STRING 
    REMOTE_ADDR
    REMOTE_HOST
    REMOTE_PORT 
    REQUEST_METHOD
    SCRIPT_NAME
    SERVER_NAME
    SERVER_PORT
    SERVER_PORT_SECURE
    SERVER_PROTOCOL
    SERVER_SOFTWARE 
    URL
    HTTP_CONNECTION
    HTTP_ACCEPT
    HTTP_ACCEPT_ENCODING
    HTTP_ACCEPT_LANGUAGE
    HTTP_COOKIE
    HTTP_HOST
    HTTP_REFERER
    HTTP_USER_AGENTenter PC 6.0)
Session Variables (these are dynamically loaded from the session)
Cache Variables (these are dynamically loaded from the cache)
Application Variables (these are dynamically loaded from the application cache)

This data could easily be extended to include web.config values or the status of windows services etc. One of the great things about this tool is that its a single self contained dll so it can deployed once into the GAC/master web.config once and will work on any application on the server.

The original idea for this was based on cache manager written by Steven Smith.

If anyone has any feedback or features they'd like to see added please let me know.

Download the binary here or the source code here.

How to make AppInfoHandler work in your application:

1. Add a reference to the sfc.AppInfoHandler.dll

2. In your application web.config add this to you system.web config section
    <httphandlers>
        <add path="AppInfo.axd" type="sfc.AppInfoHandler.AppInfo,sfc.AppInfoHandler" verb="*"/>
    </httphandlers>

3. Run your application and navigate to "http://[your application]/AppInfo.axd - Its that easy

How to turn off Skype Advertising Pop-Ups

Skype has an annoying habit of popping up little advertisements which clutter the user interface and have animations that I find distracting. Here is how to get rid of them.

Steps:

1. Open the Skype Options window by clicking Tools in the top menu Options.

2. Click on "Notifications" on the left hand menu

3. Click on "Alerts & Messages"

4. Under "Show messages about..." uncheck all options.

5. Restart Skype

The Microsoft Visual Studio Remote Debugging Monitor has been closed on the remote machine.

I was having an annoying problem with vs2008 where when I tried to debug I would sometimes get this error message:

"Error while trying to run project: Unable to start debugging.

The Microsoft Visual Studio Remote Debugging Monitor has been closed on the remote machine."

But I wasn't remote debugging! It turned out the reason this is happening is because visual studio is a 32 bit application but I'm running a 64 bit OS. When I hit F5 I'm launching a 64 bit application so for visual studio to debug it it has to silently start remote debug to bridge the gap.

The fix: The most reliable way to get around this I've found is to set your startup projects platform to "x86" in the project settings. This removes the need for visual studio to silently do remote debugging.

How to select a list of every table in a sql server database and row count in each table

How to select a list of every table in a sql server database and row count in each table. I've found this very handy over the years when doing data migrations and comparing databases.

Tested on SQL Server 2005 & 2008

CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY table_name --CAST(Replace(a.data_size, ' KB', '') as integer) desc
DROP TABLE #temp

Visual Studio Hanging

Do get lots of hangs in Visual Studio where the window fails to redraw when scrolling, some windows don’t paint – and then VS finally hangs. It appears this is because you are hitting the default 10,000 GDI object per process limit. This seems to happen to me quiet often when viewing xaml files (in vs2008).

You can see the GDI Object count in Process Explorer or if Task Manager - by clicking on the view -> select columns menu and ticking GDI Objects.

You can't fix this as far as I know but you can delay the inevitable by increasing the limit.

See this link for information on how to change the GDIProcessHandleQuota limit in the registry: http://msdn.microsoft.com/en-us/library/ms724291(VS.85).aspx

I set the limit to 15,000 on my windows 7 64 bit desktop with 4 gig of ram and haven't had any issues.

For more detailed information on GDI Objects see this page: http://blogs.technet.com/markrussinovich/archive/2010/03/31/3322423.aspx

Grant exec on all stored procs

Its so annoying that sql server doesn't come with a default role to just have exec permissions on all stored procs. Oh well.

I love this little stored proc. Its usually a good idea to prevent your database user from having direct read/write access to your database to tricks some nasty hacker might throw at you - Its usually good practice to give your user just access to the stored procedures. I usually run it at the end of every release to ensure the user can exec any stored proc.

use master
go
create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)


set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

if @pattern is not null
set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''

if @debug = 1 print @sql
else
exec @ret = master.dbo.xp_execresultset @sql,@db

If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end

Script to kill all connections to a database

When restoring/moving databases its a pretty common requirement to kill all the connections to the database in question otherwise you can't get a lock on it. Killing all the connections to a database can be a pain though if you have to use the UI. This script is much quicker and can be a time saver. If you’re super keen you could modify the script to make it a stored procedure so it’s always ready to go. If you do want to use the UI here is a blog post on how to do it.

--
-- Kill connections to a given sql server database
--

declare @execSql nvarchar(MAX), @databaseName varchar(100)
set @databaseName = '<your database name here>'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)