Wednesday, October 22, 2014

Regex in sql server

The like statement is sometimes a bit frustrating, because it has nothing to do with the regular expression you know from development in e.g. .NET. Similar to like is patindex.

The proprietary placeholders are:

  • % for 0-n characters
  • _ for 1 character
  • [] ranges
    • e.g. [a-f] for any single character between a and f inclusively
    • e.g. [abcdef] for any single character between a and f inclusively
    • e.g. [[] for [, - can be set as first item in list to use it, []], [_], [%]
  • [^] ranges (any character which is not in the range.

Really using Regex:
  • VBScript, COM
    http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx
  • Project for native regex
    http://www.codeproject.com/Articles/4733/xp-pcre-Regular-Expressions-in-T-SQL
  • CLR function
    http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
    http://msdn.microsoft.com/en-us/library/w2kae45k%28v=vs.80%29.aspx

kind regards,
Daniel


EDIT: in contrary to mysql ... there you can use it in the where statement "native". E.g.:
select * from xyTable where name regexp 'asdf$' 

Monday, October 20, 2014

fill gaps of an identity column

I found out that it is possible to fill gaps in an identity table without reseeding the table ( http://msdn.microsoft.com/en-us/library/ms176057.aspx ).

You can "set identity_insert" option to on to set the values directly ( http://msdn.microsoft.com/en-us/library/ms188059.aspx ).

But:

  • only 1 Table can have this setting on
  • If value inserted > the current identity value for the table -> automatically uses the new inserted value as the current identity value.
kind regards,
Daniel

Thursday, October 16, 2014

Tuesday, October 7, 2014

design data in wpf (best practise)

I used to have a DB connection in my wpf applications which will be set from outside so that the application has no idea what the connection string could be. The result is, that I start my application development without any design data.

What is nice here is that you can check whether you are in design mode or not and set the connection string. Therefore check:

http://msdn.microsoft.com/en-us/library/system.componentmodel.designerproperties.getisindesignmode.aspx

... but in fact, I really don't like that solution because it works only if you have wpf references in the project what a DB-access layer typically doesn't have. Pushing the connection string from the ui-layer to the business logic layer and then to the dbaccess-layer for design makes it a mess (spaghetti code). Moreover you need a database connection even at design time to work on the ui, what can be painful too.

I found my solution inside the following msdn blog post with some additional brain work:

http://blogs.msdn.com/b/wpfsldesigner/archive/2010/06/30/sample-data-in-the-wpf-and-silverlight-designer.aspx

I would recommend in a MVVM pattern UI to set the datacontext to the VM and set d:DesignData right afterwards to a child class of that class.

so e.g.: a grid's data context is bound to a class called something like MainWindowViewModel and d:DataContext (as described at the msdn article) is bound to something like MainWindowViewModelDesignData.

The ...DesignData should inherit from the base view model and initialize the properties with hard coded design data values (or maybe xml files which are always available on hard drive). I would recommend to set extreme values (min/max values) inside the view model or even better prepare more than one vm-designdata class (or what I would do: put in a switch which can be modified quickly), because it is much easier to change the desing data, compile, close and open the window than to compile, start, prepare test data, check, prepare other test data, check,... and so on. The design data is giving you a perfect overview how the ui would look like and is a perfect solution to test your ui about extreme values entered by the user (e.g.: a login name can have 7 to 50 characters: make cases with a) only 7 char logins, b) only 50 char logins and c) a mix with even 25 char logins).

This worked fine for me. Please let me know if you see it differently.

kr, daniel

EDIT:

today (about a month later) I used the mvvm light framework which is shipped with a dependency injection service, where view-models are registered. With mvvm light it is even easier to register design data in design mode.

Service-Locator code:


1
2
3
4
5
6
7
8
            if (ViewModelBase.IsInDesignModeStatic)
            {
                SimpleIoc.Default.Register<MainViewModel, MainDesignViewModel>();
            }
            else
            {
                SimpleIoc.Default.Register<MainViewModel>();
            }


kind regards, Daniel