September 29, 2012

Collation in SQL

CREATE Table ColTable( Col1 varchar(15) COLLATE Latin1_General_CI_AS, Col2 varchar(15) COLLATE Latin1_General_CS_AS);

Case sensitive/insensitive order of data in the column

September 27, 2012

C# Read data from Excel File or Text File

====> Read data from Excel file
RadAsyncronousUpload
 Count
 Directory.Exists(path) Directory.CreateDirectory(path);
 UploadedFiles[index].GetName();
 UploadedFiles[index].SaveAs(path, true);
 file.EndsWith(".xlsx")
 var conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';Extended Properties=Excel 12.0;");
                        OleDbDataAdapter da = new OleDbDataAdapter("SELECT FileType FROM [Sheet1$]", conn);

 try{} (OleDbException)

====> Read data from text file
 StreamReader readFile
  ReadLine()
 [UNICODE_SUBSTITUTE_CHARACTER = '\x1A'] ??

September 21, 2012

Dynamic SQL to get any table by variable name with any columns - Dynamic SSRS report

DECLARE @colName VARCHAR(MAX)

 SELECT @colName = COALESCE(@colName +',','') + col.name from sys.columns AS col
 INNER JOIN sys.tables AS tbl ON tbl.object_id = col.object_id
 WHERE tbl.name = @tblName AND
 ((col.name NOT LIKE '%id%' AND col.name NOT Like '%ID%') AND 
 (col.name != 'CreatedByUserName' AND col.name != 'CreatedDate' AND col.name != 'LastUpdatedByUserName' AND col.name != 'LastUpdatedDate'))
 AND col.system_type_id NOT IN (56,61)
 AND col.column_id < 8
 EXEC ('SELECT '+ @colName +' FROM ' + @tblName)