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)


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
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:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<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
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    /// <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:


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
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

super simple event bus C#

Hi,

today I thought about how to decouple parts of my application. With decoupling I at first thought about a manager which dynamically loads modules and then uses their output to feed a following module... not quite flexible if you more or less "pipe" one part after another and then just chain their results (like in a LINUX-bash command call e.g.: "ps -aux | grep vi" where a manager calls first ps and then grep).

A better approach is to let a global instance store the data when it appears and then push it dependent of the use case to the module which needs it (application #1 lists its errors -> manager pushes it to the log manager -> write to log). This brought me to a big question: Do I need a coordinator if the software parts can talk between each other?

As usual the answer is "it depends". A manager or coordinator is e.g.: OK if you have a main system like notepad++ and you want to load add-on functionality to use another output formatter. Let the modules talk between each other if e.g.: you separate the business logic in independent parts (divide and conquer), but on the same level (not as master-slave).

For my application the communication of the software parts seems to look good. Now I asked myself how to communicate inside of an application (and still as a condition: decoupled). Middleware is over sized for a single application. Message queues or messaging in general seems over sized too. Eventbus was over sized in the first place too, but here google came in the game. I google-d different kinds of event buses and found awesome solutions. Open source and commercial. Big libraries and light-weight solutions. Then I found a very inspiring post of trystan ( http://trystans.blogspot.co.at/2012/01/simplest-eventbus.html / http://trystans.blogspot.co.at/2012/01/simplest-typesafe-eventbus.html ).

I decided to use an own event bus. The following code shows the basic idea (an event bus based on the version of trystan's blog). I decided to build the event bus system more like the .Net's event system using EventHandler and EventArgs and call an event by name.

My code follows below... it is still a basic solution, but it worked for me.

kind regards,
Daniel

BusEventArgs.cs
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EventBus
{
    public class BusEventArgs : EventArgs
    {
        public BusEventArgs(object value = null)
        {
            Value = value;
        }

        public object Value { get; private set; }

        public static BusEventArgs Empty = new BusEventArgs();
    }
}

EventBus.cs
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EventBus
{
    public static class EventBus
    {
        private static Dictionary<string, List<EventHandler<BusEventArgs>>> handlers
            = new Dictionary<string, List<EventHandler<BusEventArgs>>>();

        public static void Publish(string eventName, BusEventArgs e)
        {
            Publish(null, eventName, e);
        }

        public static void Publish(object sender, string eventName, BusEventArgs e)
        {

            foreach (EventHandler<BusEventArgs> handler in handlers[eventName])
            {
                handler(sender, e);
            }
        }

        public static void Subscribe(string eventName, EventHandler<BusEventArgs> handler)
        {
            if (!handlers.ContainsKey(eventName))
            {
                handlers.Add(eventName, new List<EventHandler<BusEventArgs>>());
            }
            handlers[eventName].Add(handler);
        }
    }

}

Tester.cs
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace EventBus
{
    class Program
    {
        public static object locker = new object();

        public static bool IsStoped;

        static void Main(string[] args)
        {
            Thread raiser = new Thread(() => Raise());
            Thread listener = new Thread(() => Listen());

            raiser.Start();
            listener.Start();

            Console.ReadLine();
        }

        private static object Listen()
        {
            IsStoped = false;

            EventBus.Subscribe("onRaise", (sender, e) => WriteRaised());

            while (!IsStoped)
            {
                Thread.Sleep(100);
            }
            Console.WriteLine("finished");

            return null;
        }

        private static object WriteRaised()
        {
            Console.WriteLine("raised");
            IsStoped = true;
            return null;
        }

        private static object Raise()
        {
            Thread.Sleep(5000);
            EventBus.Publish(new object(), "onRaise", new BusEventArgs());

            return null;
        }
    }
}