IT / Administration / Development / other stuff

Thursday, May 29, 2014

disconnect all users from db

Hi,

it is often necessary to disconnect all open connections from a database (e.g.: if you want to delete it or take it offline). Management Studio has a checkbox to kill all connections in most of the cases where it makes sense. This is not so helpful if we want to script our stuff, so the following code suggests a way to kill all connections. (I found 90% of this piece of code on the web as a code snippet, but I forgot where... sorry for not mentioning the original source)

Declare @dbname sysname

Set @dbname = 'db'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

the only open task to execute this script is to enter the name of the db you want to disconnect the user from and enter it instead of the value db in the @dbname variable.

Kind regards,
Daniel

Wednesday, May 28, 2014

find colors for a style sheet

Hi,

today I needed some colors to build a website (or in detail to build a css style sheet).

The following pages supported me much while choosing matching colors:

- https://kuler.adobe.com
- http://colorschemedesigner.com/

Kind regards,
Daniel

get inserted id of identity column

Hi,

a situation I am always confronted with is: insert into a table, get this damn auto generated value to work with it. I don't know any database independent solution (except of using the max id inserted, what can be ok in some cases, but can fail too).

In sql server I found a solution for this problem at technet: http://technet.microsoft.com/de-de/library/ms190315.aspx ... this page describes a stored function which returns exactly the value we need (some kind of magic happens here).

The (filtered) example of technet:

CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL);

INSERT TZ
   VALUES ('Lisa'),('Mike'),('Carla');

-- inserts 1,2,3

INSERT TZ VALUES ('Rosalie');

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

-- returns 4

Kind regards,
Daniel

Column Panel

Hi,

today I wanted to arrange my elements in a grid in another way than stackpanel does. So I started to search for panel-implementations and found a simple implementation for a radial panel. Unfortunately a radial panel was not the solution for my problem so I had to build it myself. To tell the truth: it is even easier than I thought.

Panel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;

namespace ColumnPanelProject
{
    public class ColumnPanel : Panel
    {
        #region ColumnsCount

        public int ColumnsCount
        {
            get { return (int)GetValue(ColumnsCountProperty); }
            set { SetValue(ColumnsCountProperty, value); }
        }

        // Using a DependencyProperty as the backing store for ColumnsCount.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty ColumnsCountProperty =
            DependencyProperty.Register("ColumnsCount", typeof(int), typeof(ColumnPanel), new PropertyMetadata(0));

        #endregion

        private readonly Size InfinitySize = new Size(double.PositiveInfinity, double.PositiveInfinity);
        
        protected override Size MeasureOverride(Size availableSize)
        {
            
            foreach (UIElement child in Children)
            {
                child.Measure(InfinitySize);
            }

            return base.MeasureOverride(availableSize);
        }

        protected override Size ArrangeOverride(Size finalSize)
        {
            if(this.Children.Count > 0)
            {            
                int columns = this.ColumnsCount != 0 ? 
                                this.ColumnsCount : 
                                (int)(finalSize.Width / Children[0].DesiredSize.Width) ;

                int x = 0;
                int y = 0;

                foreach (UIElement child in Children)
                {
                    child.Arrange(new Rect( x * child.DesiredSize.Width,
                                            y * child.DesiredSize.Height, 
                                            child.DesiredSize.Width, 
                                            child.DesiredSize.Height));

                    x = (x + 1) % columns;
                    y = y + (x == 0 ? 1 : 0);
                }
            }

            return finalSize;
        }
    }
}

Window

<Window x:Class="ColumnPanelProject.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:local="clr-namespace:ColumnPanelProject"
        xmlns:System="clr-namespace:System;assembly=mscorlib"
        Title="MainWindow" Height="456.579" Width="481.579"
        >
    <Window.Resources>
        <System:Int32 x:Key="columnCount">0</System:Int32>
        <ItemsPanelTemplate x:Key="columnTemplate">
            <local:ColumnPanel ColumnsCount="{DynamicResource columnCount}" />
        </ItemsPanelTemplate>

        <DataTemplate x:Key="dataTemplate">
            <Grid Margin="3" Width="100" Height="100" HorizontalAlignment="Center" VerticalAlignment="Center">
                <Border BorderThickness="5" BorderBrush="LightGray" CornerRadius="10">
                    <TextBlock TextWrapping="Wrap" Text="{Binding}" HorizontalAlignment="Center" VerticalAlignment="Center"></TextBlock>
                </Border>
            </Grid>
        </DataTemplate>
    </Window.Resources>
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="*"/>
            <ColumnDefinition Width="100"/>
        </Grid.ColumnDefinitions>
        <Grid.RowDefinitions>
            <RowDefinition Height="*"/>
            <RowDefinition Height="30"/>
        </Grid.RowDefinitions>

        <ItemsControl
            Grid.Column="0"
            Grid.Row="0"
            Grid.ColumnSpan="2"
            ItemsSource="{x:Static Member=local:MainWindow.MyItems}"
            Name="itemsControl" 
            ItemTemplate="{DynamicResource dataTemplate}" 
            ItemsPanel="{DynamicResource columnTemplate}" Margin="0,2,0,-2" 
            />
        <Label Grid.Row="1" Grid.Column="1" Content="{DynamicResource columnCount}" HorizontalContentAlignment="Center" />
    </Grid>
</Window>

Code-Behind

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace ColumnPanelProject
{
    /// <summary>
    /// Interaktionslogik für MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private static ObservableCollection<string> myItems = null;

        public static ObservableCollection<string> MyItems
        {
            get
            {
                if(myItems == null)
                {
                    myItems = new ObservableCollection<string>();
                    (new[]{
                        "Dr. Leonard Leakey Hofstadter",
                        "Dr. Dr. Sheldon Lee Cooper",
                        "Penny",
                        "Howard Joel Wolowitz",
                        "Dr. Rajesh „Raj“ Ramayan Koothrappali",
                        "Dr. Bernadette Maryann Rostenkowski-Wolowitz",
                        "Dr. Amy Farrah Fowler",
                        "Stuart Bloom"}).ToList().ForEach(x => myItems.Add(x));
                }

                return myItems;
            }
        }

        public MainWindow()
        {
            InitializeComponent();
        }
    }
}

This article should show how a grid arrangement can be set up using only MeasureOverride / ArrangeOverride.

referring to msdn MeasureOverride should implement:

Iterate your element's particular collection of children that are part of layout, call Measure on each child element.
Immediately get DesiredSize on the child (this is set as a property after Measure is called).
Compute the net desired size of the parent based upon the measurement of the child elements.

The return value of MeasureOverride should be the element's own desired size, which then becomes the measure input for the parent element of the current element. This same process continues through the layout system until the root element of the page is reached.

ArrangeOverride is obviously responsible for the arrangement of the items.

A good related article can be found here: http://drwpf.com/blog/2009/08/05/itemscontrol-n-is-for-natural-user-interface/

Kind regards,
Daniel

Tuesday, May 13, 2014

catch uncaught exceptions c# wpf

Hi,

today I heard about a solution to catch exceptions globally in C# with WPF:

    /// <summary>
    /// Interaction logic for App.xaml
    /// </summary>
    public partial class App : Application
    {
        public App()
        {
            DispatcherUnhandledException += App_DispatcherUnhandledException;
        }

        void App_DispatcherUnhandledException(object sender, DispatcherUnhandledExceptionEventArgs e)
        {
            MessageBox.Show("error");
            e.Handled = true;
            this.Shutdown(1);
        }
    }

This snippet handles ALL exceptions which are not caught, shows the message "error" and quits with error.

Kind regards,
Daniel

Friday, May 9, 2014

how to check the size of my database...

Hi,

I had the problem that my databases grow and I had no idea why... (in the end I found out that it was a badly developed function, but this article will focus on the way to find the item which is growing...)

My entry point was an error on my sql server. (The agent sends me errors with severity > 16 by mail, so the real entry point was the mail. I double checked the error after the mail using xp_readerrorlog). The error indicated that it was not possible to store any data to the disk, because it was full (what is bad).

To get the information which folder is the bad guy in this scenario I used a freeware tool called tree-size. It did exactly what I needed. The tool shows which folder takes which amount of data in percent. ( download page e.g.: http://www.chip.de/downloads/TreeSize-Free_13001595.html ) I was lucky because the data folder of my database showed up as the folder that took 90% of the storage. Even more luckily I found the mdf file (database file) of a specific database which showed up as the problem.

Next step was to make some space and restart my database (I'm not sure if this is an optional step or not, but I would always do that) and to stop my corresponding application. I connected to the database and wanted to know how space usage looked like. So I called (after some minutes of google-ing)

sp_spaceused

the result was the information about the current database... and with the first parameter I can check my table (select * from sys.tables shows a list of tables, so I can use: sp_spaceused 'mytablename'). Now I needed a trick to evaluate all tables (and to evaluate them in only one record set to filter them)... and here it is:

CREATE TABLE #tmp
(
ID INT IDENTITY(1,1)
,name NVARCHAR(75)
,rows INT
,reserved NVARCHAR(50)
,data NVARCHAR(50)
,index_size NVARCHAR(50)
,unsed NVARCHAR(50)
)
INSERT INTO #tmp
   EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''
SELECT * FROM #tmp
DROP TABLE #tmp

the result of this statement is list of tables with their corresponding information. One of these entries took 90% of the whole space. This was the entry to concentrate on...

how sp_spaceused can help me:

sp_spaceused shows a list of tables and indexed views. Used without parameters the output shows 2 result sets:
- general database level: name, size, unallocated space (<-- see shrink databases)
- general data info: reserved, data, index_size, unused

used with parameter (object name):
- table level: name, rows, reserved, data, index_size, unused

second parameter indicates whether the data should be recalculated or not (@updateusage).

kind regards,
daniel

Tuesday, May 6, 2014

source code beautifier hilite.me

Hi,

today, I found (or unfortunately it is already yesterday) a cool web-page that renders inserted source code to html code as highlighted content. It is a very simple solution to paste code e.g.: to blogger ... (I already used it in my last post.) ... for me it worked perfectly, because it supports the code-languages I use and multiple themes like e.g.: Visual Studio theme.

page-description:

hilite.me converts your code snippets into pretty-printed HTML format, easily embeddable into blog posts, emails and websites.

Just copy the source code to the left pane, select the language and the color scheme, and click "Highlight!". The HTML from the right pane can now be pasted to your blog or email, no external CSS or Javascript files are required.

hilite.me

kind regards,
Daniel