How to get the full stored procedure name (incl. schema name) within the current executing stored procedure.

Uncategorized Leave a comment

 

Here is a function you can use to get the full stored procedure name (incl. schema name) within the current executing stored procedure:

 

if  object_id('dbo.GetFullSprocName') is not null
begin
    drop function dbo.GetFullSprocName
end
go

create function dbo.GetFullSprocName (@sprocId int)
returns varchar(255)
as
begin
    declare @FullSprocName as varchar(255)
    
    set @FullSprocName = (
        select        top 1 s.name + '.' + o.name
        from        sys.objects o with(nolock)
        inner join    sys.schemas s with (nolock) on o.schema_id = s.schema_id
        where        o.object_id = @sprocId
    )

    return @FullSprocName
end
go

To call this function use:

 

declare @sprocName varchar(128) = dbo.GetFullSprocName(@@PROCID)

Dictionary lookup vs switch vs if performance in C#

Uncategorized Leave a comment

Lets say you want to execute a specific logging function based on a log level. Amongst others you could use a switch- or if statements or use a generic dictionary containing the functions to call.

 

In mine code the generic dictionary has the advantage of being more testable, so I wanted to figure out what the performance impact of this dictionary lookup was. So I wrote a little test.

Each test will execute a log function 1 million times.

 

 

Executing 1.000.000 times:

Switch: 374ms

If: 387ms (3% slower)

Dictionary: 393ms (5% slower)

 

 

In my case this was enough to convince me to use the generic dictionary lookup for better testability.

 

namespace PTB.Cs.Test.Research
{
using System;
using System.Collections.Generic;
using System.IO;
using Xunit;

public class RliTester
{
    private string _message;

    private readonly Dictionary<Levels, Action<string>> _logFunctions;
    public RliTester()
    {
        _logFunctions = new Dictionary<Levels, Action<string>>
        {
            { Levels.Debug, Debug },
            { Levels.Error, Error },
            { Levels.Info, Info },
            { Levels.Warning, Warning },
        };    
    }

    public void Debug(string message)
    {
        _message = message;
    }

    public void Error(string message)
    {
        _message = message;
    }

    public void Info(string message)
    {
        _message = message;
    }

    public void Warning(string message)
    {
        _message = message;
    }

    public void ExecuteFunctionBySwitch(Levels level, string message)
    {
        switch (level)
        {
            case Levels.Debug:
                Debug(message);
                break;
            case Levels.Error:
                Error(message);
                break;
            case Levels.Info:
                Info(message);
                break;
            case Levels.Warning:
                Warning(message);
                break;
        }
    }

    public void ExecuteFunctionByIf(Levels level, string message)
    {
        if (level == Levels.Debug)
        {
            Debug(message);
        }

        if (level == Levels.Error)
        {
            Error(message);
        }

        if (level == Levels.Info)
        {
            Info(message);
        }

        if (level == Levels.Warning)
        {
            Warning(message);
        }
    }


    public void ExecuteFunctionByDictionairyLookup(Levels level, string message)
    {
        _logFunctions[level].Invoke(message);
    }

    [Fact]
    public void Test_performance_ExecuteFunctionBySwitch()
    {
        var watch = new System.Diagnostics.Stopwatch();
        watch.Start();

        for(int i = 0; i < 1000000; i++)
        {
            ExecuteFunctionBySwitch(Levels.Info, string.Format("This is info message [{0}].", i));
        }

        watch.Stop();
        System.Console.WriteLine(watch.Elapsed.TotalMilliseconds);

        // Result: 374ms
    }

    [Fact]
    public void Test_performance_ExecuteFunctionIf()
    {
        var watch = new System.Diagnostics.Stopwatch();
        watch.Start();
            
        for (int i = 0; i < 1000000; i++)
        {
            ExecuteFunctionByIf(Levels.Info, string.Format("This is info message [{0}].", i));
        }

        watch.Stop();
        System.Console.WriteLine(watch.Elapsed.TotalMilliseconds);

        // Result: 387ms
    }

    [Fact]
    public void Test_performance_ExecuteFunctionByDictionairyLookup()
    {
        var watch = new System.Diagnostics.Stopwatch();
        watch.Start();

        for (int i = 0; i < 1000000; i++)
        {
            ExecuteFunctionByDictionairyLookup(Levels.Info, string.Format("This is info message [{0}].", i));
        }

        watch.Stop();
        System.Console.WriteLine(watch.Elapsed.TotalMilliseconds);

        // Result: 393ms
    }

        
}

/// <summary>
/// Determines the level of the logging.
/// </summary>
public enum Levels
{
    Error = 1,
    Warning = 2,
    Info = 3,
    Debug = 4
}
}

How to handle variable column count with the SSIS 2008 Flat File Source.

Uncategorized 3 Comments

If you want to import flat files (e.g. *.csv) that contain rows with dynamic column count, the best way is to use this codeplex component: http://ssisdfs.codeplex.com/

 

If you don’t want to use that component, here is a script task that alters the import file, before the flat file source executes.

 

 

public void Main()
{
    Dts.TaskResult = (int)ScriptResults.Success;

    // Files should contain 46 columns
    int maxColumnCount = 46;

    // Determines if an event can be fired more than once for the duration of the current execution.
    Boolean fireAgain = true;

    Dts.Events.FireInformation(0, "Info: ", "Update file process started (update file so it contains the correct column count per row).", string.Empty, 0, ref fireAgain);

    // Get file path.
    string importFile = Dts.Variables[@"User::FileToImport"].Value.ToString();

    // Check if file exists.
    if (System.IO.File.Exists(importFile))
    {
        // Will contain the new file content with corrected column count.
        var newFileContents = new System.Text.StringBuilder(string.Empty);

        using (var streamReader = new System.IO.StreamReader(spiFile))
        {
            // Continue reading until end of file.
            while (!streamReader.EndOfStream)
            {
                // Read a line.
                string line = streamReader.ReadLine();

                // Ignore empty lines (empty lines will not be added to the new file contents).
                if (!string.IsNullOrEmpty(line))
                {
                    // Split on ",", so we can count columns. Use string[] for performance reasons (later the array is used to join).
                    string[] lineColumns = line.Split(',');

                    // Check if current row contains less columns, then max column count.
                    bool lessColumns = (lineColumns.Length < maxColumnCount);

                    if (lessColumns)
                    {
                        // Add column until maxColumnCount.
                        Array.Resize(ref lineColumns, maxColumnCount);
                    }

                    // Store only max column count in new file contents (ignoring extra columns).
                    string newLine = string.Join(",", lineColumns, 0, maxColumnCount);
                    newFileContents.AppendLine(newLine);
                }
            }
        }

        // Save new file contents.
        System.IO.File.WriteAllText(importFile, newFileContents.ToString());
    }
    else
    {
        Dts.Events.FireInformation(0, "Info: ", string.Format("File: {0} not found.", importFile), string.Empty, 0, ref fireAgain);
        Dts.TaskResult = (int)ScriptResults.Failure;
    }

    Dts.Events.FireInformation(0, "Info: ", "Update file process completed.", string.Empty, 0, ref fireAgain);
}

Uploading large files with Outsystems

Uncategorized Leave a comment

If you want to upload "large" files e.g. <= 100MB, on any ASP .NET website, you must edit the web.config file.

There are many settings that you might want to update, when uploading large files e.g. session, request timeouts etc.

 

In this post I will only address the web.config attributes:

But the same steps can be used to update other elements / attributes in the web.config file.

 

Factory Configuration

In Outsystems, on every publish of a eSpace a new web.config file is produced. In order to update this new web.config file, we will have to use the Outsystems solution pack "Factory Configuration". Download the outsystems solution pack "Factory Configuration":http://www.outsystems.com/forge/component/25/Service+Center+Factory+Configuration/

(Service.Center.Factory.Configuration-8.0.0.osp = 325kb)

clip_image001

 

Click on 1-Click Publish

clip_image002

 

Open the page http://localhost/FactoryConfiguration in a browser.

clip_image003

Click on Shared Configurations

clip_image004

Click on Create New Shared Configuration

 

Paste in the XSLT from below:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" encoding="UTF-8"/>
<!-- 
Description
This xslt is used to add uploading of large files capabilities to a outsystems eSpace on IIS.
- It will upsert the attribute system.web/httpRuntime/@maxRequestLength (<=IIS6).
- It will upsert the attribute system.webServer/security/requestFiltering/requestLimits/@maxAllowedContentLength (>=IIS7).
-->
<!-- Just copy all other xml parts, that don't have a specific template match. -->
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
<xsl:template match="configuration">
<!-- Create an "configuration" element in the output document. -->
<configuration>
<!-- Copy child elements to output document. -->
<xsl:apply-templates select="@*|node()"/>
<!-- If the element "system.web" does not exist, create it in the output document. -->
<xsl:if test="count(system.web) = 0">
<system.web>
<httpRuntime maxRequestLength="102400"  />
</system.web>
</xsl:if>   
<!-- If the element "system.webServer" does not exist, create it in the output document. -->
<xsl:if test="count(system.webServer) = 0">
<system.webServer>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength="104857600"/>
</requestFiltering>
</security>
</system.webServer>
</xsl:if>
</configuration>
</xsl:template>
<!-- If the element "system.web/httpRuntime" does not exist, add it. -->
<xsl:template match="system.web[not(httpRuntime)]">
<system.web>
<xsl:apply-templates select="@*|node()"/>
<httpRuntime maxRequestLength="102400"  />
</system.web>
</xsl:template>
<!-- If the attribute "system.web/httpRuntime/@maxRequestLength" does not exist, add it. -->
<xsl:template match="system.web/httpRuntime[not(@maxRequestLength)]">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
<xsl:attribute name="maxRequestLength">
<xsl:value-of select="'102400'"/>
</xsl:attribute>
</xsl:copy>
</xsl:template>
<!-- If the attribute "system.web/httpRuntime/@maxRequestLength" exists update it. -->
<xsl:template match="system.web/httpRuntime/@maxRequestLength">
<xsl:attribute name="maxRequestLength">
<xsl:value-of select="'102400'"/>
</xsl:attribute>
</xsl:template>
<!-- If the element "system.webServer/security" does not exist, add it. -->
<xsl:template match="system.webServer[not(security)]">
<system.webServer>
<xsl:apply-templates select="@*|node()"/>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength="104857600"/>
</requestFiltering>
</security>
</system.webServer>
</xsl:template>
<!-- If the element "system.webServer/security/requestFiltering" does not exist, add it. -->
<xsl:template match="system.webServer/security[not(requestFiltering)]">
<security>
<xsl:apply-templates select="@*|node()"/>
<requestFiltering>
<requestLimits maxAllowedContentLength="104857600"/>
</requestFiltering>
</security>
</xsl:template>
<!-- If the element "system.webServer/security/requestFiltering/requestLimits" does not exist, add it. -->
<xsl:template match="system.webServer/security/requestFiltering[not(requestLimits)]">
<requestFiltering>
<xsl:apply-templates select="@*|node()"/>
<requestLimits maxAllowedContentLength="104857600"/>
</requestFiltering>
</xsl:template>
<!-- If the attribute "system.webServer/security/requestFiltering/requestLimits/@maxAllowedContentLength" does not exist, add it. -->
<xsl:template match="system.webServer/security/requestFiltering/requestLimits[not(@maxAllowedContentLength)]">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
<xsl:attribute name="maxAllowedContentLength">
<xsl:value-of select="'104857600'"/>
</xsl:attribute>
</xsl:copy>
</xsl:template>
<!-- If the attribute "system.webServer/security/requestFiltering/requestLimits/@maxAllowedContentLength" exists update it. -->
<xsl:template match="system.webServer/security/requestFiltering/requestLimits/@maxAllowedContentLength">
<xsl:attribute name="maxAllowedContentLength">
<xsl:value-of select="'104857600'"/>
</xsl:attribute>
</xsl:template>
</xsl:stylesheet>

Now as you can see, I ‘m not a XSLT goeroe, so someone will tell me, this is a lot of XSLT code for something that simple, please do Smile.

 

 

Click on Create

clip_image005

Now the shared configuration is created, we must associate it to the eSpaces in which we want to use it.

 

Go to the eSpaces tab

clip_image006

 

Select "Add_upload_large_files_capabilities"

clip_image007

Click on Associate

 

 

Now on every publish of an eSpace the web.config of that eSpace will be altered to support uploading of large (<= 100MB) files. After deploying your eSpace, you can check the web.config on the filesystem of the server, to verify the changes were correctly executed. In my case, the web.config was found at: C:\Program Files\OutSystems\Platform Server\running\RliTest.0494247501