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
 Directory.Exists(path) Directory.CreateDirectory(path);
 UploadedFiles[index].SaveAs(path, true);
 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

September 21, 2012

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


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