Short Fast Code: 2010

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)

A better way to declare your ValueConverters in XAML

Generally most people define a resource like this "<conv:AdditionConverter x:Key="AdditionConverter" />" and then use the converter like this "{Binding Converter="{StaticResource NegatingConverter}} "

This is OK but it’s fairly verbose and leads to converters being defined all over the place. An alternative is to use a MarkupExtension to minimize the amount of XAML code required. E.g.:

public class AdditionConverter : MarkupExtension, IValueConverter

{

private static AdditionConverter _converter;

public object Convert(object value, Type targetType,

object parameter, System.Globalization.CultureInfo culture)

{

// convert and return something

}

public object ConvertBack(object value, Type targetType,

object parameter, System.Globalization.CultureInfo culture)

{

// convert and return something (if needed)

}

public override object ProvideValue(IServiceProvider serviceProvider)

{

if (_converter == null)

_converter = new AdditionConverter();

return _converter;

}

Doing this you no longer need to define a resource as the XAML parser knows how to ask the markup extension to supply the converter. You end up with a syntax like this:

{Binding Converter={conv:MyConverter}}

This approach has an added advantage of ensuring that all your converters are singletons.

Taking it further: Making a wrapper base class

This article explains a generic markup extension class that hides the MarkupExtension implementation details and makes the process above even easier.

ConverterMarkupExtension.cs

using System;

using System.Windows.Data;

using System.Windows.Markup;

namespace sfc.Converters

{

[MarkupExtensionReturnType(typeof(IValueConverter))]

public abstract class ConverterMarkupExtension :

MarkupExtension where T : class, IValueConverter, new()

{

private static T _converter;

public override object ProvideValue(IServiceProvider serviceProvider)

{

if (_converter == null)

{

_converter = new T();

}

return _converter;

}

[MarkupExtensionReturnType(typeof(IMultiValueConverter))]

public abstract class MultiConverterMarkupExtension :

MarkupExtension where T : class, IMultiValueConverter, new()

{

private static T _converter;

public override object ProvideValue(IServiceProvider serviceProvider)

{

if (_converter == null)

{

_converter = new T();

}

return _converter;

}

There are 2 classes here, one for IValueConverter's and one for IMultiValueConverters.

You can then modify your classes to inherit from this class and they nolonger need to worry about implementing MarkupExtension E.g.:

public class AdditionConverter : ConverterMarkupExtension<AdditionConverter>

{

public object Convert(object value, Type targetType,

object parameter, System.Globalization.CultureInfo culture)

{

// convert and return something

}

public object ConvertBack(object value, Type targetType,

object parameter, System.Globalization.CultureInfo culture)

{

// convert and return something (if needed)

}

How to login to sql server using windows authentication over a VPN

This only applies to people that want to connect to a SQL Server instance over VPN to a domain that doesn't have a trust relationship with the source domain and don't want to have to use remote desktop. The method described below will allow you to access the SQL database seamlessly as if it were a database insist your domain.

1) Open a command prompt window

2) runas /user:"domain\user" /netonly "c:\program files\microsoft sql server\100\tools\binn\vsshell\common7\ide\ssms.exe" - replacing "domain\login" with your login details obviously

3) The command prompt with ask for "domain\login"'s password

4) SMSS will then run and you can connect to the database over VPN using the Windows Authentication option.

Generic EventArgs

This has been done before but I think this is a cool tip that can save a heap of time and reduce the number of classes in your app.

There are a lot of times when you want to raise a quick event and just simply pass an value type or object as part of the EventArgs parameter. Instead of having to create separate EventArgs for every event you want to raise, we can create an EventArgs class that uses generics.

public class EventArgs : EventArgs

{

public EventArgs(T value)

{

Value = value;

}

public T Value { get; set; }

}

Usage:

public event EventHandler<EventArgs<int>> SomeEventWithIntArg;

...

if (SomeEventWithIntArg != null)

{

SomeEventWithIntArg(this, new EventArgs<int>(123));

}

You can wire up the SomeIntEvent event and I will be able to access eventArgs.Value

SomeEventWithIntArg += OnSomeEventWithIntArg;

void OnSomeEventWithIntArg(object sender, EventArgs<int> eArgs)

{

int i = eArgs.Value;

}

WPF Xceed Grid: How to prevent/disable validation for objects that implement IDataErrorInfo

From the Xceed documentation:

"Built-in support for IDataErrorInfo provides business-object level validation that can be used in combination with validation rules.

Unless the ValidationRules collection is cleared, it will always contain an ExceptionValidationRule and DataErrorValidationRule. If the DataErrorValidationRule is excluded from the collection of validation rules, validation errors reported by IDataErrorInfo will be ignored. "

The solution is to manually loop over the columns and clear the validators

foreach(Column c in grid.Columns)

{

c.CellValidationRules.Clear();

}

How to redirect Console and Debug output to your UI

How many times have you been in your test environment trying to understand whats happening with no debugger to call on.

I find this especially frustrating in WPF where binding errors fail silently. Log files are a drag - wouldn't it be cool if you could pipe the console/debug output to somewhere in your UI?

It turns out this is pretty easy. Console output can be redirected to a stream

You can easily subclass TextWriter to pipe the stream output to a textbox for example

// Instantiate the writer

TextWriter _writer = new TextBoxStreamWriter(txtMessage);

// Redirect the out Console stream

Console.SetOut(_writer);

Console.WriteLine("Now redirecting console output to the text box");

public class TextBoxStreamWriter : TextWriter

{

TextBox _output = null;

public TextBoxStreamWriter(TextBox output)

{

_output = output;

}

public override void Write(char value)

{

base.Write(value);

_output.Dispatcher.BeginInvoke(new Action(() =>

{

_output.AppendText(value.ToString());

})

); // When character data is written, append it to the text box.

}

public override Encoding Encoding

{

get { return System.Text.Encoding.UTF8; }

}

Undoing what you've done.

StreamWriter standardOutput = new StreamWriter(Console.OpenStandardOutput());

standardOutput.AutoFlush = true;

Console.SetOut(standardOutput);

Redirecting debug output is even easier as it offers a lot of flexibility via tracelisteners out of the box

In this example I’m leverging the code from above to send debug output to the console and hence to my textbox

Debug.Listeners.Add(new TextWriterTraceListener(Console.Out));

Debug.WriteLine("Now redirecting debug output to the text box");

Unable to create the virtual directory. To access local IIS Web sites, you must install the following IIS components: IIS 6 Metabase and IIS 6 Configuration Compatibility ASP.NET

---------------------------
Microsoft Visual Studio
---------------------------
Unable to create the virtual directory. To access local IIS Web sites, you must install the following IIS components:

      IIS 6 Metabase and IIS 6 Configuration Compatibility
      ASP.NET

In addition, you must run Visual Studio in the context of an administrator account.

For more information, press F1.
---------------------------
OK
---------------------------

I just received this error when I tried to create a new asp.net web app with vs2010/windows 7. The solution is you need to go to "Control Panel > Programs and Features > Turn Windows features on or off" and install at least the two missing components. After this is done you need to open visual studios command prompt and type "aspnet_regiis -i".

How to create a shortcut file with .NET

Unfortunately .NET doesn't have direct support for this task but luckily you can leverage Interop and the Windows Scripting Host Object Model to do this with only a few lines of code. Windows Scripting Host is an automation technology for Microsoft Windows operating systems that provides scripting capabilities comparable to batch files, but with a greater range of supported features.

Steps to get this working:

1) Add a reference to "Windows Script Host Object Model" to your project - this will be found under the COM tab in the add reference dialog.

2) Add a using to the class where your shortcut create code will go. e.g.

using IWshRuntimeLibrary;

3) Use the following code to create a shortcut. I would recommend making a helper class for this. e.g.

WshShell shell = new WshShell();

IWshShortcut link = (IWshShortcut)shell.CreateShortcut("c:\My Notepad Shortcut.lnk");

link.TargetPath = "c:\windows\notepad.exe";

link.Save();

Why doesn't my keyboard navigation work on templated ComboBoxes/ItemControls

There is a little gotcha here when you overwrite the itemcontainerstyle on an items control. If you want to allow quick access items by typing prefixes of strings you need to setup some attached properties.

There is 2 ways you can go you can either add the TextSearch.TextPath attached property on the ItemControl or add the TextSearch.Text attached property on the individual item.

Example:

ComboBox>

ComboBox>

Example of how to do this when overwriting the itemcontainerstyle:

<ComboBox.ItemContainerStyle>

<Setter.Value>

MultiBinding>

Setter.Value>

Setter>

Style>

ComboBox.ItemContainerStyle>

MSDN Documentation http://msdn.microsoft.com/en-us/library/ms617617(v=VS.100).aspx