Thursday, December 20, 2012

Monitor a Task time out

There are some ways to wait for a task timeout. This is the simplest:
using System.Threading.Tasks;


    Task executeTask = STask.Factory.StartNew(() => DoSomething());

    //Wait for 5 minutes
    int index = System.Threading.Tasks.Task.WaitAny(new[] { executeTask },

    if (index == -1)
        //Task execution Time Out
        //Task executed in-time
catch (AggregateException aex)
    foreach (Exception ex in aex.InnerExceptions)
        LogError(ex, ex.Message);
catch (Exception ex)
    LogError(ex, "Error");

Sunday, December 2, 2012 menu from Web.sitemap with separators

If you use the Web.sitemap file to automatically generate menu items, you immediatly notice all the items are "grouped together" and is not possible to put a separator. But if you use the code below, it's possible.

In the Web.sitemap file (for each node you want have a separator before):

Into code:

Protected Sub Menu1_MenuItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.MenuEventArgs) Handles Menu1.MenuItemDataBound
    If e.Item.Text.IndexOf("[sep]") > 0 Then
        e.Item.Text = e.Item.Text.Replace("[sep]", "")
        e.Item.SeparatorImageUrl = Page.ResolveUrl("~/Common/images/dividerhoriz.gif")
    End If
        If Menu1.SelectedItem Is Nothing Then
            If IsNodeAncestor(CType(e.Item.DataItem, SiteMapNode), System.Web.SiteMap.CurrentNode) Then
                If e.Item.Selectable Then
                    e.Item.Selected = True
                End If
            End If
        End If
    Catch ex As Exception
 'Exception management
    End Try
End Sub
''' Determines if a  is the ancestor of a second one.
''' The  to analyze.
''' A  which may or may not be 
''' the 's child.
''' true, if the two nodes are related, false otherwise.
Private Function IsNodeAncestor(ByVal ancestor As SiteMapNode, ByVal child As SiteMapNode) As Boolean
    Dim result As Boolean = False
        If Not ancestor.ChildNodes Is Nothing AndAlso ancestor.ChildNodes.Contains(child) Then
            Return True
            If (Not child.ParentNode Is Nothing) AndAlso (Not ancestor Is child.RootNode) Then
                Return IsNodeAncestor(ancestor, child.ParentNode)
            End If
        End If
    Catch ex As Exception
 'Exception management
        Return False
    End Try

    Return result
End Function
Protected Sub SiteMapPath1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SiteMapNodeItemEventArgs) Handles SiteMapPath1.ItemDataBound
    If Not e.Item.SiteMapNode Is Nothing Then
        If e.Item.SiteMapNode.Title.IndexOf("[sep]") >= 0 Then
                e.Item.SiteMapNode.Title = e.Item.SiteMapNode.Title.Replace("[sep]", "")
  'Exception management
            End Try
        End If
    End If
End Sub

Saturday, December 1, 2012

Media sources

I decided to put together a reference post for when you're looking for stock photos, clipart, audio, music, or video. I will update this post as I am made aware of new sources.

Icons, Illustrations, and Photos
The Noun Project


Icons 8

The XAML Project

SyncFusion Metro Studio

266 Icons

Windows 8 App Icons




Open Game Art



none yet

Wednesday, May 23, 2012

Convert a MyClass array into DataTable

Using Relfection and Generics is possible to convert (or transform) an array of classes into a DataTable:

Imports System.Reflection

Public Shared Function convertFromClassToDatatable(Of T)(ByVal tableName As String, ByVal datas As T(), ByVal withHeader As Boolean) As DataTable

    Dim dt As New DataTable
    dt.TableName = tableName

    For Each prop As PropertyInfo In GetType(T).GetProperties

    If withHeader Then
        Dim drHeader As DataRow = dt.NewRow
        For Each col As DataColumn In dt.Columns
            drHeader.Item(col.ColumnName) = col.ColumnName.ToUpper
    End If

    For Each data As T In datas 
        Dim dr As DataRow = dt.NewRow

        For Each prop As PropertyInfo In GetType(T).GetProperties
            dr.Item(dt.Columns.IndexOf(prop.Name)) = prop.GetValue(data, Nothing)


    Return dt
End Function


Dim myArray As myClass()

convertFromClassToDatatable("Test Table"),  myArray, False)

Siccome vengono utilizzati i Generics, in automatico il sistema sa che classe è "dati", che viene passata alla funzione. 
La DataTable risultante avrà TableName = tableName e i vari ColumnName saranno i nomi delle proprietà della classe "miaClasse"

Monday, April 16, 2012

SqlServer Title Case

Some time ago, I've published a post (HERE) where it was written hot to capitalize strings in SQL Server.

Now I want to show how to capitalize every word in a string.

This topic is more complex, it's necessary to create a User Defined Function that does the work for us:

CREATE FUNCTION dbo.CapitalizeEveryWord(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)

It's done! Now just invoke that function:

SELECT dbo.CapitalizeEveryWord (Lower(ColumName)) FROM TableName

Thursday, January 26, 2012

Export from SQL Server to Excel

Is it possible to export a database table or a SQL query output directly in Excel? Maybe using a stored procedure or with a SQL command? The answer is: YES!

To start, we have to create a Stored Procedure wich does the work. This SP reads table columns and data and creates an excel file with all data and also with columns.

CREATE PROCEDURE proc_generate_excel_with_columns
@db_name varchar (100),
@table_name varchar (100),
@file_name varchar (100)

--Generate column names as a recordset
DECLARE @columns varchar(8000), @sql varchar (8000), @data_file varchar (100)
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
SELECT @columns=''''''+replace( replace (@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
SELECT @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
SET @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''

--Generate data in the dummy file
SET @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T'''

--Copy dummy file to passed EXCEL file
SET @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

--Delete dummy file 
SET @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

Then we have to invoke the SP passing it the Database name, the table* name and the excel file path (It isn't necessary to create it in advance).

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

In most systems, some system commands used in the SP are disabled by default. To enable them, use following commands instead of the one before:

EXEC master.dbo.sp_configure 'show advanced options', 1


EXEC master.dbo.sp_configure 'xp_cmdshell', 1


EXEC proc_generate_excel_with_columns 'DB_NAME''TABLE_NAME','FILE_PATH'

EXEC master.dbo.sp_configure 'xp_cmdshell', 0


*TIP: We can use also a View or a Temporary Table instead of "TABLE_NAME"