.NET - Extend ListControl with GetSelectedItems and GetSelectedValues

Sometimes we need to get from ListBox or CheckBoxList the all selected items. Unfortunately there is only one ready to use function: GetSelectedIndices().
With the help of a very simple extensions we can add two additional functions, GetSelectedItems() and GetSelectedValues():

using System.Collections.Generic;
using System.Linq;
using System.Web.UI.WebControls;

namespace TypeExtensions
{
    // ReSharper disable InconsistentNaming
    public static class System_Web_UI_ListControl
    // ReSharper restore InconsistentNaming
    {
        /// <summary>
        /// Returns ListControl selected items.
        /// </summary>
        public static List<ListItem> GetSelectedItems(this ListControl listControl)
        {
            var selectedItems = listControl.Items
                .OfType<ListItem>()
                .Where(listItem => listItem.Selected)
                .ToList();
            return selectedItems;
        }

        /// <summary>
        /// Returns ListControl selected values.
        /// </summary>
        public static List<string> GetSelectedValues(this ListControl listControl)
        {
            var selectedItems = listControl.GetSelectedItems();
            var selectedValues = selectedItems
                .Select(listItem => listItem.Value)
                .ToList();
            return selectedValues;
        }
    }
}

Simply add Namespace of this class to your namespace usages and you will become two additional functions:


These extension functions are working for all ListControl-based classes.

MSSQL - Query Templates - usage of system tables

Sometimes you have to find all the tables (their names), which have the field with the specified name.
The easiest way to do this is through queries to the system tables.
Because this task occurs frequently, I have created T-SQL template:

-- Find Tables by Column-Name
USE <DataBaseName, sysname, tempdb>;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('<schema_name, sysname, dbo>')
    AND
    tables.name  LIKE '<TableNamePattern, sysname, %>'
    AND
    columns.name LIKE '<ColumnNamePattern, sysname, %_ID>'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];

Save this template to your templates folder with for example following name:
"SysTables - Sch-Tb-Clm". After replacing of placeholders you will see something like this:

-- Find Dimension-Tables with Code-Columns
USE AdventureWorksDW2008R2;

SELECT
    [SCHEMA] = schemas.name,
    [TABLE]  = tables.name,
    [COLUMN] = columns.name
FROM
    sys.schemas
    INNER JOIN sys.tables ON
        schemas.schema_id = tables.schema_id
    INNER JOIN sys.columns ON
        tables.object_id  = columns.object_id
WHERE
    schemas.name IN ('dbo')
    AND
    tables.name  LIKE 'Dim%'
    AND
    columns.name LIKE '%Code'
ORDER BY
    [SCHEMA],
    [TABLE],
    [COLUMN];

The result of execution will be something like this:

MSSQL - Query Templates

Often working with the database we have to write lot of typical queries.
In this case it is convenient to create templates of such requests.
SQL Server Management Studio (SSMS) gives us that opportunity.

First of all, make sure that you have activated the tab "Templates Explorer":
[Menu] - [View] - [Template Explorer] or <Control>+<Alt>+<T>:


SSMS will show us lot of ready-to-use templates:


For example in the folder "SQL Server Templates" - "Stored Procedure" template "Create Procedure Basic Template". Click on it with double click or click with right mouse button and select from the context menu item "Open". SMS will open a new T-SQL Scripting window with this text:


In this script, there are some expressions in the form of <Name, DataType, DefaultValue>.
You can fill/replace these placeholders with help of corresponding dialog.
Just call a [Menu]-[Query]-[Specify Values for Template Parameters...]:


or press the button on the toolbar:

In the dialog, you can set new values ​​for the placeholders or leave the default values:


After you click [OK], the placeholders will be replaced with the appropriate values​​:



You can extend templates directory with your custom templates. Just call a context menu for the appropriate folder:


Or copy the folder with your templates (for example @inovex) in this folder:
C:\Users\<Your User>\AppData\Roaming\Microsoft\Microsoft SQL Server\<MSSQL Version>\Tools\Shell\Templates\Sql\



In future articles, I will show examples of some useful templates.

MSSQL - Function to format detailed error message

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.fnGetDetailedErrorMessage'))
    DROP FUNCTION dbo.fnGetDetailedErrorMessage;

GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to format last error as detailed message

Usage: Log last error
    BEGIN TRY
        DECLARE @i INT;
        PRINT 'Try division by zero'
        SET @i = 1/0;
        PRINT 'After'
    END TRY
    BEGIN CATCH
        -- Display error
        PRINT dbo.fnGetDetailedErrorMessage();
        IF @@TRANCOUNT > 0 ROLLBACK;
    END CATCH;
=============================================*/
CREATE FUNCTION dbo.fnGetDetailedErrorMessage()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN ('ERROR:
        ErrorNumber        = ' + CAST(ERROR_NUMBER() AS VARCHAR(20)) + ',
        ErrorSeverity    = ' + CAST(ERROR_SEVERITY() AS VARCHAR(20)) + ',
        ErrorState        = ' + CAST(ERROR_STATE() AS VARCHAR(20)) + ',
        ErrorProcedure    = "' + ISNULL(ERROR_PROCEDURE(), '') + ',
        ErrorLine        = ' + CAST(ERROR_LINE() AS VARCHAR(20)) + ',
        ErrorMessage    = "' + ERROR_MESSAGE() + '"' + CHAR(13) + CHAR(10));
END;
GO

BEGIN TRY
    DECLARE @i INT;
    PRINT 'Try division by zero'
    SET @i = 1/0;
    PRINT 'After'
END TRY
BEGIN CATCH
    -- Display error
    PRINT dbo.fnGetDetailedErrorMessage();
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

Results of execution:

MSSQL - Convert list to table #2 (array of string items)

Often, when working with databases there is a need to pass a parameter with set of elements.
Function described here (TABLE-Value UDF) converts a string in the form "aaa;bbb;ccc" to tabular form.
Additionally could be removed empty items.
In case if list has non-unique items, the function returns the serial number for each items group (RANK).

-- Drop Function if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tf_array2table'))
    DROP FUNCTION dbo.tf_array2table;
GO
/*=============================================
Author:
    Yuri Abele
Changes:
    11.06.2013 - Yuri Abele - initial
Description:
    Function to convert list of Values to Table
 
Usage: Get all items
    SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
    FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);
Usage: Get all non-empty items
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);
Usage: Get all non-empty items and filter non-unique
    SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
    WHERE item_rank=1;
=============================================*/
CREATE FUNCTION dbo.tf_array2table
(
    @array NVARCHAR(MAX), -- List of delimited values
    @delim NCHAR(1),      -- Delimiter
    @remove_empty BIT     -- Flag to remove empty values
)
-- Container for Array Items
RETURNS @data TABLE(
    item_index INT IDENTITY(1,1), -- 1-based index of item
    item_value NVARCHAR(MAX),     -- item value
    item_rank  INT                -- item rank if items are not unique
)
AS BEGIN
    -- Container for XML
    DECLARE
        @xml_text NVARCHAR(MAX),
        @xml XML;
 
    IF @remove_empty = 1 BEGIN
        -- Remove empty inner items
        WHILE (CHARINDEX(@delim + @delim, @array, 0) > 0) BEGIN
            SET @array = REPLACE(@array, @delim + @delim, @delim);
        END;
        -- Remove empty left item
        IF(LEFT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 2, LEN(@array)-1)
        END;
        -- Remove empty right item
        IF(RIGHT(@array, 1) = @delim) BEGIN
            SET @array = SUBSTRING(@array, 1, LEN(@array)-1)
        END;
    END;
    
    -- Prepare XML-Text
    SET @xml_text = N'<L><I>' +
        REPLACE(@array, @delim, N'</I><I>') +
        N'</I></L>';
    
    -- Convert Array to XML
    SET @xml = CAST(@xml_text AS XML);
 
    DECLARE @temp_data TABLE(
        item_index INT IDENTITY(1,1),
        item_value NVARCHAR(MAX)
    )
    -- Extract Array Items to Table-Variable
    INSERT INTO @temp_data(item_value)
    SELECT
        item_value = item.value('.', 'NVARCHAR(MAX)')
    FROM
        @xml.nodes('//I') XMLDATA(item);
    
    -- Calculate Rank for each item (to find non-unique items)
    INSERT INTO @data(item_value, item_rank)
    SELECT
        item_value,
        item_rank=RANK() OVER(PARTITION BY item_value ORDER BY item_index, item_value)
    FROM
        @temp_data
    ORDER BY
        item_index;
    
    RETURN;
END;
GO

Usages:

-- Get all items
SELECT item_index, item_value = IIF(item_value = '', '---', item_value), item_rank
FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 0);

Result

item_index	item_value	item_rank
1	---	1
2	ddd	1
3	bbb	1
4	ccc	1
5	---	2
6	---	3
7	ccc	2
8	ddd	2
9	eee	1
10	---	4

-- Get all non-empty items
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1);

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
4	ccc	2
5	ddd	2
6	eee	1

-- Get all non-empty items and filter non-unique
SELECT * FROM dbo.tf_array2table(N';ddd;bbb;ccc;;;ccc;ddd;eee;', N';', 1)
WHERE item_rank=1;

Result

item_index	item_value	item_rank
1	ddd	1
2	bbb	1
3	ccc	1
6	eee	1