Monday, 31 January 2011

Asp.net web server control for rendering script blocks

There are many ways to add script block to our asp.net pages. This is a very simple problem until we had got a complex project with custom controls, partial page rendering, updatepanels and dynamically loaded controls.
We have to add script blocks different ways if we asynchrony post backs or full page post backs.
The other problem is when the script block don’t have to load at the first page load, it depends on some logic dynamically.

After many trying I use this solution since about half year and it works at any case.

The simple and easy to use source code is:

public class InlineScript : Control
    {
        protected override void Render(HtmlTextWriter writer)
        {
            StringBuilder sb = new StringBuilder();
            base.Render(new HtmlTextWriter(new StringWriter(sb)));

            string script = sb.ToString();

            ScriptManager sm = ScriptManager.GetCurrent(Page);
            if (!(null == sm))
            {
                if (sm.IsInAsyncPostBack)
                {
                    ScriptManager.RegisterStartupScript(this, typeof(InlineScript), UniqueID, script, false);
                }
                else
                {
                    base.Render(writer);
                }
            }
            else
            {
                this.Page.ClientScript.RegisterStartupScript(this.GetType(), this.ID, sb.ToString());
            }
        }
    }

After registering into web.config, we can use it easily everywhere on our program:

<wc:InlineScript ID="isTest" runat="server">
    <script type="text/javascript">
        function pageLoad() {
            alert('hi');
        }
    </script>
</wc:InlineScript>

Saturday, 29 January 2011

How to set up Windows 7 environment for PHP + MySQL development on IIS7

We need a following tools for an average PHP + MySql development procedure:

- a good IDE

- something for managing database

- web server

- ability to debugging applications

- get all of them free.

IDE: We need intellisense support and tools for debugging. Smart people doesn’t start any development without these. If you have Windows operating system, you can use Web Platform Installer and it offers a development environment named Webmatrix free of charge. Webmatrix doesn’t have debugger or intellisense support so it’s only a memory killer, good looking notepad. After some googling and based on my experiments I have been chosen Netbeans IDE.

Database Manager: I don’t use MySql too often so I let mysql.com to choose my tool. I wanted a native sql IDE and found MySql Workbench. It doesn’t have too long feature list but we can do the basic SQL tasks and it is not PHPMyAdmin so we doesn’t need to run web browser for insert a simple record into our database.

Web server: I use Windows 7 and of course it will IIS7. I have been bought my Windows before and I can install it free of charge.

 

Installing IIS7: I won’t describe how to install it. There is a good description how to install it on IIS website.

Installing Netbeans IDE:

- Download from netbeans.org the php development version (or we can choose version named ALL, that will install every development tools from C to Java that netbeans supports).

- Install the executable. (Not too hard, next-next-next…..-finish).

- Navigate to PHP tab on Tools->Options on Netbeans menu. If we got ALL version, we have to make a new php project. In that case Netbeans initializes himself for php development and we will see PHP tab on Options->Tools after creating New PHP Project. If we installed the PHP version, we don’t need to make new PHP project, Netbeans initializes php settings during the first run.

- Check the php development settings. We need to set Debugger Port to 9000 and Session_ID to netbeans-xdebug for debugging php applications.

Installing Mysql

- Go to MySQL Download page.

- Download the proper version for our operating system (In my case it means Windows (x86, 32-bit), MSI Installer).

- Install the executable. (Not too hard, next-next-next…..-finish). We have to set security settings during the installation.

- Download MySql Workbench. We have to chose a version again.

- Install the executable. (Not too hard, next-next-next…..-finish).

- Start MySql Workbench and test if we can connect to our database. WorkbenchStartPage

Installing PHP binaries:

- Start Web Platform Installer. If doesn’t have, download and install it.

- Go to products tab, Frameworks entry and check PHP 5.3 for WebMatrix and PHP Manager for IIS.WebPlatformInstallerSelectItems

- Click on Install button.

- Installer shows us what will it install and what will it install more. We didn’t check WebMatrix but we have to install it. Click on Accept button. Ha must install it but don’t need to use this piece of shit.WebPlatformInstallerDependencies

- At the end of the installation Web Platform Installer shows us a message window and offers to run WebMatrix. Simple close the window, don’t need to start it.WebPlatformInstallerInstalComplete

- Install CGI Extension for IIS7:

  • Start Control Panel and go to Programs entry.
    IISCGIModuleInstallation
  • Click on Turn Windows features on or off entry.
  • Got to Information Services-World Wide Web Services->Application Development Feature and check CGI
  • Install it.

Installing xDebug:

- Start IIS7 manager.

- We can find PHP Manager on IIS section on IIS Manager.IISManagerPHPManagerIcon

- Click on Check phpinfo() link.

PHPManagerCheckPHPInfo

- Choose site and url.

- Start our browser and go to the home page of xDebug. There is a page named fnd binary on that site and it helps us deciding which xDebug version we need. There is a big, empty textbox in that page. Paste our phpinfo output into this textbox and click on Analyse my phpinfo() button. After analysed, the page offers the proper xDebug version for us. That means MS VC9 – Architecture: x86 in my case. After analyising this page offers a link direct to download page under Instructions.

- Download the proper dll and copy it to an ext subfolder to our php installation. At my case it means the folder c:\Program Files (x86)\PHP\v5.3\ext\ .

- Go back to PHP Manager on IIS and click on  Configuration file link and we can edit our php.ini settting file.

- Add the following lines to the end of the file:

- Restart IIS.

- Check phpinfo() again. If we installed xDebug successfully we will find a ‘… with xDebug…’ part of the output at the end of the first block of the html result of phpinfo that signs that our installation is successful.PHPInfoWithXDebug

At that point we don’t have any to do, only write our firs PHP application (of course a simple Hello World) and check if we can debug it and if we can connect to our MySql database.

 

Note: We have to use the same port at xDebug port in php.ini and netbeans-xdebug port in Netbeans settings. If we start a php project on debug mode in Netbeans and we have wrong port settings, we can’t debug and Netbeans cannot connect to xDebug process only shows a messange: Waiting for conncection (netbeans-xdebug).NetbeansWrongXDebugSettings

Saturday, 8 January 2011

C#: throwable exceptions and Visual Studio Productivity Power Tools

 

There is a really great add-on for Visual Studio named Productivity Power Tools. It helps a lot during my work but hides some information that so important for me: the list of throwable exceptions.

At the university I didn’t have a choice. I had to learn Java. Java is a great language and has a restriction that I like so: you must specify the list of the throwable exceptions at the specification of each method. I work with C# nowadays. It doesn’t has this rule but Visual Studio tries to help us on a simple way: it shows us this list when we hover on the name of the given method with the mouse like this:
originalLook

As we can se, we now a bit closer to the Java restrictions. We can find out fast the given list but the language still don’t force us into handle all exceptions. It smart or not – I don’t know. But in fact I’m happy to see this list.

There is a bit problem with the power tools: It gives us a new window for our Visual Studio, named Solution Navigator. It has a lot of functions among others coloring the hints that I shown before:

withSolutionNavigator

Ops! As we can see, the list of throwable exceptions has disappeared. We have an other function instead of this, every can try this out by clicking on the triangle at the right side of the popup.

At this time we have two choice. We can use our Visual Studio with these settings or we can disable this function by setting of on the Visual Studio menu –> Tools –> Options –> Productivity Power Tools –> Solution Navigator. We got the original function after restarting our development environment.

powerToolsSettings

 

Remarks: Solution Navigator is so slow. Sometimes useful but the most part of my times it only slows down my work without any benefits.

Monday, 3 January 2011

Sql server–Using indexes with LIKE operator in stored procedures

 

In the most cases msdn is a trusted source of information. Of course sometimes some mistakes are slipping into. The other day I read a documentation about CREATING STORED PROCEDURES when I found an interesting slip-up:

“Avoid using a wildcard as the leading character in a LIKE clause, for example, LIKE ‘%a%’. Because the first character is non-deterministic, the query processor is unable to use available indexes. Use LIKE ‘a%’ instead.”

Well, I thought there was a catch in it because I use the LIKE operator with leading wildcard in many cases and I know, we can get a significant speed increase with indexes in this case too.

I made a simple test using my Northwind database:

-- create sample stored procedure for demonstrate
-- the using of indexex with leading wildcards
create procedure SelectCustomers
as begin
    select * from Customers
    where CompanyName like 'a%'
end

go

-- running the example
exec SelectCustomers

go

-- delete the example
drop procedure SelectCustomers

After running I got the following result in my actual execution plan:

Like

The conclusion about using indexes with LIKE operator is the following:

- without wildcards: Index seek (like ‘a’)

- with wildcards: Index scan (like ‘a%’, like ‘%a’, like ‘%a%’)

Thursday, 30 December 2010

Microsoft Sql Server stored procedures – don’t trust in sql server optimization

 

 

It is an understood thing that Microsoft SQL Server (as many dbms) makes optimizations in the background for the user to try make queries faster. Well, it’s a nice and good thing and of course if we spend less time for optimization we got more time for beer. We could be happy at this point, but let see what happens when using stored procedures.

Sql server generates a so-called ‘execution plans’ and executes queries by them. It stores these plans and decides by them which indexes will be used at the next running.

The problem starts at stored procedures that contains optional parameters. In that time we don’t need to give all of the parameters. In that case we should need different query plans with different given parameters.

When sql server executes a stored procedure it makes a single query plan for each stored procedures based on the given parameters at the first run. The server uses this plan for all runs and generates new query plan only in this cases:

  • executing stored procedure with WITH RECOMPILE option
  • making schema changes on any objects depending on stored procedures
  • executing sp_recompile on a table referenced by the stored procedure
  • on cross database operations restoring any dependent object
  • making a given number of server activities

    …

    Let’s see a simple example for query plan caching (using northwind sample database):

     

    create procedure [dbo].[GetCustomersStatic]
        @customerID nchar(5)        = null
        , @companyName nvarchar(40)    = null    -- both parameters are nullable
    as begin
        set @companyName = '%' + @companyName + '%'  
        select CustomerID, CompanyName
        from Customers
        where
            (@customerID is null or CustomerID = @customerID)
            and (@companyName is null or CompanyName like @companyName)
    end

    go

    exec dbo.GetCustomersStatic @companyName = N'a'
    exec dbo.GetCustomersStatic @customerID = N'BONAP'

    Let’s see the actual execution plans:

    StoredProcedureQueryPlans

    As we can see sql server uses the same query plan for both executing: it uses the index for company name when user searches by ID.

     

    The best solution that I found and has well expressed had written by Erland Sommarskog (Sql server MVP). He says we can solve it this ways:

    Static sql. Use WITH RECOMPILE option when executing stored procedure. At this time we make sql server to recompile the stored procedure and it must generate a brand new query plan. In that case the problem is given when we call the procedure many times. Recompile needs processor time and many recompiles causes performance problems.

    -- Static sql with recompile
    exec dbo.GetCustomersStatic @companyName = N'a' with recompile
    exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile



    2.: Dynamic sql. Create a query string by hand dynamically and run it with sp_executesl:
    CREATE procedure [dbo].[GetCustomersDynamic]
        @customerID nchar(5)        = null
        , @companyName nvarchar(40)    = null    -- both parameters are nullable
    as begin
        declare @query nvarchar(max)
                , @paramTypes nvarchar(100)

        set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
        set @query = N'
        select CustomerID, CompanyName
        from Customers
        where 1 = 1'
        
        if @customerID is not null
            set @query = @query + N'
            and CustomerID = @customerID'
            
        if @companyName is not null
        begin
            set @companyName = '%' + @companyName + '%'
            
            set @query = @query + N'
            and CompanyName like @companyName'
        end
        
        exec sp_executesql @query, @paramTypes, @customerID, @companyName
    end

    go

    -- Dynamic sql
    exec dbo.GetCustomersDynamic @customerID = N'BONAP'
    exec dbo.GetCustomersDynamic @companyName = N'a'


    The complete source code is:

    USE [Northwind]
    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
      IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[GetCustomersStatic]
    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    create procedure [dbo].[GetCustomersStatic]
        @customerID nchar(5)        = null
        , @companyName nvarchar(40)    = null    -- both parameters are nullable
    as begin
        set @companyName = '%' + @companyName + '%'  
        select CustomerID, CompanyName
        from Customers
        where
            (@customerID is null or CustomerID = @customerID)
            and (@companyName is null or CompanyName like @companyName)
    end


    GO


    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
      IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[GetCustomersDynamic]
    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE procedure [dbo].[GetCustomersDynamic]
        @customerID nchar(5)        = null
        , @companyName nvarchar(40)    = null    -- both parameters are nullable
    as begin
        declare @query nvarchar(max)
                , @paramTypes nvarchar(100)

        set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
        set @query = N'
        select CustomerID, CompanyName
        from Customers
        where 1 = 1'
        
        if @customerID is not null
            set @query = @query + N'
            and CustomerID = @customerID'
            
        if @companyName is not null
        begin
            set @companyName = '%' + @companyName + '%'
            
            set @query = @query + N'
            and CompanyName like @companyName'
        end
        
        exec sp_executesql @query, @paramTypes, @customerID, @companyName
    end
    GO


    go

    exec dbo.GetCustomersStatic @companyName = N'a'
    exec dbo.GetCustomersStatic @customerID = N'BONAP'


    -- Solutions

    -- Static sql with recompile
    exec dbo.GetCustomersStatic @companyName = N'a' with recompile
    exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile

    -- Dynamic sql
    exec dbo.GetCustomersDynamic @customerID = N'BONAP'
    exec dbo.GetCustomersDynamic @companyName = N'a'

    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:29 ******/
      IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[GetCustomersStatic]
    GO


    GO

    /****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:09:46 ******/
      IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[GetCustomersDynamic]
    GO
  • Friday, 17 December 2010

    C# object initializers: use them smart!

    Object initializers came with .Net framework in 2008. Using object initializers we can easily initialize our objects fields without writing many constructors:

                // Using object initializer
                Person demoP = new Person()
                {
                    ID = 0,
                    Name = "Happy",
                    Age = 1
                };


    As we can see, this method is simple. As in the old way, we can write a constructor or set fields by hand:

                Person oldDemoP = new Person();
                oldDemoP.ID = 0;
                oldDemoP.Name = "Happy";
                oldDemoP.Age = 1;



    Using object initializers, we are able to save a lot of coding. That's good, isn't it? Less coding means less work and more beer, so we happy, aren't we?

    Sorry, but the answer is: Not at every cases. In the real life, we spend our work hours with creating new lines of codes only about 10% and we are debugging our code all day from morning to night. And in real life, things don't work. You have databases and objects with bad design or with good design and bad data. You think every person has Age but after 3 migrates you can find some records that don't have.
    Let's take a simple object (Person):

        public class Person
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
        }

    Person has ID, Name and Age properties.

    In real life scenarios there are many case when you know that, you can get _Always_ the age of the person and at two case in the 1.5 million records you won't get it. In this way you get Exception when try assign a null value to an Age property. The question is: How does the Exception look like? What we can find in the error logs?

    Presenting the problem I made a simple example. I simulated my database with a list and use a special person class with object as property types:

        public class PersonDS
        {
            public object ID { get; set; }
            public object Name { get; set; }
            public object Age { get; set; }
        }


    I used this type for making an emulated simple sample database:

                List<PersonDS> database = new List<PersonDS>();
     
                database.Add(new PersonDS() { ID = 1, Name = "Peter", Age = 18 });
                database.Add(new PersonDS() { ID = 2, Name = "Liza", Age = null });
                database.Add(new PersonDS() { ID = 3, Name = "Cloe", Age = 16 });


    I. In the old way, we can set values one by one. Now, we get error at the assignment:


     // Old way to read datas
                try
                {
                    List<Person> oldPersons = new List<Person>();
                    foreach (var datarow in database)
                    {
                        Person p = new Person();
                        p.ID = (datarow.ID as int?).Value;
                        p.Name = datarow.Name as string;
                        p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment
                    }
                }
                catch (Exception exc)
                {
                    Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                        , exc.Message, exc.StackTrace));
                }


    II. Using the new way, we get the Exception when initializing the object:

                // The new way to read datas
                try
                {
                    List<Person> newPersons =   
                            (from p in database
                             select new Person()
                             {// Throws Exception at object initialize
                                 ID = (p.ID as int?).Value,
                                 Name = p.Name as string,
                                 Age = (p.Age as int?).Value
                             }).ToList();
                }
                catch (Exception exc)
                {
                    Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                        , exc.Message, exc.StackTrace));
                }
            }
    As we can see, we get our exceptions in both cases. But at the old way, we can see that we have a missing Age value at the first look. With object initializers, we see only that we got an error at the initialize. And we have to find out what was the error exactly.
    We can find many problems with this example: at the real life, I never used this form to read data from database: p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment
    There are many ways to do this properly but the target is showing a simple problem with object initializers: error messages are not enough well detailed. In real life we usually don't have objects with only three properties. And we can get data with many ways: databases, xmls, streams, user input, ... 
    The conclusion is: let's type a bit more but make error safe code. When we should bet our month salary for that property and want to say: 'Oh, it must be filled', don't be lazy and handle the possibility when it's not and we can sit down and open a beer.
    
    
    
    Morzel
    
    
    The entire code example:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
     
    namespace ObjectInitializersVSOldWays
    {
        public class Person
        {
            public int ID { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
        }
     
        public class PersonDS
        {
            public object ID { get; set; }
            public object Name { get; set; }
            public object Age { get; set; }
        }
     
        class Program
        {
            static void Main(string[] args)
            {
                // Using object initializer
                Person demoP = new Person()
                {
                    ID = 0,
                    Name = "Happy",
                    Age = 1
                };
     
                Person oldDemoP = new Person();
                oldDemoP.ID = 0;
                oldDemoP.Name = "Happy";
                oldDemoP.Age = 1;
     
                List<PersonDS> database = new List<PersonDS>();
     
                database.Add(new PersonDS() { ID = 1, Name = "Peter", Age = 18 });
                database.Add(new PersonDS() { ID = 2, Name = "Liza", Age = null });
                database.Add(new PersonDS() { ID = 3, Name = "Cloe", Age = 16 });
     
                // Old way to read datas
                try
                {
                    List<Person> oldPersons = new List<Person>();
                    foreach (var datarow in database)
                    {
                        Person p = new Person();
                        p.ID = (datarow.ID as int?).Value;
                        p.Name = datarow.Name as string;
                        p.Age = (datarow.Age as int?).Value;// Throws Exception at value assignment
                    }
                }
                catch (Exception exc)
                {
                    Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                        , exc.Message, exc.StackTrace));
                }
     
                // The new way to read datas
                try
                {
                    List<Person> newPersons =   
                            (from p in database
                             select new Person()
                             {   // Throws Exception at object initialize
                                 ID = (p.ID as int?).Value,
                                 Name = p.Name as string,
                                 Age = (p.Age as int?).Value
                             }).ToList();
                }
                catch (Exception exc)
                {
                    Console.WriteLine(string.Format("Message: {1}\nStackTrace: {1}"
                        , exc.Message, exc.StackTrace));
                }
            }
        }
    }
    And the output is:
    
    Message:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
       at System.Nullable`1.get_Value()
       at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 41
    StackTrace:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
       at System.Nullable`1.get_Value()
       at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 41
    Message:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
       at System.Nullable`1.get_Value()
       at ObjectInitializersVSOldWays.Program.<Main>b__4(PersonDS p) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 54
       at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 52
    StackTrace:    at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
       at System.Nullable`1.get_Value()
       at ObjectInitializersVSOldWays.Program.<Main>b__4(PersonDS p) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 54
       at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at ObjectInitializersVSOldWays.Program.Main(String[] args) in D:\--- Souce ---\Blog\ObjectInitializersVSOldWays\ObjectInitializersVSOldWays\Program.cs:line 52
    

    Sunday, 12 September 2010

    How to delete Custom Dotnetnuke 5.5 settings

    After my previous post, we can insert and update host settings in DotNetNuke 5.5.

    Now after some mistypes, I want to delete my bad custom settings.

    Unfortunately as at inserting, we cannot use HostController because it provides only querying information, no update or insert, and however DataProvider has method for adding new setting but there is no method for delete.
    Now we know what we can use so let's search something useful. After some thinking and searching in DotNetNuke api I realized that: there is nothing for delete host setting. At this point I decided, I return to the pure sql and there is a simple solution:

    public static void InsertSetting(string key, string value)
    {
      var dp = DataProvider.Instance();
                    
      dp.AddHostSetting(key, value, false, -2);   // -2 is not an existing user. -1 is system default
     
      DotNetNuke.Common.Utilities.DataCache.ClearHostCache(true);
    }



    There is a new DotNetNuke api method there:
    ClearHostCache(bool Cascade)
    The explanation is simple:  HostController.Instance is a cache object and after deleting from database, we have to refresh the host cache.


    And of course after the work, we can open a cold beer...