HV-Lima Peru

Offshore your .NET/SQL Development to Latin America! Languages: en-de-es

Visual Studio 2008 and .NET Framework 3.5 Training Kit

Have a look at the freshly released .NET Framework 3.5 Training

 

After unzipping the Training Kit it feels like a Training CD on your hard disk.

Great Presentations, Demos and Labs

Das .NET Language Integrated Query Framework

Mit Daten arbeiten – aber wie?
Webcast vom 02.11.2007 in deutscher Sprache
Super, super, super gemacht von Jens Häupel,
Developer Evangelist Microsoft Deutschland.
Das .NET Language Integrated Query Framework
Pre-Requisite ist aber auf jeden Fall:
Die Spracherweiterungen in C# 3.0

C# 3.0 Spracherweiterungen als Pre-Requisites zu LINQ


Wichtig für die C#-Programmierer:
UNBEDINGT zuerst diesen Webcast angucken BEVOR man sich mit LINQ beschäftigt!

Wie wäre es mit:
Typinferenz                (the variable type is inferred by the compiler)
Anonyme Typen         (var)
Extension-Methoden  (string.function1.function2)
Lambda-Ausdrücke    (=>)

und schon haben wir die Neuerungen in C# 3.0 um die Wahnsinns-Syntax im Bild unten zu kapieren.

DAS ist die GRUNDLAGE um dann in LINQ zu programmieren.

Denn dort weiss man im Vorneherein eben nicht, was genau für Typen zurückkommen.
Aber das ist weiter nicht schlimm, denn der wirklich perfekte Webcast (in deutscher Sprache) ist von
Bernd Marquardt, vom 12.11.2007.
C# 3.0 – Die Standard Spracherweiterugen

LINQ, Stichwort: Stored Procedure Recompilation

There was no time to translate these thoughts to English, sorry for that!

Hier meine aktuelle Meinung zu LINQ mit Ausholer:
(siehe auch allerletzten Satz ganz unten: …LINQ ist m.E. gar nicht das Problem…)

Wenn man Performance will oder die Logik nicht rausgeben kann, ist es sicher besser eine Stored Proc zu schreiben, die dann von einem DBA optimiert wird.

Es gibt da nämlich so viele Haken.
Allein was die Stored Proc-Recompilation betrifft, siehe im Anhang ganz unten.

Ansonsten:

Pro für LINQ:

Wer sich nicht mehr mit XPath und XQuery auseinandersetzen will, kann das alles mit LINQ abstrahieren, wobei die Queries dann auch gleich für andere Datasources gelten, wie auch sonstige Textfiles oder Collections.
Hier bin ich sehr für LINQ!

– Unify the way of accessing data from different data sources (Objects, database, XML…etc
– It shields you from learning the specification details of each data source

Hier schreibt Ahmed Nagy

Und weiter:

 

Wer mit LINQ to SQL arbeiten will, kann ein Compile konfigurieren.

Wobei ich mich mit diesem LINQ to SQL Compile frage, ob man da nicht mit einem DBA zusammen arbeiten muss, denn das ist nicht so einfach, siehe Anhang ganz unten mit dem Recompile!!!
Wenn man aber sowieso mit dem DBA arbeiten muss, dann kann man doch gleich Stored Procs bauen lassen.
Wie soll der arme Programmierer das alles auf der DB blicken sollen?

Wenn ich alleine gesehen habe, was die Kimberly L. Tripp an Webcasts gemacht hat… meine Güte.

Ganz interessant ist auch folgender Webcast von Bill Graziano, SQL Server MVP:
What I Wish Developers Knew About Writing Queries in SQL Server 2005
Wie soll da der Developer richtige DB-Abfragen machen, so aus dem Stegreif?
Entweder gibt es einen DBA zum Optimieren, oder der Programmierer ist der DBA, dann kann er gleich im SSMS graphisch die SQL-Queries zusammenbauen.
Am Besten ist es dann am Schluß mit dem Profiler nach Recompiles zu fahnden!!!

Hier, von Jim  Wooley, MVP, wie man LINQ to SQL Compiled Queries macht.

Das ist ja wieder extrem abstrakt!!!
OCaml und F#? Hallo ich wollte doch meine Arbeit vereinfachen!!!

Hier ein weiterer Ausschnitt

Anhang wegen der Recompilation von SPs

War Euch die Recompilation von SPs in dieser Art richtig klar??

Siehe: SQL Stored Procedure Recompilation 

A: ALL DDL first, ALL DML last
Die folgende SP wird bei jedem Ausführen genau dreimal recompiled!!

Also nicht nur beim ersten Ausführen drei Mal sondern JEDES MAL DREI MAL!!!
–DDL
–DML
–DDL
–DML etc…

B: Certain Temporary Table Operations

Und vor allen Dingen:

Hier die KEEP PLAN Option verwenden (sprich unkommentiere diese Option in der folgenden SP:

Also dann noch viel Spaß mit LINQ!
Denn LINQ ist m.E. ja gar nicht das Problem!
Alle meinen man nähme was Neues, das man nicht richtig versteht, um die ganzen alten Probleme wegzubekommen, die man auch nicht so richtig versteht.
Anstatt wieder einer neuen Sau durch das Dorf nachzurennen, wäre es vielleicht besser den alten Sachverhalt zu verstehen und dann mit dem Profiler zu arbeiten. Wie bei CSI: Es geht um Evidenz, nicht Kristallkugel.
Oder es gibt eine politische Vorgabe, dann ist es wieder etwas anderes.

FreeWare HV.AmazonClient © 2007-2008 by H&V-Lima

New Version 1.14 (02. October 2008)

Why this application?
To find a new book on Amazon.com it takes so much time to load and reload the web pages. And what about referencing a book quickly in the bibliography of your documentation?
HV.AmazonClient lets you find your desired book quickly. A double-click takes you to its cover and a right click lets you copy a standard citation sentence incl. ISBN-nº.

 

  • SOAP access to Amazon.com

  • super responsive in all means (scrolling/sorting the grid, revealing the book cover, copying citation, opening Amazon, ALL while loading)
    using the following features

  • Control.Invoke, Backgroundworker and LoadAsync

  • New Version 1.14 (02. October 2008)

FreeWare HV.ScreenCapture © 2007-2008 by H&V-Lima

Why this application?
When you write down your IT-work e.g. for EN ISO 9000:2005 documentation. How many times do you need a partly screenshot to paste something in low resolution into your editor? Or how many times you’d like to copy/paste an open picture from your desktop into your chat program? Probably liking to annotate a quick phrase on it?Take HV.ScreenCapture. Click the Capture button, drag the mouse over a screen area and you’re done. Put Text, Date, Time in a second. Rotate or flip the picture, adjust Brightness in a snap, add a drop shadow with a click or erase a password with the drawing pen.

   
  • Full blown standard Windows Forms Application
  • Extensive utilization of User Settings
  • Embedded user control and own class library
  • Owner drawn forms graphics
  • HWND handling shows dynamically picture size in status bar
  • Complete round-robin Undo-/Redo Capability
  • Unhandled exception handler with RTF output
  • Download HV.ScreenCapture here Version 1.32

Multithreading with asynchronous Delegates and Backgroundworker

Why that?
Programming true Multithreading is a hard job.
Yet using Multithreading to just create responsive UIs is a lot easier.
The biggest problem for later reuse of any code is always programming UI-code with intermingled business logic.
This HV.Pattern creates the possibility to encapsulate business logic in a backgroundworker that is called from within an own independent class.
The big hit is (see following illustration), that B (here in the HV.Tools-Assembly) encapsulates an asynchronous C.
During runtime the C will run on its own thread, making A’s UI absolutely responsive.
This is no pseudo-background working as with the use of a timer-control or with calls to me.update or Application.DoEvents.
This is true preemptive multitasking. You can even input the thread priority, receive whole objects on Progress_Changed.
And all this without the hassles of fighting with semaphores, queues and mutexes.
OK, clearly, you can’t let your code be reentrant to its own Bgw_DoWork. But we are talking about single instance background working.

Class Library Skeleton for the above Wait Class:

HV.Tools Class Library
Wait Members:
Waits on a separate thread and sends feedback on intervals.
The following tables list the members exposed by the Wait type.
Not all Args implement getters and setters in this version.

Public Constructor

Public Properties

 

Public Args

Public Methods

Protected Methods

Public Events

How to implement a CLR Assembly in SQL Server 2005?

Why that?
Manipulating strings with T-SQL is overly slow.
When it comes to strings, consider implementing a CLR Assembly, it will run around 100 times faster!

In VS2005 Create New Project, Visual Basic, Database, SQL Server Project

Below follows an example on how to add single quotes to comma separated values.
This is necessary in T-SQL when using the IN-clause.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
 
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function SingleQuote(ByVal input As String, ByVal trim As Integer, _
                                       ByVal separator As String) As String
        ‘input is csv-string or only one value
        ‘e.g. 2,3,4
        ‘that should be converted to
        ”2′,’3′,’4′
        ‘trim = 0 means that output is as input
        ‘trim = 1 means that each output string is left- and right-trimmed from spaces
        ‘separator is the character that separates the values, normally a comma ","
        Dim output As String = ""
        ‘if there is only one input value without separator
        ‘process this one value directly
        If input.IndexOf(separator) = -1 Then
            ‘trim the output if necessary
            If trim = 1 Then
                output = "’" & input.Trim() & "’"
            Else
                output = "’" & input & "’"
            End If
        Else
            ‘there is more than one value
            Dim returnValue As String()
            ‘csv-input is split in a collection of strings
            ‘separated by ‘separator’
            returnValue = input.Split(separator)
            Dim i As Integer
            If trim = 1 Then
                ‘trim the output if necessary
                For i = 0 To returnValue.Length – 1
                    output &= "’" & returnValue(i).Trim & "’,"
                Next
                output = output.TrimEnd(",")
            Else
                For i = 0 To returnValue.Length – 1
                    output &= "’" & returnValue(i) & "’,"
                Next
                ‘cut off the last comma
                output = output.TrimEnd(",")
            End If
        End If
        Return output
    End Function
End Class

Deployment in SSMS:

create assembly stringmanipulation from ‘c:\testsql\StringManipulation.dll’
create function InvertInt (@InStr int) returns int
external name stringmanipulation.UserDefinedFunctions.InvertInt
create function SingleQuote (@a nvarchar(max), @b int, @c nvarchar(max)) returns nvarchar(max)
external name stringmanipulation.UserDefinedFunctions.SingleQuote

Test in SSMS:

How to join in T-SQL logically a multi-value Reporting Services Parameter with a Freetext Entry?

From Entry-Logic to the final T-SQL Program:
The most interesting aspect in the following procedure is the one-to-one mapping between the discovered cases in 1., the flowchart in 2. and the T-SQL program in 3..
Initially apparently not very elegant goto-programming in SQL brings here two big advantages:
a. Nothing is easier to discover as a coding error, so when in later years another programmer needs to change the SQL logic, every aspect can be followed, reconstructed and changed, step-by-step.
b. Any ending goto-branch in T-SQL can be implemented by its own stored procedure, which leads to the best possible utilization of precompiled SQL execution plans.
1. Case Construction:
Examples:
– in cases 9 to 16: it doesn’t matter what else the user clicks or if she enters any freetext, ALL incl. NULL means ALL.
– since SSRS doesn’t accept no entry in a multi-value field, the value NOTHING was invented. This means: to enter nothing in the multi-value field, the value Nothing must be selected.

2. Logic-Flowchart:
The 16 cases from above are flagged (A, B, C, D)

3. T-SQL Program:
Like above, the 16 cases are identified in parentheses (abcd)

–Case 9-16
IF CHARINDEX(‘All’,@SearchValues)<>0 GOTO ALLL –A ‘All’=1??? selected
 
IF CHARINDEX(‘Nothing’,@SearchValues) = 0 — ‘Nothing’=0 not selected (00)
IF @SearchValues <>          — there is Multi-Input=1 (001)
       IF @strOrElse <>           — there is Else-Input=1 (0011)
        –Case 4
        GOTO MULTIORELSE –C
       ELSE
        –Case 3
        GOTO MULTI –B           — there is NO Else-Input=0 (0010)
 
IF CHARINDEX(‘Nothing’,@SearchValues) <> 0     –‘Nothing’=1 selected (01)
  –Cases 7 and 8 will be considered in later release
  –if @SearchValues <> ” GOTO FIN   — there is Multi-Input=1 (011?)
       –Case 6
       IF @strOrElse = GOTO FIN –Case5 (0100)
       ELSE
       GOTO EELSE –D there is Else-Input=1 (0101)
GOTO NOCASE

How to debug custom .NET Assembly in SSRS, using a single Instance of Visual Studio?

Create a new report project using Visual Studio.
1. At the time you create a report project, Visual Studio also creates a solution to contain it.
2. Add a new Class Library project to the existing solution. Make sure that the report project is set as the startup project.

3. How2 call functions in your Class Library, see here
4. In Solution Explorer, select the solution.
5. On the Project menu, click Properties

6. The Solution Property Pages dialog box opens.
7. In the left pane, expand Common Properties if necessary, and click Project Dependencies. Select the report project from the Project drop-down list. Select your assembly project in the Depends On list.

8. Click OK to save the changes, and close the Property Pages dialog.
9. In Solution Explorer, select your custom assembly project.
10. On the Project menu, click Properties.
11. The Project Property Pages dialog box opens.
12. In the left pane, expand Configuration Properties, and click Build if you’re in a C# project or Compile if you’re in a Visual Basic project.
13. On the Build/Compile page, enter the path to the Report Designer folder. By default, this is C:\Program Files\Microsoft SQL Server. Server\90\Tools\Binn\VSShell\Common7\IDE) in the Output Path text box. This builds and deploys an updated version of your custom assembly directly to Report Designer before your report is executed.

14. Disable Deploy in the DebugLocal Config in Configuration Manager
15. Set the Active Solution Configuration to DebugLocal.

16. Once you have designed your report and developed your custom assembly, set breakpoints in your custom assembly code.
17. Run the report under DebugLocal mode by pressing the F5 key. When the report executes in the pop-up preview window, the debugger hits any breakpoints that correspond to executable code in your assembly. Use F11 to step through your custom assembly code.
18. Special Hack: The moment you decide to make the next Build (inside to the VS2005’s private assemblies directory), this will not function, because the assembly is hold open by VS. Instead of shutting down/reopening VS, act smarter: rename your DLL (rename works, delete not).