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ứ Tư, 28 tháng 10, 2015
Convert DataTable to CSV string
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
Đăng ký:
Bài đăng
(
Atom
)