Thứ Tư, 28 tháng 10, 2015

Convert DataTable to CSV string

public static class DataTableExtension
{
    public static string ToCSV(this DataTable dt)
    {
        StringBuilder sb = new StringBuilder();

        for (int i = 0; i <= dt.Columns.Count - 1; i++)
        {
            sb.Append(dt.Columns[i].ColumnName);

            if (i < dt.Columns.Count - 1)
            {
                sb.Append(",");
            }
        }

        sb.AppendLine();

        for (int i = 0; i <= dt.Rows.Count - 1; i++)
        {
            for (int j = 0; j <= dt.Columns.Count - 1; j++)
            {
                object o = dt.Rows[i][j];
                if (o != null)
                {
                    string value = o.ToString();

                    if (value.Contains(","))
                    {
                        value = string.Concat("\"", value, "\"");
                    }

                    if (value.Contains("\r"))
                    {
                        value = value.Replace("\r", " ");
                    }
                    if (value.Contains("\n"))
                    {
                        value = value.Replace("\n", " ");
                    }

                    sb.Append(value);
                }

                if (j < dt.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.AppendLine();
        }

        return sb.ToString();
    }
}

Thứ Năm, 22 tháng 10, 2015

InitCap function for SQL Server

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END

GO

Reference: http://www.sql-server-helper.com/faq/oracle-functions-p01.aspx

Thứ Tư, 21 tháng 10, 2015

sp_refreshview

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''' 
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id 
WHERE so.type = 'V';

Reference: https://msdn.microsoft.com/en-us/library/ms187821.aspx